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@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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: