Home » SQL & PL/SQL » SQL & PL/SQL » Bypass package function with name conflict (Oracle 10gR2)  () 1 Vote
icon8.gif  Bypass package function with name conflict [message #439601] Mon, 18 January 2010 10:42 Go to next message
brlav35
Messages: 4
Registered: September 2009
Location: QUEBEC
Junior Member
Hello,

I have to maintain a package having 2 functions named: true and false. As you're seeing these two functions are named with the same name of TRUE and FALSE boolean values.

It was compiling successfully until I needed to add a function that return a real boolean. When I do a RETURN {TRUE|FALSE}, the compilation fails with the message:
PLS-00382: expression is of wrong type

Because the pl/sql boolean that I try to return takes one of the functions pkg.true or pkg.false, that is not of boolean type but char.

How to tell oracle that wanted true and false are these of PL/SQL rather than the local functions?

Thanks
Bruno
Re: Bypass package function with name conflict [message #439602 is a reply to message #439601] Mon, 18 January 2010 11:01 Go to previous messageGo to next message
BlackSwan
Messages: 24914
Registered: January 2009
Senior Member
>How to tell oracle that wanted true and false are these of PL/SQL rather than the local functions?
Do NOT use RESERVE words for object names!
Re: Bypass package function with name conflict [message #439603 is a reply to message #439601] Mon, 18 January 2010 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63814
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create or replace package pkg is
  2    function true return boolean;
  3    function false return boolean;
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg is
  2    function true return boolean is begin return standard.true; end;
  3    function false return boolean is begin return standard.false; end;
  4  end;
  5  /

Package body created.

SQL> begin
  2    if pkg.true then dbms_output.put_line('PKG.TRUE returns TRUE'); end if;
  3    if not pkg.false then dbms_output.put_line('PKG.FALSE returns FALSE'); end if;
  4  end;
  5  /
PKG.TRUE returns TRUE
PKG.FALSE returns FALSE

PL/SQL procedure successfully completed.

Regards
Michel
Re: Bypass package function with name conflict [message #439700 is a reply to message #439603] Tue, 19 January 2010 02:45 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The concept behind the example Michel posted is name resolution. Here's what the documentation has to say about this:
The Oracle documentation
During compilation, the PL/SQL compiler determines which objects are associated with each name in a PL/SQL subprogram. A name might refer to a local variable, a table, a package, a subprogram, a schema, and so on. When a subprogram is recompiled, that association might change if objects were created or deleted.

A declaration or definition in an inner scope can hide another in an outer scope.
...
More can be found here. Especially the part about capture is interesting.

And although Oracle can accept the usage of reserved words for qualifiers, I think BlackSwan is right. Don't use a reserved word as a name of your own object, parameter, ... .

MHE
Re: Bypass package function with name conflict [message #439855 is a reply to message #439700] Tue, 19 January 2010 14:08 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
I feel very strongly about this - get rid of those true & false function names - they should never have made it into the code in the first place. As your code gets bigger & more complex, you'll run into more & more issues by not respecting reserved words. Code formatters stop working properly, and in some case you won't be lucky enough to even see an error. Just because you can do a bad thing, it's never an excuse to do so...
SQL> create table bad_table ("null" varchar2(10), "USER" varchar2(10), "SYSDATE" varchar2(10));

Table created.

SQL> insert into bad_table values ('xxx', 'yyy', 'zzz');

1 row created.

SQL> select null, user, sysdate from bad_table;

N USER                           SYSDATE
- ------------------------------ ---------
  SCOTT                          19-JAN-10

SQL>



http://awads.net/wp/2007/01/10/what-does-reserved-y-really-mean/



[Updated on: Tue, 19 January 2010 14:09]

Report message to a moderator

Re: Bypass package function with name conflict [message #439868 is a reply to message #439855] Tue, 19 January 2010 15:27 Go to previous messageGo to next message
joy_division
Messages: 4618
Registered: February 2005
Location: East Coast USA
Senior Member
Great example by Andrew, again Wink
icon7.gif  Re: Bypass package function with name conflict [message #439869 is a reply to message #439602] Tue, 19 January 2010 15:32 Go to previous messageGo to next message
brlav35
Messages: 4
Registered: September 2009
Location: QUEBEC
Junior Member
thanks to Michel Cadot,

Be sure guys, it's the only place with reserved word.....

Thanks
Re: Bypass package function with name conflict [message #439906 is a reply to message #439855] Wed, 20 January 2010 01:21 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
BAD_TABLE shows another functionality: if you create a table whose column names are enclosed into double quotes, remember to use the same when selecting from that table:
SQL> select "null", "USER", "SYSDATE" from bad_table;

null       USER       SYSDATE
---------- ---------- ----------
xxx        yyy        zzz

SQL>

I'm a 100% sure that I'd forget that so - no, thank you.
Re: Bypass package function with name conflict [message #439910 is a reply to message #439906] Wed, 20 January 2010 01:23 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
./fa/1581/0/
sriram Smile
Previous Topic: display the first two node names from a variable
Next Topic: Multi rows convert into one row
Goto Forum:
  


Current Time: Fri Sep 30 14:03:25 CDT 2016

Total time taken to generate the page: 0.28275 seconds