| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Resetting defined sql*plus variable in pl/sql block
I've used Jonathan's technique a lot too with much success.
If you don't want the query results to show up in SQLPlus, tell it:
column COLNAME new_value VARNAME noprint select :bindVar COLNAME from USER_USERS;
The value of :bindVar will be "defined" into &&VARNAME
...Rudy
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On
Behalf Of Jonathan Gennick
Sent: Tuesday, July 27, 2004 10:24 PM
To: Scott Canaan
Cc: oracle-l_at_freelists.org
Subject: Re: Resetting defined sql*plus variable in pl/sql block
Hello Scott,
You can change the value of a SQL*Plus bind variable from within a PL/SQL block. Then, after the block has ended, you can use a SELECT statement to get the bind variable value into your substitution variable. Something along the lines of:
COLUMN x NEW_VALUE sub_var
SELECT :bind_variable x FROM dual;
HTH Best regards,
Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
Join the Oracle-article list and receive one
article on Oracle technologies per month by=20
email. To join, visit
http://five.pairlist.net/mailman/listinfo/oracle-article,=20
or send email to Oracle-article-request_at_gennick.com and=20
include the word "subscribe" in either the subject or body.
Tuesday, July 27, 2004, 4:32:04 PM, Scott Canaan (srcdco_at_rit.edu) wrote:
SC> I have an interesting problem. I am writing a series of Unix/SQL SC> scripts that need to communicate with each other. That seems to be SC> working pretty well, but I've run into an issue that I can't find an SC> answer to. I have a SQL*Plus script that has embedded pl/sql code SC> blocks in it. There are parameters passed to the SQL*Plus script =that
SC> set serveroutput on
SC> def sql_job =3D &1
SC> def run_stat =3D &2
SC> def run_rest =3D &3
SC> select 'Ethnic' from dual;
SC> update rit_current_job_seq
SC> set curr_job =3D rit_job_seq.nextval
SC> where build_type =3D 'W';
SC> commit;
SC> begin
SC> if '&run_stat' =3D 'R' and '&sql_job' =3D 'test_exit' or &run_rest =
=3D 0 then
SC> insert into rit_run_statistics_test
SC> select 'test_exit','S',sysdate, rit_step_seq.nextval, curr_job, SC> 'W','test_exit.sh','test_exit.sql',null
SC> from rit_current_job_seq
SC> where build_type =3D 'W';
SC> commit;
SC> end if;
SC> end;
SC> /
=20
SC> whenever sqlerror exit failure;
SC> begin
SC> if '&run_stat' =3D 'R' and '&sql_job' =3D 'test_exit' or &run_rest =
=3D 0 then
SC> rit_test_exit(0);
SC> end if;
SC> end;
SC> /
SC> whenever sqlerror continue;
=20
SC> begin
SC> if '&sql_job' =3D 'test_exit' or &run_rest =3D 0 then
SC> if '&run_stat' =3D 'R' then
SC> insert into rit_run_statistics_test
SC> select 'test_exit','E',sysdate, rit_step_seq.currval, SC> curr_job, 'W','test_exit.sh','test_exit.sql',null
SC> from rit_current_job_seq
SC> where build_type =3D 'W';
SC> commit;
SC> end if;
SC> def run_rest =3D 0; <=3D=3D This is where I need to change the =
value of
SC> run_rest.
SC> end if;
SC> end;
SC> /
SC> exit;
=20
SC> The line I am having trouble with is in red. I have tried run_rest =
=3D
SC> 0;, run_rest :=3D 0;, and &run_rest =3D 0. Nothing seems to work =
and I
SC> can't find anything in the Oracle documentation about this.
=20
SC> Thank you,
=20
SC> Scott Canaan (srcdco_at_rit.edu)
SC> (585) 475-7886
SC> "Life is like a sewer, what you get out of it depends on what you =
put
SC> into it." - Tom Lehrer.
=20
SC> ---------------------------------------------------------------- SC> Please see the official ORACLE-L FAQ: http://www.orafaq.com SC> ---------------------------------------------------------------- SC> To unsubscribe send email to: oracle-l-request_at_freelists.org SC> put 'unsubscribe' in the subject line. SC> -- SC> Archives are at http://www.freelists.org/archives/oracle-l/SC> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
SC> ----------------------------------------------------------------- ----------------------------------------------------------------Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Jul 28 2004 - 09:53:41 CDT
 |
 |