Write Oracle proc/function to create dynamic views with changing column headers

oracleplsqlstored-procedures

I had similar requirement earlier but now i have been given a clear approach that needs to be implemented.

I need to write a procedure(preferably)/function, which would on basis of app_id passed as parameter, change the column headers for corresponding values pivoted in the dynamic view.

The entire sample data and structure is available here:

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=63c08123fbf2fb13de81df0bff360c0b

The table with column headers is DATA_HEADER.

The table with value is DATA_VALUE.

The column headers and values need to be pivoted in order.

Both tables have app_id on common. So for app_id=1, labels from DATA_HEADER would be used against values in DATA_VALUE. However, the values change on basis of PID only, so they would remain constant, only headers would change per app id.

When app_id is passed in proc/funct, the expected view should be:

So basically, the headers change for each app_id, and the max number of column headers will be 20. So the number or name shall vary as in the table DATA_HEADER.
The values are uniquely identified on the basis of pid.

The order of column headers would be as per the seq column in DATA_HEADER.
Similarly the order of values would be as per seq column in data value, so the sequence must be followed and pivoted accordingly.

P.S. Application at the end is Oracle apex from where the proc/function would be called.

Oracle Version: 12.1

Best Answer

Why does it have to be one query?

Why not retrieve the column headers in one step, pull the real data in another and then stitch the two together, if that's even required?

If this is being done under application control (as opposed to a single database "query"), you may not need to do this "reassembly" at all. It might be possible to populate your column headers separately from the real data.

Related Solutions

Return a fully dynamic table from an Oracle function

It is possible, though quite complicated, to write a pipelined table function that returns a variable structure. Your pipeline table function could take the two arguments and use the Oracle Data Cartridge interface and the magic of the AnyDataSet type to return a dynamic structure at runtime. You can then use that in subsequent SQL statements as if it was a table, i.e.

SELECT *
  FROM TABLE( your_pipelined_function( p_1, p_2 ));

A couple more references that discuss the same sample implementation

Dynamic SQL Pivoting
The Implementing the Interface Approach section of the Oracle Data Cartridge Developer's Guide

Determine structure of out cursor

This example code shows how to parse a ref_cursor. There are additional columns available, see the definition of SYS.DBMS_SQL.desc_tab2 for a full list of available columns.

In this example, my ref_cursor is defined as select * from user_tables. You would of course substitute in your own ref_cursor.

DECLARE
   l_csrid       INTEGER;
   l_coldesc     SYS.DBMS_SQL.desc_tab2;
   l_colcnt      INTEGER;
   l_refcursor   SYS_REFCURSOR;

   FUNCTION tostring (p_label   IN VARCHAR2
                     , p_value   IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN RPAD (p_label, 30)
             || ': '
             || p_value
             || UTL_TCP.crlf;
   END tostring;

   PROCEDURE myput_line (p_rec      IN DBMS_SQL.desc_rec2
                       , p_colnum   IN INTEGER)
   AS
   BEGIN
      DBMS_OUTPUT.put_line (   RPAD ('_', 30, '_') 
                        || UTL_TCP.crlf
                        || tostring (p_label => 'Column'
                                   , p_value => p_colnum)
                        || tostring (p_label => 'Name'
                                   , p_value => p_rec.col_name)
                        || tostring (p_label => 'Type'
                                   , p_value => p_rec.col_type)
                        || tostring (p_label => 'Null OK'
                                   , p_value => CASE WHEN p_rec.col_null_ok
                                                     THEN 'Yes' 
                                                     ELSE 'No' 
                                                END));
   END myput_line;
 BEGIN
   OPEN l_refcursor FOR
      SELECT * FROM user_tables;

   l_csrid   := DBMS_SQL.to_cursor_number (rc => l_refcursor);
   DBMS_SQL.describe_columns2 (c       => l_csrid
                             , col_cnt => l_colcnt
                             , desc_t  => l_coldesc);

   FOR i IN 1 .. l_colcnt LOOP
      myput_line (l_coldesc (i), i);
   END LOOP;
END;

Related Question