ORACLE: Parameter reference in WHERE doesn't work

Question

Hello, I have created a simple static function in oracle 10g to get the reference of an object based on his pk.

STATIC FUNCTION getRef(nome IN VARCHAR2) 
RETURN REF folder_typ IS

  fl_r REF folder_typ := null;

BEGIN

   SELECT REF(fl) 
     INTO fl_r
     FROM folder_tab fl
    WHERE fl.nome = nome;

   RETURN fl_r;

END getRef;

This gives me an error because it could't fetch a row. If insted of WHERE fl.nome = nome; I write WHERE fl.nome = 'folder1'; -- it works.

I think im not using the parameter in the right way. How can I use it?

Answer 1

This is a scoping problem.

Here is my version of your set-up, extrapolated from what you posted. Note that the parameter name is different from the attribute name.

create or replace type folder_t as object
(name varchar2(128))
/

create table folders of folder_t
/

create or replace function getRef
    (nome in varchar2)
    return ref folder_t
is
    fl_r REF folder_t;
begin
    select ref(fl)
    into fl_r
    from folders fl
    where fl.name = nome;

    return fl_r;
end  getRef;
/

As you can see, given this test data ...

SQL> insert into folders values (folder_t('temp'))
  2  /

1 row created.

SQL> insert into folders values (folder_t('work'))
  2  /

1 row created.

SQL>

... I can query the two REFs:

SQL> select getRef('temp') from dual
  2  /

GETREF('TEMP')
--------------------------------------------------------------------------------
00002802091051432318864AF594741916D743E1291CF597373A4F4D7A93F159DA53A73FC0010372
2D0000


SQL> select getRef('work') from dual
  2  /

GETREF('WORK')
--------------------------------------------------------------------------------
0000280209F31778C18D5740FBA0CB90929E1B6FBD1CF597373A4F4D7A93F159DA53A73FC0010372
2D0001


SQL>

But, if I change the function declaration so the parameter name is the same as the attribute name, this happens:

SQL> create or replace function getRef
  2      (name in varchar2)
  3      return ref folder_t
  4  is
  5      fl_r REF folder_t;
  6  begin
  7      select ref(fl)
  8      into fl_r
  9      from folders fl
 10      where fl.name = name;
 11
 12      return fl_r;
 13  end  getRef;
 14  /

Function created.

SQL> select getRef('temp') from dual
  2  /

GETREF('TEMP')
------------------------------------------------------------


SQL>

The SQL engine applies scope from the table outwards. Because the unqualified NAME matches a column on the table it doesn't check to see that whether there is a parameter of that name too. This is why it is a good idea to give the parameters a distinct name. Persoanlly I favour the practice of prefixing parameters with a P_, so there's no chance of the paremeter clashing with local variables or object names.

Answer 2

Best practice is to always provide the scope for all variables, not just for those from table aliases:

FUNCTION getRef(nome IN VARCHAR2) 
RETURN REF folder_typ IS
  fl_r REF folder_typ := null;
BEGIN

   SELECT REF(fl) 
     INTO fl_r
     FROM folder_tab fl
    WHERE fl.nome = getRef.nome;

   RETURN fl_r;
END getRef;