This content has been marked as final. Show 98 replies
The funcion has a bulk collect so i need to redesign the function to a inline view...and will let you know...
Just use the query from function and merge it with main query. You have already posted that SQL - is there some other code inside the function?
Yes it has code inside the function which call a view(Which i posted earlier).The code has bulk collect into a variable...just wondering how to use that in a sql...
Just use the query and put it in inline view. Is it possible to post the code for short time?
Edited by: Maran Viswarayar on Nov 18, 2009 10:36 PM
CREATE OR REPLACE FUNCTION ADMIN.find_record_dtl ( p_report_id IN NUMBER DEFAULT 0, p_pay_calendar_id IN VARCHAR2 DEFAULT '%', p_employee_id IN VARCHAR2 DEFAULT '%' ) RETURN tbl_pay_item_t AS r_tbl_pay_item_t tbl_pay_item_t; BEGIN SELECT pay_item_t(report_id, employee_id, employee_name, pay_calendar_id, data_name, data_desc, data_seq, display_name, section_seq, item_id, base_amt, input_amt, calculated_amt) BULK COLLECT INTO r_tbl_pay_item_t FROM ( SELECT DISTINCT * FROM list_record_dtl WHERE report_id = p_report_id AND pay_calendar_id LIKE p_pay_calendar_id AND employee_id LIKE p_employee_id ); RETURN r_tbl_pay_item_t; EXCEPTION WHEN OTHERS THEN r_tbl_pay_item_t.DELETE; RETURN r_tbl_pay_item_t; END find_record_dtl;
Ok, thanks - what does pay_item_t function ?
Its an user defined datatype
Thanks for your patience....
Edited by: Maran Viswarayar on Nov 18, 2009 10:55 PM
Why you don't provide parameters to ADMIN.find_record_dtl in you base query as it can accept parameters:
in order to limit the number of rows from list_record_dtl
FUNCTION ADMIN.find_record_dtl ( p_report_id IN NUMBER DEFAULT 0, p_pay_calendar_id IN VARCHAR2 DEFAULT '%', p_employee_id IN VARCHAR2 DEFAULT '%' )
I would change the function call in base query like this.
Can you test how it works with this change. Currently you limit the rows from list_record_dtl only by report_id, but you could do it also with other two parameters, which contain now '%' instead of values which are available in base query. Do this test before you test inline view. I assume that this will solve your problem.
...FROM TABLE (ADMIN.find_record_dtl (8, pc.pay_calendar_id, 'HQPRM003')) rd, pr_calendar pc WHERE pc.pay_calendar_id = rd.pay_calendar_id AND ...
Its an user defined datatypeOk, default constructor. Thanks. Looking forward to get response from my previous post.
Sorry, Joze..I can only get back to you only by tomorrow....Looks like going to do the trick :)
Again...Excellent learning and appreciate you patience and sincerity and the desire to finish of the stuffs....
I have the same problem - I have to drive to my home. Looking forward to see if we were successful.
Looking forward to see if we were successful.We or YOU?....:)
Just curious. How do you get the time information in the explain plan ? i normally use utlxpls.sql it does not have time column at the end. Do you use your own query to generate the explain plan.
I learned that stuff today from joze segecnik,charles hooper....If you have read the thread you would have got the answer
set server output off
turn off autotrace
select * from table(dbms_xplan.display_cursor(NULL,NULL, 'allstats last'));
select /*+ gather_plan_statistics */ * from ........(e.g.)
Will give all the stuffs
(Deleted as already answered by Maran.)