CODEWITHSUNDEEP
oracle-databasedynamicplsqlruntime-error
Artha Wijendra
Artha Wijendra

Reputation: 191

Catch PL/SQL error PLS-00306 iside PL/SQL method

I am running a method (the method name is sent by the end user) using dynamic PL/SQL (ex: EXECUTE IMMEDIATE).

When the method signature does not match the error ORA-06550 is raised (PLS-00306 is also mentioned in the stack).

I need to raise a custom message when the method signatures does not match with the required signature.

So I catch ORA-06550 inside the PL/SQL exception block and raise an error. Only to notice that ORA-06550 is raised for any invalid PL/SQL code (including method signature mismatch)

My Questions

  1. What is the difference between ORA- type messages and PLS- types ones. Can't one catch PLS- type messages (ex: PLS-00306) like they do catch ORA- type ones (ex: ORA-06550). If possible how?
  2. If not possible how to catch signature mismatches? (apart from running a query against USER_ARGUMENTS)

Upvotes: 1

Views: 2237

Answers (2)

user272735
user272735

Reputation: 10648

PLS-errors are PL/SQL compiler's compilation errors and can't be directly caught run-time as they are wrapped with ORA-errors.

If the compilation error is triggered by static PL/SQL the unit under compilation is created as invalid and execution of the unit triggers PLS-00905 (wrapped with ORA-06550).

If the compilation error is triggered by dynamic PL/SQL the unit under compilation is created without errors as dynamic PL/SQL is not checked during compilation. Instead the error is raised run-time when the unit is executed and can be caught but only ORA-code, not PLS-code. If you have do something based on PLS-code process the error stack string:

create or replace function get_custom_error(p_pls_code in varchar2) return varchar2 is
begin
  return
    case p_pls_code
      when 'PLS-00201' then 'this is my custom error code'
      else 'unknown PLS error code'
    end;
end;
/
show errors

create or replace procedure foo is
  plsql_compilation_error exception;
  pragma exception_init(plsql_compilation_error, -6550);
  v_a number;
begin
  dbms_output.put_line('foo started');
  execute immediate 'begin bar; end;';
  dbms_output.put_line('foo ended normally');
exception
  when plsql_compilation_error then
    declare
      v_pls_error_code constant varchar2(20) := 
        regexp_substr(dbms_utility.format_error_stack,
                      '(PLS-[[:digit:]]+):', 1, 1, '', 1);
    begin
      dbms_output.put_line(get_custom_error(v_pls_error_code));
    end;
end;
/
show errors

Execution example: 

SQL> exec foo
foo started
this is my custom error code

PL/SQL procedure successfully completed.

SQL>

All Oracle error codes are listed and explained in Oracle Database Error Messages.

Upvotes: 2

Naeel Maqsudov
Naeel Maqsudov

Reputation: 1434

1) The difference between ORA and PLS is which engine raised the exception. Error on the top of error stack shows general error. Deeper errors provides more and more details. Like ORA-06550 happened because of PLS-00306

2) In any potentially dangerous place you should add BEGIN…EXCEPTION…END. To differentiate any ORA error you can declare exception in parent block and use PRAGMA directive to link it with error code.

begin
  …
  begin
    …
    execute immediate …
  exception
    when …
  end;
  …
end;

Upvotes: 0

Related Questions