Wednesday, January 09, 2008

PLS-00707: unsupported construct or internal error

A reader asked me to investigate why he was getting the error being discussed whenever he tried to execute the following PL/SQL block:
declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
number pls_integer := 1;
begin
--VALS := WARRAY(null, null);
--LLISTA := WARRAY(null);
--LLISTA := VALS;
dbms_output.put_line(number);
loop
exit when LLISTA(number)%NOTFOUND;
LLISTA(number) := VALS(number);
Number:= Number + 1;
end loop;
end;

Error report:
ORA-06550: line 0, column 0:
PLS-00707: unsupported construct or internal error [2704]
ORA-06550: line 12, column 2:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

There are several things worth noting in this tiny PL/SQL snippet, but let's start off with the main issue.
PLS-00707 is raised because Oracle doesn't support the %NOTFOUND construct with collections as it is meant to be used with cursors. I must guess that the user got confused with the EXISTS method that allows to check for the presence of non-initiatilized elements in collections (not to be confused with null elements!).
declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd');
--LLISTA := VALS;
loop
--exit when LLISTA(number)%NOTFOUND;
exit when not LLISTA.EXISTS(number);
LLISTA(number) := VALS(number);
number:= number + 1;
end loop;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number - 1));
end;
If the intention of this procedure was to copy the elements of collection VALS into LLISTA, then it must be noted that if both varrays are exactly of the same type and dimension, then we could get rid of the loop altogether:

declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
number pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;
Entire collections can be copied as if they were simple variables when the aforementioned conditions are met.
Note also that the third element of LLISTA ('e') has disappeared after the copy, so, don't expect that Oracle copies only the subset of the collection containing the initialized elements, you'll need to do it yourself (see later on).
Let's see what happens when the type looks equal but is not, as in the following example where i duplicated they custom type WARRAY:
declare
type WARRAY is varray(324) OF varchar2(200);
type WARRAY2 is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY2;
number pls_integer := 1;
begin
VALS := WARRAY2('a', 'b');
LLISTA := WARRAY('c', 'd');
LLISTA := VALS;
number := LLISTA.COUNT;
dbms_output.put_line(number);
dbms_output.put_line(LLISTA(number));
end;

Error report:
ORA-06550: line 10, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 10, column 2:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
The collection assignment doesn't work anymore because even if WARRAY and WARRAY2 appear to be perfectly equivalent, they are not the same type, strictly speaking.

Finally, let's go back to the previous step, the working one, and assume that we want to copy only a subset of the elements of VALS. I guess that for some reason VALS contains either updated or saved values that at a certain point we want to restore in LLISTA, but without erasing the elements of LLISTA that have no corresponding subscript in VALS:
declare
LLISTA string_table_type;
VALS string_table_type;
BKP string_table_type;
n pls_integer := 1;
begin
VALS := string_table_type('a','b');
LLISTA := string_table_type('c','d','e','f');
BKP := LLISTA;
n := VALS.count;
select c
bulk collect into LLISTA
from (
select column_value as c
from table(VALS)
union all
select c
from (select rownum r, column_value as c
from table(BKP))
where r > n
);
dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
dbms_output.put_line(LLISTA(i));
end loop;
end;

4
a
b
e
f

Again, some remarks: first of all i had to use an externally defined collection type (string_table_type), not a locally defined collection type because PL/SQL doesn't allow me to use local collections in SQL statements:
create or replace TYPE "STRING_TABLE_TYPE"                                                                                                                                                                                                                                                                                                     as table
of varchar2(200)
Secondly, i had to create a third collection named BKP where i staged the data of LLISTA because otherwise i'd get an empty subset. Oracle is implicitly erasing LLISTA before performing BULK COLLECT so i cannot access its elements while performing the query.

Is this memory wasting approach faster than manually copying each and every element from VALS to LLISTA?
declare
type WARRAY is varray(324) OF varchar2(200);
LLISTA WARRAY;
VALS WARRAY;
n pls_integer := 1;
begin
VALS := WARRAY('a', 'b');
LLISTA := WARRAY('c', 'd', 'e', 'f');
loop
exit when not VALS.EXISTS(n);
LLISTA(n) := VALS(n);
n:= n + 1;
end loop;

dbms_output.put_line(LLISTA.count);
for i in LLISTA.first .. LLISTA.last
loop
dbms_output.put_line(LLISTA(i));
end loop;
end;

4
a
b
e
f
The performance comparison test is left to the reader as exercise, as some professors say when they are running late for lunch... ;-)

See message translations for PLS-00707 and search additional resources.

2 comments:

Sharmin said...

Well, my problem is different. I am able to compile a package body in sqlplus, but cannot do the same from sqldeveloper. When I do it from sqldeveoper, I get the error, "PLS-00707: unsupported construct or internal error [2603]". I copied the content from file and removed the "/", but it did not help me. Any helps are appreciated.

Byte64 said...

Hi Sharmin,
it's hard to say.
Oracle db version?
Oracle SQL Developer version?
By compiling do you mean that you are pressing the compile button or are you simply executing the CREATE OR REPLACE command inside the SQL workspace as if it were a simple SQL statement?
Did you post your problem in the OTN Sqldeveloper forum?

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles