This discussion is archived
1 Reply Latest reply: May 18, 2011 10:58 AM by Frank Kulash RSS

Only run script if required by input

SJM Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points