Newbie questions about scripts and SQL *Plus

[Erikxxx]

Hi

I'm quite new to Oracle development and especially in how to master scripting and SQL *Plus.
To be able to better understand the PROMPT, ACCEPT, VARIABLE, DEFINE commands etc. I have setup the following scenario.

I want to create a script that enables the user to choose an option from a menu.

1. Add name (Id, firstname, lastname, city)
2. List all names
3. Delete a name


This is how I'm thinking of writing my implementation

SET SERVEROUTPUT ON;

PROMPT '1. Add a name';
PROMPT '2. List all names';
PROMPT '3. Delete a name';
PROMPT '4. Exit';

ACCEPT myopt NUMBER PROMPT 'Enter option';

DECLARE

BEGIN
IF &myopt = 1 THEN
/*
How can I move the control back to
SQL*Plus so that I can prompt the
user to input FIRSTNAME, LASTNAME and CITY
using ACCEPT?
*/
ELSIF &myopt = 2 THEN
-- Call procedure printNames.
printNames
ELSIF &myopt = 3 THEN
-- Here I have similar problem to option 1.
I want to prompt the user for
an ID and then run a delete query
ELSIF &myopt = 4 THEN
-- How to exit app?
END IF
END

-- After an user have selected an option how can I get the menu to retain control over the app?


Thanks
Erik

 

[cheguvera]

Hi,

To be honest, I am trying to divert you from your goal first, instead of trying to solve your problem.

As far as I think, the sqlplus commands are not shell commands or I should say, sqlplus is not like a shell language.

So, after a lot of tricks, you may be able to do the same, but there will be many restrictions. Also, if you are doing it just for practise, its OK, but for a real production system, I have my doubts.

Go for a shell language like KSH or Perl and this will be much better, flexible and reliable.

Regards

 

[Erikxxx]

Hi Che,

This was for practical purpose. Just to get to learn more about SQL *Plus scripting. I actually went to the local bookstore today and spent an hour reading an Oreilly's book about SQL *Plus. I did find answers to many of the questions I have such as How in SQL *Plus you can "simulate" branching, substitution variables, bind-variables, reporting etc.

The name of the book is "Oracle SQL*Plus: The Definitive Guide, Second Edition"

Thanks
Erik

 

[SantaMufasa]

But to respond directly to your original question, Erik: Your code looks fine. I use that type of (informal) scripting all the time as a utilitarian interface with Oracle. It's just fine for its limited-scope application.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Erikxxx]

Hi SantaMufasa and everyone else

For example in option 1 (add name) how can I prompt the user when I'm inside a PL/SQL Block? Can I make use of ACCEPT? or just do something like this
fname VARCHAR2(20) :=&in_firstname;
lname VARCHAR2(100) :=&in_lastname;

Thanks
Erik

 

[SantaMufasa]

No, Erik, PL/SQL does not provide for keyboard interaction. All of your ACCEPT/PROMPT results must be "in place" prior to the PL/SQL block's execution. There are, however, methods to evaluate responses to your ACCEPT statement, then cause SQL*Plus to execute other scripts (e.g. "AddName.sql", "ListNames.sql","DeleteName.sql") based upon the results of your ACCEPT. Then, within the "AddName.sql" script, you would have your SQL*Plus ACCEPT/PROMPT pairs that:

accept in_firstname prompt "Enter first name: "
accept in_lastname prompt "Enter last name: "
...et cetera...

Then in your PL/SQL block that follows those prompts in your "AddName.sql" script, you do the assignments:

fname VARCHAR2(20) :=&in_firstname;
lname VARCHAR2(100) :=&in_lastname;

How does that sound?

I must go finish another assignment before my close of business today, but if you need a set of sample code, just let me know and I'll post a sample as soon as I get done with the other activity.

If you post

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.