This function cannot be called from a SQL statement: cannot perform a DML operation inside a query

 
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION InsertTemp(
  2    p_Num IN MyTable.num_col%TYPE,
  3    p_Char IN MyTable.char_col%type)
  4    RETURN NUMBER AS
  5  BEGIN
  6    INSERT INTO MyTable (num_col, char_col)
  7      VALUES (p_Num, p_Char);
  8    RETURN 0;
  9  END InsertTemp;
 10  /

Function created.

SQL>
SQL> REM Illegal query
SQL> SELECT InsertTemp(1, 'Hello')
  2    FROM dual;
SELECT InsertTemp(1, 'Hello')
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "JAVA2S.INSERTTEMP", line 6


SQL>
SQL>
SQL> drop table MyTable;

Table dropped.

SQL>

Related examples in the same category

1.	Call function and store the return value to a variable
2.	Call function in dbms_output.put_line
3.	Use a user-defined function in stored procedure
4.	Local Subprograms
5.	Calling a Function
6.	This function can be called from a SQL statement.
7.	Function with insert statement can be called from a DML statement.

This function cannot be called from a SQL statement: cannot perform a DML operation inside a query : Function Call « Stored Procedure Function « Oracle PL / SQL

Related examples in the same category