cancel
Showing results for 
Search instead for 
Did you mean: 

Executing Oracle Stored Procedure from EP6

0 Kudos

Has anyone had any experience executing Oracle Stored Procedures using JDBC from the EP6 Portal?

I am able to establish a connection to my database, and perform queries and display results OK.

I need to execute a stored proc which takes some input parameters, and returns a record set of results within a refcursor. I can create an iView within the Portal using the wizard, and execute the proc and get my results, but I'm trying to do this from my Java code.

I have tried implementing the example found on the SDN for executing an existing function, but with no luck.

Any help would be greatly appreciated.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Doug,

What do you have for Connection URL and Driver Class Name when creating an iView within the Portal using the wizard?

Thanks in advance

Former Member
0 Kudos

Hi Doug,

It should be fairly simple to execute store procedures from Portal. Using DBPool get a connection object to the database. Once you have the connection object you can execute a procedure that returns a cursor. It is like executing a procedure from any other java code. Don't think that portal makes a difference here.

Lakshmi

0 Kudos

Lakshmi,

Thanks for you r quick response. I tried duplicating the way I was calling this from straight Java code, and the only part I was having trouble with was having the Portal resolve the OracleTypes. Example code below:

//Register the driver

Class.forName("oracle.jdbc.driver.OracleDriver");

// Connect to the remote database

Connection conn =

DriverManager.getConnection ("jdbc:oracle:thin:@<host>:<port>:<SID>", "user", "passwd");

CallableStatement call =

conn.prepareCall("{call proc_name(?,?,?)}");

call.registerOutParameter(++iIndex, OracleTypes.CURSOR);

call.registerOutParameter(++iIndex, Types.INTEGER);

call.setString(iIndex, "");

call.registerOutParameter(++iIndex, Types.VARCHAR);

call.setString(iIndex, "");

call.execute();

In my old code, I imported oracle.jdbc.OracleTypes, but the Portal can not resolve that.

Thanks,

Doug

Former Member
0 Kudos

Hi Doug,

Seems like the jar file is not there in Portal's classpath. You have two options to resolve this. Add the Jar file to the portal classpath. You can do this from the console. The second way to do this would be to create a par file with just the jar file (that contains Oracle Type). Upload the par file into the portal. In Service Ref of the portal component that actually connects to the database add the par file.

Hope this helps.

Lakshmi