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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Daxesh.

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

Last updated: November 24, 2021 - 2:16 pm UTC

Version: 20.1

Viewed 1000+ 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 Chris 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'




Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ITS WORKING

DAXESH LAIWALA, May 19, 2020 - 10:22 am UTC

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

DAXESH LAIWALA, May 19, 2020 - 11:31 am UTC

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.

A reader, July 01, 2021 - 11:49 am UTC


Not able to login to your Workspace on apex.oracle.com

Gopal, November 24, 2021 - 11:35 am UTC

Hello,

i tried using the credentials you shared. Not able to login.
Please help
Chris Saxon
November 24, 2021 - 2:16 pm UTC

You mean you've requested a free workspace on apex.oracle.com and have lost your credentials? Have you tried resetting your password?

Or do you mean something else?

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library