CODEWITHSUNDEEP
oracle-databasefunctionplsqlprocedure
rajesh4ext
rajesh4ext

Reputation: 5

PL/SQL Procedure/function with params to return a table from(of) a select query

how can a (Oracle)function/Procedure with parameters can be created to return a table, which was returned by a select statement, inside a block

tried with refcursor getting data as below ,all in a single row

{<G_ID=748,P_G_ID=746,P_NO=null,M_DATE=06-OCT-20 >, <G_ID=749,P_G_ID=746,P_NO=null,M_DATE=06-OCT-20>} instead of a table format

image link for the sample output

P.S. I am pretty new to PL/SQL

Upvotes: 0

Views: 129

Answers (1)

Littlefoot
Littlefoot

Reputation: 142768

One option is to return refcursor. Here's an example:

SQL> create or replace function f_test (par_deptno in number)
  2    return sys_refcursor
  3  is
  4    rc sys_refcursor;
  5  begin
  6    open rc for select * from emp where deptno = par_deptno;
  7    return rc;
  8  end;
  9  /

Function created.

SQL> select f_test(10) from dual;
   
F_TEST(10)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81      10000                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10


SQL>

Upvotes: 1

Related Questions