I recently came across a requirement where I needed to dynamically extract metadata definitions (such as column names, data types, etc.) of a variety of objects such as Tables, Views etc.
I achieved this using apex_data_export API in Oracle APEX. It’s important to note that apex_data_export is primarily used to export data and not the metadata. So to extract metadata definitions dynamically, we must first query metadata from data dictionary sources, and then pass that query result to the API call.
Let's see it in action.
Here's a sample PL/SQL block using apex_data_export to get Metadata:
DECLARE
l_context apex_exec.t_context;
l_export apex_data_export.t_export;
BEGIN
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select column_name, data_type, data_length from all_tab_cols where table_name=' || '''' || 'AJ_AP_INV' || '''');
l_export := apex_data_export.export (
p_context => l_context,
p_format => apex_data_export.c_format_csv,
p_file_name => 'AJ_AP_INV.csv' );
apex_data_export.download( p_export => l_export );
apex_exec.close( l_context );
EXCEPTION
when others THEN
apex_exec.close( l_context );
raise;
END;
Now, let's incorporate this in an APEX page and invoke it upon a Button press.
- Let's create a Process in After Submit section
- Set above code in PLSQL section -
- Set above code in PLSQL section -
- Let's set the Server-side condition When Button Pressed to our export button
- That's it. Now when we run the app and click the Export button, it will automatically download the Metadata definition of our sample table AJ_AP_INV
- Now, if we open the downloaded file, we will be able to see the metadata of this table -
How it was used in my use case:
- My requirement was to obtain metadata definitions of Public View Objects (PVOs) and their underlying tables.
How it was used in my use case:
- My requirement was to obtain metadata definitions of Public View Objects (PVOs) and their underlying tables.
- In this case, the data lineage information for PVOs was ingested into Autonomous database which was accessed by APEX. The table holding all the PVO information was XXCUST_FSCM_DATA_LINEAGE
- To provide more perspective, let's take an example of FscmTopModelAM.AnalyticsServiceAM.TerritoriesPVO.
This PVO essentially contains columns from FND_TERRITORIES_B and FND_TERRITORIES_TL.
This PVO essentially contains columns from FND_TERRITORIES_B and FND_TERRITORIES_TL.
Now we want our solution to extract Metadata definition of this PVO as well as metadata definitions of both the underlying tables.
Let's see how this was achieved.
- I created a new page with a text box accepting the PVO name and three buttons to export metadata of PVO and the underlying tables.
- I created a new process to export PVO metadata by using below PL/SQL block and set it to trigger upon click of PVO button:
- I created a new page with a text box accepting the PVO name and three buttons to export metadata of PVO and the underlying tables.
- I created a new process to export PVO metadata by using below PL/SQL block and set it to trigger upon click of PVO button:
DECLARE
l_context apex_exec.t_context;
l_export apex_data_export.t_export;
BEGIN
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'SELECT LISTAGG(VIEW_OBJECT_ATTRIBUTE, '''|| ',' || ''') WITHIN GROUP (ORDER BY NULL) "Col"
FROM XXCUST_FSCM_DATA_LINEAGE
WHERE VIEW_OBJECT = :P_PVO_NAME' );
l_export := apex_data_export.export (
p_context => l_context,
p_format => apex_data_export.c_format_csv,
p_file_name => :P_PVO_NAME );
apex_data_export.download( p_export => l_export );
apex_exec.close( l_context );
EXCEPTION
when others THEN
apex_exec.close( l_context );
raise;
END;
- Similarly, I created two more processes, one for each underlying table using below code and set it to trigger upon click of each table button:
- Similarly, I created two more processes, one for each underlying table using below code and set it to trigger upon click of each table button:
DECLARE
l_context apex_exec.t_context;
l_export apex_data_export.t_export;
CURSOR c1 IS
SELECT DATABASE_TABLE TABLE_NAME
FROM
(
SELECT DATABASE_TABLE
,row_number() over (order by NULL) rnum
FROM(
SELECT DISTINCT DATABASE_TABLE
FROM XXCGI_FSCM_DATA_LINEAGE
WHERE VIEW_OBJECT = :P_PVO_NAME
ORDER BY 1
)
)
WHERE rnum=1;
BEGIN
FOR I IN c1
LOOP
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'SELECT LISTAGG(DATABASE_COLUMN, '''|| ',' || ''') WITHIN GROUP (ORDER BY NULL) "Col"
FROM XXCUST_FSCM_DATA_LINEAGE
WHERE VIEW_OBJECT = :P_PVO_NAME
AND DATABASE_TABLE='|| ''''||I.TABLE_NAME|| '''');
l_export := apex_data_export.export (
p_context => l_context,
p_format => apex_data_export.c_format_csv,
p_file_name => I.TABLE_NAME );
apex_data_export.download( p_export => l_export );
apex_exec.close( l_context );
END LOOP;
EXCEPTION
when others THEN
apex_exec.close( l_context );
raise;
END;
- Let's run the app, provide the PVO name and click the three buttons to see the result
- As we can see, it downloaded three files, one for PVO meta definition, one for table #1 and another for table #2.
Possible Use cases:
This mechanism would certainly come handy in below use cases and many more:
• Dynamically inspecting a table/view
• Building data dictionaries
• Exporting data structure to Excel
• Validating report configuration dynamically
