2 Replies Latest reply: Nov 7, 2012 3:39 PM by AlbertoFaenza RSS

    SQLPLUS - then /else run script.sql

      I am having trouble with the script below, would anyone have any suggestions or be able to help me out?

      I am trying to do a case statement where I select to see if an ID exists or not and if it doesnt i want to run a .sql file but its not working properly.

      select case when exists (select ID from VERSION where ID = '16.1')
      then 'ID Exists'
      else @script.sql
      from dual;

      Its not allowing me to run the @script.sql with the else statement...does anyone have any suggestions?

        • 1. Re: SQLPLUS - then /else run script.sql
          Frank Kulash

          SQL*Plus wasn't designed to do if-then-else logic. I would use PL/SQL for this kind of thing.
              num_found     PLS_INTEGER;
              target_id     version.id%TYPE     := '16.1';
              SELECT  COUNT (*)
              INTO    num_found
              FROM    version
              WHERE   id     = target_id;
              IF  num_found = 0
                  dbms_output.put_line (target_id || ' was not found');
               --     ...
                  dbms_output.put_line (target_id || ' exists');
               --     ...
              END IF;
          IF ... THEN ... ELSE only works in PL/SQL, and you can't issue SQL*Plus commands (like @script) from PL/SQL.

          Perhaps the best you can do in SQL*Plus is:
          COLUMN     next_script_col     NEW_VALUE next_script
          SELECT     CASE  
                 WHEN  COUNT (*) = 0  THEN  'no_id.sql'
                                        ELSE  'id_exists.sql'
               END    AS next_script_col
          FROM     version
          WHERE     id     = '16.1'
          no_id.sql       is a script to be run when the given id is not found, and
          id_exists.sql       is a script to be run when the given id is found in the table.

          Edited by: Frank Kulash on Nov 7, 2012 4:45 PM
          Added PL/SQL example
          • 2. Re: SQLPLUS - then /else run script.sql

            welcome to the forum.

            Please read SQL and PL/SQL FAQ

            Additionally when you put some code please enclose it between two lines starting with {noformat}
            SELECT ...
            Don't ask the same question in different forums: {message:id=10680243}. Mark the other thread as answered.
            Regarding your question you are making confusion between PL/SQL and SQLPlus commands.
            That syntax is not allowed. IF THEN ELSE is not available in SQLPlus.