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

From: Daniel Fink <>
Date: Wed, 04 Jun 2008 13:37:06 -0600
Message-ID: <>

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


    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

Oracle Blog

Lost Data?

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
Received on Wed Jun 04 2008 - 14:37:06 CDT

Original text of this message