Home » SQL & PL/SQL » SQL & PL/SQL » Define OUT parameter and how to call
Define OUT parameter and how to call [message #7531] Thu, 19 June 2003 06:26 Go to next message
JOHN
Messages: 182
Registered: April 1998
Senior Member
Hi,

I'm writing a function which returns a number which indicates if an exception has occurred in the function and what that exception is. I want to include a way to set a custom exception message and allow this message to be accessible as an OUT parameter i.e.

CREATE OR REPLACE FUNCTION FNC_INSERT_DEPARTMENT (
department_name IN VARCHAR2
, exception_message OUT VARCHAR2
) RETURN NUMBER ...

When I call this function using
SET SERVEROUTPUT ON
declare func_retval number;
begin
func_retval := fnc_insert_department('test 01');
dbms_output.put_line('fnc retval: ' || func_retval);
end;
/
it looks for a value for the OUT parameter. In SQL Server, you can specify a default for procedure OUT params so that they don't need to be explicitly initialised.

How can I use the OUT paramter successfully ?
Re: Define OUT parameter and how to call [message #7534 is a reply to message #7531] Thu, 19 June 2003 09:52 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
out parameter is supposed to read by another program like a front end.
you can create another procedure in sqlplus to read and display the out parameter
or
use a place holder like this

  1  create or replace procedure test1
  2  (name in varchar2,
  3   osal  out number)
  4  as
  5  begin
  6  select sal into osal from emp where ename =name;
  7* end;
dbadmin@mutation_mutation > /

Procedure created.

dbadmin@mutation_mutation > save test1
Created file test1.sql
dbadmin@mutation_mutation > variable a number;
dbadmin@mutation_mutation > exec test1('KING',:a);

PL/SQL procedure successfully completed.

dbadmin@mutation_mutation > print :a

         A
----------
      5000

dbadmin@mutation_mutation > 

Re: Define OUT parameter and how to call [message #7538 is a reply to message #7531] Thu, 19 June 2003 20:34 Go to previous message
Shailender Mehta
Messages: 49
Registered: June 2003
Member
Another alternative is to define a IN OUT variable in the function.

Create Or Replace Function aTest ( pInVal In Varchar2
,pOutVal In Out Varchar2) Return Number Is
Begin
Dbms_Output.Put_Line ('In Value = ' || pInVal);
pOutVal := 'ORACLE Error ' || '-1403 : No Data Found';

Return (0);
End;
/

Calling the stored function from PL/SQL block,

Set Serveroutput On Size 999999;

Declare
lRtnVal Number;
lOutStr Varchar2(100);
Begin
lRtnVal := aTest ('This is Test', lOutStr);
Dbms_Output.Put_Line ('Out String from Function = ' || lOutStr);
End;
/

Test Db>@t1
Input truncated to 1 characters
In Value = This is Test
Out String from Function = ORACLE Error -1403 : No Data Found
Previous Topic: how to insert single quote(') in a var char field
Next Topic: oracle pinning
Goto Forum:
  


Current Time: Fri Apr 26 12:47:54 CDT 2024