begin
raise too_many_rows;
exception
when others then
dbms_output.put_line(sqlerrm);
end; Statement processed.
ORA-01422: exact fetch returns more than requested number of rows
declare
l_codes dbms_sql.number_table := dbms_sql.number_table(
0 => -1,
1 => 100,
2 => - 1403,
3 => - 1422,
4 => 1422,
5 => 1,
6 => -20000,
7 => -21000,
8 => -21002,
9 => -50000
);
begin
for indx in l_codes.first..l_codes.last loop
begin
dbms_output.put_line('sqlerrm('||l_codes(indx)||') => ' || sqlerrm(l_codes(indx)));
exception
when others then
dbms_output.put_line('Unable to call sqlerrm with code ' || l_codes(indx));
end;
end loop;
end; Statement processed.
sqlerrm(-1) => ORA-00001: unique constraint (.) violated
sqlerrm(100) => ORA-01403: no data found
sqlerrm(-1403) => ORA-01403: no data found
sqlerrm(-1422) => ORA-01422: exact fetch returns more than requested number of rows
sqlerrm(1422) => -1422: non-ORACLE exception
sqlerrm(1) => User-Defined Exception
sqlerrm(-20000) => ORA-20000:
sqlerrm(-21000) => ORA-21000: error number argument to raise_application_error of is out of range
sqlerrm(-21002) => ORA-21002: Message 21002 not found; product=RDBMS; facility=ORA
sqlerrm(-50000) => ORA-50000: Message 50000 not found; product=RDBMS; facility=ORA
declare
l_code integer := -21000;
begin
loop
dbms_output.put_line('sqlerrm('||l_code||') => ' || sqlerrm(l_code));
exit when sqlerrm(l_code) not like
'%error number argument to raise_application_error%out of range%';
exit when l_code < -30000;
l_code := l_code - 1;
end loop;
end; Statement processed.
sqlerrm(-21000) => ORA-21000: error number argument to raise_application_error of is out of range
sqlerrm(-21001) => ORA-21001: error number argument to raise_system_error of is out of range
create or replace procedure show_errors is
begin
dbms_output.put_line('-------SQLERRM-------------');
dbms_output.put_line(length(sqlerrm));
dbms_output.put_line(sqlerrm);
dbms_output.put_line('-------FORMAT_ERROR_STACK--');
dbms_output.put_line(length(dbms_utility.format_error_stack));
dbms_output.put_line(dbms_utility.format_error_stack);
end; Procedure created.
declare
procedure proc1 is
begin
raise no_data_found;
end;
procedure proc2 is
begin
proc1;
exception
when others then
raise value_error;
end;
procedure proc3 is
begin
proc2;
exception
when others then
raise program_error;
end;
begin
proc3;
exception
when others then
show_errors;
end; Statement processed.
-------SQLERRM-------------
32
ORA-06501: PL/SQL: program error
-------FORMAT_ERROR_STACK--
208
ORA-06501: PL/SQL: program error ORA-06512: at line 20 ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 12 ORA-01403: no data found ORA-06512: at line 4 ORA-06512: at line 9 ORA-06512: at line 17