3

I used the same code, but instead of running the scripts in options 1, and 2, the script asks for this

This is the link I used: How to make a menu in SQLPlus or PL/SQL?

    PROMPT  1: Make a sales invoice 
    PROMPT  2: Inquire a sales invoice    
    accept selection PROMPT "Enter option 1-2: "

    set term off
    column script new_value v_script  --Q1. What's column script?
    select case '&selection.'          --from accept above
    when '1' then '@test1.sql'  --script to run when chosen option 1.
    when '2' then '@test2.sql'  --script to run when chosen option 2.
    else '@FinalAssignment.sql' --this script
    end as script  --Q2. What script is this referring to? 
    from dual; --Q3. Don't know this

    set term on

    @&v_script. --Q4. What script is being ran here?

This is my output what I got 

    SQL> @myScript
    1: Make a sales invoice
    2: Inquire a sales invoice
    Enter option 1-2: 1
    Enter value for v_script: 1
    SP2-0310: unable to open file "1.sql"

The name of my Script file is "myScript" so I think the menu should reload by itself if an invalid option is entered. However it is not doing so for some reason..

I dont get this, and also I want the code to run in a loop even when one script is executed , it should come back to menu and ask for another selection. If invalid option is entered again, it should come back to menu for the user to pick another selection.

My code with the Loop Included is here:

    PROMPT  1: Make a sales invoice 
    PROMPT  2: Inquire a sales invoice    
    accept selection PROMPT "Enter option 1-2: "

    set term off
    LOOP
    column script new_value v_script  --Q1. What's column script?
    select case '&selection'          --from accept above
    when '1' then @test1.sql --script to run when chosen option 1.
    when '2' then @test2.sql  --script to run when chosen option 2.
    when '3' then @test3.sql
    EXIT WHEN &selection = 4;
    else '@myScript.sql' --this script
    end as script  --Q2. What script is this referring to? 
    from dual; --Q3. Don't know this
    END LOOP;
    set term on

    @&v_script. --Q4. What script is being ran here?
Community
  • 1
  • 1
SAM
  • 31
  • 1
  • Your loop example has so many problems I don't know where to begin. For one thing, you don't even need a loop at all. Throw that away and go back to the original example. Post a working example with your scripts, or as close to working as you can get. Then we can figure out how to modify to make it do what you want. – Todd Gibson Apr 26 '13 at 22:02

1 Answers1

0

the following works:

PROMPT  1: Make a sales invoice 
PROMPT  2: Inquire a sales invoice    
accept selection PROMPT "Enter option 1-2: "

column script new_value v_script
select case '&selection'
when '1' then '@test1.sql'
when '2' then '@test2.sql'
else '@FinalAssignment.sql'
end as script
from dual;

prompt '&v_script'

@&v_script

The comments you have in the code are causing problems (don't know if you have them in your version of the script)

What it's doing is prompting for a value and sticking that in a variable called "selection". It is then doing a select from dual and calling the returned column "script" - the previous "column" command is looking for columns with this name, and when it sees one it creates a new variable called "v_script" and puts the value from the "script" column into it. (which will be, for example "@test1.sql")

Finally this variable is being used as the name of the script to call.

Creating loops in sqlplus is tricky and is probably not a sensible thing to be doing.

Calling your script again via the mechanism you are attempting is also going to run into sqlplus's call depth (which is about 20 scripts, IIRC) - don't know if that's been removed/increased in later versions though.

Note that "@" means "run the script with the given name in the sqlpath - putting another "@" in the selected result turns this into "@@", which means "run the script in the current directory".

William
  • 121
  • 2