PL/SQL Warning PLW-07202 Returned with Data Types that are the Same (Doc ID 445136.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.3 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
Checked for relevance on 20-May-2011
Affected Database: Oracle

Symptoms

PL/SQL Stored Procedure fails to compile a 'timestamp with time zone' or 'varchar2' bind variable and returns a warning: 

PLW-07202: bind type would result in conversion away from column type

The following code demonstrates this issue:

ALTER SESSION SET plsql_warnings='ENABLE:ALL'
/
CREATE TABLE tab (dt timestamp with time zone)
/
CREATE OR REPLACE PROCEDURE proc_test AS
BEGIN
  UPDATE tab SET dt = systimestamp;
END;
/
SP2-0804: Procedure created with compilation warnings

The result is:

SQL> show errors
Errors for PROCEDURE PROC_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/23 PLW-07202: bind type would result in conversion away from column type

Also (another example)

CREATE TABLE t(v varchar2(80))
/
INSERT INTO t(v) values('x')
/
CREATE OR REPLACE PROCEDURE p IS
BEGIN
  UPDATE t set t.v = Substr('abc', 1, 2);
END;
/
SP2-0804: Procedure created with compilation warnings

Result:

SQL> show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/36     PLW-07202: bind type would result in conversion away from column
         type

3/39     PLW-07202: bind type would result in conversion away from column
         type


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms