Re: trying to use sqlplus to do a build and making it dynamic

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Wed, 04 Jun 2008 13:37:06 -0600
Message-ID: <4846EEE2.2040401@optimaldba.com>

You can use the sql*plus NEW_VALUE option for the COLUMN clause to populate a SQL*Plus variable.

    SQL> COLUMN ts_name NEW_VALUE ts_name NOPRINT     SQL> SELECT default_tablespace ts_name

      2  FROM dba_users
      3  WHERE username = 'DEMO'
      4  /

    SQL> SELECT '&ts_name' FROM dual;
    old 1: SELECT '&ts_name' FROM dual     new 1: SELECT 'DEMO' FROM dual

    'DEM

---

    DEMO Now that you have the ts_name variable populated, you can use it to call the script.

    @mytablescripts &ts_name

Word of warning - if the variable value can have spaces in it, you will need to enclose it in double quotes inside single quotes (' " variable name " ')

-- 
Daniel Fink

Oracle Performance, Diagnosis, Data Recovery and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.nl/introduction.htm


Rick Ricky wrote:


> I can do this with a shell or with any other programming language and 

> call sqlplus. I am trying to do this with just sqlplus scripts. I have 

> code to do a build where I create tables, users, etc... Some of these 

> can have some differences between databases (such as tablespaces) and 

> some other things. 

>

> I would prefer not to have to do:

>

> prompt please enter tablespace name

> accept tablespace

> @mytablescripts &tablespace

>

>

> I would like to use some form of configuration table. Is there anyway 

> to do the following. 

>

> have a configuration table with data in it

> query it in a script and put it in a variable. (this is easily done 

> with pl/sql)

> then pass that variable to a new script.

>

> I would need to query the data and put it in some form of sqlplus 

> variable that I can pass to another sqlplus script. 

> ------------------------------------------------------------------------

>

> No virus found in this incoming message.

> Checked by AVG. 

> Version: 7.5.524 / Virus Database: 269.24.6/1482 - Release Date: 6/4/2008 7:10 AM

>   




--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 04 2008 - 14:37:06 CDT