Home » SQL & PL/SQL » SQL & PL/SQL » Unable to Exit from the Program in the middle (Merged)
Unable to Exit from the Program in the middle (Merged) [message #247317] Mon, 25 June 2007 09:28 Go to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

Let me ask again clearly ....

I have a select statemet at the beginning, if it fails the rest should not execute, iam capturing that by WHEN OTHERS, and doing 1 insertion operation there (Logging into Error Table) there itself iam giving EXIT , it throws error like 'EXIT' Should appear inside loop only.

My code will start cursor loop only if the SELECT succeed...

Pls give your idea....

'return' / 'quit' doesnt work...

Thx in advance
Imran





*******************


Can any 1 please resolve my tech problem

When i try to exit from the program (not frm loop) but it does not,

command 'exit' also doesnt work....


pls reply

Thx
Imran

[Updated on: Mon, 25 June 2007 10:30]

Report message to a moderator

Re: Unable to Exit from the Program in the middle [message #247318 is a reply to message #247317] Mon, 25 June 2007 09:37 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
pls dnt use im spk tht sm flk cnt undrsn. also pls pst the prcdre tht u r tlkn abt
Re: Unable to Exit from the Program in the middle [message #247320 is a reply to message #247317] Mon, 25 June 2007 09:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
One point of entry, one point of exit.
Golden rule in modular desing. Don't create more than one exit point for a procedure.
Re: Unable to Exit from the Program in the middle [message #247321 is a reply to message #247317] Mon, 25 June 2007 09:50 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
One way to handle an exit in the middle ofr a procedure, would be to raise an exception and then apply some logic within the execption handler.
Re: Unable to Exit from the Program in the middle [message #247326 is a reply to message #247317] Mon, 25 June 2007 10:01 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
What about RETURN statement?

Sometimes it is good to leave in the middle, especially in functions, returning the termination cause as a result.
Unable to Exit from the Program in the middle [message #247337 is a reply to message #247317] Mon, 25 June 2007 11:04 Go to previous messageGo to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

Hi,
Can any 1 pls help me to resolve my tech problem...

I have a select statemet at the beginning, if it fails the rest should not execute, iam capturing that by WHEN OTHERS Excpn, and doing 1 insertion operation there (Logging into Error Table) there itself iam giving EXIT , it throws error like 'EXIT' Should appear inside loop only, as well it executes the rest, but that shud not happen

My code will start cursor loop only if the SELECT succeed...

Pls give your idea....

'return' / 'quit' doesnt work...

Thx in advance
Imran
Re: Unable to Exit from the Program in the middle [message #247340 is a reply to message #247337] Mon, 25 June 2007 11:19 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Posting the code may help.

Michael

[Updated on: Mon, 25 June 2007 11:20]

Report message to a moderator

Re: Unable to Exit from the Program in the middle [message #247345 is a reply to message #247340] Mon, 25 June 2007 11:29 Go to previous messageGo to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

declare

V_Client_Code varchar2(4);
V_addr1 varchar2(60);
V_addr2 varchar2(60);
V_city_st_zp varchar2(60);
V_OrdNo_OrdPmt varchar2(15);
etc..

cursor cur_refund is
SELECT ... FROM...WHERE ....'
cur_var cur_refund%rowtype;

BEGIN
---- Selecting CLIENT CODE from database ----
begin
select Cad$param.get_parameter_number_value (
'CLIENT_CODE', cad$user_api.Get_User_Company_Id)
into V_Client_Code from dual;
Exception
When OTHERS then
-- Write a message to the application log if any error --
Insert into ..... ,SQLERRM);
exit;
end;
<< code continues.... >>

for cur_var in cur_refund loop
exit when cur_refund %notfound;
..........
........
commit;
end if;
end loop;


---- Closing the local files ----
UTL_FILE.fclose (Name_filetype);
UTL_FILE.fclose (Addr_filetype);

Exception

when NO_DATA_FOUND then
dbms_output.put_line('No Data Found '||TO_CHAR(SQLCODE)||' '||SQLErrM);

when VALUE_ERROR then
dbms_output.put_line('Value Error '||TO_CHAR(SQLCODE)||' '||SQLErrM);

when NOT_LOGGED_ON then
dbms_output.put_line('Login Error '||TO_CHAR(SQLCODE)||' '||SQLErrM);

when UTL_File.Invalid_Path then
dbms_output.put_line('Invalid Path Error '||TO_CHAR(SQLCODE)||' '||SQLErrM);

when UTL_File.Invalid_Mode then
dbms_output.put_line('Invalid Mode Error '||TO_CHAR(SQLCODE)||' '||SQLErrM);

when UTL_File.Invalid_Operation then
dbms_output.put_line('Invalid Operation Error '||TO_CHAR(SQLCODE)||' '||SQLErrM);

when UTL_File.Internal_Error then
dbms_output.put_line('Internal Error '||TO_CHAR(SQLCODE)||' '||SQLErrM);

when OTHERS then
dbms_output.put_line('Other Error Occurred '||To_Char(SQLCODE)||' '||SQLErrM);

---- Closing the local files ----
IF utl_file.is_open(Name_filetype) or utl_file.is_open(Addr_filetype) THEN
utl_file.fclose_all;
dbms_output.put_line('Closed All');
END IF;
end;
Re: Unable to Exit from the Program in the middle [message #247374 is a reply to message #247326] Mon, 25 June 2007 13:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
flyboy wrote on Mon, 25 June 2007 17:01
What about RETURN statement?

Sometimes it is good to leave in the middle, especially in functions, returning the termination cause as a result.

There are other ways to achieve that. (just because Goto can come in handy sometimes, does not mean you should use it)
Re: Unable to Exit from the Program in the middle (Merged) [message #247400 is a reply to message #247317] Mon, 25 June 2007 14:58 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hi.

IMHO you have a number of problems in your code:

1. Following code may not raise NO_DATA_FOUND (unless it's raised inside called function):

BEGIN
---- Selecting CLIENT CODE from database ----
begin 
select Cad$param.get_parameter_number_value (
'CLIENT_CODE', cad$user_api.Get_User_Company_Id)
into V_Client_Code from dual;
Exception
When OTHERS then
-- Write a message to the application log if any error --
Insert into ..... ,SQLERRM);
exit; 
end;


As I see it - you don't need to use SQL - use simple assignment:
  V_Client_Code := Cad$param.get_parameter_number_value (
'CLIENT_CODE', cad$user_api.Get_User_Company_Id);


I would like to see the code of the called function as well.


2. Cursor will NOT raise NO_DATA_FOUND exception.
You are already checking it by
exit when cur_refund %notfound;



It's quite possible that Cad$param.get_parameter_number_value function just returns NULL value, when it doesn't find client code.

Try

   V_Client_Code := Cad$param.get_parameter_number_value (
'CLIENT_CODE', cad$user_api.Get_User_Company_Id);
   IF V_Client_Code IS NULL THEN
      -- Log your error here
      ...
      RETURN;
   END IF;
   ... -- Continue your code


HTH.
Michael

[Updated on: Mon, 25 June 2007 14:59]

Report message to a moderator

Re: Unable to Exit from the Program in the middle (Merged) [message #247791 is a reply to message #247400] Wed, 27 June 2007 04:16 Go to previous messageGo to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

Hi,

Thanks Michak for your concern.

Still i am facing the same problem

cur_var cur_refund%rowtype;
BEGIN

---- Selecting CLIENT CODE from database ----
begin
/*select Cad$param.get_parameter_number_value (
'CLIENTONE', cad$user_api.Get_User_Company_Id)
into V_Client_Code
from dual;*/
V_Client_Code := Cad$param.get_parameter_number_value (
'CLIENTONE', cad$user_api.Get_User_Company_Id);
IF V_Client_Code IS NULL THEN
-- Write a message to the application log if any error --
dbms_output.put_line('Client code is null');
cad$trace_api.log_application_message(
'Processing'
,'Client code to be send'
,cad$trace_api.gnc_log_urgent
,NULL
,SQLERRM);
dbms_output.put_line('Control before Exit when Client code is null -- Returning');
RETURN; -- HERE IT IS NOT EXITING FROM PRGM INSTEAD CONTINUES..

END IF;
End;


---- Opening empty file for NAME TRUNCATE REPORT----
Name_filetype := UTL_FILE.fopen ('/tmp','trunc_name.dat','w',3000);

---- SETTING XLS HEADER for NAME REPORT----
UTL_FILE.put_line (Name_filetype,'<TABLE border="1">');
UTL_FILE.put_line (Name_filetype,'<td><b>'||'CUSTOMER_ID'||'</b></td>');
UTL_FILE.put_line (Name_filetype,'<td><b>'||'CUSTOMER_NUMBER'||'</b></td>');
UTL_FILE.put_line (Name_filetype,'<td><b>'||'ORDER_NUMBER'||'</b></td>');
UTL_FILE.put_line (Name_filetype,'<td><b>'||'FULL_NAME'||'</b></td>');
UTL_FILE.put_line (Name_filetype,'</TABLE>');

---- Opening empty file for ADDRESS TRUNCATE REPORT ----
Addr_filetype := UTL_FILE.fopen ('/tmp','addr.dat','w',3000);

---- SETTING XLS HEADER for ADDRESS REPORT ----
.....(Addr_filetype,'<td><b>'||'SUITE_APARTMENT'||'</b></td>'||'<td><b>'||'STREET_ADDRESS'||'</b></td>'||'<td><b>'||'CITY'||' </b></td>');
UTL_FILE.put_line (Addr_filetype,'<td><b>'||'STATE'||'</b></td>'||'<td><b>'||'POSTAL_CODE'||'</b></td>');
UTL_FILE.put_line (Addr_filetype,'</TABLE>');

for cur_var in cur_refund loop
exit when cur_refund %notfound;

---- Capturing Original Name and Addresses for length validation ----
V_orig_name := cur_var.FULL_NAME;
V_orig_street := cur_var.STREET_ADDRESS;


---- Writing Customer, Order and Name Info if FULL_NAME exceeds
.......



So , its not coming out the pgm ... what to do now...
finally the o/p from log is

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Jun 27 02:59:02 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

Client code is null
Control before Exit when Client code is null -- Returning

PL/SQL procedure successfully completed.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
----------
pls reply

Thanks
Imran

[Updated on: Wed, 27 June 2007 04:18]

Report message to a moderator

Re: Unable to Exit from the Program in the middle (Merged) [message #247799 is a reply to message #247317] Wed, 27 June 2007 04:45 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. How do you know that it's NOT returning.
-- Insert following line right after RETURN; statement
DBMS_OUTPUT.PUT_LINE ( '+++++' ) ;


2. You may by-pass the problem by
2.1. Removing unnecessary BEGIN and END;
2.2. Using
     IF V_Client_Code IS NULL THEN
          ...
     ELSE
       ... Put the remains of your proc here
     END IF;


HTH.
Michael
Re: Unable to Exit from the Program in the middle (Merged) [message #247809 is a reply to message #247791] Wed, 27 June 2007 05:08 Go to previous messageGo to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

Hi,

From the above modified code , i think the control gets back from RETURN Command thats fine

but it exits with 'ORA-00000 Noraml, Successful Completion' code

As effect of this, my called program is shell pgm where ftp operations continues....it shud not

my need is when sql fails my shell script also shud fail and exit using the command given as 'exit 50'

Your comments/suggestions are most welcome

Thanks
Imran
Re: Unable to Exit from the Program in the middle (Merged) [message #247817 is a reply to message #247317] Wed, 27 June 2007 05:17 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Change you procedure to function.

You will be able to return 0 for success and any other code for failure.

HTH.
Michael

[Updated on: Wed, 27 June 2007 05:17]

Report message to a moderator

Re: Unable to Exit from the Program in the middle (Merged) [message #247821 is a reply to message #247817] Wed, 27 June 2007 05:33 Go to previous messageGo to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

It is neither proc / fn, just anonymous block ... to be called from shell script.

I dont know how to call proc / fn from shell pgm

Thanks
Imran
Re: Unable to Exit from the Program in the middle [message #247960 is a reply to message #247321] Wed, 27 June 2007 11:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
MarcL wrote on Mon, 25 June 2007 16:50
One way to handle an exit in the middle ofr a procedure, would be to raise an exception and then apply some logic within the execption handler.


Search for RAISE and RAISE_APPLICATION_ERROR in PL/SQL User's Guide and Reference.

Another option could be using variable in your SQL*Plus session like this:
sqlplus $CONN_STR <<+++
whenever sqlerror exit 1
whenever oserror exit 1
-- another settings

var p_result number

-- your anonymous block
begin
 :p_result := 0;
 <...>
 if <condition> then
  :p_result := 1;
 end if;
 <...>
end;
/

exit :p_result

+++

Quote:
I dont know how to call proc / fn from shell pgm

Call it the same way as your anonymous block or simply use EXECUTE command.
Re: Unable to Exit from the Program in the middle [message #248271 is a reply to message #247960] Thu, 28 June 2007 09:08 Go to previous message
Nusrat
Messages: 38
Registered: June 2007
Location: Mumbai(India)
Member
hi

in exception handling block
use the nested anonymous block and try to handle a loop and use the exit command in that loop .

Nusrat
Previous Topic: Selecting Ranges
Next Topic: TABLESPACE SIZE
Goto Forum:
  


Current Time: Thu Dec 08 08:37:52 CST 2016

Total time taken to generate the page: 0.11412 seconds