Home » SQL & PL/SQL » SQL & PL/SQL » Oracle User Defined Functions
Oracle User Defined Functions [message #1517] Wed, 08 May 2002 09:52 Go to next message
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 Go to previous message
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.
Previous Topic: Job???
Next Topic: How do you save your Sqlplus format settings???
Goto Forum:
  


Current Time: Thu Mar 28 03:49:08 CDT 2024