This discussion is archived
10 Replies Latest reply: Sep 9, 2011 7:43 AM by 887295 RSS

Stupid PLW-07202 warning

chris_here Newbie
Currently Being Moderated
Hi,

I get a stupid bunch of warning when compiling this under 11.1.0.7:
create table test ( t varchar2(50) );

create or replace procedure test_warn as 

    BEGIN

        INSERT INTO test (t)
             SELECT  substr('blah', 1, 2)
                       FROM dual;
                        
  END; 

SP2-0804: Procedure created with compilation warnings

show errors
Errors for PROCEDURE TEST_WARN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/37     PLW-07202: bind type would result in conversion away from column type
6/40     PLW-07202: bind type would result in conversion away from column type
The locations point to the "1" and "2" parameters in substr(). WTF???

Thanks,
Chris
  • 1. Re: Stupid PLW-07202 warning
    Karthick_Arp Guru
    Currently Being Moderated
    If you dont want that warning you may need to modify PLSQL_WARNINGS parameter

    http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/initparams165.htm
  • 2. Re: Stupid PLW-07202 warning
    chris_here Newbie
    Currently Being Moderated
    alter session set PLSQL_WARNINGS = 'DISABLE:STUPID';
    
    ORA-02097: Done! Sorry, my bad, will not do it again :-(
    Thanks! :-)
    Chris
  • 3. Re: Stupid PLW-07202 warning
    Hoek Guru
    Currently Being Moderated
    http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php#compile_time_warnings
  • 4. Re: Stupid PLW-07202 warning
    MichaelS Guru
    Currently Being Moderated
    Some times the warning will be good though:

    In your case it means you should code
    create or replace procedure test_warn 
    as 
      var varchar2(2) := substr('blah', 1, 2) ;
    begin
        insert into test (t)  select var from dual;    ---- no dual necessary here                    
    end; 
  • 5. Re: Stupid PLW-07202 warning
    chris_here Newbie
    Currently Being Moderated
    Well in fact the real code is more complex:
      PROCEDURE upload_trad IS
    
          maxlev  integer;   -- Maximum block length
    
        BEGIN
          SELECT max(len) INTO maxlev FROM view_blocks;
    
          FOR lev IN 1 .. maxlev
          LOOP
    
            INSERT INTO ref_trad (entry, block_id, num_id)
                 SELECT r.root || d.digit, v.block_id, v.num_id
                   FROM (SELECT DISTINCT substr(entry, 1, lev) AS root
                           FROM view_blocks
                          WHERE block_id > 0
                            AND (block IS NULL OR block NOT LIKE 'D%')
                            AND len >= lev) r,
                        ref_digit d, view_blocks v
                  WHERE v.block_id = get_block_id(r.root || d.digit);
    
          END LOOP;
      END;
    Oracle is unhappy with the "1" and "lev" parameters in substr() and also with the "len >= lev" comparison.
    And I am unhappy with this stupid warning.
    What is the database trying to warn me from precisely?

    Thanks,
    Chris
  • 6. Re: Stupid PLW-07202 warning
    Hoek Guru
    Currently Being Moderated
    "PLW-07202: bind type would result in conversion away from column type
    Cause: The column type and the bind type do not exactly match. This will result in the column being converted to the type of the bind variable. This type conversion may prevent the SQL optimizer from using any index the column participates in. This may adversely affect the execution performance of this statement.
    Action: To make use of any index for this column, make sure the bind type is the same type as the column type."
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/plwus.htm#sthref14013
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:637694300346290510
  • 7. Re: Stupid PLW-07202 warning
    chris_here Newbie
    Currently Being Moderated
    Yes but from the substr() doc page:
    >
    Both position and substring_length must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer.
    >
    of which at least "1" is as far as I can tell. So what gives?
  • 8. Re: Stupid PLW-07202 warning
    MichaelS Guru
    Currently Being Moderated
    What is the database trying to warn me from precisely?
    Looks like Bug 5983734: SPURIOUS PLW-07202, which still holds in 11gR2:
    SQL> select * from v$version where rownum = 1
    /
    BANNER                                                                          
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production          
    1 row selected.
    
    SQL> alter session set plsql_warnings='enable:all'
    /
    Session altered.
    
    SQL> create table t (a varchar2(40))
    /
    Table created.
    
    SQL> alter system flush shared_pool
    /
    System altered.
    
    SQL> create or replace procedure p as
    begin
     insert into t values (substr('abc',1,2));
    end;
    /
    Procedure created.
    
    SQL> show err
    Errors for PROCEDURE P
    
    LINE/COL ERROR                                                            
    -------- -----------------------------------------------------------------
    3/37     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                                                             
                                                                                    
    1/1      PLW-05018: unit P omitted optional AUTHID clause; default value D
             EFINER used                                                      
                                                                                    
    SQL> exec p
    PL/SQL procedure successfully completed.
    
    
    SQL> select  sql_text from v$sql
    where   lower(sql_text) not like '%v$sql$'
    and     lower(sql_text) like 'insert into t values%'
    /
    SQL_TEXT                                                                        
    --------------------------------------------------------------------------------
    INSERT INTO T VALUES (SUBSTR('abc',1,2))                                        
    1 row selected.
    /* no binding takes place at all, so a message about binding seems non-sensical. */
    
    
    SQL> alter system flush shared_pool
    /
    System altered.
    
    SQL> create or replace procedure p as
     s varchar2(3) := 'abc';
     s1 int := 1;
     s2 int := 1;
    begin
     insert into t values (substr(s, s1, s2));
    end;
    /
    Procedure created.
    
    SQL> show err
    Errors for PROCEDURE P
    
    LINE/COL ERROR                                                            
    -------- -----------------------------------------------------------------
    6/34     PLW-07202: bind type would result in conversion away from column 
             type                                                             
                                                                                    
    6/38     PLW-07202: bind type would result in conversion away from column 
             type                                                             
                                                                                    
    1/1      PLW-05018: unit P omitted optional AUTHID clause; default value D
             EFINER used                                                      
                                                                                    
    SQL> exec p
    PL/SQL procedure successfully completed.
    
    SQL> select  sql_text from v$sql
    where   lower(sql_text) not like '%v$sql$'
    and     lower(sql_text) like 'insert into t values%'
    /
    SQL_TEXT                                                                        
    --------------------------------------------------------------------------------
    INSERT INTO T VALUES (SUBSTR(:B3 , :B2 , :B1 ))                                 
    1 row selected.
    
    /*
    But (1) the datatypes of the PL/SQL variables that are bound are exactly 
    right for Substr(). And (2), the binding occurs in an expression that's that 
    right-hand-side for "set <column> =" so there's possibility of an index 
    being in use.
    */
  • 9. Re: Stupid PLW-07202 warning
    chris_here Newbie
    Currently Being Moderated
    Hi Michael,

    so that's the issue. Thanks for pointing to the appropriate material.

    Chris
  • 10. Re: Stupid PLW-07202 warning
    887295 Newbie
    Currently Being Moderated
    Just had to deal with this myself for some packages that use a lot of SUBSTR calls in INSERT...SELECT statements.

    Came up with this workaround to suppress these, but also suppresses any legitimate 7202 warnings:

    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;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points