FAQ
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.

Search Discussions

  • Wolfgang Breitling at Jun 4, 2008 at 6:27 pm
    Have a look at the sqlplus documentation, especially the column
    command with the new_value parameter.
    I do that a lot. E.g. have a skeleton script which queries the
    database version and then executes a version-specific script. Or RAC
    vs non-RAC.
    At 11:05 AM 6/4/2008, 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.
    Regards

    Wolfgang Breitling
    Centrex Consulting Corporation
    http://www.centrexcc.com
  • Daniel Fink at Jun 4, 2008 at 7:37 pm
    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
  • Jared Still at Jun 4, 2008 at 9:37 pm
    Here's a book you will find quite useful:

    http://www.amazon.com/Mastering-Oracle-SQL-Plus/dp/1590594487
    On Wed, Jun 4, 2008 at 10:05 AM, 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.
    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist

    --
    http://www.freelists.org/webpage/oracle-l
  • Jack van Zanen at Jun 5, 2008 at 12:15 am
    Hi,

    something like this?

    undefine s_next_script
    set echo off
    set termout off
    column xyz NOPRINT NEW_VALUE s_next_script
    select ' ' || as xyz from table
    where;
    set termout on
    @@&s_next_script

    Jack
    On 05/06/2008, 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.
    --
    J.A. van Zanen

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 4, '08 at 5:05p
activeJun 5, '08 at 12:15a
posts5
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase