User interaction in PL/SQL

I'm trying to create an interactive PL/SQL script, which will insert default records into different tables depending on what the user enters.

I have 5 separate SQL script files which each contains the insert statements for the 5 different tables.

I would like to prompt the user and do the following (Show logic in pseudo-code).

- Ask if the user wants to insert default data (Accept command?)
-- If No, quit PL/SQL code
-- If Yes, do next step
----- Ask if the user wants to insert all default tables (Accept command?)
-------- If Yes, Run all 5 scripts (Use @@ScriptName.sql), then go to end.
-------- If No, do next step
------------- Ask if the user wants to insert table 1 data?
------------------ If Yes, Run Table 1 script (Use @@ scriptname.sql)
------------------ If No, do next table question
----- After running the default scripts, undefine parameters done with in accept command.

One problem I have had is that I can not use the If / Else command outside of a PL/SQL block and Accept can not be used inside a PL/SQL block.

Another problem I have had is that I don’t know how to NOT run a SQL file, especially if I’m calling it outside of a PL/SQL block.

One option that may work is to have a PL/SQL script that will create another script based on the prompts entered by the user. Then after all the prompts are entered, run the generated script.

Any feedback would be greatly appreciated.

David

Don´t mix up things here is my advice. See, the PL/SQL language was built to read record, process cursors, make calcs and related tasks - it DO NOT have any command to interact with users, make screens/display itens, and the like. So, you need use the resources from the environment where your PL/SQl is running. If you r using sql*plus, it implies in using the only commands to interaction with users : ACCEPT, PROMPT and running text-scripts with @ / START / @@.
Similarly, the SQL language only copes with data-manipulation (INSERTs, UPDATEs, SELECTs, etc) = YOU CAN NOT MIX FREELY ONE LANGUAGE WITH ANOTHER, there are restrictions.

With this info in mind, to resolve your problems :

a) read the SQL*PLUS user´s guide and the sql*plus reference manuals, to know the possibilities and get some examples

b) check the sql*plus FAQ in http://www.orafaq.com/faq.htm

c) AFTER a) and b), get an advanced sql*plus book , I liked "Oracle SQL*Plus, The Definitive Guide", by Jonathan Gennick, O'Reilly & Associates, 1999.

WIth all this info, you will be able to alter your scripts successfully.

OBS : another option can be run PL/SQL in a more sofisticated environment, like Oracle Forms - this way, you will be able to get user input, let the user interact with your PL/SQL code with mouse/GUI, etc.

Regards,

Chiappa