| 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
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 email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and 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> are referenced in the pl/sql code blocks. In one of the pl/sql code SC> blocks, I need to be able to change the value of one of the parameters. SC> I am running on Oracle 8.1.7.4, on Solaris 9. Here is the code:
SC> set serveroutput on
SC> def sql_job = &1
SC> def run_stat = &2
SC> def run_rest = &3
SC> select 'Ethnic' from dual;
SC> update rit_current_job_seq
SC> set curr_job = rit_job_seq.nextval
SC> where build_type = 'W';
SC> commit;
SC> begin
SC> if '&run_stat' = 'R' and '&sql_job' = 'test_exit' or &run_rest = 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 = 'W';
SC> commit;
SC> end if;
SC> end;
SC> /
SC> whenever sqlerror exit failure;
SC> begin
SC> if '&run_stat' = 'R' and '&sql_job' = 'test_exit' or &run_rest = 0 then
SC> rit_test_exit(0);
SC> end if;
SC> end;
SC> /
SC> whenever sqlerror continue;
SC> begin
SC> if '&sql_job' = 'test_exit' or &run_rest = 0 then
SC> if '&run_stat' = '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 = 'W';
SC> commit;
SC> end if;
SC> def run_rest = 0; <== This is where I need to change the value of SC> run_rest.
SC> end if;
SC> end;
SC> /
SC> exit;
SC> The line I am having trouble with is in red. I have tried run_rest = SC> 0;, run_rest := 0;, and &run_rest = 0. Nothing seems to work and I SC> can't find anything in the Oracle documentation about this.
SC> Thank you,
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.
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
----------------------------------------------------------------
 |
 |