Home » Other » Client Tools » SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0, windows XP)
SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters [message #495829] Tue, 22 February 2011 10:31 Go to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Hi to all,

I am trying to execute a STORE PROCEDURE from SQL*PLUS with no succes:

SQL> execute PACKAGE.PROC(201011,'144792');
BEGIN PACKAGE.PROC(201011,'144792'); END;

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



In fact, when i do: desc PACKAGENAME
I see that the procedure is waiting for 3 parameters and one of them is a REF CURSOR type:

SQL> desc PACKAGENAME
PROCEDURE PROCEDURENAME

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
PWEEK                           NUMBER                  IN
PCLIENT                         VARCHAR2                IN
CRESULTS                        REF CURSOR              IN/OUT


After searching a bit, i try the following:

SQL> execute PACKAGE.NAME(201011,'144792','CRESULTS
'=>:C1);
SP2-0552: Bind variable "C1" not declared.
SQL>


This is a preview of the PACKAGE header:
CREATE OR REPLACE PACKAGE PACKAGENAME
   AUTHID CURRENT_USER 
AS

  -- 
   TYPE CurTyp_Supp IS REF CURSOR;

   -- 
   TYPE TabTyp_Supp IS TABLE OF VARCHAR2 (10 BYTE);

   --
   TYPE ObjTyp_Prmt IS OBJECT (p_schemaname VARCHAR, p_filename VARCHAR);

   --
   PROCEDURE PROC(pWEEK       NUMBER,
                  pCLIENT     VARCHAR,
                  cResults    IN OUT CurTyp_Supp);



This what the PACKAGE BODY looks like:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS

PROCEDURE PROC (pWEEK       NUMBER, 
                pCLIENT     VARCHAR, 
                cResults    IN OUT CurTyp_Supp)


QUESTION:
HOW DO I MANAGE TO EXECUTE THIS PROCEDURE FROM SQL*PLUS

Thanks !

Report message to a moderator

Re: SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters [message #495831 is a reply to message #495829] Tue, 22 February 2011 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
How to execute store proc when passing only 2 out of 3 parameters

Give a default value to the third one.

Regards
Michel

Report message to a moderator

Re: SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters [message #495832 is a reply to message #495831] Tue, 22 February 2011 10:39 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
You mean in the PL/SQL code of the store proc ?

Or when passing parameters when calling the store proc ?
If so how can we give a default value on a parameter of type REF CURSOR

Thanks for the fast feed-back !

Report message to a moderator

Re: SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters [message #495833 is a reply to message #495832] Tue, 22 February 2011 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can give NULL but I see it is an IN/OUT parameter so it can't be omitted, the logic of your procedure makes it mandatory.

As this is in a package, create a second procedure with same name and only 2 parameters and internally call the first one as you want, with a dummy ref cursor for instance.

Regards
Michel

[Updated on: Tue, 22 February 2011 13:40]

Report message to a moderator

Re: SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters [message #495848 is a reply to message #495833] Tue, 22 February 2011 13:33 Go to previous messageGo to next message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
O i see,

I will talk to the developper of this store proc about this suggestion/change.

I might return for further questions on this issue but until then have a great day or end of day.

Thanks !

Report message to a moderator

Re: SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters [message #496149 is a reply to message #495848] Thu, 24 February 2011 17:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
You need to declare a variable of type refcursor in SQL*Plus. Then you can pass that, prefaced with a colon, as the third parameter, to your procedure, then print it. Please see the simplified demonstration below.

-- sample package and package body:
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE PACKAGENAME
  2  AS
  3  	TYPE CurTyp_Supp IS REF CURSOR;
  4  	PROCEDURE PROC(pWEEK	   NUMBER,
  5  		       pCLIENT	   VARCHAR2,
  6  		       cResults    IN OUT CurTyp_Supp);
  7  END PACKAGENAME;
  8  /

Package created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PACKAGE BODY PACKAGENAME
  2  AS
  3    PROCEDURE PROC (pWEEK	   NUMBER,
  4  		       pCLIENT	   VARCHAR2,
  5  		       cResults    IN OUT CurTyp_Supp)
  6    IS
  7    BEGIN
  8  	 OPEN cResults FOR
  9  	 SELECT pweek week, pclient client
 10  	 FROM	DUAL;
 11    END PROC;
 12    END PACKAGENAME;
 13  /

Package body created.

SCOTT@orcl_11gR2> show errors
No errors.


-- variable declaration, execution, and display:
SCOTT@orcl_11gR2> variable c1 refcursor
SCOTT@orcl_11gR2> execute packagename.proc (201011, '144792', :c1)

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> print c1

      WEEK CLIENT
---------- --------------------------------
    201011 144792

1 row selected.

SCOTT@orcl_11gR2>


Report message to a moderator

Re: SQL*PLUS - How to execute store proc when passing only 2 out of 3 parameters [message #496479 is a reply to message #495829] Mon, 28 February 2011 08:53 Go to previous message
Quetzalcoatl1
Messages: 33
Registered: January 2011
Member
Wow Barbara,

I tried what you suggested and the procedure was in deed executed succesfully !

Althought in my case the printing of the refcursor variable displays no rows i get the point and i now know how to execute a procedure containing a refcursor parameter.


Thanks to all and chat you soon !

[Updated on: Mon, 28 February 2011 08:55] by Moderator

Report message to a moderator

Previous Topic: Closed connection error after procedure debug in SQL Develoiper
Next Topic: SQLPLUS WIndow in oracle 11g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 05:01:14 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.