Stored Procedure Error - Page 2

JCScoobyRS · #16 (**permalink**) 07-22-03, 12:04

I guess I don't know what I'm doing.

All I need to do is have a dynamic query to allow for a user's input of sdate and edate. How would you suggest doing this? It appears that a stored procedure isn't the way to go or I'm just doing it all wrong. Thanks, Jeremy

JCScoobyRS · #17 (**permalink**) 07-22-03, 16:38

Anyone got an answer? How can I run a query and still pass variables to the query on the Oracle side? I was told I could do it BUT the guy that told me works on MS SQLServer and it's a bit different. Anyone???

The_Duck · #18 (**permalink**) 07-22-03, 17:56

Lookup 'RETSET', that is what you need to store multiple lines.

If you just want the data to come out of the proc, then you are already set.

this worked for me just fine:

Code:

17:46:46 kod:platform> CREATE OR REPLACE PROCEDURE SP_GET_CUST (
17:47:42   2   iCUST_ID      IN VARCHAR2
17:47:42   3  -- oLNAME_TXT      OUT VARCHAR2,
17:47:42   4  -- oFNAME_TXT      OUT VARCHAR2
17:47:42   5  -- pRETSET      IN OUT CV_2A_TYPES.CV_2A_ASSOC_SA,
17:47:42   6  )
17:47:42   7  AS
17:47:42   8   vLNAME_TXT      VARCHAR2(10);
17:47:42   9   vFNAME_TXT      VARCHAR2(10);
17:47:42  10  
17:47:42  11  
17:47:42  12  BEGIN
17:47:42  13  
17:47:42  14  --OPEN pRETSET FOR
17:47:42  15    SELECT
17:47:42  16     LNAME_TXT,
17:47:42  17     FNAME_TXT
17:47:42  18    INTO
17:47:42  19     vLNAME_TXT,
17:47:42  20     vFNAME_TXT
17:47:42  21    FROM
17:47:42  22     customer
17:47:42  23    WHERE
17:47:42  24     cust_id  = iCUST_ID;
17:47:42  25  
17:47:42  26  END SP_GET_CUST;
17:47:42  27  /

Procedure created.

Elapsed: 00:00:00.01
17:47:42 kod:platform> variable P1 varchar2(20);
17:47:47 kod:platform> execute :P1:='10001'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:47:53 kod:platform> execute SP_GET_CUST(:P1)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:47:59 kod:platform>

JCScoobyRS · #19 (**permalink**) 07-22-03, 18:04

I tried this:

VARIABLE sdate VARCHAR2(20);
VARIABLE edate VARCHAR2(20);
EXEC :sdate :='01-JUL-2003';
EXEC :edate :='15-JUL-2003';
EXEC attendance(:sdate, :edate);

And got this:

BEGIN attendance(:sdate, :edate); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ATTENDANCE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am using the same stored procedure as before BUT I did change DATE to VARCHAR2. Got any ideas? Thanks, Jeremy

The_Duck · #20 (**permalink**) 07-22-03, 18:06

Bah!
I am in a good mood today I guess.
I'll write the whole thing for you.

You need to send in the two dates and you also need variables that will be coming out to be declared in the execute statement.

Look below:

Code:

17:55:55 kod:platform> CREATE OR REPLACE PROCEDURE SP_GET_CUST (
17:56:37   2   iCUST_ID      IN VARCHAR2,
17:56:37   3   vLNAME_TXT      OUT VARCHAR2,
17:56:37   4   vFNAME_TXT      OUT VARCHAR2
17:56:37   5  )
17:56:37   6  AS
17:56:37   7  
17:56:37   8  BEGIN
17:56:37   9  
17:56:37  10    SELECT
17:56:37  11     LNAME_TXT,
17:56:37  12     FNAME_TXT
17:56:37  13    INTO
17:56:37  14     vLNAME_TXT,
17:56:37  15     vFNAME_TXT
17:56:37  16    FROM
17:56:37  17     customer
17:56:37  18    WHERE
17:56:37  19     cust_id  = iCUST_ID;
17:56:37  20  
17:56:37  21  END SP_GET_CUST;
17:56:37  22  /

Procedure created.

Elapsed: 00:00:00.01
17:56:37 kod:platform> variable P1 varchar2(20);
17:56:46 kod:platform> variable P2 varchar2(20);
17:56:46 kod:platform> variable P3 varchar2(20);
17:56:46 kod:platform> 
17:56:46 kod:platform> execute :P1:='10001'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:56:46 kod:platform> execute SP_GET_CUST(:P1,:P2,:P3)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
17:56:46 kod:platform> 
17:56:46 kod:platform> select :P2, :P3 from dual;

:P2                              :P3
-------------------------------- --------------------------------
A                                B

1 row selected.

Elapsed: 00:00:00.00
17:57:03 kod:platform>

that should be enough of an example for you to write anything you want.
If your select statement is getting back more than one row then you need to create a RETSET.

JCScoobyRS · #21 (**permalink**) 07-22-03, 18:08

I will be returning many rows so a RETSET will be necessary. I'll give it a whirl and come back later. Sorry I'm being such a pain but I haven't learned this yet and I have only written SP that do something like email and such. Thanks for your patience, Jeremy

The_Duck · #22 (**permalink**) 07-22-03, 18:08

Quote:

Originally posted by JCScoobyRS
I tried this:

VARIABLE sdate VARCHAR2(20);
VARIABLE edate VARCHAR2(20);
EXEC :sdate :='01-JUL-2003';
EXEC :edate :='15-JUL-2003';
EXEC attendance(:sdate, :edate);

And got this:

BEGIN attendance(:sdate, :edate); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ATTENDANCE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I am using the same stored procedure as before BUT I did change DATE to VARCHAR2. Got any ideas? Thanks, Jeremy

You got the error because Oracle is telling you to provide variables in the execute statement for the output. See my example above where I expect TWO fields as output so I provide Oracle a place to put them.

The_Duck · #23 (**permalink**) 07-22-03, 18:09

Lookup what a RETSET is and how to use it here:

http://tahiti.oracle.com/pls/db92/db92.homepage

Internet Services

Web Development

Digital Marketing

Consumer Tech