Home » SQL & PL/SQL » Client Tools » PROMPT & ACCEPT (Oracle 10G)
PROMPT & ACCEPT [message #494230] Sat, 12 February 2011 00:57 Go to next message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Hi,
I have a login.sql file. In this file i'm prompting for an ticket ID. when ever an user login to DB, he will be prompted for a ticket num, after entering the number it will take to sql> prompt.
My requirement, i want to implement for a particular user means only for one user(say USER1). if USER2 logins it should not ask for a ticket.
  • Attachment: req.txt
    (Size: 0.71KB, Downloaded 163 times)
Re: PROMPT & ACCEPT [message #494242 is a reply to message #494230] Sat, 12 February 2011 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
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

Re: PROMPT & ACCEPT [message #494273 is a reply to message #494242] Sat, 12 February 2011 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I deleted your duplicate in SQL & PL/SQL forum.
Please post a feedback on this topic if you got what you need or tell us what is missing.

Regards
Michel

[Updated on: Sat, 12 February 2011 12:01]

Report message to a moderator

Re: PROMPT & ACCEPT [message #494276 is a reply to message #494242] Sat, 12 February 2011 12:46 Go to previous message
Yuvraaj
Messages: 122
Registered: January 2011
Location: California, USA
Senior Member
Thank you very much Michel...
Issue resolved Smile Smile
Previous Topic: dbms_metadata.get_ddl in Sql*Plus
Next Topic: Unable to delete a row
Goto Forum:
  


Current Time: Tue Jan 16 20:12:25 CST 2018

Total time taken to generate the page: 0.01655 seconds