• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Piet Souris
  • Carey Brown
  • Stephan van Hulst
Bartenders:
  • Frits Walraven
  • fred rosenberger
  • salvin francis

Run dynamic sql in stored procedure problem

 
Ranch Hand
Posts: 320
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
------------------------------------
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
JulianInactive KennedyInactive
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic