Scripting question in SQL *Plus (9i)

[swdstar]

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

[davey23uk]

and your users will be using sqlplus as their tool for connecting to the database?

[swdstar]

Hi Dave,

In this scenario the user would be using sql *plus. Well, the purpose for doing this is entirely to learn more about SQL *plus scripting. I guess by doing this I hope to learn how to use branching in scripting, accepts, prompts, interaction between sql *plus and PL/SQL blocks etc.

Cheers
Erik

[WilliamR]

You cannot switch back and forth between SQL*Plus and PL/SQL. It would be best to gather all the information you are going to need upfront and then call the relevant procedures. Possibly a scripting language such as Korn shell or Perl might provide a basic application front end, but then if you want one of them it might be worth developing an actual application IMHO.

[JChiappa]

True, for end-users you will be ** never ** using sqlplus as front-end. But, for your own purposes, as a DBA, surely you can (and will) in many cases make use of sqlplus (among others, sqlplus is the default interface to Oracle admin, not all shops have OEM and others!), so YES, menuing, branching, calling sub-routines and others techniques in sqlplus are, IMHO, an very useful thing to know. Jonathan Gennick shows some of them in the paper available in http://www.dbaclick.com/ftp/document...Techniques.doc - as stated there, you can build .SQLs on the fly, you can have diverse .SQLs for each "menu" option, and each .SQL ** can ** do some sqlplus-only commands abfetr/before PL/SQL blocks...

Sure, very quickly in many cases the need for more complex routines can make sqlplus useless, but NOT in all cases.

[]s

Chiappa

[swdstar]

Hi JChiappa,

Thanks for this. For some reason the link doesn't work. I can't even access www.dbaclick.com

I'm not sure if the problems are on my side with my router/firewall settings. I'm having problems accessing some sites that should normally work.

Are you able to email me the document?


Thanks
Erik
erik_swed@hotmail.com

[jmodic]

Google "Jonathan Gennick" - you'll find that paper on his web site.

Or to save your time, click here.

[swdstar]

Thanks!!!
That was such a good website with PLENTY of good readings.

Many thanks
Erik