pcarrollnf
asked on
Multi-statement table-valued function for Oracle
I have a function that returns a table in SQL Server with the following format:
CREATE FUNCTION my_sql_function(@dummy int)
RETURNS @pseudo_temp_table TABLE (sql_server_int int)
AS
BEGIN
INSERT @pseudo_temp_table
SELECT DISTINCT id FROM MyTable
RETURN
END
Is there a way to create a similar User-Defined Function in Oracle? Thanks.
CREATE FUNCTION my_sql_function(@dummy int)
RETURNS @pseudo_temp_table TABLE (sql_server_int int)
AS
BEGIN
INSERT @pseudo_temp_table
SELECT DISTINCT id FROM MyTable
RETURN
END
Is there a way to create a similar User-Defined Function in Oracle? Thanks.
ASKER
I created the type as you stated above.
I also created my function similar to above. My SQL statment looks like the following:
SELECT id INTO it FROM MyTable;
I recevie the following error when I compile it:
19 Error Text = PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER
id in MyTable is a NUMBER but the SQL statement will return many ids. Any ideas? Thanks.
I also created my function similar to above. My SQL statment looks like the following:
SELECT id INTO it FROM MyTable;
I recevie the following error when I compile it:
19 Error Text = PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got NUMBER
id in MyTable is a NUMBER but the SQL statement will return many ids. Any ideas? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
create type int_tab is table of integer;
/
Then your function becomes:
CREATE FUNCTION my_sql_function
RETURN int_tab
AS
it int_tab;
BEGIN
SELECT id
BULK COLLECT INTO it FROM MyTable;
RETURN it;
END;
/
This can then be used in SQL like this:
SQL> select * from table( cast(my_sql_function as int_tab))
2 /
COLUMN_VALUE
------------
1
2