Home » SQL & PL/SQL » SQL & PL/SQL » Help Needed in selecting the values from PL/SQL Table and Unable to Bulk Collect in PLSQL table (2 t
Help Needed in selecting the values from PL/SQL Table and Unable to Bulk Collect in PLSQL table (2 t [message #338281] Mon, 04 August 2008 07:45 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi,

I need help in writing a PL/SQL procedure/package on below scenario.

I've 3 main tables and 3 archive tables for main tables. The descirption of tables is mentioned in code section.
Table ACTNOTICE have a primary key NOTID.
Table Term1 have a primary key TERM1ID and NOTID is the foriegn key to ACTNOTICE.
Table Term2 have a primary key TERM2ID and TERM1ID is the foriegn key to TERM1.

Based on some condition say where NOTNAME of ACTNOTICE is 'I', I need to select the records and put them into archive tables.
Also I need to store the primary key of that table in a PL/SQL Table, which can be used to transfer 2nd table(TERM1) records into his acrchive table based on the relation between 2 tables.
and store the primary key values of term1 table into another PL/SQL table, , which can be used to transfer 3rd table(TERM2) records into his acrchive table based on the relation between 2 tables.
Then I need to delete the records of third table based on the join of 2nd and 3rd table.
Then I need to delete the records of second table based on the join of 1st and 2nd table.
Then I need to delete the records of main(ACTNOTICE) Table.

I would like to mention that these tables having 50 millions, 100 millions and 140 million records respectively.
So the procedure must be tuned and should work faster.

I tried doing the same by using bulk collect and for all, but when I tried to select the values from a PL/SQL table using Select statement, it fails.
I tried to use the CAST function also but not succeded.

Can anybody help me out in this.


SQL> desc ACTNOTICE
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 NOTID                                     NOT NULL NUMBER(4)
 NOTNAME                                            VARCHAR2(30)
 A1                                                 NUMBER(5)
 A2                                                 NUMBER(5)
 A3                                                 NUMBER(5)


SQL> desc term1
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 TERMID                                    NOT NULL NUMBER(4)
 NOTID                                              NUMBER(4)
 B1                                                 NUMBER(4)
 B2                                                 NUMBER(4)
 B3                                                 NUMBER(4)

SQL> desc term2
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------
 TERM2ID                                   NOT NULL NUMBER(4)
 TERMID                                             NUMBER(4)
 C1                                                 NUMBER(4)
 C2                                                 NUMBER(4)
 C3                                                 NUMBER(4)

CREATE OR REPLACE PACKAGE pack1
IS
PROCEDURE p1;
PROCEDURE p2;

TYPE actnotice_type IS TABLE OF ACTNOTICE%ROWTYPE INDEX BY PLS_INTEGER;
TYPE actnotice_pk_type IS TABLE OF ACTNOTICE.notid%TYPE;
actnotice_type_var  actnotice_type;
actnotice_pk_type_var actnotice_pk_type;


TYPE term1_type IS TABLE OF TERM1%ROWTYPE INDEX BY PLS_INTEGER;
TYPE term1_pk_type IS TABLE OF TERM1.termid%TYPE;
term1_type_var term1_type;
term1_pk_type_var term1_pk_type;

ROWS PLS_INTEGER := 100;

END pack1;
/


CREATE OR REPLACE PACKAGE BODY pack1
IS
PROCEDURE  p1
IS
  CURSOR c1 IS
 SELECT * FROM ACTNOTICE WHERE notname='I';
BEGIN
  OPEN c1;
    LOOP
   FETCH c1  BULK COLLECT  INTO actnotice_type_var LIMIT ROWS;
                      EXIT WHEN actnotice_type_var.COUNT = 0;
      FORALL i IN actnotice_type_var.first .. actnotice_type_var.last
   INSERT INTO  ARCACTNOTICE VALUES actnotice_type_var(i)
    RETURNING notid BULK COLLECT INTO actnotice_pk_type_var;
 END LOOP;
  CLOSE c1;
END p1;

PROCEDURE p2
IS
CURSOR c1 IS
 SELECT * FROM TERM1 WHERE notid IN
(  SELECT notid FROM TABLE(CAST(actnotice_pk_type_var AS actnotice_pk_type))   );
BEGIN
OPEN c1;
   LOOP
           FETCH c1  BULK COLLECT INTO term1_type_var LIMIT ROWS;
 EXIT WHEN term1_type_var.COUNT = 0;
           FORALL i IN term1_type_var.first .. term1_type_var.last
 INSERT INTO ARCTERM1 VALUES term1_type_var(i)
   RETURNING termid BULK COLLECT INTO term1_pk_type_var;
  END LOOP;
CLOSE c1;
END p2;
END pack1;
/



Regards
Amit

Re: Help Needed in selecting the values from PL/SQL Table [message #338305 is a reply to message #338281] Mon, 04 August 2008 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above


>I tried to select the values from a PL/SQL table using Select statement, it fails.


My car fails.
Please tell me how to make my car go.
Re: Help Needed in selecting the values from PL/SQL Table [message #338404 is a reply to message #338281] Mon, 04 August 2008 18:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
In order to use a SQL select statement with the TABLE function on a collection, the collection must be based on a SQL type created outside of the package, not a PL/SQL type created inside the package. So, do the following:

CREATE OR REPLACE TYPE actnotice_pk_type AS TABLE OF NUMBER(4);
/

Then remove this line from the package:

TYPE actnotice_pk_type IS TABLE OF ACTNOTICE.notid%TYPE;
Re: Help Needed in selecting the values from PL/SQL Table [message #338891 is a reply to message #338404] Wed, 06 August 2008 03:39 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Thanks Barbara,

We can't create the type outside the package as per client requirement. Is there any alternative way for that?

Appreaciate your help.

Regards
Amit
Re: Help Needed in selecting the values from PL/SQL Table [message #338909 is a reply to message #338281] Wed, 06 August 2008 04:27 Go to previous messageGo to next message
gurupatham
Messages: 66
Registered: March 2008
Location: Chennai
Member
Is it throws any runtime error? if it so ,what is error code and name?
Unable to Bulk Collect in PLSQL table [message #339025 is a reply to message #338281] Wed, 06 August 2008 10:23 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Hi,
I created an Object actnotice_obj followed by creation of Table of that object(namely,actnotice_obj_table).
I then created a procedure to Bulk Collect data into that table but faced an error of type mismatch
SQL>  CREATE OR REPLACE TYPE actnotice_obj as OBJECT
  2   ( NOTID  NUMBER(4),
  3     NOTNAME VARCHAR2(30),
  4     A1 NUMBER(5),
  5     A2 NUMBER(5),
  6     A3 NUMBER(5) );
  7  /

Type created.


SQL> CREATE OR REPLACE TYPE actnotice_obj_table AS TABLE OF actnotice_obj;

Type created.



SQL> CREATE OR REPLACE PROCEDURE p1
  2  IS
  3
  4  CURSOR c1 is
  5   Select * from actnotice where notname='I';
  6
  7  actnotice_var  actnotice_obj_table;
  8  rows pls_integer := 100;
  9
 10  BEGIN
 11
 12             Open c1;
 13                 Loop
 14                     FETCH c1  BULK COLLECT  INTO actnotice_var LIMIT rows;
 15                                     EXIT when actnotice_var.COUNT = 0;
 16                 END Loop;
 17
 18  END P1;
 19  /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE P1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
14/6     PL/SQL: SQL Statement ignored
14/35    PLS-00386: type mismatch found at 'ACTNOTICE_VAR' between FETCH
         cursor and INTO variables





Please help!

Amit Pandey
Re: Help Needed in selecting the values from PL/SQL Table [message #339051 is a reply to message #338891] Wed, 06 August 2008 12:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If you can't create a type outside of the package, then you can just use nested queries from the original tables in IN clauses to get your cursor data:

CREATE OR REPLACE PACKAGE pack1
IS
  PROCEDURE p1;
  PROCEDURE p2;
  PROCEDURE p3;

  TYPE actnotice_type IS TABLE OF ACTNOTICE%ROWTYPE INDEX BY PLS_INTEGER;
  actnotice_type_var  actnotice_type;

  TYPE term1_type IS TABLE OF TERM1%ROWTYPE INDEX BY PLS_INTEGER;
  term1_type_var term1_type;

  TYPE term2_type IS TABLE OF TERM2%ROWTYPE INDEX BY PLS_INTEGER;
  term2_type_var term2_type;

  ROWS PLS_INTEGER := 100;
END pack1;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY pack1
IS
  PROCEDURE  p1
  IS
    CURSOR c1 IS
    SELECT * FROM ACTNOTICE WHERE notname='I';
  BEGIN
    OPEN c1;
    LOOP
      FETCH c1  BULK COLLECT  INTO actnotice_type_var LIMIT ROWS;
      EXIT WHEN actnotice_type_var.COUNT = 0;
      FORALL i IN actnotice_type_var.first .. actnotice_type_var.last
        INSERT INTO  ARCACTNOTICE VALUES actnotice_type_var(i);
    END LOOP;
    CLOSE c1;
  END p1;

  PROCEDURE p2
  IS
    CURSOR c1 IS
    SELECT * FROM TERM1 WHERE notid IN
      (SELECT notid FROM ACTNOTICE WHERE notname='I');
  BEGIN
    OPEN c1;
    LOOP
      FETCH c1  BULK COLLECT INTO term1_type_var LIMIT ROWS;
      EXIT WHEN term1_type_var.COUNT = 0;
      FORALL i IN term1_type_var.first .. term1_type_var.last
        INSERT INTO ARCTERM1 VALUES term1_type_var(i);
    END LOOP;
    CLOSE c1;
  END p2;

  PROCEDURE p3
  IS
    CURSOR c1 IS
    SELECT * FROM TERM2 WHERE termid IN
      (SELECT termid FROM TERM1 WHERE notid IN
        (SELECT notid FROM ACTNOTICE WHERE notname='I'));
  BEGIN
    OPEN c1;
    LOOP
      FETCH c1  BULK COLLECT INTO term2_type_var LIMIT ROWS;
      EXIT WHEN term2_type_var.COUNT = 0;
      FORALL i IN term2_type_var.first .. term2_type_var.last
        INSERT INTO ARCTERM1 VALUES term2_type_var(i);
    END LOOP;
    CLOSE c1;
  END p3;
END pack1;
/
SHOW ERRORS

[Updated on: Wed, 06 August 2008 12:06]

Report message to a moderator

Re: Unable to Bulk Collect in PLSQL table [message #339110 is a reply to message #339025] Wed, 06 August 2008 15:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I didn't see your newest post before my last response, since you began a new thread with it, which I have now merged. You need different types for different purposes. If you can now create a SQL type then you can use the following.


SCOTT@orcl_11g> 
SCOTT@orcl_11g> CREATE TABLE ACTNOTICE
  2  ( NOTID						 NUMBER(4),
  3   NOTNAME						 VARCHAR2(30),
  4   A1						 NUMBER(5),
  5   A2						 NUMBER(5),
  6   A3						 NUMBER(5)
  7  )
  8  /

Table created.

SCOTT@orcl_11g> INSERT INTO actnotice VALUES (1, 'I', 2, 3, 4)
  2  /

1 row created.

SCOTT@orcl_11g> CREATE TABLE term
  2  ( TERMID						 NUMBER(4),
  3   NOTID						 NUMBER(4),
  4   B1						 NUMBER(4),
  5   B2						 NUMBER(4),
  6   B3						 NUMBER(4)
  7  )
  8  /

Table created.

SCOTT@orcl_11g> INSERT INTO term VALUES (10, 1, 5, 6, 7)
  2  /

1 row created.

SCOTT@orcl_11g> CREATE TABLE term2
  2  ( TERM2ID						 NUMBER(4),
  3   TERMID						 NUMBER(4),
  4   C1						 NUMBER(4),
  5   C2						 NUMBER(4),
  6   C3						 NUMBER(4)
  7  )
  8  /

Table created.

SCOTT@orcl_11g> INSERT INTO term2 VALUES (100, 10, 8, 9, 0)
  2  /

1 row created.

SCOTT@orcl_11g> CREATE TABLE arcactnotice AS SELECT * FROM actnotice WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE arcterm AS SELECT * FROM term WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE arcterm2 AS SELECT * FROM term2 WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE actnotice_pk_type AS TABLE OF NUMBER(4);
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pack1
  2  IS
  3    PROCEDURE p1;
  4    PROCEDURE p2;
  5    PROCEDURE p3;
  6  
  7    TYPE actnotice_type IS TABLE OF ACTNOTICE%ROWTYPE INDEX BY PLS_INTEGER;
  8    actnotice_type_var  actnotice_type;
  9  
 10    TYPE term_type IS TABLE OF term%ROWTYPE INDEX BY PLS_INTEGER;
 11    term_type_var term_type;
 12  
 13    TYPE term2_type IS TABLE OF TERM2%ROWTYPE INDEX BY PLS_INTEGER;
 14    term2_type_var term2_type;
 15  
 16    ROWS PLS_INTEGER := 100;
 17  
 18    actnotice_pk_type_var actnotice_pk_type;
 19    term_pk_type_var     actnotice_pk_type;
 20  END pack1;
 21  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pack1
  2  IS
  3    PROCEDURE  p1
  4    IS
  5  	 CURSOR c1 IS
  6  	 SELECT * FROM ACTNOTICE WHERE notname='I';
  7    BEGIN
  8  	 OPEN c1;
  9  	 LOOP
 10  	   FETCH c1  BULK COLLECT  INTO actnotice_type_var LIMIT ROWS;
 11  	   EXIT WHEN actnotice_type_var.COUNT = 0;
 12  	   FORALL i IN actnotice_type_var.first .. actnotice_type_var.last
 13  	     INSERT INTO  ARCACTNOTICE VALUES actnotice_type_var(i)
 14  	     RETURNING notid BULK COLLECT INTO actnotice_pk_type_var;
 15  	 END LOOP;
 16  	 CLOSE c1;
 17  	 p2;
 18    END p1;
 19  
 20    PROCEDURE p2
 21    IS
 22  	 CURSOR c1 IS
 23  	 SELECT * FROM term WHERE notid IN
 24  	   (SELECT notid FROM TABLE (CAST (actnotice_pk_type_var AS actnotice_pk_type)));
 25    BEGIN
 26  	 OPEN c1;
 27  	 LOOP
 28  	   FETCH c1  BULK COLLECT INTO term_type_var LIMIT ROWS;
 29  	   EXIT WHEN term_type_var.COUNT = 0;
 30  	   FORALL i IN term_type_var.first .. term_type_var.last
 31  	     INSERT INTO ARCterm VALUES term_type_var(i)
 32  	     RETURNING termid BULK COLLECT INTO term_pk_type_var;
 33  	 END LOOP;
 34  	 CLOSE c1;
 35  	 p3;
 36    END p2;
 37  
 38    PROCEDURE p3
 39    IS
 40  	 CURSOR c1 IS
 41  	 SELECT * FROM TERM2 WHERE termid IN
 42  	   (SELECT termid FROM TABLE (CAST (term_pk_type_var AS actnotice_pk_type)));
 43    BEGIN
 44  	 OPEN c1;
 45  	 LOOP
 46  	   FETCH c1  BULK COLLECT INTO term2_type_var LIMIT ROWS;
 47  	   EXIT WHEN term2_type_var.COUNT = 0;
 48  	   FORALL i IN term2_type_var.first .. term2_type_var.last
 49  	     INSERT INTO ARCterm2 VALUES term2_type_var(i);
 50  	 END LOOP;
 51  	 CLOSE c1;
 52    END p3;
 53  END pack1;
 54  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXECUTE pack1.p1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM arcactnotice
  2  /

     NOTID NOTNAME                                A1         A2         A3
---------- ------------------------------ ---------- ---------- ----------
         1 I                                       2          3          4

SCOTT@orcl_11g> SELECT * FROM arcterm
  2  /

    TERMID      NOTID         B1         B2         B3
---------- ---------- ---------- ---------- ----------
        10          1          5          6          7

SCOTT@orcl_11g> SELECT * FROM arcterm2
  2  /

   TERM2ID     TERMID         C1         C2         C3
---------- ---------- ---------- ---------- ----------
       100         10          8          9          0

SCOTT@orcl_11g> 


Re: Unable to Bulk Collect in PLSQL table [message #339416 is a reply to message #339110] Thu, 07 August 2008 08:22 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Thanks a Lot Barbara

The way you responsded and explain was fabulous.
Smile
Re: Unable to Bulk Collect in PLSQL table [message #339775 is a reply to message #339416] Fri, 08 August 2008 07:53 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Barbara,

I'm not able to figure out why it is inserting all the rows in term1.

PROCEDURE p2
    IS
  	 CURSOR c1 IS
[B]  	 SELECT * FROM term WHERE notid IN
  	   (SELECT notid FROM TABLE (CAST (actnotice_pk_type_var AS actnotice_pk_type)));[/B]    
   BEGIN
  	 OPEN c1;
  	 LOOP
  	   FETCH c1  BULK COLLECT INTO term_type_var LIMIT ROWS;
  	   EXIT WHEN term_type_var.COUNT = 0;
  	   FORALL i IN term_type_var.first .. term_type_var.last
  	     INSERT INTO ARCterm VALUES term_type_var(i)
 	     RETURNING termid BULK COLLECT INTO term_pk_type_var;
  	 END LOOP;
 	 CLOSE c1;
 	 p3;
   END p2;



Regards
Amit
Re: Unable to Bulk Collect in PLSQL table [message #339837 is a reply to message #339775] Fri, 08 August 2008 16:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
We would need to see some sample data, a copy and paste of a run of your current code using that sample data, the results it produces, and the results that you want instead to see what the problem is.
Re: Unable to Bulk Collect in PLSQL table [message #340076 is a reply to message #339837] Mon, 11 August 2008 06:05 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
The data for the tables are as below
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
1, 'A', 11, 21, 31); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
2, 'B', 12, 22, 32); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
3, 'C', 13, 23, 33); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
4, 'D', 14, 24, 34); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
5, 'E', 15, 25, 35); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
6, 'F', 16, 26, 36); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
7, 'G', 17, 27, 37); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
8, 'H', 18, 28, 38); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
9, 'I', 19, 29, 39); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
10, 'J', 20, 30, 40); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
11, 'K', 21, 31, 41); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
12, 'L', 22, 32, 42); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
13, 'M', 23, 33, 43); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
14, 'N', 24, 34, 44); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
15, 'O', 25, 35, 45); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
16, 'P', 26, 36, 46); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
17, 'Q', 27, 37, 47); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
18, 'R', 28, 38, 48); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
19, 'S', 29, 39, 49); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
20, 'T', 30, 40, 50); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
21, 'U', 31, 41, 51); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
22, 'V', 32, 42, 52); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
23, 'W', 33, 43, 53); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
24, 'X', 34, 44, 54); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
25, 'Y', 35, 45, 55); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
26, 'Z', 36, 46, 56); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
27, 'A', 37, 47, 57); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
28, 'B', 38, 48, 58); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
29, 'C', 39, 49, 59); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
30, 'D', 40, 50, 60); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
31, 'E', 41, 51, 61); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
32, 'F', 42, 52, 62); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
33, 'G', 43, 53, 63); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
34, 'H', 44, 54, 64); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
35, 'I', 45, 55, 65); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
36, 'J', 46, 56, 66); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
37, 'K', 47, 57, 67); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
38, 'L', 48, 58, 68); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
39, 'M', 49, 59, 69); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
40, 'N', 50, 60, 70); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
41, 'O', 51, 61, 71); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
42, 'P', 52, 62, 72); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
43, 'Q', 53, 63, 73); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
44, 'R', 54, 64, 74); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
45, 'S', 55, 65, 75); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
46, 'T', 56, 66, 76); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
47, 'U', 57, 67, 77); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
48, 'V', 58, 68, 78); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
49, 'W', 59, 69, 79); 
INSERT INTO ACTNOTICE ( NOTID, NOTNAME, A1, A2, A3 ) VALUES ( 
50, 'X', 60, 70, 80); 
COMMIT;


