CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
Velocity
Velocity

Reputation: 479

Oracle PL/SQL Procedure/function to create view with columns

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 table with column headers is DATA_HEADER.

enter image description here

The table with value is DATA_VALUE.

enter image description here

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:

enter image description here

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

Upvotes: 0

Views: 998

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6084

One option is to create a function like the on below, then call the cursor from SQLPlus, SQLcl, or whatever other tool you are using. If you are using Java or APEX, you can use the refcursor or even just the value in l_sql to get the select statement you need.

Function

CREATE OR REPLACE FUNCTION get_data (p_app_id data_header.app_id%type)
    RETURN SYS_REFCURSOR
IS
    TYPE headers_t IS TABLE OF data_header.label%TYPE;
    l_headers    headers_t;
    l_cur        SYS_REFCURSOR;
    l_sql        VARCHAR2 (32767);
BEGIN
      SELECT label
        BULK COLLECT INTO l_headers
        FROM data_header
       WHERE app_id = p_app_id
    ORDER BY seq;

    l_sql := 'select * FROM (SELECT *
          FROM data_value
         WHERE app_id = ' || p_app_id || ')
       PIVOT (MIN (VALUE) FOR seq IN (';

    FOR i IN 1 .. l_headers.COUNT
    LOOP
        l_sql :=
               l_sql
            || i
            || ' as "'
            || l_headers (i)
            || CASE WHEN i < l_headers.COUNT THEN '", ' ELSE '"' END;
    END LOOP;

    l_sql := l_sql || '))';

    OPEN l_cur FOR l_sql;

    RETURN l_cur;
END;
/

SQLPlus or SQLcl

You can use the cursor like this

SQL> variable l_cursor refcursor;
SQL> begin                                                                                                                                                                                                                                     
  2    :l_cursor := get_data(1);                                                                                                                                                                                                               
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print l_cursor;

Update

Since your end UI is APEX, this query can be built to work in APEX. By returning the value in l_sql, you can create a Classic Report utilizing that query. I have built a demo application on apex.oracle.com showing that it is possible.

Upvotes: 1

Related Questions