Session_table_type1

View All Public Scripts Login to Run Script

Script Name Session_table_type1
Visibility Unlisted - anyone with the share link can access
Description There are records which are supposed to be stored in table type variable as a result of cursor loop on table - test_mgr_hierar5. The table type is expected to hold values - 177603,157564,157286,186196 for column person_id. Next step is - Need to delete records from table - test_mgr_hierar90 where person_id's are not the above ones. so virtually its like - delete from test_mgr_hierar50 where person_id NOT IN ( 177603,157564,157286,186196) -- these values are collected in table type variable. The issue which I am facing is not the deletion , rather somehow I am somehow not able to collect those values in a table type variable by looping/extend clause. So my issue is only pertaining to the below clause - for v IN csr1 LOOP depts_max := depts_max + 1; exch_rt.EXTEND; --exch_rt(depts_max) := v.person_id; ---------This is the one which is erroring END LOOP; I have shared all the steps/scripts and session is attached. I tried multiple ways to change that statement but that doesn't seem to be worki(details in attached session) or the assignment statement but unable to find out after different trial and errors. So need you advise only the correct way of assigning the values into table type variables.
Area PL/SQL General
Contributor
Created Monday May 25, 2020

Statement 1

create type exch_row as object ( 
    person_id NUMBER);

Type created.

Statement 2

create type exch_tbl as table of exch_row;

Type created.

Statement 3

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 exist

Statement 4

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 exist

Statement 5

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.

Statement 6
```
DROP TEST_MGR_HIERAR5
```
ORA-00950: invalid DROP option
Statement 7
```
DROP table TEST_MGR_HIERAR5
```
Table dropped.

Statement 8

CREATE TABLE "TEST_MGR_HIERAR5"  
   (	"EMPLOYEE_NUMBER" VARCHAR2(30),  
	"PERSON_ID" NUMBER 
   )

Table created.

Statement 9

INSERT INTO test_mgr_hierar5 VALUES ('126141',177603)

1 row(s) inserted.

Statement 10

INSERT INTO test_mgr_hierar5 VALUES ('108994',157564)

1 row(s) inserted.

Statement 11

INSERT INTO test_mgr_hierar5 VALUES ('108791',157286)

1 row(s) inserted.

Statement 12

INSERT INTO test_mgr_hierar5 VALUES ('133406',186196)

1 row(s) inserted.

Statement 13
```
commit
```
Statement processed.
Statement 14
```
commit
```
Statement processed.
Statement 15
```
SELECT * FROM TEST_MGR_HIERAR5 
```
EMPLOYEE_NUMBER PERSON_ID
126141 177603
108994 157564
108791 157286
133406 186196

4 rows selected.

EMPLOYEE_NUMBER	PERSON_ID
126141	177603
108994	157564
108791	157286
133406	186196

Statement 16

CREATE TABLE "TEST_MGR_HIERAR50"  
   (	"EMPLOYEE_NUMBER" VARCHAR2(30),  
	"PERSON_ID" NUMBER 
   )

Table created.

Statement 17

INSERT INTO test_mgr_hierar50 VALUES ('126141',177603)

1 row(s) inserted.

Statement 18

INSERT INTO test_mgr_hierar50 VALUES ('108994',157564)

1 row(s) inserted.

Statement 19

INSERT INTO test_mgr_hierar50 VALUES ('108791',157286)

1 row(s) inserted.

Statement 20

INSERT INTO test_mgr_hierar50 VALUES ('133406',186196)

1 row(s) inserted.

Statement 21

INSERT INTO test_mgr_hierar50 VALUES ('128682',180424)

1 row(s) inserted.

Statement 22
```
commit
```
Statement processed.

Statement 23

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 type

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials