Home » SQL & PL/SQL » SQL & PL/SQL » Return Table in a Function (ORACLE 9)
Return Table in a Function [message #289946] Wed, 26 December 2007 17:10 Go to next message
Krann
Messages: 4
Registered: December 2007
Junior Member
hi, i am new creating functions in Oracle and i want to know if it´s possible to return a table type from a user function. I knwon in Transact SQL is possible but i am not sure in PL/SQL.

i make this:

create or replace funcion <name>
return
is
type r is record(
c int
);
type t table of r;
begin
t(1).c:=3;
return t;
end;

But it have compiling errors.
Thank you for your help.

Krann.
Re: Return Table in a Function [message #289949 is a reply to message #289946] Wed, 26 December 2007 18:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Yes, you can return a table type in a function. If you are going to use it in sql, then it must be a sql type created outside the function, not a pl/sql type created inside the function. The following is what you asked for, but since I don't know your total requirements, may not be the best thing for your needs. Commonly, people coming from SQL Server and MySQL are used to populating a temporary table of sorts and selecting from it. In Oracle, we do this using a ref cursor to return the result set.


SCOTT@orcl_11g> create or replace type r as object (c number);
  2  /

Type created.

SCOTT@orcl_11g> create or replace type t as table of r;
  2  /

Type created.

SCOTT@orcl_11g> create or replace function your_function
  2    return t
  3  is
  4    v_tab t := t();
  5  begin
  6    v_tab.extend;
  7    v_tab(v_tab.count) := r (3);
  8    v_tab.extend;
  9    v_tab(v_tab.count) := r (4);
 10    return v_tab;
 11  end your_function;
 12  /

Function created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> select * from table (cast (your_function() as t))
  2  /

         C
----------
         3
         4

SCOTT@orcl_11g> 

icon14.gif  Re: Return Table in a Function [message #290159 is a reply to message #289949] Thu, 27 December 2007 17:53 Go to previous message
Krann
Messages: 4
Registered: December 2007
Junior Member
Thank you Barbara.
Your example help me a lot !!!!
Previous Topic: Forcing Commit
Next Topic: breaking column opening and production
Goto Forum:
  


Current Time: Sun Dec 11 02:11:10 CST 2016

Total time taken to generate the page: 0.12264 seconds