Forums Register Login

Run dynamic sql in stored procedure problem

+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
------------------------------------
+Pie Number of slices to send: Send
Hmmm, OK so maybe a tiny bit more thought was required. Try this:

Hope that helps.

Jules
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
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
I have a knack for fixing things like this ... um ... sorry ... here is a consilitory tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 3285 times.
Similar Threads
Simple sql
Need Expert Advice
Handling cursor declared in oracle in java program
registerOutParameter Problem when the type is ARRAY
Returning Cursor from Stored Procedure
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 19:06:47.