• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

Run dynamic sql in stored procedure problem  RSS feed

 
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.
 
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
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!