Overview
This document is about how to add or remove columns from the input table based on the parameters you pass in oracle.
Technologies and Tools Used
The following technologies have been used to add or remove columns from the input table based on the parameters you pass in oracle.
- Oracle SQL/ PLSQL
Use Case
Let us have the requirement on how to add or remove columns from the input table in oracle.
Steps with Screenshot
Polymorphic table functions (PTF) are a subset of table functions where the schema of the returned table is determined dynamically. The returned table schema can depend on the arguments you pass to the function.
Create a table setup:
Code :
CREATE TABLE DEPT (
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
) ;
/
CREATE TABLE EMP (
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
/
Insert records into table:
INSERT INTO DEPT VALUES (10,’ACCOUNTING’,’NEW YORK’);
INSERT INTO DEPT VALUES (20,’RESEARCH’,’DALLAS’);
INSERT INTO DEPT VALUES (30,’SALES’,’CHICAGO’);
INSERT INTO DEPT VALUES (40,’OPERATIONS’,’BOSTON’);
4 row(s) inserted.
/
INSERT INTO EMP VALUES (7369,’SMITH’,’CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,NULL,20);
INSERT INTO EMP VALUES (7499,’ALLEN’,’SALESMAN’,7698,to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);
INSERT INTO EMP VALUES (7521,’WARD’,’SALESMAN’,7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30);
INSERT INTO EMP VALUES (7566,’JONES’,’MANAGER’,7839,to_date(‘2-4-1981′,’dd-mm-yyyy’),2975,NULL,20);
INSERT INTO EMP VALUES (7654,’MARTIN’,’SALESMAN’,7698,to_date(’28-9-1981′,’dd-mm-yyyy’),1250,1400,30);
INSERT INTO EMP VALUES (7698,’BLAKE’,’MANAGER’,7839,to_date(‘1-5-1981′,’dd-mm-yyyy’),2850,NULL,30);
INSERT INTO EMP VALUES (7782,’CLARK’,’MANAGER’,7839,to_date(‘9-6-1981′,’dd-mm-yyyy’),2450,NULL,10);
INSERT INTO EMP VALUES (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,’dd-mm-rr’)-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,’KING’,’PRESIDENT’,NULL,to_date(’17-11-1981′,’dd-mm-yyyy’),5000,NULL,10);
INSERT INTO EMP VALUES (7844,’TURNER’,’SALESMAN’,7698,to_date(‘8-9-1981′,’dd-mm-yyyy’),1500,0,30);
INSERT INTO EMP VALUES (7876,’ADAMS’,’CLERK’,7788,to_date(’13-JUL-87′, ‘dd-mm-rr’)-51,1100,NULL,20);
INSERT INTO EMP VALUES (7900,’JAMES’,’CLERK’,7698,to_date(‘3-12-1981′,’dd-mm-yyyy’),950,NULL,30);
INSERT INTO EMP VALUES (7902,’FORD’,’ANALYST’,7566,to_date(‘3-12-1981′,’dd-mm-yyyy’),3000,NULL,20);
INSERT INTO EMP VALUES (7934,’MILLER’,’CLERK’,7782,to_date(’23-1-1982′,’dd-mm-yyyy’),1300,NULL,10);
COMMIT;
14 row(s) inserted.
Basic Example
We need to define a package that will be used to describe the table input, so the polymorphic table function will understand the table structure. This is really simple if we don’t want to modify the structure.
Code:
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
RETURN NULL;
END;
END poly_pkg;
/
With the package in place, we can define a polymorphic table function which references the package. At minimum the function must accept a parameter of type TABLE and have a return type of TABLE. Notice the ROW POLYMORPHIC USING clause referencing the package. There must be a DESCRIBE function in the associated package with a matching parameter list. In this case we’ve defined the polymorphic table function as a standalone function, but it could have been included in a package above.
Code:
CREATE OR REPLACE FUNCTION my_ptf(tab IN TABLE)
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
/
With the package and function in place we can use the function to query different tables.
Code:
SELECT * FROM my_ptf(dept);
Output:
DEPTNO DNAME LOC ---------- ------------------------------------------ 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Code:
SELECT * FROM my_ptf(emp);
Output:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ------------------------------ --------------------------- ---------- 7369 SMITH CLERK 902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 7-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD CLERK 7782 23-JAN-82 1300 10
Remove Columns
In the following example we have included the polymorphic table function in the package definition to make things cleaner. We’ve also added an extra parameter to supply a list of columns which will be excluded from the result set. Notice the DESCRIBE function has changed to match the parameter list.
Code:
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION my_ptf(tab IN TABLE,
col IN COLUMNS)
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
FUNCTION describe (tab IN OUT DBMS_TF.table_t,
col IN dbms_tf.columns_t)
RETURN DBMS_TF.describe_t;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t,
col IN dbms_tf.columns_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
— Loop through all the table columns.
FOR i IN 1 .. tab.column.count() LOOP
— Loop through all the columns listed in the second parameter.
FOR j IN 1 .. col.count() LOOP
— Set pass_through to true for any columns not in the exclude list.
tab.column(i).pass_through := (tab.column(i).description.name != col(j));
— Exit inner loop if you find a column that shouldn’t be included.
EXIT WHEN NOT tab.column(i).pass_through;
END LOOP;
END LOOP;
RETURN NULL;
END;
END poly_pkg;
/
The implementation of the DESCRIBE function tests to see if the table column is in the exclude list. If it should be excluded the columns PASS_THROUGH element is set to false, otherwise it is set to true.
We can now query the packaged polymorphic table function, passing a table and a column exclude list. Notice how the output has changed now.
Code:
SELECT * FROM poly_pkg.my_ptf(dept, COLUMNS(loc));
Output:
DEPTNO DNAME ---------- ------------------------------------------ 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS
Code:
SELECT * FROM poly_pkg.my_ptf(emp, COLUMNS(hiredate, sal, mgr, comm));
Output:
EMPNO ENAME JOB DEPTNO ---------- ------------------------------ 7369 SMITH CLERK 20 7499 ALLEN SALESMAN 30 7521 WARD SALESMAN 30 7566 JONES MANAGER 20 7654 MARTIN SALESMAN 30 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7788 SCOTT ANALYST 20 7839 KING PRESIDENT 10 7844 TURNER SALESMAN 30 7876 ADAMS CLERK 20 7900 JAMES CLERK 30 7902 FORD ANALYST 20 7934 MILLER CLERK 10
Add Columns
The following code creates a package for a polymorphic table function that adds a JSON_DOC column on to any table passed in. The DESCRIBE function turns on the FOR_READ flag for any supported column, so it will be included in the document. The RETURN clause returns the new column metadata. The FETCH_ROWS procedure builds a collection containing a JSON doc returned by the ROW_TO_CHAR function for each row and associates the resulting collection with the new column.
Code:
CREATE OR REPLACE PACKAGE poly_pkg AS
FUNCTION my_ptf(tab IN TABLE)
RETURN TABLE PIPELINED
ROW POLYMORPHIC USING poly_pkg;
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t;
PROCEDURE fetch_rows;
END poly_pkg;
/
CREATE OR REPLACE PACKAGE BODY poly_pkg AS
FUNCTION describe (tab IN OUT DBMS_TF.table_t)
RETURN DBMS_TF.describe_t
AS
BEGIN
— Make sure the for_read flag for each real column
— is set, or get_row_set has no values.
FOR i IN 1 .. tab.column.count LOOP
CONTINUE WHEN NOT DBMS_TF.supported_type(tab.column(i).description.TYPE);
tab.column(i).for_read := TRUE;
END LOOP;
— Add the new JSON_DOC column.
RETURN DBMS_TF.describe_t(
new_columns => DBMS_TF.columns_new_t(1 => DBMS_TF.column_metadata_t(name =>’JSON_DOC’))
);
END;
PROCEDURE fetch_rows AS
l_row_set DBMS_TF.row_set_t;
l_new_col DBMS_TF.tab_varchar2_t;
l_row_count PLS_INTEGER;
BEGIN
DBMS_TF.get_row_set(l_row_set, row_count => l_row_count);
–DBMS_TF.trace(l_row_set);
— Populate the new column with a JSON doc of the associated row.
FOR row_num IN 1 .. l_row_count LOOP
l_new_col(row_num) := DBMS_TF.row_to_char(l_row_set, row_num);
END LOOP;
— Associate the new values with the new column.
DBMS_TF.put_col(1, l_new_col);
END;
END poly_pkg;
/
We can now query the JSON_DOC column produced by the packaged polymorphic table function, passing two different tables.
Code:
SELECT deptno, json_doc FROM poly_pkg.my_ptf(dept);
Output:
DEPTNO JSON_DOC
---------- --------------------------------------------------
10 {"DEPTNO":10, "DNAME":"ACCOUNTING", "LOC":"NEW YORK"}
20 {"DEPTNO":20, "DNAME":"RESEARCH", "LOC":"DALLAS"}
30 {"DEPTNO":30, "DNAME":"SALES", "LOC":"CHICAGO"}
40 {"DEPTNO":40, "DNAME":"OPERATIONS", "LOC":"BOSTON" }
Code:
SELECT empno, json_doc
FROM poly_pkg.my_ptf(emp);
Output:
EMPNO JSON_DOC
---------- --------------------------------------------------
7369 {"EMPNO":7369, "ENAME":"SMITH", "JOB":"CLERK", "MG
R":7902, "HIREDATE":"17-DEC-80", "SAL":800, "DEPTN
O":20}
7499 {"EMPNO":7499, "ENAME":"ALLEN", "JOB":"SALESMAN",
"MGR":7698, "HIREDATE":"20-FEB-81", "SAL":1600, "C
OMM":300, "DEPTNO":30}
7521 {"EMPNO":7521, "ENAME":"WARD", "JOB":"SALESMAN", "
MGR":7698, "HIREDATE":"22-FEB-81", "SAL":1250, "CO
MM":500, "DEPTNO":30}
7566 {"EMPNO":7566, "ENAME":"JONES", "JOB":"MANAGER", "
MGR":7839, "HIREDATE":"02-APR-81", "SAL":2975, "DE
PTNO":20}
7654 {"EMPNO":7654, "ENAME":"MARTIN", "JOB":"SALESMAN",
"MGR":7698, "HIREDATE":"28-SEP-81", "SAL":1250, "
COMM":1400, "DEPTNO":30}
7698 {"EMPNO":7698, "ENAME":"BLAKE", "JOB":"MANAGER", "
MGR":7839, "HIREDATE":"01-MAY-81", "SAL":2850, "DE
PTNO":30}
7782 {"EMPNO":7782, "ENAME":"CLARK", "JOB":"MANAGER", "
MGR":7839, "HIREDATE":"09-JUN-81", "SAL":2450, "DE
PTNO":10}
7788 {"EMPNO":7788, "ENAME":"SCOTT", "JOB":"ANALYST", "
MGR":7566, "HIREDATE":"19-APR-87", "SAL":3000, "DE
PTNO":20}
7839 {"EMPNO":7839, "ENAME":"KING", "JOB":"PRESIDENT",
"HIREDATE":"17-NOV-81", "SAL":5000, "DEPTNO":10}
7844 {"EMPNO":7844, "ENAME":"TURNER", "JOB":"SALESMAN",
"MGR":7698, "HIREDATE":"08-SEP-81", "SAL":1500, "
COMM":0, "DEPTNO":30}
7876 {"EMPNO":7876, "ENAME":"ADAMS", "JOB":"CLERK", "MG
R":7788, "HIREDATE":"23-MAY-87", "SAL":1100, "DEPT
NO":20}
7900 {"EMPNO":7900, "ENAME":"JAMES", "JOB":"CLERK", "MG
R":7698, "HIREDATE":"03-DEC-81", "SAL":950, "DEPTN
O":30}
7902 {"EMPNO":7902, "ENAME":"FORD", "JOB":"ANALYST", "M
GR":7566, "HIREDATE":"03-DEC-81", "SAL":3000, "DEP
TNO":20}
7934 {"EMPNO":7934, "ENAME":"MILLER", "JOB":"CLERK", "M
GR":7782, "HIREDATE":"23-JAN-82", "SAL":1300, "DEP
TNO":10}
