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