Re: EXEC syntax for SP which returns row set

m0002a_at_yahoo.com wrote:
> I have a package and stored procedure as follows:
>
> create or replace package TEST.ReturnCur is
> -- REF CURSOR type for returning result sets from procedures
> TYPE REF_CUR IS REF CURSOR;
> END ReturnCur;
>
>
> CREATE or replace PROCEDURE TEST.SPTEST
> (C1 OUT ReturnCur.REF_CUR)
> AS
> BEGIN
> open C1 for SELECT ID from TEST.EMPLOYEE;
> END;
>
>
> How do I test this procedure from SQL*Plus.
> When I use: exec sec.test2
>
> I get the following error message:
>
> BEGIN sec.test2; END;
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEST2'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> When I use: exec sec.test2()
>
> I get the following error message:
>
> BEGIN sec.test2(); END;
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEST2'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
>
> When I use [ID is char(8)]: exec sec.test2('12345678')
>
> I get:
>
> BEGIN sec.test2('12345678'); END;
> *
> ERROR at line 1:
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEST2'
> ORA-06550: line 1, column 17:
> PLS-00363: expression '12345678' cannot be used as an assignment target
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored

My first question is what leads you to believe a text string is suitable as a return variable? My second question then becomes can't you read an error message? Your answer is staring you in the face:

PLS-00363: expression '12345678' cannot be used as an assignment target

You are RETURNING a REF CURSOR. Go back to square one and start over, or have someone who knows what they're doing do this for you. Obviously you're not capable of completing the task yourself.

David Fitzjarrell Received on Wed Jul 13 2005 - 22:02:17 CDT