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)