Oracle: Select Data Type from Entry

Question

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

In particular, I am trying to use an OracleCommand object trying to get an object in C # code. At first I tried ...

CALL FUNCTION_NAME() INTO :loRetVal 

... but I get a data type error for any type that I use. I also tried ...

 SELECT * FROM FUNCTION_NAME() 

... and ...

 SELECT * FROM TABLE ( FUNCTION_NAME() ) 

... to no avail. I guess I'm looking ...

 SELECT * FROM RECORD ( FUNCTION_NAME() ) 

... which of course does not exist.

The only solution I could come up with was to wrap this function call with another function call, in which the external function returns a TABLE of records containing this single record. This, however, seems cumbersome, and I'm looking for a simpler method. Any help would be appreciated.

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

Answer 1

The record data type is the PL / SQL data type. SQL does not know about this. This is probably why you get the 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 talking about. 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 a function in SQL, then you can create an object of type SQL. Note that calling your function directly from C # seems to be more preferable than insisting on using SQL for 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 2

I think this is what you are looking for; to get the values ​​in a select expression:

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

Since your function returns a record of a non-native type, you cannot use standard methods. If you want to get the actual record as an object in C #, then you will do some readings on user types in the ODP.net documentation.

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

Answer 3

Can you

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

and use it directly in a SQL statement? I ask because I have a stored procedure that I cannot edit. The stored proc has an output variable, which is the type of record that I must reference in the SQL statement. I already created a function to call proc, but if I don’t need to convert the record to print an object that would be nice.

I would call it this way:

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

Answer 4

The formatting of my comment for Rob van Wake is bad. Continue the 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 5

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 has been tested and should give you a good start. Just search for the PIPELINED keyword in Oracle for more information (assuming you are working with Oracle ...)

Answer 6

Why do you even need to use SQL? You can use System.Data.CommandType.StoredProcedure to call a function.

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

Call Oracle function in package with C #