ORA-14551: cannot perform a DML operation inside a query : ORA Error « System Packages « Oracle PL / SQL

1. Oracle PL / SQL
2. System Packages
3. ORA Error

ORA-14551: cannot perform a DML operation inside a query

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

Table created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE emp (
  2     id         NUMBER PRIMARY KEY,
  3     fname VARCHAR2(50),
  4     lname  VARCHAR2(50)
  5   );

Table created.

SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, 'A', 'B');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, 'C', 'D');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, 'Enn', 'F');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, 'G', 'H');

1 row created.

SQL>
SQL>
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, 'G', 'Z');

1 row created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> COLUMN char_col format a60
SQL> SELECT * FROM myTable ORDER BY num_col;

no rows selected

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION FullName (p_empID  emp.ID%TYPE)
  2     RETURN VARCHAR2 IS
  3     v_Result  VARCHAR2(100);
  4   BEGIN
  5     SELECT fname || ' ' || lname INTO v_Result FROM emp WHERE ID = p_empID;
  6
  7     INSERT INTO myTable (num_col, char_col)VALUES (p_empID, 'called by FullName!');
  8
  9     RETURN v_Result;
 10   END FullName;
 11   /

Function created.

SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> -- The same query will now raise an error.
SQL> SELECT FullName(ID) full_name
  2     FROM emp
  3     ORDER BY full_name;
SELECT FullName(ID) full_name
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "JAVA2S.FULLNAME", line 7


SQL>
SQL>
SQL>
SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL>
SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>
SQL>

   
    
  

Related examples in the same category