Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Plus: procedural problem
You can pass values between sql*plus and PL/SQL, dependent on which
version of sql*plus you are on (I think it's 3.1 or higher is required).
To do this you use :-
variable p_number number
variable p_varchar varchar2(n) (n=1 to 2000)
in the pl/sql you reference the variable as :p_number etc (ie a bind variable)
number and varchar2 are currently the only datatypes available
===========================================================Subject: SQL Plus: procedural problem
I am in search of the proper product to handle a problem:
A sql script that is something like:
DECLARE
BEGIN
some pl/sql processing
EXCEPTION
insert of error codes into an error table
END;
....
more pl/sql processing
....
@purgrpts -- calling another sql script
....
exit
What I want to do is to look at the error table after the first pl/sql block finishes. If there are any errors logged I want the sql script to exit. So just after the "END;" statement there would be code something like:
select count(*) into errs from err where error_num = 12;
if errs>0 then
exit;
end if;
Of course, "exit" doesn't work this way in pl/sql. And there is no way to pass values from pl/sql to sql*plus. My question: what is the best product to handle something like this: oraperl, Pro*C, SQR (a reporting tool but it has a lot of other capabilities) or something else?
-- ========================================================================== = Ed Steinman What the Zen Buddhist said to the Computing Unit, School of Dentistry hot dog vendor: University of Michigan Ann Arbor, MI 48109-1078 "Make me one with everything." voice: 313-764-9850 FAX: 313-647-4024 e-mail: esteinma_at_umich.eduReceived on Mon Feb 24 1997 - 00:00:00 CST