RE: SQLPlus Substitution Variables
I was not looking to pass the variable in when calling the script, but to explicitly set it at the beginning of the script so that I could use it over again in multiple statements. Kind of like what the preprocessor does, it replaces all instances of the defined variable with a constant. The DEFINE keyword was exactly what I was looking for, although I have already thought of a use for your example! Thanks Erik -Original Message- Sent: Wednesday, January 15, 2003 6:29 PM To: Multiple recipients of list ORACLE-L To expand on your description of define, I thought Erik may be looking for a way to pass the variables in. Maybe this will be of some use. sqlplus id/pwd @test 'a b c' 'd e f' Where test.sql is: define field1 = '&1' define field2 = '&2' select '&field1', '&field2' from dual; HTH, John >>> [EMAIL PROTECTED] 01/15/03 07:43AM >>> I'm not sure this is what you wnat, but here is a short example of the use of DEFINE / UNDEFINE and && (Accept once but use many times inside script without re-prompt) -- define testtyp = Repair / select * from buy_type where buy_typ = '&&testtyp'; select buy_typ||'---' from buy_type where buy_typ = '&&testtyp'; undefine testtyp / HTH D. Phillips - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 15, 2003 10:04 AM > I am using substitution variables in SQLPlus, but don't want to be prompted > for their values. I want to set them at the start of the script. Kind of > like a preprocessor directive. Is this possible? > > Erik > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Erik Williams > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David L Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Carlson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQLPlus Substitution Variables
To expand on your description of define, I thought Erik may be looking for a way to pass the variables in. Maybe this will be of some use. sqlplus id/pwd @test 'a b c' 'd e f' Where test.sql is: define field1 = '&1' define field2 = '&2' select '&field1', '&field2' from dual; HTH, John >>> [EMAIL PROTECTED] 01/15/03 07:43AM >>> I'm not sure this is what you wnat, but here is a short example of the use of DEFINE / UNDEFINE and && (Accept once but use many times inside script without re-prompt) -- define testtyp = Repair / select * from buy_type where buy_typ = '&&testtyp'; select buy_typ||'---' from buy_type where buy_typ = '&&testtyp'; undefine testtyp / HTH D. Phillips - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 15, 2003 10:04 AM > I am using substitution variables in SQLPlus, but don't want to be prompted > for their values. I want to set them at the start of the script. Kind of > like a preprocessor directive. Is this possible? > > Erik > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Erik Williams > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David L Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Carlson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQLPlus Substitution Variables
If you pass arguments to your script, they would get reflected in the substitution variables defined in the script. test1.sql: * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com begin 500 begin ` end -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQLPlus Substitution Variables
I'm not sure this is what you wnat, but here is a short example of the use of DEFINE / UNDEFINE and && (Accept once but use many times inside script without re-prompt) -- define testtyp = Repair / select * from buy_type where buy_typ = '&&testtyp'; select buy_typ||'---' from buy_type where buy_typ = '&&testtyp'; undefine testtyp / HTH D. Phillips - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 15, 2003 10:04 AM > I am using substitution variables in SQLPlus, but don't want to be prompted > for their values. I want to set them at the start of the script. Kind of > like a preprocessor directive. Is this possible? > > Erik > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Erik Williams > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: David L Phillips INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQLPlus Substitution Variables
Something like this, perhaps.
accept var1 char prompt 'What grantee? ';
select *
from dba_sys_privs
where grantee = upper('&&var1');
> -Original Message-
> From: Erik Williams [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 15, 2003 10:04 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SQLPlus Substitution Variables
>
>
> I am using substitution variables in SQLPlus, but don't want
> to be prompted
> for their values. I want to set them at the start of the
> script. Kind of
> like a preprocessor directive. Is this possible?
>
> Erik
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Erik Williams
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Anderson, Brian
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: SQLPlus Substitution Variables
You can use the SQL*Plus commands DEFINE to provide values for variables and UNDEFINE to "reset" them. Karen Original Message: - Date: Wed, 15 Jan 2003 07:04:18 -0800 To: [EMAIL PROTECTED] I am using substitution variables in SQLPlus, but don't want to be prompted for their values. I want to set them at the start of the script. Kind of like a preprocessor directive. Is this possible? Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). mail2web - Check your email from the web at http://mail2web.com/ . -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQLPlus Substitution Variables
define myvar='whats up, doc?' select '&&myvar' from dba_objects; Erik Williams <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/15/2003 07:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:SQLPlus Substitution Variables I am using substitution variables in SQLPlus, but don't want to be prompted for their values. I want to set them at the start of the script. Kind of like a preprocessor directive. Is this possible? Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQLPlus Substitution Variables
Yes. SQL> define tname = t1 SQL> select * from &tname where rownum < 2; old 1: select * from &&tname where rownum < 2 new 1: select * from t1 where rownum < 2 EMPNUM SEQNUM LAST_BID -- -- -- 100 1 0 SQL> undefine tname SQL> / Enter value for tname:t1 old 1: select * from &tname where rownum < 2 new 1: select * from t1 where rownum < 2 EMPNUM SEQNUM LAST_BID -- -- -- 100 1 0 :) - Kirti -Original Message- Sent: Wednesday, January 15, 2003 9:04 AM To: Multiple recipients of list ORACLE-L I am using substitution variables in SQLPlus, but don't want to be prompted for their values. I want to set them at the start of the script. Kind of like a preprocessor directive. Is this possible? Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQLPlus Substitution Variables
That's not quite what he asked for... Erik, you're looking for the DEFINE command, and you don't really need to use '&&' either (unless you want to reuse the script without the DEFINE), e.g.: define datestamp = "sysdate - 1" select count(*) from logtab2003 where datestamp = to_char(&datestamp, 'mmdd'); select count(*) from errtab2003 where datestamp = to_char(&datestamp, 'mmdd'); quit - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 15, 2003 10:24 AM Use && instead of & and you should not get prompted if the variable has a value set. -Original Message- Sent: 15 January 2003 15:04 To: Multiple recipients of list ORACLE-L I am using substitution variables in SQLPlus, but don't want to be prompted for their values. I want to set them at the start of the script. Kind of like a preprocessor directive. Is this possible? Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: McBain, Neil SITI-ITDSEL314 INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Philip Douglass INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQLPlus Substitution Variables
Use && instead of & and you should not get prompted if the variable has a value set. -Original Message- Sent: 15 January 2003 15:04 To: Multiple recipients of list ORACLE-L I am using substitution variables in SQLPlus, but don't want to be prompted for their values. I want to set them at the start of the script. Kind of like a preprocessor directive. Is this possible? Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: McBain, Neil SITI-ITDSEL314 INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQLPlus Substitution Variables
SET SCAN OFF - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 15, 2003 3:04 PM > I am using substitution variables in SQLPlus, but don't want to be prompted > for their values. I want to set them at the start of the script. Kind of > like a preprocessor directive. Is this possible? > > Erik > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Erik Williams > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Imran Ashraf INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQLPlus Substitution Variables
You can explicitly assign the values in the script or you can use parameters and pass in the values. Make sure you use the && and not &. Dan Fink -Original Message- Sent: Wednesday, January 15, 2003 8:04 AM To: Multiple recipients of list ORACLE-L I am using substitution variables in SQLPlus, but don't want to be prompted for their values. I want to set them at the start of the script. Kind of like a preprocessor directive. Is this possible? Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fink, Dan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
