Re: Easy SQL*Plus question (I think)

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

Anne Hanson wrote:

> 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
Received on Thu Oct 14 1999 - 13:51:49 CDT