Home » SQL & PL/SQL » SQL & PL/SQL » Function returns Record - How to select
Function returns Record - How to select [message #657899] Fri, 25 November 2016 23:06 Go to next message
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 #657900 is a reply to message #657899] Fri, 25 November 2016 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 26204
Registered: January 2009
Location: SoCal
Senior Member
vactirio wrote on Fri, 25 November 2016 21:06
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
what do you expect & desire result to be & why?

Please post URL to Oracle documentation that indicates your SQL should succeed without error.
Re: Function returns Record - How to select [message #657902 is a reply to message #657900] Fri, 25 November 2016 23:58 Go to previous messageGo to next message
vactirio
Messages: 4
Registered: January 2009
Junior Member
The expected should be:

select testp.test_func(1).a from dual
Return -> 1

I am looking for a way to use
testp.test_func(1).a
in an sql statement
Re: Function returns Record - How to select [message #657903 is a reply to message #657899] Sat, 26 November 2016 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I get ORA-00902 Invalid Datatype...
You cannot use a PL/SQL type in a SQL statement, you have to create a SQL type using CREATE TYPE:
SQL> create or replace  type my_record_type is object (
  2      a number(10),
  3      b number(10)
  4  );
  5  /

Type created.

SQL> create or replace package testp is
  2    function test_func(v in number) return my_record_type;
  3  end;
  4  /

Package created.

SQL> create or replace package body testp is
  2    function test_func(v in number) return my_record_type is
  3      tmp_rec my_record_type := my_record_type(null, null);
  4    begin
  5      tmp_rec.a := 1;
  6      tmp_rec.b := 2;
  7      return tmp_rec;
  8    end;
  9  end;
 10  /

Package body created.

SQL> select testp.test_func(1).a from dual
  2  /
TESTP.TEST_FUNC(1).A
--------------------
                   1

1 row selected.
Re: Function returns Record - How to select [message #657904 is a reply to message #657903] Sat, 26 November 2016 00:59 Go to previous messageGo to next message
vactirio
Messages: 4
Registered: January 2009
Junior Member
Thank you for the answer.
Is it possible to do that with the "Record Type" in the package?

Re: Function returns Record - How to select [message #657905 is a reply to message #657904] Sat, 26 November 2016 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
You cannot use a PL/SQL type in a SQL statement.
Re: Function returns Record - How to select [message #657912 is a reply to message #657905] Sat, 26 November 2016 06:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 #657913 is a reply to message #657912] Sat, 26 November 2016 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I didn't want to enter into these details and all possible solutions.
When the purpose is to return only one value/object, a pipelined function is quite like to use a sledgehammer to crack a nut.
In addition, in my opinion, it is not really smart to let Oracle create for you a type you need (more not one type, 3 types), I hate these side and hidden effects.

Re: Function returns Record - How to select [message #657914 is a reply to message #657913] Sat, 26 November 2016 06:44 Go to previous message
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
Previous Topic: How to generate a insert script as it is what ever we running the insert script?
Next Topic: How to pass date parameters in dynamic sql?
Goto Forum:
  


Current Time: Sun Oct 21 00:39:15 CDT 2018