|
Re: PROMPT & ACCEPT [message #494242 is a reply to message #494230] |
Sat, 12 February 2011 02:33 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
This is a tricky one of executing something for a user (or some users) and something else for others (here nothing).
Prepare 2 scripts which
1/ ask for the variable
2/ do nothing
In my case, I will ask if user is MICHEL and do nothing for other users so I add the following lines to the glogin.sql:
col script new_value script
set termout off
select decode('&_USER','MICHEL','c:\ask.sql','c:\nothing.sql') script from dual;
set termout on
@&script
See how it works. Here to avoid pollute my glogin.sql I put the lines in a t.sql script I execute at the first prompt:
C:\>type ask.sql
accept ticket_num prompt 'Ticket number ? '
C:\>type nothing.sql
-- Nothing in this script
C:\>type t.sql
col script new_value script
set termout off
select decode('&_USER','MICHEL','c:\ask.sql','c:\nothing.sql') script from dual;
set termout on
@&script
C:\>sqlplus michel/michel
SQL*Plus: Release 10.2.0.4.0 - Production on Sam. FÚvr. 12 09:23:53 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> @t
Ticket number ? 1
SQL> def ticket_num
DEFINE TICKET_NUM = "1" (CHAR)
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
C:\>sqlplus system/michel
SQL*Plus: Release 10.2.0.4.0 - Production on Sam. FÚvr. 12 09:24:07 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> @t.sql
SQL> def ticket_num
SP2-0135: symbol ticket_num is UNDEFINED
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
C:\>
Regards
Michel
[Updated on: Sat, 12 February 2011 13:05] Report message to a moderator
|
|
|
|
|