Solved

Multi-statement table-valued function for Oracle

Posted on 2003-11-11
3
837 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

622 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