|
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>
|