I had a requirement which was answered in the thread:
Oracle PL/SQL Procedure/function to dynamically create view with dynamic columns (0 Bytes)
Now, there is an additional requirement to modify the procedure such that the way its currently creating view, similarly the select statement would also get generated directly inside the procedure,.i.e. something like function returning sql query.
The end requirement is to call this as source for interactive report in oracle apex, so by directly calling the procedure it would display the data directly.
So basically, the select * from APP_&APP_ID_VW : this would also get dynamically created via proc after view is created.
Current Solution:
CREATE OR REPLACE
PROCEDURE CREATE_VIEW(
P_APP_ID NUMBER
)
IS
V_STMT VARCHAR2(4000);
BEGIN
SELECT 'CREATE OR REPLACE' || CHR(10) ||
' VIEW APP_' || APP_ID || '_VW' || CHR(10) ||
' AS' || CHR(10) ||
' SELECT *' || CHR(10) ||
' FROM DATA_VALUE' || CHR(10) ||
' PIVOT(' || CHR(10) ||
' MAX(VAL)' || CHR(10) ||
' FOR SEQ IN (' || CHR(10) ||
' ' || LISTAGG(
SEQ || ' "' || LABEL || '"',
',' || CHR(10) ||' '
)
WITHIN GROUP(ORDER BY SEQ) || CHR(10) ||
' )' || CHR(10) ||
' )' || CHR(10) ||
' WHERE APP_ID = ' || APP_ID
INTO V_STMT
FROM DATA_HEADER
WHERE APP_ID = P_APP_ID
GROUP BY APP_ID;
DBMS_OUTPUT.PUT_LINE('Creating view: ' || CHR(10) || V_STMT);
EXECUTE IMMEDIATE V_STMT;
END;
/
PL/SQL procedure successfully completed.
SQL>
Now:
SQL> EXEC CREATE_VIEW(1)
Creating view:
CREATE OR REPLACE
VIEW APP_1_VW
AS
SELECT *
FROM DATA_VALUE
PIVOT(
MAX(VAL)
FOR SEQ IN (
1 "Title",
2 "Group",
3 "Aspect",
4 "EPT",
5 "IT",
6 "BU",
7 "Section",
8 "Class",
9 "Label Value",
10 "Comment"
)
)
WHERE APP_ID = 1
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM APP_1_VW
3 /
PID APP_ID Title Group Aspect EPT IT BU Section Class Label Va Comment
---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
121 1 Stanley Deputy IT Synopsis Standard Code Green Need
120 1 Robert Deputy IT Synopsis Standard Code Green Need
SQL> EXEC CREATE_VIEW(2)
Creating view:
CREATE OR REPLACE
VIEW APP_2_VW
AS
SELECT *
FROM DATA_VALUE
PIVOT(
MAX(VAL)
FOR SEQ IN (
1 "Title",
2 "Group",
3 "Aspect",
4 "EPT",
5 "IT",
6 "BU",
7 "Section",
8 "Class",
9 "Label Value",
10 "Comment",
11 "Remarks"
)
)
WHERE APP_ID = 2
PL/SQL procedure successfully completed.
SQL> SELECT *
2 FROM APP_2_VW
3 /
PID APP_ID Title Group Aspect EPT IT BU Section Class Label Va Comment Remarks
---------- ---------- -------- -------- -------- -------- -------- -------- -------- -------- -------- -------- --------
120 2 Robert Deputy IT Synopsis Standard Code Green Need
121 2 Stanley Deputy IT Synopsis Standard Code Green Need
SQL>