Thanks for the question, John.

Asked: January 24, 2008 - 9:49 am UTC

Last updated: November 02, 2011 - 3:05 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

We found some interesting behavior when working in a package the other day. There are some things at the top of the package that have been generating warnings, but we've been ignoring the warnings because it seemed trivial. What I noticed is that after compiling the package seemingly successfully, I couldn't execute it. It wasn't really compiled. This is probably a bug in SQLDeveloper, but why generate warnings on what I would expect to be a trivial type conversion?

  CREATE TABLE T
   ( TS_PK TIMESTAMP (6), 
 DATA1 NUMBER
   );

Created in SQLPlus, we know that the package did not compile.

SQL> create or replace PACKAGE TEST_WARNING AS
  2
  3    PROCEDURE do_stuff;
  4
  5  END TEST_WARNING;
  6  /

Package created.

SQL> create or replace PACKAGE BODY TEST_WARNING AS
  2
  3    PROCEDURE do_stuff AS
  4    BEGIN
  5        insert into t (ts_pk,data1) values (systimestamp, 1);
  6        insert into t (ts_pk,data1) values (systimestamp, 2);
  7        insert into t (ts_pk,data1) values (systimestamp, 3);
  8        insert into t (ts_pk,data1) values (systimestamp, 4);
  9        insert into t (ts_pk,data1) values (systimestamp, 5);
 10        insert into t (ts_pk,data1) values (systimestamp, 6);
 11        insert into t (ts_pk,data1) values (systimestamp, 7);
 12        insert into t (ts_pk,data1) values (systimestamp, 8);
 13        insert into t (ts_pk,data1) values (systimestamp, 9);
 14        insert into t (ts_pk,data1) values (systimestamp, 10);
 15        insert into t (ts_pk,data1) values (systimestamp, 10);
 16        insert into t (ts_pk,data1) values (systimestamp, 12);
 17        insert into t (ts_pk,data1) values (systimestamp, 13);
 18        insert into t (ts_pk,data1) values (systimestamp, 14);
 19        insert into t (ts_pk,data1) values (systimestamp, 15);
 20        insert into t (ts_pk,data1) values (systimestamp, 16);
 21        insert into t (ts_pk,data1) values (systimestamp, 17);
 22        insert into t (ts_pk,data1) values (systimestamp, 18);
 23        insert into t (ts_pk,data1) values (systimestamp, 19);
 24        insert into t (ts_pk,data1) values (systimestamp, 20);
 25
 26        blah; -- this should cause the compile to fail
 27
 28
 29    END do_stuff;
 30
 31  END TEST_WARNING;
 32  /

Warning: Package Body created with compilation errors.

But through SQLDevleoper:

TEST_WARNING Compiled 
TEST_WARNING Body Compiled (with warnings)

Project: D:\Data\sqldeveloper\sqldeveloper\system\oracle.sqldeveloper.1.2.0.2998\DefaultWorkspace\Project1.jpr
    PACKAGE BODY OHD.TEST_WARNING@ohd
        Warning(6,43): PLW-07202: bind type would result in conversion away from column type
        Warning(7,43): PLW-07202: bind type would result in conversion away from column type
        Warning(8,43): PLW-07202: bind type would result in conversion away from column type
        Warning(9,43): PLW-07202: bind type would result in conversion away from column type
        Warning(10,43): PLW-07202: bind type would result in conversion away from column type
        Warning(11,43): PLW-07202: bind type would result in conversion away from column type
        Warning(12,43): PLW-07202: bind type would result in conversion away from column type
        Warning(13,43): PLW-07202: bind type would result in conversion away from column type
        Warning(14,43): PLW-07202: bind type would result in conversion away from column type
        Warning(15,43): PLW-07202: bind type would result in conversion away from column type
        Warning(16,43): PLW-07202: bind type would result in conversion away from column type
        Warning(17,43): PLW-07202: bind type would result in conversion away from column type
        Warning(18,43): PLW-07202: bind type would result in conversion away from column type
        Warning(19,43): PLW-07202: bind type would result in conversion away from column type
        Warning(20,43): PLW-07202: bind type would result in conversion away from column type
        Warning(21,43): PLW-07202: bind type would result in conversion away from column type
        Warning(22,43): PLW-07202: bind type would result in conversion away from column type
        Warning(23,43): PLW-07202: bind type would result in conversion away from column type
        Warning(24,43): PLW-07202: bind type would result in conversion away from column type
        Warning(25,43): PLW-07202: bind type would result in conversion away from column type
        Warning(1,1): Only first 20 issues are reported  <-- this causing my problem

What is the best way to put the current time into a plain timestamp column so that we don't get these warnings? I know there are multiple ways to suppress them, but I'd rather fix it so that they aren't warnings anymore.

After running this, SQLDeveloper displays the error as expected, but I shouldn't need to do it in the first place.

exec dbms_warning.add_warning_setting_cat('ALL', 'DISABLE', 'SESSION')

and Tom said...

plsql stops after 20 messages (warning or otherwise).

They are looking at upping that limit in the next release..

so, sqldeveloper - which has warnings on by default (and sqlplus does not) does this.

You can turn off the warnings by:

Preferences->Database->PL/SQL Compiler Options

turn off the informational/warning messages and it'll behave just like sqlplus in that regards.

man, a timestamp as a primary key :( what a bad choice... (converted from sqlserver I'd guess. very 'popular' from sybase days gone by...)

i would expect that even if that compiled, it would FAIL sometimes (assuming ts_pk is timestamp - primary key) Oracle is fast, you'll have duplicates some time.

ops$tkyte%ORA10GR2> create table t ( ts_pk timestamp(6) );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set plsql_warnings='enable:all';

Session altered.

ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3  begin
  4          insert into t values ( systimestamp );
  5  end;
  6  /

SP2-0804: Procedure created with compilation warnings

ops$tkyte%ORA10GR2> show errors
Errors for PROCEDURE P:

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

ops$tkyte%ORA10GR2> create or replace procedure p
  2  as
  3          l_ts t.ts_pk%type;
  4  begin
  5          l_ts := systimestamp;
  6          insert into t values ( l_ts );
  7  end;
  8  /

Procedure created.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PLW-07204 and urowid

Renat, July 21, 2011 - 7:32 am UTC

With warnings enabled I found that can not eliminate the PLW-07204 in case of using urowid:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP and Real Application Testing options

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

SQL> create table a ( b number);

Table created.

SQL> create or replace  procedure plw_07204 (p_rwid urowid)
   authid DEFINER
is
   l_i number;
begin
     select b
       into l_i
      from a
      where rowid = p_rwid;
end;
/
  2    3    4    5    6    7    8    9   10   11  
SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PROCEDURE PLW_07204:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/21  PLW-07204: conversion away from column type may result in
  sub-optimal query plan

Another examples of warnings that could not be avoided

Renat, August 26, 2011 - 7:31 am UTC

plw-07202:

SQL> create table t1 as select current_timestamp ts from dual;

Table created.

SQL> create or replace procedure plw_07202
   authid definer
is 
begin 
   insert into t1 (ts) 
   values (current_timestamp);
end;
/
  2    3    4    5    6    7    8  
SP2-0804: Procedure created with compilation warnings
SQL> sho err
Errors for PROCEDURE PLW_07202:

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

SQL> desc t1
 Name        Null?    Type
 ----------------------------------------- -------- ----------------------------
 TS          TIMESTAMP(6) WITH TIME ZONE

plw-07206:

SQL> create or replace procedure plw_07206
   authid definer
is 
  lcrs number;
  l_ign number;
begin 
   l_ign := dbms_sql.open_cursor(lcrs);
   dbms_sql.close_cursor(lcrs);
exception
when others then
   if dbms_sql.is_open(lcrs) then
      dbms_sql.close_cursor(lcrs);
   end if;
end;
/
  2    3    4    5    6    7    8    9   10   11   12   13   14   15  
SP2-0804: Procedure created with compilation warnings

SQL> sho err
Errors for PROCEDURE PLW_07206:

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/4  PLW-07206: analysis suggests that the assignment to 'LCRS' may
  be unnecessary

SQL> 

How coud I avoid these warning?

August 30, 2011 - 4:45 pm UTC

wow, you missed the most evil of all:

10/6     PLW-06009: procedure "P" OTHERS handler does not end in RAISE or
         RAISE_APPLICATION_ERROR

you'll get that in 11g thankfully - and the fix is obvious, PUT THE RAISE IN THERE FOR GOODNESS SAKE.

the first one could be made to go away via direct use of the very specific type the table column is defined as:

create or replace procedure p
   authid definer
is
    l_data TIMESTAMP(6) WITH TIME ZONE;
begin
   l_data := current_timestamp;

   insert into t1 (ts)
   values ( l_data );
end;
/

the second one - you'll have to live with it. A code analysis like that is not perfect - it is just pointing out that it thinks (because of the exception block for whatever reason) that something is up with lcrs. We humans have to take the suggestions and either accept or reject them.

Warnings that hard to avoid

Renat, November 01, 2011 - 7:29 am UTC

Thank you very much for pointing out that I have to use a very specific type. However, I could not find out how to define specific variable for urowid from question above (PLW-07204 and urowid July 21, 2011)

As to the raise in "when others" it was only example. In real code we use our special procedure for handling errors, which do raise inside. It looks like
---------
exception
when others then
err.raise();
end;
---------
For this reason we have to disable raise warning, otherwise we would have to put another raise just after err.raise() wich makes no sense.

November 02, 2011 - 3:05 am UTC

you do not have to disable the warning, you just have to accept that the warning is there and in that case - ok.

that is why they are warnings and not errors. Warnings mean "take a look at this".

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

PLW-07204 and urowid

Another examples of warnings that could not be avoided

Warnings that hard to avoid

More to Explore

PL/SQL demos

PL/SQL docs

PL/SQL Warnings

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

PLW-07204 and urowid

Another examples of warnings that could not be avoided

Warnings that hard to avoid

More to Explore

PL/SQL demos

PL/SQL docs