---------------------------------------------------------------------------------


INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
1, 1, 16, 26, 36); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
2, 1, 17, 27, 37); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
3, 1, 18, 28, 38); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
4, 1, 19, 29, 39); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
5, 1, 20, 30, 40); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
6, 1, 21, 31, 41); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
7, 1, 22, 32, 42); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
8, 2, 23, 33, 43); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
9, 2, 24, 34, 44); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
10, 2, 25, 35, 45); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
11, 2, 26, 36, 46); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
12, 2, 27, 37, 47); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
13, 2, 28, 38, 48); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
14, 2, 29, 39, 49); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
15, 3, 30, 40, 50); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
16, 3, 31, 41, 51); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
17, 3, 32, 42, 52); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
18, 3, 33, 43, 53); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
19, 3, 34, 44, 54); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
20, 3, 35, 45, 55); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
21, 3, 36, 46, 56); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
22, 4, 37, 47, 57); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
23, 4, 38, 48, 58); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
24, 4, 39, 49, 59); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
25, 4, 40, 50, 60); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
26, 4, 41, 51, 61); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
27, 4, 42, 52, 62); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
28, 4, 43, 53, 63); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
29, 5, 44, 54, 64); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
30, 5, 45, 55, 65); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
31, 5, 46, 56, 66); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
32, 5, 47, 57, 67); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
33, 5, 48, 58, 68); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
34, 5, 49, 59, 69); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
35, 5, 50, 60, 70); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
36, 6, 51, 61, 71); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
37, 6, 52, 62, 72); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
38, 6, 53, 63, 73); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
39, 6, 54, 64, 74); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
40, 6, 55, 65, 75); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
41, 6, 56, 66, 76); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
42, 6, 57, 67, 77); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
43, 7, 58, 68, 78); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
44, 7, 59, 69, 79); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
45, 7, 60, 70, 80); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
46, 7, 61, 71, 81); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
47, 7, 62, 72, 82); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
48, 7, 63, 73, 83); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
49, 7, 64, 74, 84); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
50, 8, 65, 75, 85); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
51, 8, 66, 76, 86); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
52, 8, 67, 77, 87); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
53, 8, 68, 78, 88); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
54, 8, 69, 79, 89); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
55, 8, 70, 80, 90); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
56, 8, 71, 81, 91); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
57, 9, 72, 82, 92); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
58, 9, 73, 83, 93); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
59, 9, 74, 84, 94); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
60, 9, 75, 85, 95); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
61, 9, 76, 86, 96); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
62, 9, 77, 87, 97); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
63, 9, 78, 88, 98); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
64, 10, 79, 89, 99); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
65, 10, 80, 90, 100); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
66, 10, 81, 91, 101); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
67, 10, 82, 92, 102); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
68, 10, 83, 93, 103); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
69, 10, 84, 94, 104); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
70, 10, 85, 95, 105); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
71, 11, 86, 96, 106); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
72, 11, 87, 97, 107); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
73, 11, 88, 98, 108); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
74, 11, 89, 99, 109); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
75, 11, 90, 100, 110); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
76, 11, 91, 101, 111); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
77, 11, 92, 102, 112); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
78, 12, 93, 103, 113); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
79, 12, 94, 104, 114); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
80, 12, 95, 105, 115); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
81, 12, 96, 106, 116); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
82, 12, 97, 107, 117); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
83, 12, 98, 108, 118); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
84, 12, 99, 109, 119); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
85, 13, 100, 110, 120); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
86, 13, 101, 111, 121); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
87, 13, 102, 112, 122); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
88, 13, 103, 113, 123); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
89, 13, 104, 114, 124); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
90, 13, 105, 115, 125); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
91, 13, 106, 116, 126); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
92, 14, 107, 117, 127); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
93, 14, 108, 118, 128); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
94, 14, 109, 119, 129); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
95, 14, 110, 120, 130); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
96, 14, 111, 121, 131); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
97, 14, 112, 122, 132); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
98, 14, 113, 123, 133); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
99, 15, 114, 124, 134); 
INSERT INTO TERM1 ( TERMID, NOTID, B1, B2, B3 ) VALUES ( 
100, 15, 115, 125, 135); 
COMMIT;

