Please update your forum profile with a real handle instead of "23f29dfb-f3d1-42bf-b347-de28f528a12a".
I have created a tables_LOV based on:
select table_name d, table_name r from user_tab_cols
where column_name like '%_type%'
Then I created a page item ListOfTables, Display as select list and pointing to tables_LOV.
I run the page, and i can select the table i want from the drop down list.
How to create a report based on the selected item? (ex: select * from selected_table)
Could you explain why you want to do this? It seems like a strange basis for an application.
When you post to this forum you'll get a faster, more effective response by including as much relevant information as possible upfront. Please tell us:
- Full APEX version
- Full DB/version/edition/host OS
- Web server architecture (EPG, OHS or APEX listener/host OS)Browser(s) and version(s) used
- Region/item type(s) (making particular distinction as to whether a "report" is a standard report, an interactive report, or in fact an "updateable report" (i.e. a tabular form)
With APEX we're also fortunate to have a great resource in apex.oracle.com where we can reproduce and share problems. Reproducing things there is the best way to troubleshoot most issues, especially those relating to layout and visual formatting. If you expect a detailed answer then it's appropriate for you to take on a significant part of the effort by getting as far as possible with an example of the problem on apex.oracle.com before asking for assistance with specific issues, which we can then see at first hand.
I have updated my display name thx
I am using APEX 4.2
the reason i am doing that is:
In may application i have more than 60 tables, having the same data columns, like "TableName Type Identifier" "Name Englist" "Name French"
so, instead of creating a page for each table, i want to use something more efficient.
i.e, when select a table from drop down list, i want to query this table.(select * from selectedtable)
this query represent a report on this table.
preferance to have interactive report.
You can create a proces that creates an apex collection based on a query string that has your LOV as table_name. Then you can create an interactive report based on that collection.
Use a page load proces to create the collection and then force a page submit when you change the LOV. Or you can create a page load dynamic action that creates the collection, and subsequently refreshes the report region.
Or yet another solution would be to write a function that returns a table set. Use table_name as input parameter. you can do a query on that function with
select * from table( my_function( p_table_name => :P_LOV_ITEM ) );
Thanks alot Vincent, I really appreciate your help.
you provided 3 options, which i am not sure how to do any
Kindly can you please provide a sample of 1 option.
in my 1st note: i had
myTables_LOV as: SELECT table_name FROM user_tab_Cols WHERE column_name like '%TP_ID%'
Then i created a page item: P_myTables, where is select list and uses myTables ans the LOV.
With your options, do i need what i had, and how can I reuse it if needed, or just start clean?
The only missing link is: when i select an item from drop down list i want to display a report for selected table.
from your Note:
i think myTables_LOV could be the function which returns a table set.
select * from(my_function(p_table_name => p_myTables));
please note: couldn't put (:) in front of p_mytables, if shows happy face.
where do i get p_table_name? is it right the use of p_myTables in the function?
where or when i can use this function?
Allright, have a look at this page: http://apex.oracle.com/pls/apex/f?p=vincentdeelen:collection_report
I think that simulates what you're trying to accomplish. I've set up the simplest method I could think of.
The report is based on an apex collection. If you are not familiar with that, you should study the documentation: APEX_COLLECTION
To recreate my example you should:
1) create an (interactive) report on your collection
SELECT * FROM APEX_collections WHERE collection_name = 'MY_COLLECTION'
2) create a page_item select list for the tables you want to display (in my case this is called "P38_TABLES" )
3) create a dynamic action that triggers on change of your select list page_item. The dynamic action must be a PL/SQL procedure perfoming the following code:
declare l_query varchar2(4000); begin l_query := 'select * from '||:P38_TABLES; if apex_collection.collection_exists ( p_collection_name => 'MY_COLLECTION' ) then apex_collection.delete_collection ( p_collection_name => 'MY_COLLECTION' ); end if; apex_collection.create_collection_from_query ( p_collection_name => 'MY_COLLECTION' , p_query => l_query ); end;
Make sure you add your page_item to the "Page Items to Submit" section.
4) Add an extra true action that does a refresh of the report region.
Here are two pictures describing the da:
Good luck and regards,