Apex Collection: how to have actual column names instead of C001, C002

user_15528402 0
Viewed 438

created a page to allow users to input SQL statement, when user press button, it should Execute this query and another region with Classic Report will display results. on Button a Dynamic Action execute PL/SQL code below: on Click event:

begin
IF :P8_YOURSQL IS NOT NULL THEN
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
p_collection_name => 'SOMECOLLECTION',
p_query => :P8_YOURSQL,
p_truncate_if_exists => 'YES'
);
end if;
end;

Item to Submit: P8_YOURSQL

second Action refreshes CR region and CR region is based on:

SELECT * FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = 'SOMECOLLECTION';

resulted columns are not limited to the query results and column headers are C001, C002, C003 etc. (a) how i can limit the columns to the SQL statement contained? (b) how to change header to the actual column names? (c) how to check for a valid SQL statement?

please help with sample code How To.

oracle-apex collections
2 Answers

APEX_COLLECTIONS is a generic table so it has a couple of columns for the most common data types with names like C001, N001 etc. As with any table columns can be aliased in a query.

Example:

  • Create page process before header to populate the collection
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
  p_collection_name => 'EMP_COLLECTION',
  p_query => q'!SELECT * FROM emp!',
  p_truncate_if_exists => 'YES'
);

  • Run the page and open the "Session" window from the developer toolbar. Select "Collections" in the "View" dropdown and click "Set". This will list the data in the APEX_COLLECTIONS table for any collections that exist for the session. Take note of the data and the columns the data is in.

  • Leave the "Sessions" window open and create a sql query on the APEX_COLLECTIONS view with appropriate aliases.

SELECT 
  c001 as EMPNO,
  c002 as ENAME, 
  c003 as JOB, 
  c004 as MGR, 
  <rest_of_columns>...
 FROM
  APEX_COLLECTIONS WHERE collection_name = 'EMP_COLLECTION'

It's not possible to alias columns when doing a SELECT * FROM ...

If the SELECT * is important then there is another possibility. Create a view on top of APEX_COLLECTIONS with relevant column names. Example:

CREATE OR REPLACE view EMP_COLLECTION_V 
AS
SELECT 
  c001 as EMPNO,
  c002 as ENAME, 
  c003 as JOB, 
  c004 as MGR
 FROM
  APEX_COLLECTIONS WHERE collection_name = 'EMP_COLLECTION'

and then use SELECT * FROM EMP_COLLECTION_V as SQL source for the classic report.

user_41898140

To get the columns names for a SELECT * FROM ... have a look at [DBMS_SQL.DESCRIBE_COLUMNS3][https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_SQL.html#GUID-00AB5DE3-C428-4E60-9398-FD4892F32402]. There is an example in the doc that shows how to print the column names.

This can then be implemented in a classic report

  • Create a page item per column: P1_C1, P1_C2, etc
  • Set the column header for each column to the corresponding page item: col1 has header &P1_C1, col2 has header &P1_C2, etc
  • Create a page process to set the column headers based on the sql query using DBMS_SQL.DESCRIBE_COLUMNS3 - OR - use a

Note that it is strongly advised to assert the sql query to avoid unwanted sql (like a delete or drop table command).

user_41898140
Related