How to call an stored procedure defined in an Oracle Package?

21. September 2011 08:55 by Mrojas in General  //  Tags: , , , , ,   //   Comments (0)

Today I had an stored procedure defined in a package and I didn’t know how to call it.
And my stored procedure returned a cursor and I needed to see the results.

So after some tests this is the way to perform this. You can use code like the following:

variable r refcursor -- use this if you have an out parameter that is an out cursor

DECLARE 

  -- Declare a variable for each of your stored procedure arguments

  VAR_IN_1 VARCHAR2(32767);

  VAR_IN_2 VARCHAR2(32767);

  VAR_IN_3 VARCHAR2(32767);

  INFO sys_refcursor;

BEGIN 

  -- Init the variables with the parameter you want to use to test the stored procedure

  VAR_IN_1 := 'Param1';

  VAR_IN_2 := 'Param2';

  VAR_IN_3 := 'Param3';



-- Call the stored procedure. You should write something like schema.package.storedprocedure( param1, param2, ..., paramN);

MYSCHEMA.PKG_TEST.TEST_STORED_PROC ( VAR_IN_1, VAR_IN_2, VAR_IN_3, INFO );

-- If one of the parameters was an out cursor assign it to the r variable to be able to see the results

  :r := PC_INFO;

  COMMIT; 

END; 

-- Execute this code in SQL PLUS

And after executing it just call in the SQL PLUS prompt

PRINT r to see cursor results