---------------------------------------------------------------------------------------

INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
1, 4, 9, 14, 19); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
2, 4, 10, 15, 20); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
3, 4, 11, 16, 21); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
4, 4, 12, 17, 22); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
5, 4, 13, 18, 23); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
6, 7, 14, 19, 24); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
7, 7, 15, 20, 25); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
8, 7, 16, 21, 26); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
9, 7, 17, 22, 27); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
10, 7, 18, 23, 28); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
11, 10, 19, 24, 29); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
12, 10, 20, 25, 30); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
13, 10, 21, 26, 31); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
14, 10, 22, 27, 32); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
15, 10, 23, 28, 33); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
16, 13, 24, 29, 34); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
17, 13, 25, 30, 35); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
18, 13, 26, 31, 36); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
19, 13, 27, 32, 37); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
20, 13, 28, 33, 38); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
21, 16, 29, 34, 39); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
22, 16, 30, 35, 40); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
23, 16, 31, 36, 41); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
24, 16, 32, 37, 42); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
25, 16, 33, 38, 43); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
26, 19, 34, 39, 44); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
27, 19, 35, 40, 45); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
28, 19, 36, 41, 46); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
29, 19, 37, 42, 47); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
30, 19, 38, 43, 48); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
31, 22, 39, 44, 49); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
32, 22, 40, 45, 50); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
33, 22, 41, 46, 51); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
34, 22, 42, 47, 52); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
35, 22, 43, 48, 53); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
36, 25, 44, 49, 54); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
37, 25, 45, 50, 55); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
38, 25, 46, 51, 56); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
39, 25, 47, 52, 57); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
40, 25, 48, 53, 58); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
41, 28, 49, 54, 59); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
42, 28, 50, 55, 60); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
43, 28, 51, 56, 61); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
44, 28, 52, 57, 62); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
45, 28, 53, 58, 63); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
46, 31, 54, 59, 64); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
47, 31, 55, 60, 65); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
48, 31, 56, 61, 66); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
49, 31, 57, 62, 67); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
50, 31, 58, 63, 68); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
51, 34, 59, 64, 69); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
52, 34, 60, 65, 70); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
53, 34, 61, 66, 71); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
54, 34, 62, 67, 72); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
55, 34, 63, 68, 73); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
56, 37, 64, 69, 74); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
57, 37, 65, 70, 75); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
58, 37, 66, 71, 76); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
59, 37, 67, 72, 77); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
60, 37, 68, 73, 78); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
61, 40, 69, 74, 79); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
62, 40, 70, 75, 80); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
63, 40, 71, 76, 81); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
64, 40, 72, 77, 82); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
65, 40, 73, 78, 83); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
66, 43, 74, 79, 84); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
67, 43, 75, 80, 85); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
68, 43, 76, 81, 86); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
69, 43, 77, 82, 87); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
70, 43, 78, 83, 88); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
71, 46, 79, 84, 89); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
72, 46, 80, 85, 90); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
73, 46, 81, 86, 91); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
74, 46, 82, 87, 92); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
75, 46, 83, 88, 93); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
76, 49, 84, 89, 94); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
77, 49, 85, 90, 95); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
78, 49, 86, 91, 96); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
79, 49, 87, 92, 97); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
80, 49, 88, 93, 98); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
81, 52, 89, 94, 99); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
82, 52, 90, 95, 100); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
83, 52, 91, 96, 101); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
84, 52, 92, 97, 102); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
85, 52, 93, 98, 103); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
86, 55, 94, 99, 104); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
87, 55, 95, 100, 105); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
88, 55, 96, 101, 106); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
89, 55, 97, 102, 107); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
90, 55, 98, 103, 108); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
91, 58, 99, 104, 109); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
92, 58, 100, 105, 110); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
93, 58, 101, 106, 111); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
94, 58, 102, 107, 112); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
95, 58, 103, 108, 113); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
96, 61, 104, 109, 114); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
97, 61, 105, 110, 115); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
98, 61, 106, 111, 116); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
99, 61, 107, 112, 117); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
100, 61, 108, 113, 118); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
101, 64, 109, 114, 119); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
102, 64, 110, 115, 120); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
103, 64, 111, 116, 121); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
104, 64, 112, 117, 122); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
105, 64, 113, 118, 123); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
106, 67, 114, 119, 124); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
107, 67, 115, 120, 125); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
108, 67, 116, 121, 126); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
109, 67, 117, 122, 127); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
110, 67, 118, 123, 128); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
111, 70, 119, 124, 129); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
112, 70, 120, 125, 130); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
113, 70, 121, 126, 131); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
114, 70, 122, 127, 132); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
115, 70, 123, 128, 133); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
116, 73, 124, 129, 134); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
117, 73, 125, 130, 135); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
118, 73, 126, 131, 136); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
119, 73, 127, 132, 137); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
120, 73, 128, 133, 138); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
121, 76, 129, 134, 139); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
122, 76, 130, 135, 140); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
123, 76, 131, 136, 141); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
124, 76, 132, 137, 142); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
125, 76, 133, 138, 143); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
126, 79, 134, 139, 144); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
127, 79, 135, 140, 145); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
128, 79, 136, 141, 146); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
129, 79, 137, 142, 147); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
130, 79, 138, 143, 148); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
131, 82, 139, 144, 149); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
132, 82, 140, 145, 150); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
133, 82, 141, 146, 151); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
134, 82, 142, 147, 152); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
135, 82, 143, 148, 153); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
136, 85, 144, 149, 154); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
137, 85, 145, 150, 155); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
138, 85, 146, 151, 156); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
139, 85, 147, 152, 157); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
140, 85, 148, 153, 158); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
141, 88, 149, 154, 159); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
142, 88, 150, 155, 160); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
143, 88, 151, 156, 161); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
144, 88, 152, 157, 162); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
145, 88, 153, 158, 163); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
146, 91, 154, 159, 164); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
147, 91, 155, 160, 165); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
148, 91, 156, 161, 166); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
149, 91, 157, 162, 167); 
INSERT INTO TERM2 ( TERM2ID, TERMID, C1, C2, C3 ) VALUES ( 
150, 91, 158, 163, 168); 
COMMIT;



