Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Run dynamic sql in stored procedure problem

 
Rajan Chinna
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 320
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmmm, OK so maybe a tiny bit more thought was required. Try this:

Hope that helps.

Jules
 
Rajan Chinna
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic