Solved

Multi-statement table-valued function for Oracle

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
history tablespace temp usage 2 42
Extract the first word (before the , ) 2 48
Oracle and DateTime math 6 38
selective queries 7 29
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

772 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