CODEWITHSUNDEEP
oracle-databaseplsql
MuhanadY
MuhanadY

Reputation: 730

Pl/SQL query a view in function

I have the function below 

CREATE OR REPLACE FUNCTION BUTCE_REPORT_Fun (birim_id IN VARCHAR2)
   RETURN sys_refcursor
IS
   retval sys_refcursor;
BEGIN
   OPEN retval FOR
      select * 
      from ifsapp.butce_gerceklesme 
      WHERE  budget_year = '2018' 
      AND USER_GROUP = birim_id ;

   RETURN retval;
END BUTCE_REPORT_Fun;

and am trying to execute the function this way 

SELECT * from table(IFSAPP.BUTCE_REPORT_FUN('3008'))

the line above generates this exception

ora-22905 cannot access rows from a non-nested table item

to keep in mind that ifsapp.butce_gerceklesme is a view (which I do not think that it matters). So how I can solve this. any help is appreciated. Actually, am trying to create a function that returns rows from the view above according to the parameters provided. so if I can achieve that in another way that would be better.

Upvotes: 0

Views: 1720

Answers (1)

APC
APC

Reputation: 146199

Ref Cursors are for use in program calls: they map to JDBC or ODBC ResultSet classes. They can't be used as an input to a table() call. Besides, there is no value in calling your function in SQL because you can simply execute the embedded query in SQL.

the main table is huge and the inner query assigned to USER_GROUP is selected every time

So maybe what you want is subquery factoring AKA the WITH clause?

with ug as (
   select con2.CODE_PART_VALUE 
   from IFSAPP.ACCOUNTING_ATTRIBUTE_CON2 con2 
   where COMPANY = 'XYZ' 
   and ATTRIBUTE = 'ABC' 
   and CODE_PART = 'J'
   and con2.ATTRIBUTE_VALUE=407 
   AND rownum = 1
)
select * 
from ifsapp.butce_gerceklesme t
     join ug on t.USER_GROUP = ug.CODE_PART_VALUE 
WHERE t.budget_year = '2018'

Tuning queries on StackOverflow is a mug's game, because there are so many things which might be responsible for sub-optimal performance. But as a rule of thumb you should try to tune the whole query. Encapsulating a part of it in PL/SQL is unlikely to improve response times, and indeed may degrade them.

Upvotes: 2

Related Questions