Thanks for the question, Don.

Asked: November 11, 2016 - 7:13 pm UTC

Last updated: November 15, 2016 - 1:14 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I get the following error when I execute the code listed below -

ORA-21700: object does not exist or is marked for delete

The example is trivial the sake of illustration, but in real-life, I would be handing sets of several thousands to tens of thousands of elements.

If I replace the "bulk collect into" query with an iterative process of extending the xString collection then assign each element one at a time, it works fine, but I hope to avoid the row by row processing.

create or replace type String255List as table of varchar2(255);
/
create or replace type stringTest as object (
    vIndex int,
    vText varchar2(255)
    );
/

create or replace package D_TEST
is
    type stringTable is table of stringTest;
    
    procedure testStringTable;
end D_TEST;
    
create or replace package body D_TEST
is 

procedure testStringTable as  

    xString String255List; 
    testTable stringTable := stringTable(
        stringTest(1, 'One'), 
        stringTest(2, 'Dos'), 
        stringTest(3, 'Trois'), 
        stringTest(4, 'Vier') 
        );

begin
    xString := String255List(); 
    
    select vText 
        bulk collect into xString 
    from table(testTable); 
    
    for i in 1 .. xString.count 
    loop 
        DBMS_OUTPUT.put_line(xString(i)); 
    end loop; 
end testStringTable;

end D_TEST;
/

exec D_TEST.testStringTable;

Thanx, Don

and Connor said...

What exact version are you ? I couldn't replicate this on 12.1.0.2 and 11.2.0.4 and it worked for me


SQL> create or replace type String255List as table of varchar2(255);
  2  /

Type created.

SQL> create or replace type stringTest as object (
  2      vIndex int,
  3      vText varchar2(255)
  4      );
  5  /

Type created.

SQL>
SQL> create or replace package D_TEST
  2  is
  3      type stringTable is table of stringTest;
  4
  5      procedure testStringTable;
  6  end D_TEST;
  7  /

Package created.

SQL>
SQL> create or replace package body D_TEST
  2  is
  3
  4  procedure testStringTable as
  5
  6      xString String255List;
  7      testTable stringTable := stringTable(
  8          stringTest(1, 'One'),
  9          stringTest(2, 'Dos'),
 10          stringTest(3, 'Trois'),
 11          stringTest(4, 'Vier')
 12          );
 13
 14  begin
 15      xString := String255List();
 16
 17      select vText
 18          bulk collect into xString
 19      from table(testTable);
 20
 21      for i in 1 .. xString.count
 22      loop
 23          DBMS_OUTPUT.put_line(xString(i));
 24      end loop;
 25  end testStringTable;
 26
 27  end D_TEST;
 28  /

Package body created.

SQL>
SQL> exec D_TEST.testStringTable;

PL/SQL procedure successfully completed.

SQL>

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Suggest

Ghassan, November 12, 2016 - 6:56 pm UTC

Did you try to drop your types with force option
And redo the test case.
Lay be the type is corrupted. ..

Suggest

Ghassan, November 12, 2016 - 6:56 pm UTC

Did you try to drop your types with force option
And redo the test case.
Lay be the type is corrupted. ..

Don Simpson, November 14, 2016 - 4:05 pm UTC

I get this from PRODUCT_COMPONENT_VERSION:

Oracle Database 11g Enterprise Edition
11.2.0.1.0
64bit Production

Don Simpson, November 14, 2016 - 5:10 pm UTC

When I alter the code to use an anonymous block:

declare 
    type stringTable is table of stringTest;
    xString String255List; 
    testTable stringTable := stringTable(
        stringTest(1, 'One'), 
        stringTest(2, 'Dos'), 
        stringTest(3, 'Trois'), 
        stringTest(4, 'Vier') 
        );

begin
    xString := String255List(); 
    
    select vText 
        bulk collect into xString 
    from table(testTable); 
    for i in 1 .. xString.count 
    loop 
        DBMS_OUTPUT.put_line(xString(i)); 
    end loop; 
end;

I get this message:

Error at line 1
ORA-06550: line 16, column 16:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 16, column 10:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored

Don Simpson, November 14, 2016 - 8:58 pm UTC

I dropped and recreated the types:

create or replace type String255List force as table of varchar2(255);
/

create or replace type stringTest force as object (
    vIndex int,
    vText varchar2(255)
    );
/

Same error.

November 15, 2016 - 1:14 am UTC

My guess is perhaps a bug in 11.2.0.1. Try this on livesql.oracle.com and see how you go. If it works, then its time to talk to Support (although they will most probably recommend you move to 11.2.0.4)

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Suggest

Suggest

More to Explore

PL/SQL demos

PL/SQL docs

Populate varchar2 collection from object type

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Suggest

Suggest

More to Explore

PL/SQL demos

PL/SQL docs