Hi
I have data from a table, which was loaded in a type structure like as:
type SOMETYPE_ IS TABLE OF SOMETABLE%ROWTYPE;
I would like to know how do I read the data of this loaded structure and delete data from another table, based on two columns, I will post a fictional example of what I want with two tables:
CREATE TABLE TMP_T01
(
ID NUMBER,
SEQNUMBER NUMBER,
PART_NUMBER NUMBER,
DESCRI VARCHAR2(4000 BYTE)
)
CREATE TABLE TMP_T02
(
ID NUMBER,
SEQNUMBER NUMBER,
PART_NUMBER NUMBER,
MODEL_CAR VARCHAR2(4000 BYTE)
)
Now insert some data
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (1, 64, 153, '5VOB');
Insert into TMP_T01 (I
D, SEQNUMBER, PART_NUMBER, DESCRI) Values (2, 91, 133, '6WQW');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (3, 58, 136, 'P2AA');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (4, 10, 108, 'N8SF');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (5, 12, 198, 'DDSS');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (6, 41, 133, 'CZ6D');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (7, 19, 101, 'H7C4');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (8, 60, 146, '29RH');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (9, 28, 150, 'BEEU');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (10, 39, 176, 'II8H');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (11, 10, 133, 'NJWX');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (12, 26, 177, 'D6AM');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (13, 42, 170, 'JQHJ');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (14, 89, 139, 'HOR6');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (15, 63, 180, '3UIF');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (16, 83, 129, 'C5Q8');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (17, 6, 185, '8GG1');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (18, 11, 170, '7U4V');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (19, 39, 129, '5Z7Z');
Insert into TMP_T01 (ID, SEQNUMBER, PART_NUMBER, DESCRI) Values (20, 65, 172, '4QEE');
COMMIT;
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (1, 29, 172, 'CHEVROLET');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (2, 91, 133, 'VOLKSWAGEN');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (3, 21, 156, 'FIAT');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (4, 10, 108, 'MERCEDES-BENZ');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (5, 95, 172, 'CITROEN');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (6, 41, 133, 'CHANA');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (7, 46, 198, 'HONDA');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (8, 60, 146, 'SUBARU');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (10, 39, 176, 'FERRARI');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (11, 83, 151, 'BUGATTI');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (12, 26, 177, 'LAMBORGHINI');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (13, 61, 181, 'FORD');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (14, 89, 139, 'HYUNDAI');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (15, 55, 182, 'JAC');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (16, 83, 129, 'KIA');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (17, 11, 122, 'GURGEL');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (18, 11, 170, 'DODGE');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (19, 77, 142, 'CHRYSLER');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (20, 65, 172, 'BENTLEY');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (21, 68, 156, 'SSANGYONG');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (22, 83, 101, 'PEUGEOT');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (23, 62, 182, 'TOYOTA');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (24, 73, 177, 'RENAULT');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (25, 29, 163, 'ACURA');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (26, 97, 126, 'ADAMO');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (27, 42, 187, 'AGRALE');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (28, 31, 119, 'ALFA ROMEO');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (29, 54, 177, 'AMERICAR');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (31, 22, 197, 'ASTON MARTIN');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (32, 3, 176, 'AUDI');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (34, 98, 135, 'BEACH');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (35, 28, 112, 'BIANCO');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (36, 5, 190, 'BMW');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (37, 39, 193, 'BORGWARD');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (38, 78, 168, 'BRILLIANCE');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (41, 77, 105, 'BUICK');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (42, 99, 150, 'CBT');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (43, 57, 102, 'NISSAN');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (44, 50, 149, 'CHAMONIX');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (46, 20, 192, 'CHEDA');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (47, 98, 188, 'CHERY');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (48, 85, 115, 'CORD');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (49, 69, 116, 'COYOTE');
Insert into TMP_T02 (ID, SEQNUMBER, PART_NUMBER, MODEL_CAR) Values (50, 59, 176, 'CROSS LANDER');
COMMIT;
an example that some data in the two tables are the same (but remember, suppose I do not have access to the first table, only the data contained in TBL_01
SQL> SELECT T02.*
2 FROM TMP_T01 T01,TMP_T02 T02
3 WHERE T01.SEQNUMBER = T02.SEQNUMBER
4 AND T01.PART_NUMBER = T02.PART_NUMBER;
ID SEQNUMBER PART_NUMBER MODEL_CAR
---------- ---------- ----------- --------------------------------------------------------------------------------
2 91 133 VOLKSWAGEN
4 10 108 MERCEDES-BENZ
6 41 133 CHANA
8 60 146 SUBARU
10 39 176 FERRARI
12 26 177 LAMBORGHINI
14 89 139 HYUNDAI
16 83 129 KIA
18 11 170 DODGE
20 65 172 BENTLEY
10 rows selected
I'm trying to do something like below
declare
type ARR_TMP_T01 IS TABLE OF TMP_T01%ROWTYPE;
TBL_01 ARR_TMP_T01;
CURSOR c1 IS
SELECT *
FROM TMP_T01 T01;
begin
OPEN C1;
FETCH C1 BULK COLLECT INTO TBL_01;
CLOSE C1;
-- Suppose I don't have access to the TMP_T01 table, I can only see TBL_01 which is a table of type from the TMP_T01 table
--I want to read all the records that are in TBL_01 and to delete them from the TMP_T02 table where the SEQNUMBER and PART_NUMBER columns are Equal
-- In Summary, pretend that I don't have the TMP_T01 table, I only have TBL_01 with the data
end;
USING
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Pro