Home » SQL & PL/SQL » SQL & PL/SQL » Re-use a variable..
Re-use a variable.. [message #196242] Wed, 04 October 2006 10:56 Go to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
How do I set a variable once, so that it can be used in multiple scripts?

I want to launch a long text file with a bunch of UPDATE statements....but I need the name of the table to change for each run. Rather than edit the text file each time, I'd like to set the name of the table first, and then have the sript run.

Thanks in advance!
Re: Re-use a variable.. [message #196243 is a reply to message #196242] Wed, 04 October 2006 10:59 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
What sort of script are you talking about ? A SQL*Plus script ?
Re: Re-use a variable.. [message #196244 is a reply to message #196243] Wed, 04 October 2006 11:05 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
Right.

Basically, I want to launch the text file from SQL+

SQL> @H:EMPFIX.TXT

And then be prompted to set a variable that is used in the UPDATE statements in the text file.

I actually don't care if I set the variable before launching the file, or if I'm prompted to set the variable after launching the file.

I just want to set a variable that is used in multiple UPDATE statements in a text file that I'm launching from SQL+.

Thanks for your help.....
Re: Re-use a variable.. [message #196250 is a reply to message #196244] Wed, 04 October 2006 11:54 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
You can pass parameters to a sql script using the &1 command.

For example if the contents of a file say test.sql is the following  where &1 is the variable that you are 
supposed to pass as a parameter to the file

update table1
   set col1 = &1;
   
update table2
   set col1 = &1;

update table3
   set col1 = &1;

And then execute the file as 
@test.sql 234

234 will be substituted in the place of &1. But in this case you can not pass tablenames as
 parameters to the  file.

Re: Re-use a variable.. [message #196254 is a reply to message #196250] Wed, 04 October 2006 12:04 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
Why not? That blows....
Re: Re-use a variable.. [message #196274 is a reply to message #196254] Wed, 04 October 2006 13:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You can use SQL*Plus substitution variables for table names. You can undefine the variable at the beginning of the script and use the double ampersand (&&) if you do not want to be prompted for the value again, until the script is rerun. Please see the demonstration below.


SCOTT@10gXE> UNDEFINE table_name
SCOTT@10gXE> SELECT * FROM &&table_name
  2  /
Enter value for table_name: dept
old   1: SELECT * FROM &&table_name
new   1: SELECT * FROM dept

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 PERSONNEL      DALLAS

SCOTT@10gXE> UPDATE &&table_name
  2  SET    loc = 'NOWHERE'
  3  /
old   1: UPDATE &&table_name
new   1: UPDATE dept

5 rows updated.

SCOTT@10gXE> SELECT * FROM &&table_name
  2  /
old   1: SELECT * FROM &&table_name
new   1: SELECT * FROM dept

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NOWHERE
        20 RESEARCH       NOWHERE
        30 SALES          NOWHERE
        40 OPERATIONS     NOWHERE
        50 PERSONNEL      NOWHERE

SCOTT@10gXE> ROLLBACK
  2  /

Rollback complete.

SCOTT@10gXE> SELECT * FROM &&table_name
  2  /
old   1: SELECT * FROM &&table_name
new   1: SELECT * FROM dept

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 PERSONNEL      DALLAS

SCOTT@10gXE>



Re: Re-use a variable.. [message #196275 is a reply to message #196242] Wed, 04 October 2006 13:26 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
Thanks Barbara I was not aware that we can substitute table names as well.
Re: Re-use a variable.. [message #196298 is a reply to message #196275] Wed, 04 October 2006 23:05 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Four ways to set a substitution variable:
  • DEFINE command
  • ACCEPT command to prompt for a value
  • Selected from a SQL statement using the COLUMN ... NEW_VALUE syntax
  • Failing all of the above, SQL*Plus will prompt for the value of an unset variable. If the variable is used with &&, the value will be preserved and used with later commands. With &, an unset variable will not retain its value - further commands will re-prompt.


Ross Leishman
Previous Topic: creating workflow
Next Topic: Database Trigger
Goto Forum:
  


Current Time: Sat Dec 10 20:53:58 CST 2016

Total time taken to generate the page: 0.17259 seconds