Skip to Main Content
  • Questions
  • Assigning values into table type variable within loop results in error - PLS-00382: expression is of wrong type

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alok.

Asked: May 25, 2020 - 11:53 am UTC

Answered by: Connor McDonald - Last updated: June 03, 2020 - 5:37 am UTC

Category: PL/SQL - Version: Version 4.0.3.16

Viewed 100+ times

You Asked

Please refer the script I attached with this question -

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 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 (PLS-00382: expression is of wrong type
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 working (details in attached session) or the assignment statement but unable to find out after different trial and errors. So need your advise only the correct way of assigning the values into table type variables.

with LiveSQL Test Case:

and we said...

exch_tbl is table of *objects*, so each entry in the array has to be an object

So in your example

SQL> create or replace type exch_row as object (
  2      person_id NUMBER);
  3
  4  /

Type created.

SQL>
SQL> create or replace type exch_tbl as table of exch_row;
  2  /

Type created.

SQL>
SQL>
SQL> DECLARE
  2   l_row     exch_row;
  3   exch_rt   exch_tbl;
  4   depts_max NUMBER;
  5
  6  CURSOR csr1
  7  IS
  8   select 100 person_id from dual;
  9
 10  BEGIN
 11    depts_max := 0;
 12    exch_rt :=  exch_tbl();
 13
 14   for v IN csr1
 15   LOOP
 16      depts_max := depts_max + 1;
 17      exch_rt.EXTEND;
 18      exch_rt(depts_max) := exch_row(v.person_id);      <============= object
 19   END LOOP;
 20  END;
 21  /

PL/SQL procedure successfully completed.


and I've removed your "when others then null".... Please don't put it back in :-)

and you rated our response

  (1 rating)

Reviews

Solution worked for PLSQL Table/Object types

May 30, 2020 - 9:53 pm UTC

Reviewer: Alok Datta from Pune, India

The looping functionalities have worked for me for PLSQL Table.
Thanks a lot connor for your help.

I have one question though, since PLSQL object types and table types are created at schema level, do you think online patching can have any impact on he same
Connor McDonald

Followup  

June 03, 2020 - 5:37 am UTC

Unlikely.

If you have high frequency usage of packages and you also want to deploy them online, then check out edition based redefintion.

https://www.oracle.com/au/database/technologies/high-availability/ebr.html

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.