Oracle: Select From Record Datatype

Question

I have a function that returns a record datatype (2 fields: ID and Name). How can I get at the data from a select statement?

Specifically, I am trying using an OracleCommand object attempting to get the object into my C# code. I initially tried ...

CALL FUNCTION_NAME() INTO :loRetVal

... but I get a data type error for whatever type I use. I have also tried ...

SELECT * FROM FUNCTION_NAME()

... and ...

SELECT * FROM TABLE ( FUNCTION_NAME() )

... to no avail. I guess I am looking for ...

SELECT * FROM RECORD ( FUNCTION_NAME() )

... which, of course, doesn't exist.

The only solution I have been able to come up with is to wrap this function call in another function call in which the outer function returns a TABLE of records containing this sole record. This, however, seems cumbersome and I am looking for a simpler method. Any help would be appreciated.

EDIT: Sorry, I have also tried SELECT FUNCTION_NAME() FROM DUAL.

Answer 1

Can you

CREATE TYPE <object name> AS TABLE OF <record type> 

and use that directly in a SQL statement? I ask because I have a stored proc that I can not edit. The stored proc has an output variable that is record type that I have to reference in a SQL statement. I have already created a function to call the proc, but if I don't have to convert the record to type object that would be nice.

I would later call it like:

SELECT *
FROM TABLE( CAST( <function name>() as <object name>));

Answer 2

I think you are looking for PIPELINED functionality:

CREATE TABLE test_table(tt_id INTEGER,tt_text VARCHAR2(40));

CREATE PACKAGE test_pkg IS
    TYPE tp_rec IS RECORD(tt_id INTEGER,tt_text VARCHAR2(40));
    TYPE tp_recs IS TABLE OF tp_rec;

    FUNCTION test_func RETURN tp_recs PIPELINED;
    FUNCTION test_func1 RETURN tp_recs PIPELINED;
    FUNCTION test_func2(ivar INTEGER) RETURN tp_recs PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
    FUNCTION test_func RETURN tp_recs PIPELINED
    AS
       currec tp_rec;
    BEGIN
       currec.tt_id := 1;
       currec.tt_text := 'test1';
       PIPE ROW(currec);
    END;

    FUNCTION test_func1 RETURN tp_recs PIPELINED
    AS
       currec tp_rec;
       CURSOR t_cursor IS
           SELECT * FROM test_table;
    BEGIN
        OPEN t_cursor;
        LOOP
            FETCH t_cursor INTO currec;
            EXIT WHEN t_cursor%NOTFOUND;
            PIPE ROW(currec);
        END LOOP;
        CLOSE t_cursor;
    END;

    FUNCTION test_func2(ivar INTEGER) RETURN tp_recs PIPELINED
    AS
       currec tp_rec;
    BEGIN
       SELECT * INTO currec FROM test_table WHERE tt_id = ivar;
       PIPE ROW(currec);
    END;

END;
/

BEGIN
    INSERT INTO test_table VALUES(1,'test1');
    INSERT INTO test_table VALUES(2,'test2');
    INSERT INTO test_table VALUES(3,'test3');
    COMMIT;
END;
/

SELECT * FROM TABLE(test_pkg.test_func());
SELECT * FROM TABLE(test_pkg.test_func1());
SELECT * FROM TABLE(test_pkg.test_func2(2));

The above code is tested, and should give you a good start on this. Just look up the PIPELINED keyword in Oracle for more information (assuming you are working with Oracle...)

Answer 3

The formatting of my comment for Rob van Wijk is bad. To continue his thought.

-- create a collection type 
CREATE TYPE myobj_tab AS TABLE OF myobj; 

-- have the function return a collection type 
CREATE OR REPLACE function f return myobj_tab 
IS 
    objtab myobj_tab; 
BEGIN 
    objtab := myobj_tab(myobj(1,'test')); 
    return objtab; 
end f; 

-- CAST it as a table and straight up select from it. 
SELECT id, name FROM TABLE(CAST(f() AS myobj_tab));

Answer 4

A record datatype is a PL/SQL datatype. SQL doesn't know about it. That's probably why you are getting an error. See this example:

SQL> create package mypkg
  2  as
  3    type myrec is record
  4    ( id int
  5    , name varchar2(10)
  6    );
  7    function f return myrec;
  8  end mypkg;
  9  /

Package created.

SQL> create package body mypkg
  2  as
  3    function f return myrec
  4    is
  5      r myrec;
  6    begin
  7      r.id := 1;
  8      r.name := 'test';
  9      return r;
 10    end f;
 11  end mypkg;
 12  /

Package body created.

SQL> desc mypkg
FUNCTION F RETURNS RECORD
   ID                           NUMBER(38)              OUT
   NAME                         VARCHAR2(10)            OUT

SQL> select mypkg.f from dual
  2  /
select mypkg.f from dual
       *
ERROR at line 1:
ORA-00902: invalid datatype

The error in SQL I was referring to. You can call it from PL/SQL though:

SQL> declare
  2    r mypkg.myrec;
  3  begin
  4    r := mypkg.f;
  5    dbms_output.put_line(r.id);
  6    dbms_output.put_line(r.name);
  7  end;
  8  /
1
test

PL/SQL procedure successfully completed.

If you want to use the function in SQL, then you can create a SQL objecttype. Note that calling your function directly from C# looks way more preferable than insisting on using SQL to do this. But just for the record:

SQL> drop package mypkg
  2  /

Package dropped.

SQL> create type myobj is object
  2  ( id int
  3  , name varchar2(10)
  4  );
  5  /

Type created.

SQL> create package mypkg
  2  as
  3    function f return myobj;
  4  end mypkg;
  5  /

Package created.

SQL> create package body mypkg
  2  as
  3    function f return myobj
  4    is
  5    begin
  6      return myobj(1,'test');
  7    end f;
  8  end mypkg;
  9  /

Package body created.

SQL> select mypkg.f from dual
  2  /

F(ID, NAME)
--------------------------------------------------------------
MYOBJ(1, 'test')

1 row selected.

Regards, Rob.

Answer 5

I think this what you are looking for; to get the values out in a select statement:

select result.id as id, result.name
  from ( select function() as result from dual);

Because your function returns a record an not a native type you can't use the standard methods. The if you want to get the actual record as an object into C# then you have do some reading on user defined types in the ODP .net documentation.

You could also wrap the function in another function that returns a ref cursor and that is used in C# in a more standard fashion.

Answer 6

Why do you need to use SQL at all? You might be able to just use a System.Data.CommandType.StoredProcedure to call the function.

call-an-oracle-function-from-c#

Call Oracle Function in package with C#