RE: Resetting defined sql*plus variable in pl/sql block
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Jul 2004 16:42:52 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Scott Canaan
Sent: Tuesday, July 27, 2004 4:32 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxx)
(585) 475-7886
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
