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

[Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>]

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
>

• References:
  • trying to use sqlplus to do a build and making it dynamic
    • From: Rick Ricky

Other related posts:

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

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

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

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

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