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

Get Java Version for Oracle Stored Procedures

1. February 2010 06:17 by Mrojas in General  //  Tags: , , ,   //   Comments (0)

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

image