Home » SQL & PL/SQL » SQL & PL/SQL » Can i Use the INSERT INTO on pl/sql TABLE
Can i Use the INSERT INTO on pl/sql TABLE [message #224477] Wed, 14 March 2007 07:19 Go to next message
mallikj2
Messages: 7
Registered: March 2007
Junior Member

CREATE OR REPLACE TYPE obj_sample IS OBJECT
(
samp_id NUMBER(10),
samp_name VARCHAR2(200)
);
/

CREATE OR REPLACE TYPE sample_tab IS TABLE OF obj_sample;
/

What are the ways that are possible to Insert Values into the table
"sample_tab"
Re: Can i Use the INSERT INTO on pl/sql TABLE [message #224489 is a reply to message #224477] Wed, 14 March 2007 07:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
SQL> CREATE OR REPLACE TYPE obj_sample IS OBJECT
  2  (samp_id NUMBER(10)
  3  ,samp_name VARCHAR2(200));
  4  /

Type created.

SQL> 
SQL> CREATE OR REPLACE TYPE sample_tab IS TABLE OF obj_sample;
  2  /

Type created.

SQL> 
SQL> set serveroutput on size 10000
SQL> 
SQL> DECLARE
  2    t_test   sample_tab := sample_Tab();
  3  BEGIN
  4    t_test.extend(3);
  5    t_test(1) := obj_sample(1,'Test row 1');
  6    t_test(2) := obj_sample(2,'Test row 2');
  7    t_test(3) := obj_sample(3,'Test row 2');  
  8    
  9    for i in t_test.first .. t_test.last loop
 10      dbms_output.put_line('Samp_Id='||t_test(i).samp_id||' samp_name='||t_Test(i).samp_name);
 11    end loop;
 12    
 13  end;
 14  /
Samp_Id=1 samp_name=Test row 1
Samp_Id=2 samp_name=Test row 2
Samp_Id=3 samp_name=Test row 2

PL/SQL procedure successfully completed.
Re: Can i Use the INSERT INTO on pl/sql TABLE [message #224584 is a reply to message #224489] Wed, 14 March 2007 13:17 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
You can also use SELECT ... BULK COLLECT INTO ... to populate it.

(I haven't tested it, but you can use it as a general idea)

declare
   lt sample_tab := sample_tab ();
begin
   select obj_sample (s.samp_id, s.samp_name)
     bulk collect
     into lt
     from samp s;
   --
   for i in lt.first .. lt.last
   loop
      dbms_output.put_line ('i=' || i || ',samp_id=' || lt(i).samp_id || ',samp_name=' || lt(i).samp_name);
   end loop;
end;

Re: Can i Use the INSERT INTO on pl/sql TABLE [message #224591 is a reply to message #224584] Wed, 14 March 2007 13:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm, I'm afraid that will not work straight away:
SQL> drop table faq;

Table dropped.

SQL> create table faq
  2  ( col1 number
  3  , col2 varchar2(10)
  4  );

Table created.

SQL> create or replace type faq_object is object
  2  ( col1 number
  3  , col2 varchar2(10)
  4  )
  5  /

Type created.

SQL> insert into faq values (1, 'One');

1 row created.

SQL> insert into faq values (2, 'Two');

1 row created.

SQL> insert into faq values (3, 'Three');

1 row created.

SQL> insert into faq values (4, 'Four');

1 row created.

SQL> create or replace type faq_object_tab is table of faq_object
  2  /

Type created.

SQL> declare
  2    l_faq_tab faq_object_tab;
  3    cursor c_faq
  4    is
  5      select faq.col1
  6      ,      faq.col2
  7      from   faq
  8    ;
  9  begin
 10    open c_faq;
 11    fetch c_faq bulk collect into l_faq_tab;
 12    close c_faq;
 13    dbms_output.put_line(l_faq_tab.count||' records fetched');
 14  end;
 15  /
  fetch c_faq bulk collect into l_faq_tab;
                                *
ERROR at line 11:
ORA-06550: line 11, column 33:
PLS-00386: type mismatch found at 'L_FAQ_TAB' between FETCH cursor and INTO variables
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored

Re: Can i Use the INSERT INTO on pl/sql TABLE [message #224597 is a reply to message #224591] Wed, 14 March 2007 13:42 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
That's true, Frank, but I'm using record's type constructor in my SELECT, and you're not in your fetch.
SQL> create table samp (samp_id number, samp_name varchar2 (10));

Table created

SQL> insert into samp values (1, 'Name 1');

1 row inserted

SQL> insert into samp values (2, 'Name 2');

1 row inserted

SQL> create type obj_sample as object (samp_id number, samp_name varchar2 (10));
  2  /

Type created

SQL> create type sample_tab as table of obj_sample;
  2  /

Type created

SQL> set serveroutput on
SQL> declare
  2     lt sample_tab := sample_tab ();
  3  begin
  4     select obj_sample (s.samp_id, s.samp_name)
  5       bulk collect
  6       into lt
  7       from samp s;
  8     --
  9     for i in lt.first .. lt.last
 10     loop
 11        dbms_output.put_line ('i=' || i || ',samp_id=' || lt(i).samp_id || ',samp_name=' || lt(i).samp_name);
 12     end loop;
 13  end;
 14  /

i=1,samp_id=1,samp_name=Name 1
i=2,samp_id=2,samp_name=Name 2

PL/SQL procedure successfully completed

SQL> 

Re: Can i Use the INSERT INTO on pl/sql TABLE [message #224600 is a reply to message #224597] Wed, 14 March 2007 13:45 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You are absolutely right. I read over that, sorry.
SQL> declare
  2    l_faq_tab faq_object_tab;
  3    cursor c_faq
  4    is
  5      select faq_object(faq.col1
  6      ,      faq.col2)
  7      from   faq
  8    ;
  9  begin
 10    open c_faq;
 11    fetch c_faq bulk collect into l_faq_tab;
 12    close c_faq;
 13    dbms_output.put_line(l_faq_tab.count||' records fetched');
 14  end;
 15  /
4 records fetched

PL/SQL procedure successfully completed.
Previous Topic: PL/SQL Error
Next Topic: Role of ETL Tool - OWB
Goto Forum:
  


Current Time: Wed Dec 07 05:12:45 CST 2016

Total time taken to generate the page: 0.17859 seconds