Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Procedure or Function to return table.

user588690Oct 5 2017 — edited Oct 6 2017

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;

This post has been answered by Billy Verreynne on Oct 6 2017
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 3 2017
Added on Oct 5 2017
2 comments
482 views