CODEWITHSUNDEEP
oracle-databasestored-proceduresoracle11gcall
user11428944
user11428944

Reputation: 15

How to call stored procedure with only OUT parameter?

I have created a stored procedure in Oracle 11g:

CREATE OR REPLACE PROCEDURE greetings(cnt OUT VARCHAR2) 
AS 
BEGIN
    SELECT COUNT(*) 
    INTO cnt 
    FROM SYS.all_tables; 
END greetings;

but I am unable to call it.

I have tried the following code snippets:

  1. EXEC GREETINGS();
  2. EXEC GREETINGS;
  3. CALL GREETINGS;

Upvotes: 0

Views: 19620

Answers (3)

William Robertson
William Robertson

Reputation: 15991

Regarding the call example, this is explained in EXECUTE recognizes a stored procedure, CALL does not. It's not obvious from the syntax documentation but it does require brackets, so it is (rather unhelpfully) rejecting the whole thing and giving the impression that greetings is the problem, when actually it is not:

SQL> call greetings;
call greetings
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

while using the mandatory brackets gets you the real issue:

SQL> call greetings();
call greetings()
     *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'GREETINGS'

As others have pointed out, you are missing the parameter.

SQL> var n number
SQL>
SQL> call greetings(:n);

Call completed.

SQL> print :n

         N
----------
       134

execute is just a handy SQL*Plus shortcut for the PL/SQL block begin xxx; end; which is less fussy about brackets and gives the same error message with or without them.

(variable and print are SQL*Plus commands and may not be supported in other environments.)

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

There's no problem with the procedure body. You can call like this :

SQL> var nmr number;
SQL> exec greetings(:nmr);

PL/SQL procedure successfully completed
nmr
------------------------------------------
306 -- > <a numeric value returns in string format>

Oracle doesn't care assigning a numeric value to a string. The execution prints the result directly, but whenever you want you can recall that value of variable(nmr) again, and print as 

SQL> print nmr
nmr
---------
306

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142710

The procedure requires one parameter, so - provide it.

SQL> CREATE OR REPLACE PROCEDURE greetings(cnt OUT VARCHAR2)
  2  AS
  3  BEGIN
  4      SELECT COUNT(*)
  5      INTO cnt
  6      FROM SYS.all_tables;
  7  END greetings;
  8  /

Procedure created.

One option, which works everywhere, is to use an anonymous PL/SQL block:

SQL> set serveroutput on
SQL> declare
  2    l_cnt number;
  3  begin
  4    greetings(l_cnt);
  5    dbms_output.put_line(l_cnt);
  6  end;
  7  /
87

PL/SQL procedure successfully completed.

Another one works in SQL*Plus (or any other tool which is capable of simulating it):

SQL> var l_cnt number;
SQL> exec greetings(:l_cnt);

PL/SQL procedure successfully completed.

SQL> print l_cnt;

     L_CNT
----------
        87

Upvotes: 2

Related Questions