sqlplus plus program exit control
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: sqlplus plus program exit control

  1. #1
    Join Date
    Feb 2007
    Posts
    212

    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.

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Well, not very intelligent, but I think
    select 1/count(*) from .....
    will cause a zero division when count is 0

    Cheers

  3. #3
    Join Date
    Feb 2007
    Posts
    212
    thanks honey that is what I thought also ....i guess it is the best one

  4. #4
    Join Date
    Jun 2006
    Posts
    259
    Quote 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;
    /

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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
    :-)

  6. #6
    Join Date
    Feb 2007
    Posts
    212
    thanks all...but compared to sqlplus pl/sql is very slow

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    You are comparing apples and oranges. One is a tool and the other is a programming language

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote 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.

  10. #10
    Join Date
    Jun 2006
    Posts
    259
    Quote 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
  •  



Click Here to Expand Forum to Full Width