Skip to Main Content
  • Questions
  • Creation of Dynamic Region OR Dynamic Interactive Report

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Daxesh.

Asked: May 16, 2020 - 7:05 am UTC

Answered by: Connor McDonald - Last updated: May 19, 2020 - 11:08 am UTC

Category: Application Express - Version: 20.1

Viewed 100+ times

You Asked

Before understand question mind I have one table with 3 columns one is id second is description and third is sql query, i created one Apex page with two region one is static region and one is Interactive Report Region.
In Static region i have one select list for user and in Interactive report region it execute query according the selection and gives the output. I use source type : L/SQL Function Body returning SQL Query for interactive report. I write following code
DECLARE
V_TBLQRY VARCHAR2(500);
BEGIN
SELECT QRY INTO V_TBLQRY FROM BKP WHERE TBL_NM = :P2_TBLNM;
RETURN V_TBLQRY;
EXCEPTION
WHEN OTHERS THEN
RETURN 'SELECT GRP_CD, GRP_DSCR FROM MNU_USRGRP_M';
END;

when page load exception is raised and report shows but when we change selection it gives error i am unable to track it. i also tried with apex_collections but problem is that some unused columns are also on report and report heading i am not be able to change dynamically. Total case i upload on apex.oracle.com
workspace : HLL username : dax.apex@gmail.com password: Apex@1234 to run system username demo and password is demo. Problem is on page no :2.

the test case you can download it from here (i don't know how to upload and create LiveSql link i upload scripts on LiveSql but unable to creating link.
https://drive.google.com/file/d/1F0_v7xvdLlEAYB0o8Hby_P4BRcDB_LOI/view?usp=sharing

and we said...

To my knowledge you cannot change the columns (ie, the *structure*) of a report dynamically. I have an app that lets me run any query - I do it with an APEX collection as follows

declare
    l_query varchar2(32767) := nvl(:P1_sql,'select * from dual');
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    l_sql varchar2(4000) := 'select /*smartcheck*/ ';
    l_sep     varchar2(1);

begin
  IF APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => 'ANY_SQL') THEN
     APEX_COLLECTION.DELETE_COLLECTION( p_collection_name => 'ANY_SQL');
  end if;

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

   dbms_sql.close_cursor(l_theCursor);
   l_theCursor     := dbms_sql.open_cursor;
   dbms_sql.parse(  l_theCursor, l_query, dbms_sql.native );
   dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 
   :P1_COLCOUNT := l_colcnt;

   for i in 1 .. least(l_colCnt,50)
   loop
     apex_util.set_session_state( p_name  => 'P1_HEADER'||lpad(i,2,0)
                                , p_value =>  l_descTbl(i).col_name
                                );
   end loop;

end;


which populates the collection and also set the column headings to be the names of the columns. My region is then:

select 
 c001
,c002
,c003
,c004
,c005
,c006
,c007
,c008
,c009
,c010
,c011
,c012
,c013
,c014
,c015
,c016
,c017
,c018
,c019
,c020
,c021
,c022
,c023
,c024
,c025
,c026
,c027
,c028
,c029
,c030
,c031
,c032
,c033
,c034
,c035
,c036
,c037
,c038
,c039
,c040
,c041
,c042
,c043
,c044
,c045
,c046
,c047
,c048
,c049
,c050
from apex_collections 
where collection_name = 'ANY_SQL'




and you rated our response

  (2 ratings)

Reviews

ITS WORKING

May 19, 2020 - 10:22 am UTC

Reviewer: DAXESH LAIWALA from INDIA

Thanks for good support/answer/knowledge sharing. but here i have one question we set the heading in :P1_HEADING01 and etc., how to programming set grid column label of relevant column e.g. c001 heading of :P1_HEADING01 and also server side condition that if value of :P1_HEADING01 is not null for c001 and etc.,

or i have to do it manually for all columns
Connor McDonald

Followup  

May 19, 2020 - 11:08 am UTC

For each column the column label is:

&P1_HEADING01 .for c001
&P1_HEADING01. for c002

and so forth....

ITS WORKING

May 19, 2020 - 11:31 am UTC

Reviewer: DAXESH LAIWALA from INDIA

yes sir its working but can we set it by programming or manually we have to do it. any method/package/builtin for doing it by writing code or set property for each column.
and even server side condition too.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.