How to pass apex item value to database view?

Question

I've a apex IR. There are few apex items to set the filter on report.

The query is so huge (more than 60K characters long) that it cannot be pasted on region source.

It also gives ORA-06502: PL/SQL: numeric or value error if being called by PL/SQL Function Body returning SQL query.

Hence I've created a database View for this requirement.

But I don't how to pass these apex-item values to View.

Initially, I thought to use apex_application_page_items along with apex_application_items to fulfill the requirement. But again, I don't know how to check session_id or app_user.

Please note: Dute to a complex query and vast number of data, I can't do select * from view where view_column = apex_item; I need to pass apex-item into View's SQL query.

Any help would be appreciated.

Thanks.

Answer 1

You don't pass items' values to a view, but use them in interactive report's query.

Presume view name is V_EMP and it selects various values about employees. Page items are P1_DEPT (department) and P1_JOB. Query would then look like this:

select v.dept_name, v.emp_name, v.job, v.sal
from v_emp
where (v.dept = :P1_DEPT or :P1_DEPT is null)   --> this is where you'll
  and (v.job  = :P1_JOB  or :P1_JOB  is null)   --> use page items
order by v.dept_name, v.job, v.emp_name;