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.
pcarrollnfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.