Friday, January 04, 2008

PLS-00306: wrong number or types of arguments in call to 'DELETE'

This compilation error occurs when you specify a parameter for the DELETE method, as follows:
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type();
BEGIN
my_array.EXTEND(10);
my_array.DELETE(4);
dbms_output.enable;
dbms_output.put_line('total:'||my_array.COUNT);
END;

Error report:
ORA-06550: line 6, column 1:
PLS-00306: wrong number or types of arguments in call to 'DELETE'
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

The DELETE method doesn't accept parameters when it's applied to varrays.
When dealing with varrays you can only clear the whole array by specifying the DELETE method without any parameters.
If you want to remove the last n elements, use the TRIM(n) method instead.
DECLARE
TYPE array_type IS VARRAY(10) OF VARCHAR2(200);
my_array array_type := array_type();
BEGIN
my_array.EXTEND(10);
my_array.TRIM(4);
dbms_output.enable;
dbms_output.put_line('total:'||my_array.COUNT);
END;

total:6

One or two numeric parameters are allowed in DELETE when the collection is defined as TABLE, as in this example of sparse nested table:
DECLARE
TYPE table_type IS TABLE OF VARCHAR2(200);
my_table table_type := table_type();
BEGIN
my_table.EXTEND(10);
my_table.DELETE(1,10);
my_table(5) := 'a';
dbms_output.enable;
dbms_output.put_line('total:'||my_table.COUNT);
dbms_output.put_line('first subscript:'||my_table.FIRST);
END;

total:1
first subscript:5
See other articles on collection tips and techniques.

No comments:

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