1 Reply Latest reply: May 18, 2011 12:58 PM by Frank Kulash RSS

    Only run script if required by input

    SJM
      I'm trying to setup a sql script that runs various other scripts from within. A number of these scripts will only need to be run if the user requires them to be. I've tried similar to the following, to no avail:
      ACCEPT run_scripts PROMPT 'Are extra scripts required? default (N):' DEFAULT N
      
      @@script1
      
      @@script2
      
      BEGIN
        IF &&run_scripts = 'Y'
        THEN
          @@script3
          @@script4
        END IF;
      END;
      /
      Is there a way of achieving this? Thanks.
        • 1. Re: Only run script if required by input
          Frank Kulash
          Hi,

          SQL*Plus wasn't designed for that, but you can do it, using substitution variables.

          Have the users run main_script.sql, which looks like this
          --            This is main_script.sql
          
          ACCEPT run_scripts PROMPT 'Are extra scripts required? default (N):' DEFAULT N
           
          @@script1
          @@script2
          
          COLUMN     new_script_col     NEW_VALUE     next_script
          
          SELECT     CASE
                   WHEN  '&run_scripts' = 'Y'
                   THEN  'extra_scripts'
                   ELSE  'goodbye'
               END     AS next_script_col
          FROM     dual;
          
          @@next_script
          This will call either one of two other scripts: extra_scripts.sql or goodbye.sql
          Extra scripts.sql can be:
          --     This is extra_scripts.sql
          
          @@script3
          @@script4
          and goodbye.sql can be
          --     This is goodbye.sql
          
          PROMPT     This concludes the job.
          goodbye.sql can also be empty; it dioesn't actually have to do anything at all, even print a message.