This content has been marked as final. Show 6 replies
Wow, that's messy.
You use a ref cursor (unnecessarily) to bulk collect data into expensive PGA memory and then you want to pass that data back out of PGA memory into an SQL statement.
Why not just have your SQL statement query the data directly?
Why do you want to slow down your SQL query?
What is the real issue you are trying to solve?
At worst in such a scenario I would use a pipelined table function, so as to avoid querying data and storing it in PGA memory.
At best, I would step back and ask "why the hell am I writing a function to query the data?" and then either do it in SQL or give sufficient justification as to why a function was needed.
You haven't explained the actual issue that your code is trying to solve, you haven't given justification as to why you are using a function, and on top of that the 'unknown' issue is being resolved with appalling code that you are wanting us to miraculously fix when we don't know why you're doing it in the first place.
Mosaq wrote:this is normal behavior.
When I use the function get_team_members in sql query it gives error. ORA-06504:PL/SQL:Return type of Return set variables or query do not match.
your object defined differently than your return cursor def:
and your cursor defined :
i_view_type varchar2, i_sort_by IN NUMBER, i_page_rows IN NUMBER DEFAULT 20, i_current_page_no IN NUMBER )
open team_members_CUR for select 'A' view_type, emplid req_emplid, --varchar2(11) must be number (i_sort_by) emplid emplid, --varchar2(11) must be number (i_page_rows) full_name -- varchar2(100) must be number (i_current_page_no) from employee_vw;