Function returns Record - How to select [message #657899] |
Fri, 25 November 2016 23:06 |
vactirio
Messages: 4 Registered: January 2009
|
Junior Member |
|
|
Hi all,
I have the following sample package:
create or replace package testp is
type my_record_type is record(
a number(10),
b number(10));
function test_func(v in number) return my_record_type;
end;
and
create or replace package body testp is
function test_func(v in number) return my_record_type is
tmp_rec my_record_type;
begin
tmp_rec.a := 1;
tmp_rec.b := 2;
return tmp_rec;
end;
begin
null;
end;
It seems to be impossible to do this
select testp.test_func(1).a from dual
I get ORA-00902 Invalid Datatype...
Thank U
Andreas
|
|
|
|
|
|
|
|
Re: Function returns Record - How to select [message #657912 is a reply to message #657905] |
Sat, 26 November 2016 06:09 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Sat, 26 November 2016 02:02
Quote:You cannot use a PL/SQL type in a SQL statement.
With one exception - package pipelined function:
create or replace
package testp
is
type my_record_type
is
record(
a number(10),
b number(10)
);
type my_table_type
is
table of my_record_type;
function test_func(
v in number
)
return my_table_type
pipelined;
end;
/
create or replace
package body testp
is
function test_func(
v in number
)
return my_table_type
pipelined
is
tmp_rec my_record_type;
begin
tmp_rec.a := 1;
tmp_rec.b := 2;
pipe row(tmp_rec);
end;
end;
/
select a from table(testp.test_func(1))
/
A
----------
1
SQL>
Under the hood Oracle creates table & object SQL types based on PL/SQL table & record types:
SQL> select object_name
2 from dba_objects
3 where object_type = 'TYPE'
4 and created >= trunc(sysdate,'hh')
5 order by created
6 /
OBJECT_NAME
---------------------------------------------------------------
SYS_PLSQL_2F6B36F3_28_1
SYS_PLSQL_2F6B36F3_9_1
SYS_PLSQL_2F6B36F3_DUMMY_1
SQL> desc SYS_PLSQL_2F6B36F3_28_1
SYS_PLSQL_2F6B36F3_28_1 TABLE OF SYS_PLSQL_2F6B36F3_9_1
Name Null? Type
----------------------------------------- -------- -----------
A NUMBER(10)
B NUMBER(10)
SQL> desc SYS_PLSQL_2F6B36F3_9_1
Name Null? Type
----------------------------------------- -------- -----------
A NUMBER(10)
B NUMBER(10)
SQL> desc SYS_PLSQL_2F6B36F3_DUMMY_1
SYS_PLSQL_2F6B36F3_DUMMY_1 TABLE OF NUMBER
SQL>
Personally, I don't like system generated type names and prefer not to use them.
SY.
|
|
|
|
Re: Function returns Record - How to select [message #657914 is a reply to message #657913] |
Sat, 26 November 2016 06:44 |
vactirio
Messages: 4 Registered: January 2009
|
Junior Member |
|
|
Pipeline is a solution and thank you but its an overkill.
I like the idea that a package is self contained including also all the types needed but as it seems there are restrictions on how to use those package types.
Thanks
Andreas
|
|
|