Hi.
We are using Apex 20.1. We have some 30-40 reference-data-tables, that ought to be administered by power users of our application. Of course a typical use case for an interactive report. Only, that … its 30-40 tables…
Now is it possible to display a scrollable select-list of the tables on the left and display the contents on the right, in an interactive report, which receives dynamically, say the name of the table.
For the sake of simplicity, say, that all tables consist of two columns, “key” and “value”. So you would only need to pass the table name. Is that possible? How?
Many thanks
You can do this by changing the interactive report’s source type to “PL/SQL Function returning SQL query”. Follow these steps:
Click on your report region and specify the PL/SQL function returning SQL query for the source type.
Then in the function body, write the code as follows:
For items to submit, specify the P2_TABLES item.
Now you can create a dynamic action for your table drop-down to refresh the interactive report region.
step 1 create interactive report where type=”pl/sql function body return sql query”
like
DECLARE
V_SQL VARCHAR2(1000);
BEGIN
RETURN ‘SELECT KEY, VALUE FROM ‘||:P2_TABLES;
END;
SUBMIT ITEM- P2_TABLES
STEP-2 CREATE A DYNAMIC ACTION
EVENT — CHANGE
ITEM- P2_TABLES
ACTION — REFRESH [YOUR INTERACTIVE REPORT REGION]
Hi Afzal,
Have you tested this code?
I tried this before giving my answer, but it did not compile.
But now I found the solution using the nvl() function. Below is the right code:
Hi Vinish
your code is absolutely right, sorry i did not complie the code before submit the answer.
Thanks guys.
But let’s go to the deep water now 🙂 What if they have a different set of columns? E.g. in our case, all tables have the KEY and VALUE columns but one third of them has additional columns.
I was not able to apply the suggested solution in this case. The IG or IR simply ignores the additional columns, that it does not know.
I think it is not possible with an interactive grid, but it is possible with a classic report.
Recently I have created an app to execute SQL statements at runtime and giving the output using the classic report. You can check its source code for your reference:
https://github.com/devvinish/sql-command
Hi,
this solution applicable only for a table whose no of column is not greater than 50
step 1–
create a lov like– :P63_TABLE_NAME
step 2– create a interactive report like–
on this report hide the column—COLLECTION_NAME,SEQ_ID,CLOB001,BLOB001,XMLTYPE001,N001–N005, and D001 —D005 AND MD5_ORIGINAL
STEP-3– CREATE 50 HIDDEN ITEM LIKE– P63_C001—–P63_C010—-P63_C020—-P63_C050 “FOR DYNAMIC COLUMN NAME”
STEP-4– CREATE A DYNAMIC ACTION ON ONCHANGE AT ABOVE DEFINE LOV
CLENT SIDE CONDITION– P63_TABLE_NAME IS NOT NULL
TRUE ACTION 1– EXECUTE PL/SQL CODE
SUBMIT– P63_TABLE_NAME
PL/SQL CODE LIKE—
TRUE ACTION 2– REFRESH
REGION — YOUR REPORT REGION
FALSE ACTION–1—-
PL/SQL CODE
FALSE ACTION –2
REFRESH — REGION–YOUR REPORT REGION
STEP-4 EACH REPORT COLUMN HEADING “C001” REPLACE WITH &P63_C001. LIKE —-
COLUMN HEADING REPLACE WITH SERVER SIDE CONDIOTION
C001 &P63_C001. ITEM IS NOT NULL ITEM= P63_C001
C002 &P63_C002. ITEM IS NOT NULL ITEM= P63_C002
. . .
. . .
C050 &P63_C050. ITEM IS NOT NULL ITEM= P63_C050
Wow. I did not know, that using the SQL*PLUS-variable notation (e.g. &P63_C001.) really can be used and replaced at runtime. This is what I take from this.