This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Run dynamic sql in stored procedure problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Run dynamic sql in stored procedure problem" Watch "Run dynamic sql in stored procedure problem" New topic
Author

Run dynamic sql in stored procedure problem

Rajan Chinna
Ranch Hand

Joined: Jul 01, 2004
Posts: 320
Hi
Can somebody tell me whats wrong to my stored procedure (Oracle 8i)?

I want to run a dynamic SQL select stmt in my SP which returns set of records as output parameter to my java program. I am getting the following error:
8/1 PL/SQL: Statement ignored
13/6 PLS-00597: expression 'RS' in the INTO list is of wrong
type

------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE pkg_test
IS
cursor c1 IS SELECT col1,col2,col3 FROM table;
TYPE recordset IS REF CURSOR RETURN c1%rowtype;

PROCEDURE sp_select(rs IN OUT recordset, a IN varchar2);
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE sp_select(rs IN OUT recordset, para IN varchar2)
IS
BEGIN

EXECUTE IMMEDIATE
'SELECT col1,col2,col3 FROM table WHERE col5=:1'
INTO rs USING para;

END sp_select;
END pkg_test;
/
------------------------------------------------------------------------
Please let me know is there any other better way to achieve this result.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
I can tell you what's wrong, but not how to fix it just now. Your variable rs must be of "c1%rowtype", not "ref cursor return c1%rowtype". If you don't get any joy elsewhere I'll take another look when I have more time.

Jules
Rajan Chinna
Ranch Hand

Joined: Jul 01, 2004
Posts: 320
Hai Jules

Its still not working when i use c1%rowtype i get following compile error
------------------------------------
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/3 PL/SQL: Statement ignored
11/8 PLS-00597: expression 'RS' in the INTO list is of wrong
type
------------------------------------
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hmmm, OK so maybe a tiny bit more thought was required. Try this:

Hope that helps.

Jules
Rajan Chinna
Ranch Hand

Joined: Jul 01, 2004
Posts: 320
Hi Jules

This solve my SP compile problem. Now i get the following error when getting the resultset from my java program.
---------------------------------------error
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_SELECTWFMRM2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
---------------------------------------mycode
cstmt = conn.prepareCall("{call pkg_test.sp_selectWFMRM2(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(1);
-------------------------------
Am i using wrong type OracleTypes.CURSOR?
Thanks for your help.
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
OK, not rocket science this one. I don't know about the Oracle cursor type but that's not the problem here.

PLS-00306: wrong number or types of arguments in call to 'SP_SELECTWFMRM2'

cstmt = conn.prepareCall("{call pkg_test.sp_selectWFMRM2(?)}");

You're only specifying one argument. There are two.

Jules
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Run dynamic sql in stored procedure problem
 
Similar Threads
Handling cursor declared in oracle in java program
Need Expert Advice
Simple sql
Returning Cursor from Stored Procedure
registerOutParameter Problem when the type is ARRAY