PLW-07202: bind type would result in conversion away from column type
oracle, plsql
Solution
This appears to be a bug, with no fix or real workaround. If you have access to My Oracle Support, look up doc ID 445136.1.
This forum thread suggests a workaround:
Run this block before CREATING or REPLACING your packages, functions, or procedures that exhibit this issue:
BEGIN
DBMS_WARNING.SET_WARNING_SETTING_STRING('ENABLE:ALL', 'SESSION');
DBMS_WARNING.ADD_WARNING_SETTING_NUM(warning_number => 7202, warning_value => 'DISABLE', scope => 'SESSION');
END;
/
Or you can use the `ALTER SESSION` syntax described in the documentation:
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL,DISABLE:07202';
This SQL Fiddle shows the warning; this one with the PLW-07202 suppressed with `DBMS_WARNING`, and this one suppressing with `ALTER SESSION`, do not. So that approach works, but of course also suppresses any legitimate warnings of that type.
Problem
I'm trying to write my first PL/SQL Stored procedure in 15 years. I am getting a type conversion warning: ``` 2 PLW-07202: bind type would result in conversion away from column type SQL1.sql 11 60 ``` when trying to assign the current system time to the following column: ``` CRET_TIMESTMP TIMESTAMP(6) NOT NULL ``` Here's the whole table definition, for the record. ``` CREATE TABLE FIN_IT_RPT.COGNOS_RPTNG_SCHEDLNG_STAT ( JOB_NM VARCHAR2(20 BYTE) NOT NULL ,JOB_STAT_CD VARCHAR2(1 BYTE) NOT NULL ,CRET_TIMESTMP TIMESTAMP(6) NOT NULL ,CRET_OPER_ID VARCHAR2(10 BYTE) NOT NULL ,UPDT_TIMESTMP TIMESTAMP(6) ,UPDT_USR_ID VARCHAR2(10 BYTE) ) TABLESPACE USERS STORAGE (INITIAL 64 K NEXT 1 M MAXEXTENTS UNLIMITED) LOGGING; ``` Here's my procedure: ``` CREATE OR REPLACE PROCEDURE FIN_IT_RPT.UPDATE_FINODS_COGNOS_STATUS ( P_JOB_STAT_CD IN COGNOS_RPTNG_SCHEDLNG_STAT.JOB_NM % TYPE ) AS BEGIN UPDATE COGNOS_RPTNG_SCHEDLNG_STAT SET COGNOS_RPTNG_SCHEDLNG_STAT.JOB_STAT_CD = P_JOB_STAT_CD ,COGNOS_RPTNG_SCHEDLNG_STAT.UPDT_TIMESTMP = CAST(CURRENT_TIMESTAMP AS TIMESTAMP(6)) WHERE COGNOS_RPTNG_SCHEDLNG_STAT.JOB_NM = 'FINODS'; COMMIT; END UPDATE_FINODS_COGNOS_STATUS; ``` Why am I getting the warning and how do I prevent the type conversion? I know it's only a warning, but shouldn't I be able to explicitly convert it to the target type or use a function that natively gets the time in the appropriate format? Here is what I am using: ``` Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production ```