Return to _bug.html CVS log

Up to [parser3project] / parser3 / www / htdocs

checked: oracle: various ways of returning error from stored proc.
bad: no way of knowing which exactly user-defined exception were thrown.
good: raise_application_error can return error message number and string FINE
good: PRAGMA EXCEPTION_INIT works good too

@main[]
^connect[oracle://mts_imprimatur2:imp2@mts]{
#    ^in[]
#	^out[]
#	^inout[]
#	^ex[]
#	^ex2[]
#	^ex3[]
	^ex4[]
	OK
}

@inp[]
create table pif(a integer);
create or replace procedure paf(a in integer) as begin
insert into pif (a) values (:a);
end;
/

@in[]
#    ^void:sql{create table paf(a clob)}
#    ^void:sql{insert into paf (a) values (/**a**/'a')}
#    ^void:sql{insert into paf (a) values (:unk)}
	^void:sql{delete from pif}
	^void:sql{call paf(:a)}[
		$.bind[
			$.a[2]
		]
	]
	^int:sql{select count(*) from pif}
	"^string:sql{select a from pif}"

#	^string:sql{select a from paf}

@outp[]
create or replace procedure pafm(ai in integer,ao out integer) as begin
ao:=ai;
end;
/
@out[]
    $vars[
    	$.ai(2222)
    	$.ao[sad]
    ]
	^void:sql{call pafm(:ai,:ao)}[
		$.bind[$vars]
	]
	"$vars.ai"
	"$vars.ao"

#	^string:sql{select a from paf}

@inoutp[]
create or replace procedure pafio(aio in out integer) as begin
aio:=aio*2;
end;
/
@inout[]
    $vars[
    	$.aio(2222)
    ]
	before "$vars.aio"<br>
	^void:sql{call pafio(:aio)}[
		$.bind[$vars]
	]
	after "$vars.aio"

#	^string:sql{select a from paf}

@exp[]
create or replace procedure pafex is
begin
DECLARE
   out_of_stock   EXCEPTION;
begin
 raise out_of_stock;
 end;
end;
/
@ex[]
^void:sql{
begin 
pafex^; 
end^;
}
@ex[]
^void:sql{call pafex()}

@exp2[]
create or replace procedure pafex2 is
begin
 raise ZERO_DIVIDE;
end;
/
@ex2[]
^void:sql{call pafex2()}


@exp3[]
create or replace procedure pafex3 is
begin
DECLARE
   out_of_stock   EXCEPTION;
   PRAGMA EXCEPTION_INIT(out_of_stock, -333);
begin
 raise out_of_stock;
 end;
end;
/
@ex3[]
^void:sql{call pafex3()}


@exp4[]
create or replace procedure pafex4 is
begin
	raise_application_error(-20444, 'Salary is missing');
end;
/
@ex4[]
^void:sql{call pafex4()}