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

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

Get Java Version for Oracle Stored Procedures

If you have to write stored procedures for oracle is important
to notice which Java version is supported by your Oracle Database,

A common technique is create a JAVA stored procedure for that:

1. Create a function with an ORACLE SQL statement like:

CREATE OR REPLACE FUNCTION getJavaProperty(myprop IN VARCHAR2)
RETURN VARCHAR2 IS LANGUAGE JAVA
name ‘java.lang.System.getProperty(java.lang.String) return java.lang.String’;

2. Once you created the function you can use it to get the version:

SELECT getJavaProperty(‘java.version’) from dual;

You can see in the attached version that for my Oracle Database 10.1.0.4.2 the Java version is 1.4.2_04 :)