create type exch_row as object (
person_id NUMBER);
Type created.
create type exch_tbl as table of exch_row;
Type created.
DECLARE
l_row exch_row;
exch_rt exch_tbl;
depts_max NUMBER;
CURSOR csr1
IS
select * from test_mgr_hierar5;
BEGIN
depts_max := 0;
exch_rt := exch_tbl();
for v IN csr1
LOOP
depts_max := depts_max + 1;
exch_rt.EXTEND;
exch_rt(depts_max) := v.person_id;
END LOOP;
delete from test_mgr_hierar50 a
where a.person_id <> (select rt.person_id from TABLE(exch_rt) rt);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
ORA-06550: line 8, column 16: PL/SQL: ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-06550
CREATE TABLE "APPS"."TEST_MGR_HIERAR5"
( "EMPLOYEE_NUMBER" VARCHAR2(30 BYTE),
"PERSON_ID" NUMBER(15,0),
"USER_PERSON_TYPE" VARCHAR2(100 BYTE),
"FULL_NAME" VARCHAR2(240 BYTE),
"MGR_LVL_2" VARCHAR2(240 BYTE),
"MGR_LVL_3" VARCHAR2(240 BYTE),
"MGR_LVL_4" VARCHAR2(240 BYTE),
"MGR_LVL_5" VARCHAR2(240 BYTE),
"MGR_LVL_6" VARCHAR2(240 BYTE),
"MGR_LVL_7" VARCHAR2(240 BYTE),
"MGR_LVL_8" VARCHAR2(240 BYTE),
"MGR_LVL_9" VARCHAR2(240 BYTE),
"MGR_LVL_10" VARCHAR2(240 BYTE),
"MGR_HIERARCHY" VARCHAR2(4000 BYTE),
"PROCESS_DATE" DATE,
"PROCESS_FLAG" VARCHAR2(10 BYTE),
"CREATION_DATE" DATE,
"CREATED_BY" NUMBER(15,0),
"LAST_UPDATE_DATE" DATE,
"LAST_UPDATED_BY" NUMBER(15,0)
)
ORA-01918: user 'APPS' does not existMore Details: https://docs.oracle.com/error-help/db/ora-01918
CREATE TABLE "TEST_MGR_HIERAR5"
( "EMPLOYEE_NUMBER" VARCHAR2(30 BYTE),
"PERSON_ID" NUMBER(15,0),
"USER_PERSON_TYPE" VARCHAR2(100 BYTE),
"FULL_NAME" VARCHAR2(240 BYTE),
"MGR_LVL_2" VARCHAR2(240 BYTE),
"MGR_LVL_3" VARCHAR2(240 BYTE),
"MGR_LVL_4" VARCHAR2(240 BYTE),
"MGR_LVL_5" VARCHAR2(240 BYTE),
"MGR_LVL_6" VARCHAR2(240 BYTE),
"MGR_LVL_7" VARCHAR2(240 BYTE),
"MGR_LVL_8" VARCHAR2(240 BYTE),
"MGR_LVL_9" VARCHAR2(240 BYTE),
"MGR_LVL_10" VARCHAR2(240 BYTE),
"MGR_HIERARCHY" VARCHAR2(4000 BYTE),
"PROCESS_DATE" DATE,
"PROCESS_FLAG" VARCHAR2(10 BYTE),
"CREATION_DATE" DATE,
"CREATED_BY" NUMBER(15,0),
"LAST_UPDATE_DATE" DATE,
"LAST_UPDATED_BY" NUMBER(15,0)
)
Table created.
DROP TEST_MGR_HIERAR5
ORA-00950: invalid DROP optionMore Details: https://docs.oracle.com/error-help/db/ora-00950
DROP table TEST_MGR_HIERAR5
Table dropped.
CREATE TABLE "TEST_MGR_HIERAR5"
( "EMPLOYEE_NUMBER" VARCHAR2(30),
"PERSON_ID" NUMBER
)
Table created.
INSERT INTO test_mgr_hierar5 VALUES ('126141',177603)
1 row(s) inserted.
INSERT INTO test_mgr_hierar5 VALUES ('108994',157564)
1 row(s) inserted.
INSERT INTO test_mgr_hierar5 VALUES ('108791',157286)
1 row(s) inserted.
INSERT INTO test_mgr_hierar5 VALUES ('133406',186196)
1 row(s) inserted.
commit
Statement processed.
commit
Statement processed.
SELECT * FROM TEST_MGR_HIERAR5
EMPLOYEE_NUMBER | PERSON_ID | 126141 | 177603 | 108994 | 157564 | 108791 | 157286 | 133406 | 186196 |
---|
CREATE TABLE "TEST_MGR_HIERAR50"
( "EMPLOYEE_NUMBER" VARCHAR2(30),
"PERSON_ID" NUMBER
)
Table created.
INSERT INTO test_mgr_hierar50 VALUES ('126141',177603)
1 row(s) inserted.
INSERT INTO test_mgr_hierar50 VALUES ('108994',157564)
1 row(s) inserted.
INSERT INTO test_mgr_hierar50 VALUES ('108791',157286)
1 row(s) inserted.
INSERT INTO test_mgr_hierar50 VALUES ('133406',186196)
1 row(s) inserted.
INSERT INTO test_mgr_hierar50 VALUES ('128682',180424)
1 row(s) inserted.
commit
Statement processed.
DECLARE
l_row exch_row;
exch_rt exch_tbl;
depts_max NUMBER;
CURSOR csr1
IS
select * from test_mgr_hierar5;
BEGIN
depts_max := 0;
exch_rt := exch_tbl();
for v IN csr1
LOOP
depts_max := depts_max + 1;
exch_rt.EXTEND;
exch_rt(depts_max) := v.person_id; --- This is where I am getting the issue related to wrong types. something is incorrect here
END LOOP;
delete from test_mgr_hierar50 a
where a.person_id <> (select rt.person_id from TABLE(exch_rt) rt);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL; -- Kept Null just for the time being
END;
ORA-06550: line 18, column 29: PLS-00382: expression is of wrong typeMore Details: https://docs.oracle.com/error-help/db/ora-06550