Using views inside the User Defined Function 1

[henryz]

Hi,

Does anybody have experience using views inside a function in Oracle? Technically it should be fine. but I had problem with the following simple view:


CREATE OR REPLACE FUNCTION MetaData(Id NUMBER) RETURN VARCHAR2 AS

infotypes VARCHAR2(10000);
infovalue VARCHAR2(255);

CURSOR infotype_cursor IS
SELECT s.InfoType
FROM vwsensitives s
WHERE s.ID = Id;

BEGIN
OPEN infotype_cursor;
LOOP
FETCH infotype_cursor INTO infovalue;
EXIT WHEN infotype_cursor%notfound;
infotypes := infotypes || ',' || infovalue;
END LOOP;
CLOSE infotype_cursor;
RETURN substr(infotypes,2);
END;
/

For some reason, the function returns the same result even the parameters are different.
such as :
select MetaData('11890') from dual
select MetaData('10890') from dual

The above queries return the same data.

If run the views directly, such as the following:
SELECT s.InfoType
FROM vwsensitives s
WHERE s.ID = '10890'

Union
SELECT s.InfoType
FROM vwsensitives s
WHERE s.ID = '11890'

It returns with different data.

Does Oracle have caching when query views inside the function? Are there any commands I can use to make the function work properly?

Thanks in advance,
Henry

 

[BJCooperIT]

First thing to try is changing your naming conventions:

CREATE OR REPLACE FUNCTION MetaData([COLOR=red]P_[/color]Id NUMBER) RETURN VARCHAR2 AS
 
 infotypes VARCHAR2(10000);
 infovalue VARCHAR2(255);
 
 CURSOR infotype_cursor IS
  SELECT s.InfoType
  FROM vwsensitives s
  WHERE s.ID = [COLOR=red]P_[/color]Id;
 
 BEGIN
  OPEN infotype_cursor;
  LOOP
   FETCH infotype_cursor INTO infovalue;
   EXIT WHEN infotype_cursor%notfound;
   infotypes := infotypes || ',' || infovalue;
  END LOOP;
  CLOSE infotype_cursor;
 RETURN substr(infotypes,2);
END;
/

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[henryz]

Thanks. Tried the above naming convention, it did not make difference.

 

[henryz]

Sorry BJ, I double checked again, and yes, you are right, this is related to the naming convention. I change the parameter name to something else as you suggested, and it worked. Thanks a lot.