Easy SQL*Plus question (I think)

Easy SQL*Plus question (I think)

Post by Anne Hanso » Sun, 31 Dec 1899 09:00:00



Is there a simple way to set up the following SQL statement so I'm not
prompted 3 times for the &chaintab substitution variable? I've read the
Oracle online documentation on substitution variables and I bet someone
in this newgroup probably can answer my question a lot more clearly.

CREATE TABLE TMPP_&chaintab
   AS SELECT * FROM &chaintab
 WHERE ROWID IN
    (SELECT head_rowid
  FROM chained_rows
     WHERE table_name = '&chaintab');

The backdrop of my question: I am creating a script that will be used on
multiple tables with chained rows, in which I will copy these rows to a
temp table, delete them from the source table and then re-insert into
source table. So ideally I'd like to input the &chaintab variable or
something similar once and then have it used throughout the script in
various SQL and/or PL/SQL statements. Any suggestions for automating
this process also are appreciated!

Thanks,

Anne

 
 
 

Easy SQL*Plus question (I think)

Post by Michael Joos » Sun, 31 Dec 1899 09:00:00


Hi Anne,


> Is there a simple way to set up the following SQL statement so I'm not
> prompted 3 times for the &chaintab substitution variable?

I think that if you double the '&' for any than the first occurence of
the formal parameter, you will not be asked again. Also there is a
command like SET VERIFY OFF that may be necessary at front.

cheers,
Michael

 
 
 

Easy SQL*Plus question (I think)

Post by Yosi Greenfiel » Sun, 31 Dec 1899 09:00:00


If you use a &&chaintab the first time you reference it (instead of a single
&) SQLPlus will store the value until you UNDEFINE it. Or, for more
flexibility, use ACCEPT (look it up, I can never get it's syntax
straight...)

HTH,

Yosi


> Is there a simple way to set up the following SQL statement so I'm not
> prompted 3 times for the &chaintab substitution variable? I've read the
> Oracle online documentation on substitution variables and I bet someone
> in this newgroup probably can answer my question a lot more clearly.

> CREATE TABLE TMPP_&chaintab
>    AS SELECT * FROM &chaintab
>  WHERE ROWID IN
>     (SELECT head_rowid
>   FROM chained_rows
>      WHERE table_name = '&chaintab');

> The backdrop of my question: I am creating a script that will be used on
> multiple tables with chained rows, in which I will copy these rows to a
> temp table, delete them from the source table and then re-insert into
> source table. So ideally I'd like to input the &chaintab variable or
> something similar once and then have it used throughout the script in
> various SQL and/or PL/SQL statements. Any suggestions for automating
> this process also are appreciated!

> Thanks,

> Anne

 
 
 

Easy SQL*Plus question (I think)

Post by Tim Lang » Sun, 31 Dec 1899 09:00:00


Yes, use two ampersands instead of one.  It will only prompt you once.
Also, the variable will not be deleted after execution, so if you run
any query using the variable, you will not be prompted.
Tim

> Is there a simple way to set up the following SQL statement so I'm not
> prompted 3 times for the &chaintab substitution variable? I've read the
> Oracle online documentation on substitution variables and I bet someone
> in this newgroup probably can answer my question a lot more clearly.

> CREATE TABLE TMPP_&chaintab
>    AS SELECT * FROM &chaintab
>  WHERE ROWID IN
>     (SELECT head_rowid
>   FROM chained_rows
>      WHERE table_name = '&chaintab');

> The backdrop of my question: I am creating a script that will be used on
> multiple tables with chained rows, in which I will copy these rows to a
> temp table, delete them from the source table and then re-insert into
> source table. So ideally I'd like to input the &chaintab variable or
> something similar once and then have it used throughout the script in
> various SQL and/or PL/SQL statements. Any suggestions for automating
> this process also are appreciated!

> Thanks,

> Anne

 
 
 

Easy SQL*Plus question (I think)

Post by Andreas K?ste » Sun, 31 Dec 1899 09:00:00


You can start with

ACCEPT chaintab -
PROMPT "Tablename ? "
...

Andreas K?ster

Anne Hanson schrieb:

Quote:> Is there a simple way to set up the following SQL statement so I'm not
> prompted 3 times for the &chaintab substitution variable? I've read the
> Oracle online documentation on substitution variables and I bet someone
> in this newgroup probably can answer my question a lot more clearly.

> CREATE TABLE TMPP_&chaintab
>    AS SELECT * FROM &chaintab
>  WHERE ROWID IN
>     (SELECT head_rowid
>   FROM chained_rows
>      WHERE table_name = '&chaintab');

> The backdrop of my question: I am creating a script that will be used on
> multiple tables with chained rows, in which I will copy these rows to a
> temp table, delete them from the source table and then re-insert into
> source table. So ideally I'd like to input the &chaintab variable or
> something similar once and then have it used throughout the script in
> various SQL and/or PL/SQL statements. Any suggestions for automating
> this process also are appreciated!

> Thanks,

> Anne

 
 
 

Easy SQL*Plus question (I think)

Post by Jason Judg » Sun, 31 Dec 1899 09:00:00


Yes - when you want to be prompted for a substitution variable use an
ampersand:

    &var1

If you want to reuse this value in another instance of this variable then
use a double ampersand:

    &&var1

The second example will not prompt, but will reuse the value already
prompted for.

An alternative is to assign the prompted value to a variable then use the
variable, so in PL/SQL you may have:

DECLARE
    var1 VARCHAR2(60) := '&var1';
BEGIN
    dbms_output.put_line('Value is ' || var1);
END;

You can also set these variables up without having to prompt:

SET var1 = 'a value'

(check the SQL*Plus manual for the format on this one). I find this very
useful when writing scripts that contain many parts (e.g. DDL, DML, SQL
blocks in one or more files) and can set global variables such as user
names, database names and passwords once at the top then reference them in
many places as &&variable.

Regards,

Jason Judge

 
 
 

1. Easy (I think) SQL Statement question

    In a query (it could also be a stored proc if necessary) I need to have
a column output as the result of an expression or 0 if the expression is
less than 0. Okay, well that was really confusing but if I was going to
write the query using Access it might look like this:

        SELECT IIf(([Field1] - [Field2]) < 0, 0 ,([Field1] - [Field2])) AS
Expr1
        FROM Table1;

    Can someone tell me how this can be done with T-SQL? Thanks for any
help.

-Dan

2. ADABAS/NATURAL developers in New York Area

3. Newbie - Easy (I think) SQL Syntax Question, 3 table join

4. Removing NDF's and LDF's

5. Small and easy question (I think) :)

6. can CREATE REPORT be compiled into an EXE file?

7. Think this is an easy query question

8. Help with English Query !!

9. Stored procedure in select (easy question i think)

10. Easy ADO Question (I Think)

11. Easy question..I think

12. easy question...i think

13. Easy Forms Question I think...