Define OUT parameter and how to call [message #7531] |
Thu, 19 June 2003 06:26 |
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 #7538 is a reply to message #7531] |
Thu, 19 June 2003 20:34 |
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
|
|
|