Home » SQL & PL/SQL » SQL & PL/SQL » Returning control to main-body from procedure.
Returning control to main-body from procedure. [message #286683] Sun, 09 December 2007 18:30 Go to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
G'day everyone.

In short:
I'm wondering how to "end" a procedure from within the procedures own EXCEPTION handler. I've unsuccessfully tried the following:

        
PROCEDURE CROSSTAB_p
IS

BEGIN
FOR l_emp IN r_employee_number.first..r_employee_number.last
LOOP
-- Irrelevant Code Here
END LOOP;

EXCEPTION
WHEN OTHERS THEN
  END CROSSTAB_p;

END CROSSTAB_p;


In full:
I have a PLSQL containing a procedure that processes an "associative-array" (may I call it a virtual table?). I called the procedure in a test environment, however the "virtual table" did not contain any data. When I tried to execute a FOR LOOP upon the "virtual table":

FOR l_emp IN r_employee_number.first..r_employee_number.last
LOOP


I received the error:

ORA-06502: PL/SQL: numeric or value error


I have managed to solve the problem by not calling the FOR LOOP if the "virtual table" contains no data. However I wondered if another solution, albeit not as dependable, might be to simply end the procedure and pass control back to the main program using exception-handling.

Any suggestions on how I could do this? Unfortunately I couldn't find anything under Error Handling (Chapter 6) of the Oracle PL/SQL Users's Guide and Reference.

Thanks,
Tim Frazer.

[Updated on: Sun, 09 December 2007 18:31]

Report message to a moderator

Re: Returning control to main-body from procedure. [message #286686 is a reply to message #286683] Sun, 09 December 2007 20:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
RETURN;
Re: Returning control to main-body from procedure. [message #286687 is a reply to message #286686] Sun, 09 December 2007 20:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
Additionally, you can use the built-in NVL function, as demonstrated below, after the reproduction.

-- reproduction:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE CROSSTAB_p
  2  IS
  3    TYPE your_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4    r_employee_number your_typ;
  5  BEGIN
  6    FOR l_emp IN r_employee_number.first..r_employee_number.last
  7    LOOP
  8  	 DBMS_OUTPUT.PUT_LINE (r_employee_number (l_emp));
  9    END LOOP;
 10  END CROSSTAB_p;
 11  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC crosstab_p
BEGIN crosstab_p; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.CROSSTAB_P", line 6
ORA-06512: at line 1


-- solution:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE CROSSTAB_p
  2  IS
  3    TYPE your_typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4    r_employee_number your_typ;
  5  BEGIN
  6    FOR l_emp IN NVL (r_employee_number.first, 0) .. NVL (r_employee_number.last, -1)
  7    LOOP
  8  	 DBMS_OUTPUT.PUT_LINE (r_employee_number (l_emp));
  9    END LOOP;
 10  END CROSSTAB_p;
 11  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC crosstab_p

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: Returning control to main-body from procedure. [message #286689 is a reply to message #286683] Sun, 09 December 2007 20:54 Go to previous message
TimJF
Messages: 11
Registered: August 2007
Junior Member
Cheers! Smile

[Updated on: Sun, 09 December 2007 20:55]

Report message to a moderator

Previous Topic: Trigger with error on insert
Next Topic: Oracle 10g
Goto Forum:
  


Current Time: Tue Dec 06 08:24:09 CST 2016

Total time taken to generate the page: 0.11386 seconds