OraFAQ Forum: Client Tools » How to pass values for out parameters from SQLPLUS

Home » Other » Client Tools » How to pass values for out parameters from SQLPLUS

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to pass values for out parameters from SQLPLUS [message #281180]

Fri, 16 November 2007 00:18

swapnajojo
Messages: 40
Registered: June 2007
Location: India

Member

Hi team ,

I have craeted a simple procedure
I wanted to run it from sql prompt

create or replace procedure addsum
(
a number
b number,
result out number
)
is
Begin
Dbms_output.put_line('Inside teh procedure');
result:=a+b;
end;

Do i have to pass some value for the out parameter when i call that

This is what i tried just passing values for IN parameters

CHARLIL>exec addsum (3,4);
BEGIN addsum (3,4); END;

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

2)
CHARLIL>exec addsum (3,4,5);
BEGIN addsum (3,4,5); END;

*
ERROR at line 1:
ORA-06550: line 1, column 19:
PLS-00363: expression '5' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

How should i pass values for a procedure when it has out or INOUT parameters ?

Thanks In Advance
Binu

Report message to a moderator

Re: How to pass values for out parameters from SQLPLUS [message #281186 is a reply to message #281180]

Fri, 16 November 2007 00:46

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Use a bind variable:

SQL> CREATE OR REPLACE PROCEDURE addsum( a NUMBER
  2                                    , b NUMBER
  3                                    , result OUT NUMBER
  4                                    )
  5  IS
  6  BEGIN
  7     Dbms_output.put_line('Inside the procedure');
  8     result:=a+b;
  9  END;
 10  /

Procedure created.

SQL>
SQL> VAR theresult NUMBER
SQL>
SQL> EXEC addsum(3,4, :theresult);

PL/SQL procedure successfully completed.

SQL>
SQL> PRINT theresult

 THERESULT
----------
         7

SQL>
SQL> DROP PROCEDURE addsum
  2  /

Procedure dropped.

MHE

Report message to a moderator

Re: How to pass values for out parameters from SQLPLUS [message #281209 is a reply to message #281180]

Fri, 16 November 2007 01:59

swapnajojo
Messages: 40
Registered: June 2007
Location: India

Member

Supoose my procedure is this

Create or Replace PROCEDURE getcurshift
(
curr_datetime TIMESTAMP,
debugon int,
line_number SMALLINT,
a_insert_stamp_dttmmm DATE,
nerr_code OUT int,
ret_shift_name OUT char,
ret_sh_start_str OUT char,
ret_sh_end_str OUT char,
shift_start_dtdd OUT char
)

I should have
VAR nerr_code NUMBER
var ret_shift_name varchar2
var ret_sh_start_str varchar2
var sh_end_str varchar2
var shift_start_dtdd varchar2

Then in SQLPLUS

EXEC getcurshift(14-NOV-07 09:47:59',1,2,:nerr_code ,:ret_shift_name,:ret_sh_start_str ,:shift_start_dtdd )

then print one by on e
Print ret_shift_name
print ret_sh_start_str

is this correct

Report message to a moderator