The echo_package package specification defines the DESCRIBE and FETCH_ROWS methods.
CREATE PACKAGE echo_package AS
FUNCTION describe(tab IN OUT dbms_tf.table_t,
cols IN dbms_tf.columns_t)
RETURN dbms_tf.describe_t;
PROCEDURE fetch_rows;
END echo_package;
Package created.
CREATE PACKAGE BODY echo_package AS
col_name_prefix CONSTANT dbms_id := '"ECHO_';
col_name_suffix CONSTANT dbms_id := '"';
FUNCTION describe(tab IN OUT dbms_tf.table_t,
cols IN dbms_tf.columns_t)
RETURN dbms_tf.describe_t
AS
new_cols dbms_tf.columns_new_t;
col_id PLS_INTEGER := 1;
BEGIN
FOR i IN 1 .. tab.column.count LOOP
continue WHEN NOT dbms_tf.supported_type(tab.column(i).description.TYPE);
FOR j IN 1 .. cols.count LOOP
IF (tab.column(i).description.name = cols(j)) THEN
tab.column(i).for_read:= true;
new_cols(col_id) := tab.column(i).description;
new_cols(col_id).name := col_name_prefix
|| regexp_replace(new_cols(col_id).name, '^"|"$')
|| col_name_suffix;
col_id := col_id + 1;
EXIT;
END IF;
END LOOP;
END LOOP;
RETURN dbms_tf.describe_t(new_columns => new_cols);
END;
PROCEDURE fetch_rows
AS
rowset dbms_tf.row_set_t;
BEGIN
dbms_tf.get_row_set(rowset);
dbms_tf.put_row_set(rowset);
END;
END echo_package;
Package Body created.
CREATE FUNCTION echo(tab TABLE, cols columns) RETURN TABLE
PIPELINED ROW POLYMORPHIC USING echo_package;
Function created.
SELECT *
FROM echo(scott.emp, COLUMNS(ename, sal, hiredate))
WHERE deptno = 20
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ECHO_ENAME | ECHO_HIREDATE | ECHO_SAL | 7566 | JONES | MANAGER | 7839 | 02-APR-81 | 2975 | - | 20 | JONES | 02-APR-81 | 2975 | 7788 | SCOTT | ANALYST | 7566 | 19-APR-87 | 3000 | - | 20 | SCOTT | 19-APR-87 | 3000 | 7902 | FORD | ANALYST | 7566 | 03-DEC-81 | 3000 | - | 20 | FORD | 03-DEC-81 | 3000 | 7369 | SMITH | CLERK | 7902 | 17-DEC-80 | 800 | - | 20 | SMITH | 17-DEC-80 | 800 | 7876 | ADAMS | CLERK | 7788 | 23-MAY-87 | 1100 | - | 20 | ADAMS | 23-MAY-87 | 1100 |
---|
WITH w AS (
SELECT e.*, dname, loc
FROM scott.emp e, scott.dept d
WHERE e.deptno = d.deptno)
SELECT ename, echo_loc, dname
FROM echo(w, COLUMNS(sal, dname, loc, hiredate))
WHERE deptno = 30 AND echo_sal > 1000
ENAME | ECHO_LOC | DNAME | BLAKE | CHICAGO | SALES | ALLEN | CHICAGO | SALES | WARD | CHICAGO | SALES | MARTIN | CHICAGO | SALES | TURNER | CHICAGO | SALES |
---|
WITH w AS (
SELECT e.*, dname, loc
FROM scott.emp e, scott.dept d
WHERE e.deptno=d.deptno)
SELECT echo_ename, dname
FROM echo(w, COLUMNS(loc, deptno, dname, ename)) e
WHERE ename IN (SELECT echo_ename
FROM echo(scott.emp, columns(sal, deptno, ename, hiredate))
WHERE deptno = e.echo_deptno
AND sal > 1000)
ECHO_ENAME | DNAME | KING | ACCOUNTING | BLAKE | SALES | CLARK | ACCOUNTING | JONES | RESEARCH | SCOTT | RESEARCH | FORD | RESEARCH | ALLEN | SALES | WARD | SALES | MARTIN | SALES | TURNER | SALES | ADAMS | RESEARCH | MILLER | ACCOUNTING |
---|