Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

Query user_source for procedure declaration

SQL> SQL> CREATE OR REPLACE PROCEDURE compile_error AS   2       myDateTime timestamp;   3  BEGIN   4     SELECT systimestamp INTO myDateTime FROM dual;   5     DBMS_OUTPUT.PUT_LINE(myDateTime);   6  EXCEPTION   7     WHEN OTHERS   8     THEN   9        DBMS_OUTPUT.PUT_LINE(SQLERRM);  10  END;  11  / Procedure created. SQL> SQL> SELECT line||' '||text PROCEDURE FROM user_source WHERE name = 'COMPILEERROR'; PROCEDURE -------------------------------------------------------------------------------- 1 PROCEDURE CompileError 2    AS 3      v_timestamp timestamp; 4 BEGIN 5    SELECT systimestamp 6    INTO v_timestamp 7    FROM dual; 8 9    DBMS_OUTPUT.PUT_LINE(v_timestamp); 10 EXCEPTION 11    WHEN OTHERS 12    THEN 13       DBMS_OUTPUT.PUT_LINE(SQLERRM); 14 END; 14 rows selected. SQL>