Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    Unanswered: Exit Sql Session by procedure

    I have a set of sql statements which consists "create tables","drop tables" etc
    and all have to be execute one by one. I need to insert a check in begining of that script, if my given values not exist in tables then proceed further else exit the SQL session so upcomming sql statmenta never execute.

    Hopefully understand what is the actual problem.

    Noor Ali

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I guess you are using SQL*Plus. If so, you can use WHENEVER SQLERROR.

    For example :
    Code:
    WHENEVER SQLERROR EXIT ROLLBACK;
    
    DECLARE
        Check_Condition BOOLEAN := FALSE;
    BEGIN
    
        IF (NOT Check_Condition) THEN
            RAISE_APPLICATION_ERROR(-20000, 'Objects do not have to be created');
        END IF;
    
    END;
    /
    
    CREATE TABLE Test(IdTEst VARCHAR2(100));
    Here the "Test" table will never be created.

    You can also put all your DDL statements in PLSQL dynamic SQL and have one big PLSQL block with the test and if the test succeeds, execute these DDL statements (with EXECUTE IMMEDIATE).

    For example :
    Code:
    DECLARE
        Check_Condition BOOLEAN := FALSE;
    BEGIN
    
        IF (Check_Condition) THEN
        
            EXECUTE IMMEDIATE 'CREATE TABLE Test(IdTEst VARCHAR2(100));';
        
        END IF;
    
    END;
    /
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    EXIT SQL Session by procedure

    Thanx RBARAER for your reply. but its not solution of my problem.

    Look, that script consist of sql statements not a procedure and I need to exit from my current sql session not from the procuedure, so that my further remaining queries not been executed.
    for example

    a.sql
    -----
    define bb = '&group_no';
    -- some procedure or sql statment here
    -- if bb ='A' then
    exit
    -- else
    --
    session otherwise go
    select * from temp
    where column1 = &&bb
    /
    update temp2
    set column1 = &&bb
    /
    commit
    /
    lots of sql statements


    Now hopefully you understand what I try to say.

    wait for your reply

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by nooralig
    Thanx RBARAER for your reply. but its not solution of my problem.
    Yes it is .

    Try this :
    Code:
    -- Tell SQL*Plus to stop the script and exit on error
    WHENEVER SQLERROR EXIT ROLLBACK;
    
    DEFINE bb = '&group_no';
    
    BEGIN
    
        IF ('&&bb' = 'A') THEN
            -- This error will exit the session
            RAISE_APPLICATION_ERROR(-20000, 'Objects do not have to be created');
        END IF;
    
    END;
    /
    
    -- Tell SQL*Plus that from now on, errors will not stop the script
    WHENEVER SQLERROR CONTINUE NONE;
    
    CREATE TABLE Test(IdTEst VARCHAR2(100));
    
    -- This will cause an error but the script will go on
    CREATE TABLE Test(IdTEst VARCHAR2(100));
    
    INSERT INTO Test(IdTest) VALUES('1');
    
    /*
        Lots of SQL statements.
    */
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Dec 2003
    Location
    Karachi, Pakistan
    Posts
    45

    Wow it works

    Thanx a lot RBARAER it works.

    Noor Ali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •