Wednesday, January 09, 2008

PLS-00382: expression is of wrong type

There are at least two distinct situations where this error can be returned, the former scenario involves collections and the latter is a relatively "simple" implicit conversion problem.
PLS-00382: expression is of wrong type
Note that PLS-00382 can be raised when you try assign a value that cannot be converted implicitly into the target data type, as in the following example:
declare
a integer;
b timestamp := systimestamp;
begin
a := b;
dbms_output.put_line(b);
end;

Error report:
ORA-06550: line 5, column 8:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
See the following Oracle 10G document about allowed implicit conversions, where, for some reason, the implicit conversion between timestamps and dates is not mentioned and the new 11G document doesn't include it either, but is indeed perfectly working:
DECLARE
a DATE;
b TIMESTAMP WITH TIME ZONE := systimestamp;
begin
a := b;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;

09-JAN-08
09-JAN-08 02.37.02.339176 PM +01:00

However you should always avoid using implicit conversions as they are affected by the server and client locale settings and a program that works in your environment may stop working altogether when executed from another client.

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