10 Replies Latest reply: Sep 9, 2011 9:43 AM by user6409828 RSS

    Stupid PLW-07202 warning

    chris_here
      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_
          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
            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
              http://www.oracle-base.com/articles/10g/PlsqlEnhancements10g.php#compile_time_warnings
              • 4. Re: Stupid PLW-07202 warning
                MichaelS
                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
                  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
                    "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
                      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
                        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
                          Hi Michael,

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

                          Chris
                          • 10. Re: Stupid PLW-07202 warning
                            user6409828
                            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;
                            /