Home » SQL & PL/SQL » SQL & PL/SQL » Can a function out parameter be a pl/sql table
Can a function out parameter be a pl/sql table [message #188026] Wed, 16 August 2006 16:07 Go to next message
shivaram9
Messages: 35
Registered: August 2006
Member
Hi all,

Can a function have an out paramter a pl/sql table.

this function would be a standalone function.

If you what is the syntax.

Shivaram.
Re: Can a function out parameter be a pl/sql table [message #188040 is a reply to message #188026] Wed, 16 August 2006 19:20 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
By an "out parameter", I'm assuming you mean a return value (although an out parameter would work - just not common with a function).

You'll need the type defined in a package:

sql>create or replace package pkg_define
  2  is
  3    type myArrayType is table of number index by pls_integer;
  4  end;
  5  /

Package created.

sql>create or replace function f_array
  2    return pkg_define.myArrayType
  3  is
  4    v_array pkg_define.myArrayType;
  5  begin
  6    for i in 1..3 loop
  7      v_array(i) := i;
  8    end loop;
  9    return (v_array);
 10  end;
 11  /

Function created.

sql>declare
  2    v_array pkg_define.myArrayType;
  3  begin
  4    v_array := f_array();
  5    for i in 1..v_array.count loop
  6      dbms_output.put_line( 'Value: ' || v_array(i) );
  7    end loop;
  8  end;
  9  /
Value: 1
Value: 2
Value: 3

PL/SQL procedure successfully completed.

[Updated on: Wed, 16 August 2006 19:21]

Report message to a moderator

Re: Can a function out parameter be a pl/sql table [message #188071 is a reply to message #188040] Wed, 16 August 2006 22:31 Go to previous messageGo to next message
shivaram9
Messages: 35
Registered: August 2006
Member
Thanks,

I think this should work, but is there any pre-defined oracle datatype for pl/sql table, like varchar2, number.

Re: Can a function out parameter be a pl/sql table [message #188251 is a reply to message #188071] Thu, 17 August 2006 10:57 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
No. You can use a type declared in one of the SYS packages (like DBMS_UTILITY) if you want instead of creating your own package, but I would still suggest creating your own package so the types are locally defined and controlled by you (not subject to change when an Oracle version changes, as could be the case with referencing a SYS-defined type).
Re: Can a function out parameter be a pl/sql table [message #188261 is a reply to message #188026] Thu, 17 August 2006 12:33 Go to previous message
shivaram9
Messages: 35
Registered: August 2006
Member
Thanks
Previous Topic: Mutating Tables
Next Topic: SQL/XML and LONG columns - getting ORA-00932
Goto Forum:
  


Current Time: Tue Dec 06 06:04:53 CST 2016

Total time taken to generate the page: 0.12217 seconds