Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Dynamic SQL within SQL (Oracle 11i)
Dynamic SQL within SQL [message #657451] Wed, 09 November 2016 04:39
anil.bejugam
Messages: 1
Registered: November 2016
Junior Member
Hi All,

In Oracle APEX Interactive reports I can only use sql query but I am getting query from function which is not working please help

Example
DECLARE
  l_query VARCHAR2(4000);
BEGIN

IF apex_collection.collection_exists('DEAL_FIND_SEARCH') THEN
     apex_collection.delete_collection(
         p_collection_name => 'DEAL_FIND_SEARCH'
      );
  END IF;     

SELECT AX_DEAL_FIND_FUN (
  :PN_NON_CONSIGNEMENT, 
  :PN_DEAL_ID  ) AS t
  INTO l_query
  FROM dual;


 
     APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY( p_collection_name => 'DEAL_FIND_SEARCH', 
                                                    p_query           => l_query );

END; 

In Interactive reports I am using query like below
SELECT c001 deal_id,
  c002 supplier,
  c003 supplier_name,
  c004 status,
  to_date(c005, 'DD_MON_RRRR') active_date,
  to_date(c006, 'DD-MON-RRRR') close_date,
  c007 ext_ref_no,
  c008 type,
  c009 currency_code,
  c010 create_datetime,
  c011 create_id,
  c012 approval_date,
  c013 approval_id,
  c014 reject_date,
  c015 reject_id,
  c016 close_id,
  c017 order_no,
  c018 comments,
  c019 partner_id,
  c020 partner_type
FROM apex_collections
WHERE collection_name = 'DEAL_FIND_SEARCH'


and I want to use
 l_query 
in my interactive report but Oracle interactive report only accepts select query

I am facing issue in when there is huze data, if have 1 million records, query is fetching data and keeping in collection
and oracle apex page is getting un-responsive mode

in order to achieve this I want to re-write something like this but I am not able to do it, need help
select col1, col2
from (SELECT AX_DEAL_FIND_FUN (
  :PN_NON_CONSIGNEMENT, 
  :PN_DEAL_ID  ) AS t
  FROM dual)
but above code is returning query, need help in re-writing the query
Previous Topic: Search how an named "PROCEDURE MAIN(pOutputTraceFlag VARCHAR2 DEFAULT 'N');" is launched
Next Topic: Problem accessing PL/SQL Web procedure in Oracle 12c
Goto Forum:
  


Current Time: Sun Nov 19 15:26:53 CST 2017

Total time taken to generate the page: 0.01456 seconds