After using the procedure p1 of package pack1 where you havent used nested queries and data has been retrieved
by the useof PLSQL tables...


For the first table arcactnotice the data being inserted is correct , i.e.
NOTID	NOTNAME	A1	A2	A3
9	I	19	29	39
35	I	45	55	65


But for arcterm1 we have 100 records getting inserted in place of 7, the data being
TERMID	NOTID	B1	B2	B3
1	1	16	26	36
2	1	17	27	37
3	1	18	28	38
4	1	19	29	39
5	1	20	30	40
6	1	21	31	41
7	1	22	32	42
8	2	23	33	43
9	2	24	34	44
10	2	25	35	45
11	2	26	36	46
12	2	27	37	47
13	2	28	38	48
14	2	29	39	49
15	3	30	40	50
16	3	31	41	51
17	3	32	42	52
18	3	33	43	53
19	3	34	44	54
20	3	35	45	55
21	3	36	46	56
22	4	37	47	57
23	4	38	48	58
24	4	39	49	59
25	4	40	50	60
26	4	41	51	61
27	4	42	52	62
28	4	43	53	63
29	5	44	54	64
30	5	45	55	65
31	5	46	56	66
32	5	47	57	67
33	5	48	58	68
34	5	49	59	69
35	5	50	60	70
36	6	51	61	71
37	6	52	62	72
38	6	53	63	73
39	6	54	64	74
40	6	55	65	75
41	6	56	66	76
42	6	57	67	77
43	7	58	68	78
44	7	59	69	79
45	7	60	70	80
46	7	61	71	81
47	7	62	72	82
48	7	63	73	83
49	7	64	74	84
50	8	65	75	85
51	8	66	76	86
52	8	67	77	87
53	8	68	78	88
54	8	69	79	89
55	8	70	80	90
56	8	71	81	91
57	9	72	82	92
58	9	73	83	93
59	9	74	84	94
60	9	75	85	95
61	9	76	86	96
62	9	77	87	97
63	9	78	88	98
64	10	79	89	99
65	10	80	90	100
66	10	81	91	101
67	10	82	92	102
68	10	83	93	103
69	10	84	94	104
70	10	85	95	105
71	11	86	96	106
72	11	87	97	107
73	11	88	98	108
74	11	89	99	109
75	11	90	100	110
76	11	91	101	111
77	11	92	102	112
78	12	93	103	113
79	12	94	104	114
80	12	95	105	115
81	12	96	106	116
82	12	97	107	117
83	12	98	108	118
84	12	99	109	119
85	13	100	110	120
86	13	101	111	121
87	13	102	112	122
88	13	103	113	123
89	13	104	114	124
90	13	105	115	125
91	13	106	116	126
92	14	107	117	127
93	14	108	118	128
94	14	109	119	129
95	14	110	120	130
96	14	111	121	131
97	14	112	122	132
98	14	113	123	133
99	15	114	124	134
100	15	115	125	135


