| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Resetting defined sql*plus variable in pl/sql block
I have an interesting problem. I am writing a series of Unix/SQL scripts that need to communicate with each other. That seems to be working pretty well, but I've run into an issue that I can't find an answer to. I have a SQL*Plus script that has embedded pl/sql code blocks in it. There are parameters passed to the SQL*Plus script that are referenced in the pl/sql code blocks. In one of the pl/sql code blocks, I need to be able to change the value of one of the parameters. I am running on Oracle 8.1.7.4, on Solaris 9. Here is the code:
set serveroutput on
def sql_job = &1
def run_stat = &2
def run_rest = &3
select 'Ethnic' from dual;
update rit_current_job_seq
set curr_job = rit_job_seq.nextval
where build_type = 'W';
commit;
begin
if '&run_stat' = 'R' and '&sql_job' = 'test_exit' or &run_rest = 0 then
insert into rit_run_statistics_test
select 'test_exit','S',sysdate, rit_step_seq.nextval, curr_job, 'W','test_exit.sh','test_exit.sql',null
from rit_current_job_seq
where build_type = 'W';
commit;
end if;
end;
/
whenever sqlerror exit failure;
begin
if '&run_stat' = 'R' and '&sql_job' = 'test_exit' or &run_rest = 0 then
rit_test_exit(0);
end if;
end;
/
whenever sqlerror continue;
begin
if '&sql_job' = 'test_exit' or &run_rest = 0 then
if '&run_stat' = 'R' then
insert into rit_run_statistics_test
select 'test_exit','E',sysdate, rit_step_seq.currval, curr_job, 'W','test_exit.sh','test_exit.sql',null
from rit_current_job_seq
where build_type = 'W';
commit;
end if;
def run_rest = 0; <== This is where I need to change the value of run_rest.
end if;
end;
/
exit;
The line I am having trouble with is in red. I have tried run_rest = 0;, run_rest := 0;, and &run_rest = 0. Nothing seems to work and I can't find anything in the Oracle documentation about this.
Thank you,
Scott Canaan (srcdco_at_rit.edu)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.
-- 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 Tue Jul 27 2004 - 15:29:05 CDT
 |
 |