• Author / Uploaded
• Andrés Pecho Azurza

• Categories
• Pl / Sql
• Preguntas más frecuentes
• Software de gestión de datos
• Recuperación de información
• Desarrollo de software

Citation preview

How to create a menu in SQLPlus or PL/SQL - Stack Overflow

Page 1 of 5

Welcome to Q&A for professional and enthusiast programmers -- check out the FAQ!

×

How to create a menu in SQLPlus or PL/SQL

I have several scripts that I would like to start from a menu presented to the SQLPlus user. Something like: Please make a selection: 1: Do script a 2: Do script b 3: Do script c I just need a point in the right direction, not a quick answer. oracle

plsql

sqlplus

asked Dec 4 '09 at 22:35 Kyle Goddard 11 1

6 Answers Here is a SQL Plus script to do that: prompt Please make a selection: prompt 1: Do script a

http://stackoverflow.com/questions/1850105/how-to-create-a-menu-in-sqlplus-or-pl-sql

19/09/2010

How to create a menu in SQLPlus or PL/SQL - Stack Overflow

prompt 2: Do script b Welcome to Q&A prompt 3: Do script c

Page 2 of 5

for professional and enthusiast programmers -- check out the FAQ!

×

accept selection prompt "Enter option 1-3: " set term off column script new_value v_script select case '&selection.' when '1' then 'script_a' when '2' then 'script_b' when '3' then 'script_c' else 'menu' end as script from dual; set term on @&v_script. NB The 'menu' in the ELSE part of the case expression is the name of this script, so that it runs itself again when the user enters an invalid option. edited Dec 5 '09 at 13:15

1

answered Dec 5 '09 at 10:53 Tony Andrews 22.6k 1 16 38

+1. Good one. Wish I could double vote up. – Guru Dec 5 '09 at 11:03 learn something new everyday, thanks. – David Dec 5 '09 at 14:13

It's hard to accomplish what you are trying to do with SQLPlus and/or PL/SQL.

http://stackoverflow.com/questions/1850105/how-to-create-a-menu-in-sqlplus-or-pl-sql

19/09/2010

How to create a menu in SQLPlus or PL/SQL - Stack Overflow

Page 3 of 5

SQLPlus is a frontend for Oracle databases. Its main purpose is to perform queries against an Oracle Welcome to Q&A for professional and enthusiast programmers -- check RDBMS.

out the FAQ!

×

PL/SQL is a language to manipulate data in Oracle. Instead, if you want something with user interaction, I would suggest you to write a little script/program in insert your favorite language here (could python, C/C++, C#, Java) with an Oracle connection to perform the SQL queries or PL/SQL programs you need. answered Dec 4 '09 at 22:39 Pablo Santa Cruz 18.1k 1 17 41 You can prompt for variables defined in scripts run via SQLPlus. See my answer for details, but here's a link regarding variables & SQLPlus: orafaq.com/node/515 – OMG Ponies Dec 5 '09 at 2:47

You can execute scripts from a master script: CASE LOWER(&v_script_selection) WHEN 'a' THEN @script_a.sql WHEN 'b' THEN @script_b.sql WHEN 'c' THEN @script_c.sql ELSE DBMS_OUTPUT('No such option available') END &variablename is used to refer to the variable variablename in SQLPlus, much the same way $variablename is used in shell scripts. If variablename is undefined, then SQLPlus prompts the user for a value. You can provide a path relative to the master script - the example relies on the supporting scripts to be in the same directory. answered Dec 5 '09 at 2:44 OMG Ponies 50k 6 21 55

http://stackoverflow.com/questions/1850105/how-to-create-a-menu-in-sqlplus-or-pl-sql

19/09/2010

How to create a menu in SQLPlus or PL/SQL - Stack Overflow

Page 4 of 5

Welcome to Q&A for professional and enthusiast programmers -- check out the FAQ!

CASE isn't a valid SQL Plus command, so this doesn't work. – Tony Andrews Dec 5 '09 at 10:54

×

@Tony: It will work, it's just not a complete answer. – OMG Ponies Dec 5 '09 at 17:43

DBMS_OUTPUT can be used to print lines to the screen. It looks like it has has functions that do a GET_LINE as well, but I've never used it and don't know how well they work. You just need to be careful about your SQLPlus settings. It can truncate lines if you don't set it up properly. answered Dec 4 '09 at 22:39 Kris 149 4

I'd got with a real language for this (as per Pablo's comment). You could have some stuff in a login.sql that displayed a list suggestions when you connect (PRINT or PROMPT SQL*Plus statements). And you could have a bunch of scripts called 1.sql, 2.sql etc which would get run if the user entered @1, @2 etc. (as long as they are in the correct directory). But really SQL*Plus isn't suited for this. answered Dec 5 '09 at 0:38 Gary 10.1k 5 16

If the scripts are totally unrelated, I'd use a simple batch file or shell script. answered Dec 5 '09 at 14:42 IronGoofy 2,725 4 20

http://stackoverflow.com/questions/1850105/how-to-create-a-menu-in-sqlplus-or-pl-sql

19/09/2010

How to create a menu in SQLPlus or PL/SQL - Stack Overflow

to Q&Afor? for professional and enthusiast programmers -- check out the FAQ! Not the answerWelcome you're looking Browse other questions tagged oracle plsql

Page 5 of 5

×

sqlplus or ask your own question.

question feed

http://stackoverflow.com/questions/1850105/how-to-create-a-menu-in-sqlplus-or-pl-sql

19/09/2010