Expected result for arcterm1
 TERMID      NOTID         B1         B2         B3
------- ---------- ---------- ---------- ----------
     57          9         72         82         92
     58          9         73         83         93
     59          9         74         84         94
     60          9         75         85         95
     61          9         76         86         96
     62          9         77         87         97
     63          9         78         88         98


Similarly for arcterm2 i got 150 rows where only 10 rows where expected,
TERM2ID	TERMID	C1	C2	C3
1	4	9	14	19
2	4	10	15	20
3	4	11	16	21
4	4	12	17	22
5	4	13	18	23
6	7	14	19	24
7	7	15	20	25
8	7	16	21	26
9	7	17	22	27
10	7	18	23	28
11	10	19	24	29
12	10	20	25	30
13	10	21	26	31
14	10	22	27	32
15	10	23	28	33
16	13	24	29	34
17	13	25	30	35
18	13	26	31	36
19	13	27	32	37
20	13	28	33	38
21	16	29	34	39
22	16	30	35	40
23	16	31	36	41
24	16	32	37	42
25	16	33	38	43
26	19	34	39	44
27	19	35	40	45
28	19	36	41	46
29	19	37	42	47
30	19	38	43	48
31	22	39	44	49
32	22	40	45	50
33	22	41	46	51
34	22	42	47	52
35	22	43	48	53
36	25	44	49	54
37	25	45	50	55
38	25	46	51	56
39	25	47	52	57
40	25	48	53	58
41	28	49	54	59
42	28	50	55	60
43	28	51	56	61
44	28	52	57	62
45	28	53	58	63
46	31	54	59	64
47	31	55	60	65
48	31	56	61	66
49	31	57	62	67
50	31	58	63	68
51	34	59	64	69
52	34	60	65	70
53	34	61	66	71
54	34	62	67	72
55	34	63	68	73
56	37	64	69	74
57	37	65	70	75
58	37	66	71	76
59	37	67	72	77
60	37	68	73	78
61	40	69	74	79
62	40	70	75	80
63	40	71	76	81
64	40	72	77	82
65	40	73	78	83
66	43	74	79	84
67	43	75	80	85
68	43	76	81	86
69	43	77	82	87
70	43	78	83	88
71	46	79	84	89
72	46	80	85	90
73	46	81	86	91
74	46	82	87	92
75	46	83	88	93
76	49	84	89	94
77	49	85	90	95
78	49	86	91	96
79	49	87	92	97
80	49	88	93	98
81	52	89	94	99
82	52	90	95	100
83	52	91	96	101
84	52	92	97	102
85	52	93	98	103
86	55	94	99	104
87	55	95	100	105
88	55	96	101	106
89	55	97	102	107
90	55	98	103	108
91	58	99	104	109
92	58	100	105	110
93	58	101	106	111
94	58	102	107	112
95	58	103	108	113
96	61	104	109	114
97	61	105	110	115
98	61	106	111	116
99	61	107	112	117
100	61	108	113	118
101	64	109	114	119
102	64	110	115	120
103	64	111	116	121
104	64	112	117	122
105	64	113	118	123
106	67	114	119	124
107	67	115	120	125
108	67	116	121	126
109	67	117	122	127
110	67	118	123	128
111	70	119	124	129
112	70	120	125	130
113	70	121	126	131
114	70	122	127	132
115	70	123	128	133
116	73	124	129	134
117	73	125	130	135
118	73	126	131	136
119	73	127	132	137
120	73	128	133	138
121	76	129	134	139
122	76	130	135	140
123	76	131	136	141
124	76	132	137	142
125	76	133	138	143
126	79	134	139	144
127	79	135	140	145
128	79	136	141	146
129	79	137	142	147
130	79	138	143	148
131	82	139	144	149
132	82	140	145	150
133	82	141	146	151
134	82	142	147	152
135	82	143	148	153
136	85	144	149	154
137	85	145	150	155
138	85	146	151	156
139	85	147	152	157
140	85	148	153	158
141	88	149	154	159
142	88	150	155	160
143	88	151	156	161
144	88	152	157	162
145	88	153	158	163
146	91	154	159	164
147	91	155	160	165
148	91	156	161	166
149	91	157	162	167
150	91	158	163	168



