Home » SQL & PL/SQL » SQL & PL/SQL » function returning pl/sql table of dynamic structure (oracle 11g)
function returning pl/sql table of dynamic structure [message #583015] Wed, 24 April 2013 20:19 Go to next message
rkkrk2
Messages: 31
Registered: November 2005
Location: chennai
Member
I have a requirement to be coded like this:

A function to return pl/sql table(cant use ref cursor) whose columns varies everytime it runs i.e.,
means
type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000))
type pl_tab is table of pl_tab_type

func f return pl_tab
as
...
end;

note : pl_tab_type will vary for each run of function f

i.e.,for example, pl_tab_type can be changd to as follows:

type pl_tab_type is object(col1 varchar2(1000), col2 varchar2(1000),col3 varchar2(1000))


how to return pl/sql table of dynamic type from func, thanks for help in advance.

[Updated on: Wed, 24 April 2013 20:23]

Report message to a moderator

Re: function returning pl/sql table of dynamic structure [message #583016 is a reply to message #583015] Wed, 24 April 2013 20:27 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
This design is Fatally FLAWED!
Dynamic objects can only be accessed using dynamic SQL which does NOT scale.

You will discover yourself in a deep, dark hole if you decide to continue.
I hope that others here do not facilitate this folly.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Re: function returning pl/sql table of dynamic structure [message #583023 is a reply to message #583016] Thu, 25 April 2013 00:05 Go to previous messageGo to next message
Littlefoot
Messages: 19880
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why can't you use ref cursor? Maybe you *think* you can't, but it'll turn out that you, actually, can (you need someone to push you into the right direction). However, that requires some explanation from your side first.
Re: function returning pl/sql table of dynamic structure [message #583040 is a reply to message #583023] Thu, 25 April 2013 02:07 Go to previous messageGo to next message
rkkrk2
Messages: 31
Registered: November 2005
Location: chennai
Member
we cannot use sys_ref cursor because we cant use it in sql query after return from function.
So please help me if there is a way i can do it thru pl/sql table.
Re: function returning pl/sql table of dynamic structure [message #583042 is a reply to message #583040] Thu, 25 April 2013 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
we cannot use sys_ref cursor because we cant use it in sql query after return from function.


What does this mean?

Anyway, if you can handle an array returned by a PL/SQL procedure then you can handle a ref cursor.
Maybe you don't know how to handle a ref cursor in your program.

Regards
Michel
Re: function returning pl/sql table of dynamic structure [message #583055 is a reply to message #583042] Thu, 25 April 2013 03:59 Go to previous messageGo to next message
rkkrk2
Messages: 31
Registered: November 2005
Location: chennai
Member
can anyone show me how can i query sys_refcursor , tried using table(func(xyz)), where func returns sysref cursor.- didnt worked.
is there any way to query results of sys ref cursor in sql.
Re: function returning pl/sql table of dynamic structure [message #583061 is a reply to message #583055] Thu, 25 April 2013 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can anyone show me how can i query sys_refcursor ,


From what?

Regards
Michel
Re: function returning pl/sql table of dynamic structure [message #583123 is a reply to message #583055] Fri, 26 April 2013 01:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
rkkrk2 wrote on Thu, 25 April 2013 01:59
can anyone show me how can i query sys_refcursor , tried using table(func(xyz)), where func returns sysref cursor.- didnt worked.
is there any way to query results of sys ref cursor in sql.


SCOTT@orcl_11gR2> create or replace function f
  2    (p_tab in varchar2)
  3    return sys_refcursor
  4  as
  5    v_refcur sys_refcursor;
  6  begin
  7    open v_refcur for 'select * from ' || p_tab;
  8    return v_refcur;
  9  end f;
 10  /

Function created.

SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> select f('dept') from dual
  2  /

F('DEPT')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.


1 row selected.

Re: function returning pl/sql table of dynamic structure [message #583124 is a reply to message #583123] Fri, 26 April 2013 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way:
SQL> var x refcursor
SQL> exec :x := f('dept')

PL/SQL procedure successfully completed.

SQL> print x
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

Regards
Michel
Re: function returning pl/sql table of dynamic structure [message #583134 is a reply to message #583124] Fri, 26 April 2013 03:44 Go to previous messageGo to next message
sss111ind
Messages: 496
Registered: April 2012
Location: India
Senior Member

from plsql then

DECLARE
  c1 sys_refcursor;
  t1_rec dept%rowtype;
BEGIN
  c1:=f('dept');
  LOOP
    FETCH c1 INTO t1_rec;
    EXIT
  WHEN c1%notfound;
    dbms_output.put_line(t1_rec.dname||' '||t1_rec.deptno||' '||t1_rec.loc);
  END LOOP;
END;
Re: function returning pl/sql table of dynamic structure [message #583136 is a reply to message #583134] Fri, 26 April 2013 04:02 Go to previous messageGo to next message
cookiemonster
Messages: 11278
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's noto going to work if the columns vary as the OP wants.
Re: function returning pl/sql table of dynamic structure [message #583138 is a reply to message #583136] Fri, 26 April 2013 04:08 Go to previous messageGo to next message
sss111ind
Messages: 496
Registered: April 2012
Location: India
Senior Member

Yes You are right.I just wanted to show that how to use refcursor from anonymous block.
It should be to change as per table and column.
Re: function returning pl/sql table of dynamic structure [message #583162 is a reply to message #583136] Fri, 26 April 2013 07:13 Go to previous message
Solomon Yakobson
Messages: 2077
Registered: January 2010
Senior Member
cookiemonster wrote on Fri, 26 April 2013 05:02
That's noto going to work if the columns vary as the OP wants.


Right, but assuming OP is on 11g he/she can use DBMS_SQL.TO_CURSOR_NUMBER + DBMS_SQL.DESCRIBE_COLUMNS and deal with dynamic select list. Question is why? As it was pointed out it is not worth it. One size fits all rarely works.

SY.
Previous Topic: how to execute the type variable procedure.
Next Topic: adding number in sysdate
Goto Forum:
  


Current Time: Wed Dec 17 15:30:45 CST 2014

Total time taken to generate the page: 0.05315 seconds