Scripting question in SQL *Plus (9i)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Scripting question in SQL *Plus (9i)

  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Scripting question in SQL *Plus (9i)

    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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    and your users will be using sqlplus as their tool for connecting to the database?

  3. #3
    Join Date
    Oct 2005
    Posts
    4
    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

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    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.

  5. #5
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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

  6. #6
    Join Date
    Oct 2005
    Posts
    4

    Can't access www.dbaclick.com

    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Google "Jonathan Gennick" - you'll find that paper on his web site.

    Or to save your time, click here.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Oct 2005
    Posts
    4
    Thanks!!!
    That was such a good website with PLENTY of good readings.

    Many thanks
    Erik

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width