Solved

Multi-statement table-valued function for Oracle

Posted on 2003-11-11
3
835 Views
Last Modified: 2012-08-14
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
Comment
Question by:pcarrollnf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 15

Expert Comment

by:andrewst
ID: 9730954
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
 

Author Comment

by:pcarrollnf
ID: 9731789
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
 
LVL 15

Accepted Solution

by:
andrewst earned 50 total points
ID: 9731862
Yes, use the BULK COLLECT as I showed it:

SELECT id BULK COLLECT INTO it FROM MyTable;

 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
update statement in oracle 9 53
Shared Service Environment 2 54
Sorting a SQL script 5 41
Force XMLSEQUENCE to return empty tags for null values. 10 46
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question