Oracle User Defined Functions [message #1517] |
Wed, 08 May 2002 09:52 |
Mike
Messages: 417 Registered: September 1998
|
Senior Member |
|
|
Hello and thanks in advance.
Can someone tell me how to call a user defined function which has two input parameters and three output parameters as well in addition to returning a number??
eg.
( cUserName IN VARCHAR2,
cPassword IN VARCHAR2,
nUserType OUT NUMBER,
nUserId OUT NUMBER,
nCustId OUT NUMBER
)
RETURN NUMBER
|
|
|
Re: Oracle User Defined Functions [message #1520 is a reply to message #1517] |
Wed, 08 May 2002 10:51 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Here are a couple of ways to call a function like this in SQL*Plus (although it is generally not recommended to return OUT parameters in a function - a function should return one and only one value):
sql> var cUsername varchar2(30)
sql> var cPassword varchar2(30)
sql> var nUserType number
sql> var nCustId number
sql> var nUserId number
sql> var nResult number
sql> exec :cUsername := 'username'
PL/SQL procedure successfully completed.
sql> exec :cPassword := 'password'
PL/SQL procedure successfully completed.
sql> exec :nResult := foo(:cUsername, :cPassword, :nUserType, :nUserId, :nCustId)
PL/SQL procedure successfully completed.
sql> print nUserType
NUSERTYPE
---------
3
sql> print nUserId
NUSERID
---------
4
sql> print nCustId
NCUSTID
---------
5
sql> print nResult
NRESULT
---------
1
sql>set serveroutput on
sql>declare
2 cUsername varchar2(30) := 'username';
3 cPassword varchar2(30) := 'password';
4 nUserType number;
5 nUserId number;
6 nCustId number;
7 nReturn number;
8 begin
9 nReturn := foo(cUsername, cPassword, nUserType, nUserId, nCustId);
10 dbms_output.put_line( nReturn );
11 dbms_output.put_line( nUserType );
12 dbms_output.put_line( nUserId );
13 dbms_output.put_line( nCustId );
14 end;
15 /
1
3
4
5
PL/SQL procedure successfully completed.
|
|
|