Menu PL/SQL 2

[marieannie]

Hello, I have a simple Menu

PROMPT Customers
PROMPT Orders
PROMPT Production
PROMPT Quit
ACCEPT option PROMPT "option: "

Begin
/*I would like to add a condition.*/
If option = 'C' then
Start sqlCustomers.sql /*which is another menu containing option for Add,Modify,delete*/
if option = 'O' then
Start sqlOrders.sql

end if;
end;
/
I already tried but is not working...
This is the error...
Choose an option: C
old 3: if &option = C then
new 3: if C = C then
old 7: if &option = O then
new 7: if C = O then
START c:\sql\sqlCustomers.sql;
*
ERROR at line 4:
ORA-06550: line 4, column 2:
PLS-00103: Encountered the symbol "START" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
<an alternatively-quoted SQL string>

Any suggestions?
do you have an example code of how to create a simple menus?

Thank you
MA

 

[sandtek]

start or @ is a sqlplus command which cannot be processed within sql block.
as a workaround you can spool your answer into one file.

PROMPT Customers
PROMPT Orders
PROMPT Production
PROMPT Quit
ACCEPT option PROMPT "option: "
spool startthis.sql
set head off feed off 
select decode ('&option', 'C', '@sqlCustomers.sql', 'O', '@sqlOrders.sql', 'Q', 'exit')
from dual
/
set head off feed off 
spool off

@startthis.sql

Please let us know if this may help.

 

[marieannie]

Thank you Sandtek. I will try your suggestion in the afternoon.
I'm very new in Oracle and don't understand much... but I would like to use PL/SQL, is this possible?

I don't have my code test with me right now, but what I want to do is after selecting the option from the menu (which I will try your suggestion) and call the correct sql, one of my options is to update a table..
I use almost the same code from the menu..
Prompt m_id prompt 'ID to change'
Prompt m_name prompt 'Enter name'
prompt m_address prompt 'Enter address'
etc..

what I want to do is to update only the variables that have a value, for example if m_address is empty - don't update that field...

I want to do this using the IF instruccion
Begin
if ('&m_address' <> Null) then
update table set address = '&m_address'
where id=&m_id
end if;
end;

my block looks like this...
------------------------------------------------
Prompt m_id prompt 'ID to change'
Prompt m_name prompt 'Enter name'
prompt m_address prompt 'Enter address'
Begin
if ('&m_address' <> Null) then
update table set address = '&m_address'
where id=&m_id;
end if; /*more if conditions for the other fields */
end;
------------------------------------------
Oracle doesn't show me any error, but doesn't
update either...

* if I delete the Begin and if instruction the query runs correctly...

Any suggestions what am I doing wrong??

Thank you
Regards,
MA

 

[SantaMufasa]

Oracle doesn't show me any error, but doesn't update either...

Your problem, MA, derives from this part of your code:

if ('&m_address' [b]<> Null[/b]) then...

In Oracle, when you compare anything to NULL, you should use these comparison operations:

...IS NULL...

or

...IS NOT NULL...

If you use any other operators (such as =, !=, <>, >, <, >=, <=, et cetera) the answer will NEVER be TRUE regardless of whether or not the target of your comparison is or is not NULL, which is what is happening in your case, causing your code to never UPDATE.

Let us know if this is helpful/useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[marieannie]

Santa Mufasa:
Thank you. It was the operator <>...

Sandtek:
I tried to use the instructions you gave me but appears this error after choose the option...

option: C
old 2: ('&option', 'C', '@c:\sql\1.sql', 'P', '@2.sql', 'Q', 'exit')
new 2: ('C', 'C', '@c:\sql\1.sql', 'P', '@2.sql', 'Q', 'exit')

@c:\sql\1.sql
SP2-0734: unknown command beginning "old 2: (..." - rest of line ignored.
SP2-0734: unknown command beginning "new 2: (..." - rest of line ignored.




I have another question how can I avoid
that before executing a query appears something like this.
old 5: (EMPLOYEE_SEQ.NEXTVAL,'&m_e
new 5: (EMPLOYEE_SEQ.NEXTVAL,'',''
old 6: '&m_empalt','&m_empema','&
new 6: '','','','','')


Regards,
MA

 

[sandtek]

The
"old ..."
"new ..."
appears when the VERIFY option is turned on.

please include the
SET VERIFY OFF
before calling the
SELECT DECODE ... block.

I hope this clears out everything. ;-)

Please also try to look every SET options that currently
turned on like TIMING, ECHO, etc...

 

[marieannie]

Thank you.
MA