| 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
You cannot change the value of a substitution variable because it is just a
string constant substituted by SQLPlus into SQL or pl/sql before passing
each to their respective processing engines.
You will need to use a variable.
set echo on
drop procedure testproc;
create or replace procedure testproc(
p_var1 out integer
,p_var2 out integer
) is
begin
p_var1 := 1;
p_var2 := 2;
end testproc;
/
variable var1 number
variable var2 number
execute testproc(:var1, :var2);
print var1
print var2
You can pass a &label in and get back a :variable. The real question is what are you going to do with this value next.
HTH -- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Scott Canaan
Sent: Tuesday, July 27, 2004 4:32 PM
To: oracle-l_at_freelists.org
Subject: 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
-- 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:40:32 CDT
 |
 |