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
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

  l_query VARCHAR2(4000);

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

  :PN_DEAL_ID  ) AS t
  INTO l_query
  FROM dual;

                                                    p_query           => l_query );


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
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
  :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: Mon Mar 19 10:28:45 CDT 2018

Total time taken to generate the page: 0.11595 seconds