Insert values from one table of objects to another table of objects

Question

I have this datatype declared and afterwards populated:

CREATE OR REPLACE TYPE CRAMER."T_CREATELINK_PORTLIST" IS TABLE OF o_CreateLink_PORTLIST;

AND

TYPE o_CreateLink_PORTLIST AS OBJECT (
    PORTNAME                VARCHAR2(50),
    PORTID                  NUMBER,
    ISNEWPORT               NUMBER,
    SHELFNAME               VARCHAR2(50),
    SLOTNAME                VARCHAR2(50),
    BANDWIDTHNAME           VARCHAR2(50),
    ISSELECTED              NUMBER );

What I want to do is get some object/s from one table of this type to another table of same type. Something like this:

lsa_all_ports cramer.t_CreateLink_PORTLIST;
INSERT INTO lsa_filter_ports VALUES( SELECT *
      FROM TABLE(CAST(lsa_all_ports AS cramer.t_CreateLink_PORTLIST)) new
      WHERE new.bandwidthname = 'band2');

I'm pretty sure this syntax is wrong, but is there a simple way to do this??

Other atempts:

SELECT * INTO lsa_filter_ports
    FROM TABLE(CAST(lsa_all_ports AS cramer.t_CreateLink_PORTLIST)) new
    WHERE new.bandwidthname = 'band2';

ORA-06550: not enough values

lsa_filter_ports cramer.t_CreateLink_PORTLIST := cramer.t_CreateLink_PORTLIST();    
INSERT INTO lsa_filter_ports 
     (SELECT * FROM TABLE(CAST(lsa_all_ports AS cramer.t_CreateLink_PORTLIST))
     WHERE bandwidthname = 'band2');

ORA-00942: table or view does not exist

Ponder Stibbons · Accepted Answer · 2016-09-09 13:22:12Z

1

If I understood everything correctly You can do it like here:

declare 
  lsa_all_ports t_createlink_portlist := 
    t_createlink_portlist( o_createlink_portlist ('abc', 1, 'band1'), 
                           o_createlink_portlist ('def', 2, 'band2'));
  lsa_filter_ports t_createlink_portlist := t_createlink_portlist();
begin 
  select cast(multiset(select * 
                         from table(lsa_all_ports) 
                         where bandwidthname = 'band2') 
              as t_createlink_portlist)
    into lsa_filter_ports 
    from dual;
end;

answered Sep 9, 2016 at 13:22

Ponder Stibbons

14.9k2 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrei Maieras Over a year ago

Well besides that you understood what I need, you got the solution :) thanks a lot

Rahul Tripathi · Accepted Answer · 2016-09-09 10:58:21Z

0

You can better try to use the SELECT...INTO command to do this.

The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections.

And if the table is already created and you want to insert into it then you can simply do like

insert into lsa_filter_ports 
select * from o_CreateLink_PORTLIST 
WHERE bandwidthname = 'band2'

answered Sep 9, 2016 at 10:58

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

1 Comment

Andrei Maieras Over a year ago

I tried both of your proposed solutions but I'm not able to find the right syntax. I've updated the question

XING · Accepted Answer · 2016-09-09 15:10:06Z

0

Please see below the usage.

--Created object

 create or replace TYPE o_CreateLink_PORTLIST AS OBJECT (
        PORTNAME                VARCHAR2(50),
        PORTID                  NUMBER,
        ISNEWPORT               NUMBER,
        SHELFNAME               VARCHAR2(50),
        SLOTNAME                VARCHAR2(50),
        BANDWIDTHNAME           VARCHAR2(50),
        ISSELECTED              NUMBER );

---created table same as object

 create table lsa_filter_ports (
        PORTNAME                VARCHAR2(50),
        PORTID                  NUMBER,
        ISNEWPORT               NUMBER,
        SHELFNAME               VARCHAR2(50),
        SLOTNAME                VARCHAR2(50),
        BANDWIDTHNAME           VARCHAR2(50),
        ISSELECTED              NUMBER );

--A type of object

CREATE OR REPLACE TYPE T_CREATELINK_PORTLIST IS TABLE OF o_CreateLink_PORTLIST;

---Anonymous Block

 declare
    lsa_all_ports  t_CreateLink_PORTLIST;

    begin

    INSERT INTO lsa_filter_ports 
          SELECT *
             FROM TABLE(CAST(lsa_all_ports AS t_CreateLink_PORTLIST)) new
                 WHERE new.bandwidthname = 'band2';


    end;

Collection to collection copy

declare
    lsa_all_ports     t_CreateLink_PORTLIST:= t_createlink_portlist(o_createlink_portlist ('abc', 1, 'band1'),o_createlink_portlist ('def', 2, 'band2'));
    lsa_filter_ports  t_CreateLink_PORTLIST:= t_createlink_portlist();

    cursor cur is 
              SELECT *
             --FROM TABLE(CAST(lsa_all_ports AS t_CreateLink_PORTLIST)) new
                 FROM TABLE(lsa_all_ports) new
                 WHERE new.bandwidthname = 'band2';

   begin

   for i in cur
     loop

         lsa_filter_ports.extend;

         lsa_filter_ports(cur%rowcount) := lsa_all_ports(cur%rowcount);

         dbms_output.put_line(lsa_filter_ports(cur%rowcount).PORTNAME);

     end loop;            


    end;

edited Sep 9, 2016 at 15:10

answered Sep 9, 2016 at 13:01

XING

9,9164 gold badges25 silver badges40 bronze badges

4 Comments

Andrei Maieras Over a year ago

Can I do this insert for lsa_filter_ports beeing of type t_CreateLink_PORTLIST ?? and not create a phisical table?

XING Over a year ago

So you mean to say you wanted to have another object same as t_CreateLink_PORTLIST named lsa_filter_ports and wanted all the values of the table to the new table..Am i correct, If yes then why do you want to store same result to 2 tables,

Andrei Maieras Over a year ago

Yes I want to copy from one collection to another, but applying a filter WHERE new.bandwidthname = 'band2'; this 'band2' will come as a parameter for a SP and then the all_ports and filter_ports collections will be sent as output

XING Over a year ago

you can do it this way as well. Check my updated post,

Collectives™ on Stack Overflow

Insert values from one table of objects to another table of objects

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related