Solved

Oracle Table Type Needs Multiple Rows

Posted on 2011-09-27
4
274 Views
Last Modified: 2012-05-12
I have a type and a table of that type
type INV_TABLE is the table
type INV_TYPE is the type

Why cant I insert multiple rows into the table.
It only ever outputs one row with  select * from table(GET_INVENTORY(97));

create or replace
function get_inventory(in_var in number) return INV_TABLE is
      
   test_recs  INV_TABLE;
    
    begin
     
  SELECT  INV_TYPE( 1, 'A',  2,  'B' )
    BULK     COLLECT
    INTO     test_recs
    FROM    dual;
       
     --only this is seen with test:
    SELECT  INV_TYPE( 1, 'A',  2,  'C' )
    BULK     COLLECT
    INTO     test_recs
    FROM    dual;
    
-- insert into test_recs values( 1, 'A',  2, 'B' ); --does not work. why?
 
    RETURN test_recs; 
 
    end;

Open in new window

0
Comment
Question by:claghorn
[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
4 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 334 total points
ID: 36710394
CREATE OR REPLACE FUNCTION get_inventory(in_var IN NUMBER)
    RETURN inv_table
IS
    test_recs   inv_table;
BEGIN
    SELECT t
      BULK COLLECT INTO test_recs
      FROM (SELECT inv_type(1, 'A', 2, 'B') t FROM DUAL
            UNION ALL
            SELECT inv_type(1, 'A', 2, 'C') FROM DUAL);


    RETURN test_recs;
END;
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 334 total points
ID: 36710405
you also might want to consider using a pipelined function

CREATE OR REPLACE FUNCTION get_inventory(in_var IN NUMBER)
    RETURN inv_table
    PIPELINED
IS
BEGIN
    PIPE ROW (inv_type(1, 'A', 2, 'B'));
    PIPE ROW (inv_type(1, 'A', 2, 'C'));

    RETURN;
END;
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 36710407
>>--does not work. why?


PL/SQL tables are not the same as physical Oracle Tables.  You would need to EXTEND them and manually assign the values.
0
 

Author Closing Comment

by:claghorn
ID: 36711848
Thanks. I'll target these methods in my readings.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

728 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