Expected result for arcterm2
 TERM2ID TERMID C1         C2        C3
-------- ---------- ---------- ---------- ----------
      95         58        103        108        113
      94         58        102        107        112
      93         58        101        106        111
      92         58        100        105        110
      91         58         99        104        109
     100         61        108        113        118
      99         61        107        112        117
      98         61        106        111        116
      97         61        105        110        115
      96         61        104        109        114




Amit
Re: Unable to Bulk Collect in PLSQL table [message #340243 is a reply to message #340076] Tue, 12 August 2008 01:26 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Since actnotice_pk_type is a table of number(4), when you select something cast to it's datatype, you must select column_value, not the column name of the collection that you were casting to actnotice_pk_type. Notice the two places in the corrected code below where I have used column_value in c2 and c3.

SCOTT@orcl_11g> SELECT COUNT (*) FROM actnotice
  2  /

  COUNT(*)
----------
        50

SCOTT@orcl_11g> SELECT COUNT (*) FROM term1
  2  /

  COUNT(*)
----------
       100

SCOTT@orcl_11g> SELECT COUNT (*) FROM term2
  2  /

  COUNT(*)
----------
       150

SCOTT@orcl_11g> CREATE TABLE arcactnotice AS SELECT * FROM actnotice WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE arcterm1 AS SELECT * FROM term1 WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE TABLE arcterm2 AS SELECT * FROM term2 WHERE 1 = 2
  2  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE actnotice_pk_type AS TABLE OF NUMBER(4);
  2  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pack1
  2  IS
  3    PROCEDURE p1;
  4    PROCEDURE p2;
  5    PROCEDURE p3;
  6  
  7    TYPE actnotice_type IS TABLE OF ACTNOTICE%ROWTYPE INDEX BY PLS_INTEGER;
  8    actnotice_type_var  actnotice_type;
  9  
 10    TYPE term_type IS TABLE OF term1%ROWTYPE INDEX BY PLS_INTEGER;
 11    term_type_var term_type;
 12  
 13    TYPE term2_type IS TABLE OF TERM2%ROWTYPE INDEX BY PLS_INTEGER;
 14    term2_type_var term2_type;
 15  
 16    ROWS PLS_INTEGER := 100;
 17  
 18    actnotice_pk_type_var actnotice_pk_type;
 19    term_pk_type_var     actnotice_pk_type;
 20  END pack1;
 21  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pack1
  2  IS
  3    PROCEDURE  p1
  4    IS
  5  	 CURSOR c1 IS
  6  	 SELECT * FROM ACTNOTICE WHERE notname='I';
  7    BEGIN
  8  	 OPEN c1;
  9  	 LOOP
 10  	   FETCH c1  BULK COLLECT  INTO actnotice_type_var LIMIT ROWS;
 11  	   EXIT WHEN actnotice_type_var.COUNT = 0;
 12  	   FORALL i IN actnotice_type_var.first .. actnotice_type_var.last
 13  	     INSERT INTO  ARCACTNOTICE VALUES actnotice_type_var(i)
 14  	     RETURNING notid BULK COLLECT INTO actnotice_pk_type_var;
 15  	 END LOOP;
 16  	 CLOSE c1;
 17  	 p2;
 18    END p1;
 19  
 20    PROCEDURE p2
 21    IS
 22  	 CURSOR c2 IS
 23  	 SELECT * FROM term1 WHERE notid IN
 24  	   (SELECT column_value FROM TABLE (CAST (actnotice_pk_type_var AS actnotice_pk_type)));
 25    BEGIN
 26  	 OPEN c2;
 27  	 LOOP
 28  	   FETCH c2  BULK COLLECT INTO term_type_var LIMIT ROWS;
 29  	   EXIT WHEN term_type_var.COUNT = 0;
 30  	   FORALL i IN term_type_var.first .. term_type_var.last
 31  	     INSERT INTO arcterm1 VALUES term_type_var(i)
 32  	     RETURNING termid BULK COLLECT INTO term_pk_type_var;
 33  	 END LOOP;
 34  	 CLOSE c2;
 35  	 p3;
 36    END p2;
 37  
 38    PROCEDURE p3
 39    IS
 40  	 CURSOR c3 IS
 41  	 SELECT * FROM TERM2 WHERE termid IN
 42  	   (SELECT column_value FROM TABLE (CAST (term_pk_type_var AS actnotice_pk_type)));
 43    BEGIN
 44  	 OPEN c3;
 45  	 LOOP
 46  	   FETCH c3  BULK COLLECT INTO term2_type_var LIMIT ROWS;
 47  	   EXIT WHEN term2_type_var.COUNT = 0;
 48  	   FORALL i IN term2_type_var.first .. term2_type_var.last
 49  	     INSERT INTO ARCterm2 VALUES term2_type_var(i);
 50  	 END LOOP;
 51  	 CLOSE c3;
 52    END p3;
 53  END pack1;
 54  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXECUTE pack1.p1

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM arcactnotice
  2  /

     NOTID NOTNAME                                A1         A2         A3
---------- ------------------------------ ---------- ---------- ----------
         9 I                                      19         29         39
        35 I                                      45         55         65

SCOTT@orcl_11g> SELECT * FROM arcterm1
  2  /

    TERMID      NOTID         B1         B2         B3
---------- ---------- ---------- ---------- ----------
        63          9         78         88         98
        62          9         77         87         97
        61          9         76         86         96
        60          9         75         85         95
        59          9         74         84         94
        58          9         73         83         93
        57          9         72         82         92

7 rows selected.

SCOTT@orcl_11g> SELECT * FROM arcterm2
  2  /

   TERM2ID     TERMID         C1         C2         C3
---------- ---------- ---------- ---------- ----------
       100         61        108        113        118
        99         61        107        112        117
        98         61        106        111        116
        97         61        105        110        115
        96         61        104        109        114
        95         58        103        108        113
        94         58        102        107        112
        93         58        101        106        111
        92         58        100        105        110
        91         58         99        104        109

10 rows selected.

SCOTT@orcl_11g> 

Previous Topic: Sql problem
Next Topic: sql query
Goto Forum:
  


Current Time: Wed Dec 07 10:44:04 CST 2016

Total time taken to generate the page: 0.05556 seconds