Wednesday, April 08, 2009

Using JDBC to Execute Stored Procedures with Output Parameters

PeopleSoft allows developers to execute database stored procedures using PeopleCode that resembles SQLExec("EXEC PACKAGE.PROC_NAME(:1, :2)", &bind1, &bind2);. Even though stored procedures can have input and output parameters, SQLExec discards output parameters. Several years ago I found an interesting post that described how to use DBMS_PIPE with Oracle database to return output parameters, but, it appears the author removed that post. What made the DBMS_PIPE solution so compelling was that it shared the PeopleSoft database connection. The alternative presented below, unfortunately, requires you to maintain a user name and password, preferably encrypted and stored in a secure location. Because of the class loading issues mentioned in my post Using Oracle JDBC from PeopleCode, this post uses the Oracle specific data access classes. If you use a different database, the classes will differ, but the concept is the same. Furthermore, if you use a different database and JDBC driver, you may be able to use the standard, generic JDBC classes as described in the PSST0101 post Writing to Access Databases

Before demonstrating how to call a stored procedure from PeopleCode, we need a stored procedure to call. The following PL/SQL describes a stored procedure that has two parameters: one in and one in/out. The implementation of the procedure hard codes the output value for simplicity.

CREATE OR REPLACE PACKAGE JJM_IN_OUT AS

PROCEDURE P(
IN1 IN VARCHAR2,
INOUT1 IN OUT VARCHAR2);
END JJM_IN_OUT;
/

CREATE OR REPLACE PACKAGE BODY JJM_IN_OUT AS
PROCEDURE P(
IN1 IN VARCHAR2,
INOUT1 IN OUT VARCHAR2) IS
BEGIN
INOUT1 := 'Hello World';
END P;
END JJM_IN_OUT;
/
show errors

To build this package, copy the PL/SQL above into a text editor and then run it from SQLPlus. The procedure test follows:

SQL> var out1 varchar2(100)
SQL> exec JJM_IN_OUT.P('x', :out1);

PL/SQL procedure successfully completed.

SQL> print out1

OUT1
-------------------------------------------------------
Hello World

SQL>

The following IScript demonstrates calling a procedure with in/out parameters from PeopleCode:

Function IScript_OutParms()
Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");;
Local JavaObject &info = CreateJavaObject("java.util.Properties");

&info.put("user", "dbuser");
&info.put("password", "secret");

Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:@server:1521:SID", &info);
Local JavaObject &stmt = &conn.prepareCall("{call JJM_IN_OUT.P (?,?)}");
Local JavaObject &types = GetJavaClass("java.sql.Types");

REM ** set input parameter values;
&stmt.setString(1, "aa");
&stmt.setString(2, "bb");

REM ** register the second parameter as a output parameter;
&stmt.registerOutParameter(2, &types.VARCHAR);

REM ** execute the SQL statement;
&stmt.execute();

%Response.SetContentType("text/plain");

REM ** Display the value of the second parameter;
%Response.WriteLine("JJM_IN_OUT.P output parameter value: " | &stmt.getString(2));

&conn.close();
End-Function;

After making the connection, the code prepares an SQL statement: {call JJM_IN_OUT.P (?,?)}. JDBC procedure calls use the syntax {call proc_name (?,?)} (the question marks represent the procedure's parameters).

The code above is for demonstration purposes only. Be sure to store the database user name and password in a secure manner. When executing SQL using a second connection, be sure to consider deadlock, race conditions, etc.

5 comments:

Unknown said...

This post got me thinking of how to communicate the other way with an iScript. How would you access an iScript from javascript, instead of the other way around? I imagine you would be able to pass the iScript URL as a function, but I haven't worked out how just yet. Have you come across a way to do so?

Jim Marion said...

@Paul, can you elaborate? I'm not sure I follow your line of thought. You asked, "How would you access an iScript from javascript, instead of the other way around?" If I follow the logic in your question correctly, the "other way around" would be accessing JavaScript from an IScript? You can execute JavaScript from an IScript using the Apache Bean Scripting framework (I blogged about this in my post Scripting PeopleSoft), but I don't think that is what you are asking.

If your question really is, "How would you access an iScript from JavaScript," then the answer is Ajax. I use jQuery to make Ajax calls. jQuery dramatically simplifies Ajax.

Basic steps for Ajax:

1. Create an IScript data source

2. Create a Derived/Work record with the field HTMLAREA

3. Add an HTML Area to a page

4. Bind the page's HTML Area to the Derived/Work HTMLAREA field

5. Use RowInit of the level containing the HTML Area to set the value of the Derived Work record's HTMLAREA field

I generally use an HTML definition to store the HTML, JavaScript, etc that is required to call the IScript. I place an HTML Bind, %Bind(:n), in the place where the IScript URL should be called from JavaScript and use GenerateScriptContentURL to obtain a reference to the IScript's URL.

Unknown said...

Jim, You are correct - my question was "how would you access an iScript from JavaScript".

The missing piece, for me, was the vehicle for connecting to the iScript functionality. One of the Ajax posts you pointed me to sent me in the right direction, thankfully.

I ended up using something similar to the following, where the bind value is the URL of the iScript.
$.ajax({
type: "POST",
url: "%BIND(:3)",
data: "%BIND(:4)",
success: function(){alert( "success");
}
});

Without using ajax, I was stuck at redirecting the location to the iScript URL, to execute my code, and then redirecting the location back to the original page. It worked, but it was ugly.

thanks for the ajax pointer!

Jim Marion said...

@Paul, you are exactly right. That is how I do Ajax (IScripts/JavaScript). If you are just loading HTML into a DIV, then you might want to take a look at $("#div_id").get("IScript URL"). Thank you for sharing your code! I've been meaning to write an Ajax/PeopleSoft tutorial, I just haven't gotten around to it yet.

Unknown said...

Hi Jim,

Thank you very much for the post, we have same scenario where we would like to call procedure using Java in Peoplecode but it is not working.
My requirment is to drop a DB Link using procedure.


So created below procedure in Database:

create or replace PROCEDURE dropdblink(dlink in varchar2) AS
BEGIN
EXECUTE IMMEDIATE 'drop database link '||dlink;
END dropdblink;

Executed procedure in SQLDeveloper and it worked.

EXEC dropdblink('DBL_ITSHRDEV');
--Result: anonymous block completed

So tried calling this procedure using java in Peoplecode but its giving error as showed below.

********Code Start*****

Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");
Local JavaObject &info = CreateJavaObject("java.util.Properties");

&info.put("user", "*****");
&info.put("password", "*****");

Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:Host:port:server", &info);
Local JavaObject &stmt = &conn.createStatement();

Local JavaObject &proc = &conn.prepareCall("{call dropdblink(?)}");
Local JavaObject &types = GetJavaClass("java.sql.Types");
&proc.setstring(1, "");
&proc.execute();
rem &proc.executequery();

********Code End*****
Error: Java method executequery not found for class java.sql.CallableStatement. (2,760)

I tried different combinations but nothing is working. So please suggest.

Thanks,
MN