Skip to Main Content
  • Questions

  • PLSQL nested procedure hides resolution of an outer procedure

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Eddy.

Asked: May 12, 2022 - 7:24 am UTC

Last updated: May 23, 2022 - 3:32 am UTC

Version: 19c

Viewed 1000+ times

You Asked 

declare
  type t1 is record (
    f1 number
  );
  
  type t2 is record (
    f1 number
  );
  
  v1 t1;
  v2 t2;
  
  procedure q(p1 in t1)
  is
  begin
    null;
  end q;

  
  procedure p(p1 in t1, p2 in t2)
  is
    procedure q(p2 in t2)
    is
    begin
      null;
    end q; 


  begin
    q(p1);
    q(p2);
  end p;

begin

  p(v1, v2);

end;
/



Procedure p has a nested procedure with the same name of an outer procedure (q).

PLSQL cannot resolve the call to q, raising the error PLS-00306: wrong number or types of arguments in call to 'Q'.

If I move the nested procedure in an outer scope, the block runs ok:


declare
  type t1 is record (
    f1 number
  );
  
  type t2 is record (
    f1 number
  );
  
  v1 t1;
  v2 t2;
  
  procedure q(p1 in t1)
  is
  begin
    null;
  end q;

  procedure q(p2 in t2)
  is
  begin
    null;
  end q; 

  
  procedure p(p1 in t1, p2 in t2)
  is

  begin
    q(p1);
    q(p2);
  end p;

begin

  p(v1, v2);

end;
/



It seems that the local procedure q(t2) hides the outer q(t1), even if they have different signatures.


Are there any reasons for that behaviour?

Thanks
Eddy

and Chris said...

That's just how name resolution in PL/SQL works. If you have a local procedure with the same name as an outer proc, the inner always takes priority.

Depending on what you're trying to do there are a couple of solutions:

- Overload the declaration (as in your second example)
- Fully qualify references, adding block labels if necessary.

For example, you can get the first example to work by labelling the outer block. Then qualifying the reference to the call to Q that's to use the outer declaration:

<<outer_block>>
declare
  type t1 is record (
    f1 number
  );
  
  type t2 is record (
    f1 number
  );
  
  v1 t1;
  v2 t2;
  
  procedure q(p1 in t1)
  is
  begin
    dbms_output.put_line ( 'outer' );
  end q;

  procedure p(p1 in t1, p2 in t2)
  is
    procedure q(p2 in t2)
    is
    begin
      dbms_output.put_line ( 'inner' );
    end q; 

  begin
    outer_block.q(p1);
    p.q(p2);
  end p;

begin

  p(v1, v2);

end;
/

outer
inner


Which to go for depends on why exactly you're doing this. Personally, I dislike local procedures, so would tend to overloading or renaming one of the procedures.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Eddy Peron, May 12, 2022 - 2:51 pm UTC

Why does the compiler stop searching at the current scope if there is a local procedure?
(While when there is no local procedures it goes on and finds a suitable match in the outer scope.)


> If you have a local procedure with the same name as an outer proc, the inner always takes priority.
Ok, but I would add "only as long as they have the same parameter names/types".

I asked the question because I expected the compiler would accept my firt code.
Since it doesn't, I would like you could give me some justifications for such a counterintuitive behaviour (for me).

Thanks

Chris Saxon
May 13, 2022 - 8:37 am UTC

Because that's how the language was designed!

If a subunit redeclares a global identifier, then inside the subunit, both identifiers are in scope, but only the local identifier is visible.

https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-language-fundamentals.html#GUID-2FC17012-FC99-4614-90DD-ADC99F2EDBE9

So local declarations always override those in the outer scope.

This avoids outer capture problems, where an identifier in local scope now resolves to one in the outer scope - e.g. if you declare a new object in the outer scope.

Because the local scope always has precedence, the only ways to access the outer object are to remove the inner declaration or fully qualify the reference to the outer object.

It also prevents possible problems if you use the same name for different objects in the inner and outer scope - e.g. a variable and a procedure:

<<outer_block>>
declare
  same_name integer := 42;
  
  procedure p as
    procedure same_name as
    begin
      dbms_output.put_line ( 'proc' );
    end same_name;
  begin
    same_name;
    dbms_output.put_line ( outer_block.same_name );
  end p;
begin
  p ();
end;
/

proc
42

Eddy Peron, May 17, 2022 - 8:45 am UTC

I think the documentation is not 100% clear on that point.

A couple of lines below it reads:
You cannot declare the same identifier twice in the same PL/SQL unit. If you do, an error occurs when you reference the duplicate identifier.


But I can declare and use a procedure twice in the same PL/SQL unit (overloading).
So in that context (overloading), "indentifier" has to be understood as procedure name plus parameter types/names.

My question is: Why PL/SQL doesn't allow to use procedure overloading across scope levels?
(While it allows overloading at the same scope level.)


Thanks

Connor McDonald
May 23, 2022 - 3:32 am UTC

That's just the way it works.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library