Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 854
  • Last Modified:

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.
0
pcarrollnf
Asked:
pcarrollnf
  • 2
1 Solution
 
andrewstCommented:
You would first need to create (or reuse) a user-defined TYPE:

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
0
 
pcarrollnfAuthor Commented:
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.
0
 
andrewstCommented:
Yes, use the BULK COLLECT as I showed it:

SELECT id BULK COLLECT INTO it FROM MyTable;

 
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now