Home » SQL & PL/SQL » Client Tools » How to pass values for out parameters from SQLPLUS
How to pass values for out parameters from SQLPLUS [message #281180] Fri, 16 November 2007 00:18 Go to next message
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
Re: How to pass values for out parameters from SQLPLUS [message #281186 is a reply to message #281180] Fri, 16 November 2007 00:46 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: How to pass values for out parameters from SQLPLUS [message #281209 is a reply to message #281180] Fri, 16 November 2007 01:59 Go to previous messageGo to next message
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

Re: How to pass values for out parameters from SQLPLUS [message #281211 is a reply to message #281209] Fri, 16 November 2007 02:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What happens if you try it?

MHE
Re: How to pass values for out parameters from SQLPLUS [message #281215 is a reply to message #281209] Fri, 16 November 2007 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And format your posts.

Regards
Michel
Re: How to pass values for out parameters from SQLPLUS [message #281241 is a reply to message #281215] Fri, 16 November 2007 03:49 Go to previous message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Quote:

then print one by on e
Print ret_shift_name
print ret_sh_start_str


SET AUTOPRINT ON in sqlplus.

regards,

[Updated on: Fri, 16 November 2007 03:50]

Report message to a moderator

Previous Topic: Relative path to spool file
Next Topic: Q: configure default editor for PL/SQL command line
Goto Forum:
  


Current Time: Sun Dec 04 10:57:01 CST 2016

Total time taken to generate the page: 0.10756 seconds