Home » SQL & PL/SQL » SQL & PL/SQL » RETURN statement (merged by CM) (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi)
RETURN statement (merged by CM) [message #445745] Wed, 03 March 2010 16:23 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi, I have following SP with a RETURN statement used in it.

CREATE OR REPLACE PROCEDURE CheckPermissions( 
                                in_pptid          varchar2,
                                verrordesc      out varchar2);
AS
v_pptid varchar2(10);
<some statements>
BEGIN
<some statements>

IF v_pptid = 'TEMP123' THEN
verrordesc := 'Temporary Employee';
return; --dont want to continue if the emp is temp employee
END IF;

<some statements>

EXCEPTION WHEN OTHERS THEN
verrordesc := SUBSTR(sqlerrm,   1,   100);
END;


I want to know, will the usage of RETURN statement not allow the SP to return the OUT variable "verrordesc" with the error message "Temporary Employee"? If Yes, is there any other way (apart from raising user defined exceptions) to display the OUT variable when an employee is a Temp employee? I dont want to move to the other statements preceeding this check, if the employee is a temporary employee.

Thanks!

**MODS - Please DELETE the other two duplicate posts. mistakenly created.

[Updated on: Wed, 03 March 2010 16:29]

Report message to a moderator

Re: RETURN statement [message #445748 is a reply to message #445745] Wed, 03 March 2010 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
When all else fails Read The Fine Manual

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/return_statement.htm#sthref2995
Re: RETURN statement (merged by CM) [message #445750 is a reply to message #445745] Wed, 03 March 2010 17:52 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
Thanks .. but this manual didn't help. Using RETURN is going to terminate the main SP immediately and, I guess, is not going to keep the value for the verrordesc variable.
Re: RETURN statement (merged by CM) [message #445752 is a reply to message #445750] Wed, 03 March 2010 18:41 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>I guess, is not going to keep the value for the verrordesc variable.

You can when you do it correctly

17:20:09 SQL> set serveroutput on
17:20:09 SQL> set time on
17:20:09 SQL> DECLARE
17:20:09   2  PPTID VARCHAR2(10) := 'OTHER23';
17:20:09   3  LABEL1	VARCHAR2(50);
17:20:09   4  BEGIN
17:20:09   5  DBMS_OUTPUT.ENABLE(10000);
17:20:09   6  DBMS_OUTPUT.PUT_LINE('CALL #1');
17:20:09   7  CHECKPERMISSIONS(PPTID,LABEL1);
17:20:09   8  DBMS_OUTPUT.PUT_LINE(LABEL1);
17:20:09   9  PPTID := 'TEMP123';
17:20:09  10  DBMS_OUTPUT.PUT_LINE('CALL #2');
17:20:09  11  CHECKPERMISSIONS(PPTID,LABEL1);
17:20:09  12  DBMS_OUTPUT.PUT_LINE(LABEL1);
17:20:09  13  PPTID := 'FALSE23';
17:20:09  14  DBMS_OUTPUT.PUT_LINE('CALL #3');
17:20:09  15  CHECKPERMISSIONS(PPTID,LABEL1);
17:20:09  16  DBMS_OUTPUT.PUT_LINE(LABEL1);
17:20:09  17  END;
17:20:09  18  /
CALL #1
CALL #2
Temporary Employee
CALL #3

PL/SQL procedure successfully completed.

[Updated on: Wed, 03 March 2010 19:22]

Report message to a moderator

Re: RETURN statement (merged by CM) [message #445930 is a reply to message #445745] Thu, 04 March 2010 14:04 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
You are right BlackSwan!! It does return the OUT variables.
Thanks!

However I guess I was too early to raise a question on this forum. The SP I posted was a part of a package and thought may be this was causing the issue, but unfortunately after some more debugging found that this isn't the root cause.

Package is calling a Procedure which is returning some OUT variables other than the errordesc. These OUT variables are Ref Cursors. Now at some point of time, the flow skips using these cursors due to the temp employee check and thus never opens the cursor. But as I'm returning the Ref Cursor as an OUT param, I get "ORA-24338: statement handle not executed" error instead of the OUT "verrordesc" param.

CREATE OR REPLACE PROCEDURE CheckPermissions( 
                                in_pptid          varchar2,
                                pptdesc_cv OUT PPTDetailCV,
                                verrordesc      out varchar2);
AS
v_pptid varchar2(10);
<some code>
BEGIN
<some code>

IF v_pptid = 'TEMP123' THEN
verrordesc := 'Temporary Employee';
return; --dont want to continue if the emp is temp employee
ELSE
open pptdesc_cv for select pptid, pptname, iddesc, idfield1desc, idfield1val from pptdetails;
fetch *********
close *********
<some code>
END IF;

<some code>

EXCEPTION WHEN OTHERS THEN
verrordesc := SUBSTR(sqlerrm,   1,   100);
END;


Below is the definition of the Ref Cursor:
TYPE PPtDetailRec IS RECORD (
        p_pptid        number,
        p_name        char (11),
        p_iddesc        char(15),
        p_idfield1desc    char(15),
        p_idfield1value    char(50)); 

TYPE   PPTDetailCV IS REF CURSOR RETURN PPtDetailRec; 


I tried handling this by opening the cursors with some dummy values right in the beginning of the SP like below.

BEGIN
open pptdesc_cv for select 11, ' ', ' ', ' ', 11 from dual;
<some code>


which I think is only a workaround and not exactly a solution. Is there a way to handle such scenarios?

Thanks!
Re: RETURN statement (merged by CM) [message #445933 is a reply to message #445930] Thu, 04 March 2010 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
I can't fix faulty implementation logic; especially when posted "code" is totally obfuscated.

So You're On Your Own (YOYO)!
Re: RETURN statement (merged by CM) [message #445935 is a reply to message #445745] Thu, 04 March 2010 15:04 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
In layman's terms, I OPEN a cursor in the ELSE part of the statement and then return it as OUT param.
But I never get to open the same cursor in "IF" part of the statement and thus get ORA-24338 when trying to return the OUT params.

For now, to handle it, I'm opening the Cursor for DUMMY values in the "IF" part of the statement.

Is there a better solution for this?

Re: RETURN statement (merged by CM) [message #445936 is a reply to message #445935] Thu, 04 March 2010 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 24907
Registered: January 2009
Senior Member
>Is there a better solution for this?
BETTER is in the eye of the person doing the code review.
What about open cursor for real BEFORE the IF?

Realize we don't have any clue, other than what you post.

Doctor, It hurts when I poke myself in the eye.
Doctor, How do I stop the pain?

So will I get a shot a Novacaine?
Re: RETURN statement (merged by CM) [message #445939 is a reply to message #445745] Thu, 04 March 2010 15:57 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You can check if the ref cursor is open in the calling procedure using %isopen. No need for dummy values.
Previous Topic: Trigger Throw Error Msg
Next Topic: Need help on to get the number of records (not a simple count())
Goto Forum:
  


Current Time: Tue Sep 27 11:06:58 CDT 2016

Total time taken to generate the page: 0.15685 seconds