Oracle Procedures and Functions

List All Functions and Procedures

SELECT DISTINCT name, type
FROM user_source

Show Contents of a Function or Procedure

SET ARRAYSIZE 1
SET MAXDATA 4096
SELECT text
FROM user_source
WHERE UPPER(name) LIKE '%EMPLOYEE%';

Create a Procedure

CREATE OR REPLACE PROCEDURE sampleprocedure
(infield1 IN varchar2,
infield2 IN integer)
AS
BEGIN
DELETE FROM testtable
WHERE field1 = infield1;
INSERT INTO testtable
(field1, field2) VALUES
(infield1, infield2);
END sampleprocedure;
/
run;

Check for Procedure Errors

SELECT line, position, text FROM USER_Errors
WHERE name='SAMPLEPROCEDURE'
ORDER BY sequence, line, position;

Execute a Procedure

EXECUTE sampleprocedure('abc',9);
exit;

History Comments