-
sqlplus program exit control
Hi Friends,
Before, my dba friend was able share a simple sqlplus batch program that has a exit control at the beginning. But I lost the program and I cant remember how to do it again :(
My problem is like this: Say I have a program1.sql
program1.sql
=========
whenever sqlerror exit;
select count(*) from table1 where code='U';
update table2 blah blah....
(more sqlplus programs follows)....
exit;
======================
My question is ...How do I force exit(forced error) the program
if the count(*)=0 for the first code? This means that there is no
transactions to be processed and to save resources just skip the
entire process.
Do you understand why I mean here
Thanks a lot
Last edited by jennifer2007; 06-01-2007 at 06:15 AM.
-
Well, not very intelligent, but I think
select 1/count(*) from .....
will cause a zero division when count is 0
Cheers
-
thanks honey that is what I thought also ....i guess it is the best one
-
Originally Posted by Bore
Well, not very intelligent, but I think
select 1/count(*) from .....
will cause a zero division when count is 0
Cheers
Bore that is one ugly solution! but it should work.
Why not use anonymous pl/sql block:
declare
n number;
begin
select count(*) into n from xyz;
if (n = 0) raise exception;
end;
/
-
Ixion,
As I said, it's ugly :-) and definitely should be followed by a comment why u are doing such stupid query, but that is how SQL scripts works.
If u go for PL/SQL anonymous block, then I would write whole my logic in it and forget about
whenever sql error then exit
:-)
-
thanks all...but compared to sqlplus pl/sql is very slow
-
You are comparing apples and oranges. One is a tool and the other is a programming language
-
Jenn, please allow me not to agree here. If u make that a stored procedure, and u use bulk operations I doubt it will be notably slower than the SQL, u could even make it faster.
Cheers
-
Originally Posted by jennifer2007
thanks all...but compared to sqlplus pl/sql is very slow
A PL/SQL written storedproc will run on the backend.
Jenn, you are rewriting fundamental database concepts with no base for it, at least tell me you are drop-dead beautifull
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by jennifer2007
thanks all...but compared to sqlplus pl/sql is very slow
Like most things it just depends. As others above said, PL/sql is a language, that can be compiled and executed on the back end DB server (if saved and compiled). But an anonymous block must be first transmitted, parsed etc...
I like Bore's reasoning, just put as much as possible into pl/sql that way you have significantly more control over error handling. The capability to do all sorts of nice things that can't ever be done inside of a sql script.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|