Version: Oracle 12.1
I need to build several stored procedures/function which will return table as its output, This is the only way I can call stored procedures through Tableau(a visualisation tool).
I am trying to create a simple sample pipelined function so that I can just reference it as a normal table, some thing like this. My issue is with declaration of l_rec, since I have no %ROWTYPE to base that upon. Any insights?
> SELECT * FROM TABLE(func_name);
CREATE OR REPLACE TYPE emp_rc AS OBJECT (
empno NUMBER,
ENAME VARCHAR2(20),
EJOB VARCHAR2(50) -- ejob is a derived variable from ename ||,|| job
);
CREATE OR REPLACE TYPE emp_tb AS TABLE OF emp_rc;
CREATE OR REPLACE FUNCTION EMP_FUNC RETURN emp_tb
PIPELINED IS
l_sql VARCHAR2(32767);
TYPE l_cur_type IS REF CURSOR;
l_cur l_cur_type;
l_rec ; -- This is where I am stuck, not sure which type should i declare here, cant use row type or type.
BEGIN
l_sql := 'SELECT empno, ename, ename ||'',''||job ejob FROM EMP';
OPEN l_cur FOR l_sql;
LOOP
FETCH l_cur INTO l_rec;
EXIT WHEN l_cur%NOTFOUND;
PIPE ROW (emp_rc (empno => l_rec.empno, ename => l_rec.ename, ejob => lrec. ?? ));
END LOOP;
RETURN;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM || chr(10) || l_sql);
END;