Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Question Unanswered: exit a sql script

    Is there a way to exit a sql script run from SQL*Plus before its completion without exiting SQL*Plus?

    I know the PL/SQL command RETURN will let me exit a PL/SQL block, but the other SQL statements following the block will still be run.

    BEGIN
    IF USER != '<AUTHORIZED_USER>' THEN
    RETURN; --THIS WILL EXIT THE BLOCK BUT NOT THE SCRIPT
    <ABORT SQL SCRIPT ENTIRELY, BUT DON'T CLOSE SQL PLUS>
    END IF;
    END;
    /

    <OTHER SQL STATEMENTS>

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: exit a sql script

    Originally posted by pd9n
    Is there a way to exit a sql script run from SQL*Plus before its completion without exiting SQL*Plus?

    I know the PL/SQL command RETURN will let me exit a PL/SQL block, but the other SQL statements following the block will still be run.

    BEGIN
    IF USER != '<AUTHORIZED_USER>' THEN
    RETURN; --THIS WILL EXIT THE BLOCK BUT NOT THE SCRIPT
    <ABORT SQL SCRIPT ENTIRELY, BUT DON'T CLOSE SQL PLUS>
    END IF;
    END;
    /

    <OTHER SQL STATEMENTS>
    Hi,
    To exit a SQL Script you have to raise an application error.
    This is how you do it.

    in Sql*plus

    SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE

    SQL> BEGIN
    IF USER != '<AUTHORIZED_USER>' THEN
    --RETURN; --THIS WILL EXIT THE BLOCK BUT NOT THE SCRIPT
    RAISE_APPLICATION_ERROR(-20001,'ERROR OUT');
    END IF;
    END;
    /

    This will work. Hope it Helps.
    nn

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    You can use GOTO labels:

    BEGIN

    IF USER != '<AUTHORIZED_USER>' THEN
    GOTO exit_the_script;
    END IF;

    <OTHER SQL STATEMENTS>
    <OTHER SQL STATEMENTS>
    <OTHER SQL STATEMENTS>
    <OTHER SQL STATEMENTS>

    <<exit_the_script>>
    NULL;

    END;
    /



    Hope that helps,

    clio_usa - OCP - DBA


  4. #4
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    still stumped

    Colleagues,

    I tried the suggestions, but the results aren't what I need.

    Using the first method, RAISE_APPLICATION_ERROR(-20001,'ERROR OUT'); causes the user to be disconnected and the session terminated, i.e. SQL*Plus shuts down. I was hoping to abort the SQL script without terminating the session. The effect would be that I could throw a message to the SQL*Plus screen like 'You are not authorized to run this script'. The user would then be allowed to continue working in their current session.

    The second method, using GOTO, exits the block, but the SQL statements following the block are still executed, so the result is just like using the RETURN command.

    The script would have to look something like this, as far as I can tell, but I'm not sure I can do it:

    -- THIS IS THE CHECK OF THE USER
    SET SERVEROUT ON
    BEGIN
    IF USER != 'authorized_user_name' THEN
    DBMS_OUTPUT.PUT_LINE('YOU ARE NOT authorized_user_name!');
    <SOMETHING HERE TO EXIT SQL SCRIPT W/O EXITING SQL PLUS>
    END IF;
    END;
    /

    -- THIS IS THE BODY OF THE SCRIPT, WHICH I WANT TO 'SKIP'
    SELECT 'REMAINING SQL' "OTHER CODE" FROM DUAL;

    Is there a way?

  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: still stumped

    It's not pretty, but couldn't you simply wrap the remaining stuff in an else clause of the initial if statement? I'm not a script jockey and I haven't tried this, so please forgive me if I am missing something too obvious.

    BEGIN
    IF USER != 'authorized_user_name' THEN
    DBMS_OUTPUT.PUT_LINE('YOU ARE NOT authorized_user_name!');
    <SOMETHING HERE TO EXIT SQL SCRIPT W/O EXITING SQL PLUS>
    ELSE
    -- THIS IS THE BODY OF THE SCRIPT, WHICH I WANT TO 'SKIP'
    SELECT 'REMAINING SQL' "OTHER CODE" FROM DUAL;
    END IF;
    END;
    /

    HTH,
    Patrick
    Originally posted by pd9n
    Colleagues,

    I tried the suggestions, but the results aren't what I need.

    Using the first method, RAISE_APPLICATION_ERROR(-20001,'ERROR OUT'); causes the user to be disconnected and the session terminated, i.e. SQL*Plus shuts down. I was hoping to abort the SQL script without terminating the session. The effect would be that I could throw a message to the SQL*Plus screen like 'You are not authorized to run this script'. The user would then be allowed to continue working in their current session.

    The second method, using GOTO, exits the block, but the SQL statements following the block are still executed, so the result is just like using the RETURN command.

    The script would have to look something like this, as far as I can tell, but I'm not sure I can do it:

    -- THIS IS THE CHECK OF THE USER
    SET SERVEROUT ON
    BEGIN
    IF USER != 'authorized_user_name' THEN
    DBMS_OUTPUT.PUT_LINE('YOU ARE NOT authorized_user_name!');
    <SOMETHING HERE TO EXIT SQL SCRIPT W/O EXITING SQL PLUS>
    END IF;
    END;
    /

    -- THIS IS THE BODY OF THE SCRIPT, WHICH I WANT TO 'SKIP'
    SELECT 'REMAINING SQL' "OTHER CODE" FROM DUAL;

    Is there a way?

  6. #6
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67

    Red face

    The problem is that the report (the 'remaining SQL' in the pseudo code above) consists of SQL*Plus commands followed by SELECT statements, and that won't work inside a PL/SQL block as far as I know.

    Maybe the unauthorized users will just have to deal with their sessions going down, although I really don't want to do that.

  7. #7
    Join Date
    Jan 2012
    Posts
    1
    This may be a forum thread excavation, however I have to say that RETURN; worked for me.

    I wanted to exit from a PL/SQL BEGIN... END; block, but not stop the current script execution.

    Code:
    BEGIN
      IF <condition> THEN
        RETURN; -- exit from the BEGIN... END; block, then continue execution
      END IF;
      
      -- OTHER SQL commands
    END;
    /
    
    -- OTHER SQL commands
    For entierely stopping the script execution, RAISE_APPLICATION_ERROR() is the way to go (see previous posts).
    Last edited by FrostyZ; 01-27-12 at 11:25.

Posting Permissions

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