Home » SQL & PL/SQL » SQL & PL/SQL » How to add days to a date without warnings? (Oracle 10g R2)
How to add days to a date without warnings? [message #310861] Wed, 02 April 2008 12:17 Go to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Hi. I have a question:
How do you add a day to a date without warnings at compile time?
My code is the following:
.....
   select d.idusuario,d.fechaalta,d.nombre,d.apellidos
    bulk collect into res_bulk
    from (
      select idusuario,fechaalta,nombre,apellidos
      from datos_usuario
      where fechasolicitudbaja is null
      order by datos_usuario.fechaalta desc
    )d
    where d.fechaalta > fecha_inicio
    and   d.fechaalta < fecha_fin + interval '1' day
    and   rownum <= fin_paginacion;
.....

It gives me a warning at compile time:
Warning--->:
"Warning(28,25): PLW-07204: puede que la conversión que no sea de tipo de columna dé como resultado un plan de consulta subóptimo"

If you need anything else to help me just ask.

Thanks Joaquin.
Re: How to add days to a date without warnings? [message #310864 is a reply to message #310861] Wed, 02 April 2008 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You set your session to get PL/SQL warnings so I think you want to get them.
If you don't want this one you can execute:
alter session set plsql_warnings='disable:7204';

However you should pay attention of what it says.

Regards
Michel
Re: How to add days to a date without warnings? [message #310867 is a reply to message #310864] Wed, 02 April 2008 12:41 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Ok. That´s half the solution I´m looking for.
I would really like to know how to avoid those warnings if you could help me with that i´ll really appreciate it.
Thanks, Joaquin
Re: How to add days to a date without warnings? [message #310870 is a reply to message #310867] Wed, 02 April 2008 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The statement I gave prevent you from this warning or do you have something else in mind?

Regards
Michel
Re: How to add days to a date without warnings? [message #310871 is a reply to message #310861] Wed, 02 April 2008 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
7204,0, "conversion away from column type may result in sub-optimal query plan"
// MANUAL: PL/SQL
// INDEX:
// RELEASE:  10.0.0
// CAUSE:  The column type and the bind type do not exactly match. This
//         may 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.

Re: How to add days to a date without warnings? [message #310872 is a reply to message #310867] Wed, 02 April 2008 12:55 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
I would really like to know how to avoid those warnings if you could help me with that i&acute;ll really appreciate it

In order to achieve that you should the documentation. It clearly states why you are getting that warning. I don't think it cannot be more explicitly than what it says in the documentation.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/plwus.htm#sthref14004
Search for PLW-07204. Read it and you will understand how to avoid it.

Regards

Raj
Re: How to add days to a date without warnings? [message #310873 is a reply to message #310870] Wed, 02 April 2008 12:57 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
to Michel Cadot:
You may have misinterpret me.
I want to add a day to a date without the warning showing up.
I understood that your statement disable's it. I don't want that, I want to add a day correctly without warnings.
Maybe it's impossible, i don't know, but there is a function called add_month to a date so maybe oracle has something like this for days.
Regards, Joaquin
Re: How to add days to a date without warnings? [message #310874 is a reply to message #310873] Wed, 02 April 2008 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the type of d.fechaalta and fecha_fin?
If fecha_fin is of datatype DATE to add a day you just have to add 1 and not an interval of one day.

Regards
Michel
Re: How to add days to a date without warnings? [message #310875 is a reply to message #310873] Wed, 02 April 2008 13:04 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
to s.Rajaram:

I understand that dates are different from numbers. What i would like to know is :
How do I add a day to a date without getting this warnings?
Maybe there is a function but I don't know about it. So if you could help me or point me in the right direction, I would be very grateful.

to Michel:

Yes they are. I've tried but the output is the same:
select d.idusuario,d.fechaalta,d.nombre,d.apellidos
bulk collect into res_bulk
from (
select idusuario,fechaalta,nombre,apellidos
from datos_usuario
where fechasolicitudbaja is null
order by datos_usuario.fechaalta desc
)d
where d.fechaalta > fecha_inicio
and d.fechaalta < fecha_fin + 1
and rownum <= fin_paginacion;
Joaquin

[Updated on: Wed, 02 April 2008 13:04]

Report message to a moderator

Re: How to add days to a date without warnings? [message #310876 is a reply to message #310875] Wed, 02 April 2008 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure the warning comes from this line?
Can you post a simple test case we can reproduce?

Regards
Michel
Re: How to add days to a date without warnings? [message #310877 is a reply to message #310876] Wed, 02 April 2008 13:21 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
How do i do that?
I'm working with sqldeveloper 1.5 EA2 if I execute the same code on an sql worksheet i don't see the warnings.
If you could tell how to do it,i'll be delighted to do it.
Here is some code that doesn't show warnings nor errors on the sql worksheet:
declare 
  date_f date := to_date('01/02/2008', 'dd/mm/yyyy');
begin
  dbms_output.put_line('Original : ' || date_f);
  dbms_output.put_line('Modified 1: ' || (date_f + interval '1' day));
  dbms_output.put_line('Modified 2: ' || (date_f + 2));
end;


Here you have an screen. Tomorrow I'll keep following this thread.

See ya! Joaquin

[Updated on: Wed, 02 April 2008 13:29]

Report message to a moderator

Re: How to add days to a date without warnings? [message #310880 is a reply to message #310877] Wed, 02 April 2008 13:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Test case. I am stuck basically how to do it. Probably oracle is saying don't do any arithmetic with dates you might end up with a sub-obtimal plan.
SQL> create table test_data_tab (curr_date date);

Table created.

SQL> alter session set plsql_warnings='ENABLE:ALL';

Session altered.

  1  create or replace procedure test_warning
  2  is
  3  a number;
  4  begin
  5  select count(*) into a from test_data_tab
  6  where curr_date < curr_date + 1  ;
  7* end;
SQL> /

SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE TEST_WARNING:

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

6/29     PLW-07204: conversion away from column type may result in
         sub-optimal query plan

6/31     PLW-07204: conversion away from column type may result in
         sub-optimal query plan

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Re: How to add days to a date without warnings? [message #310950 is a reply to message #310880] Wed, 02 April 2008 23:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following first reproduces the warning, then eliminates it. However, I should point out that the where clause is totally useless as any column value will always be less than itself + 1, so your actual requirement is unclear.

-- with warning:
SCOTT@orcl_11g> alter session set plsql_warnings = 'ENABLE:ALL'
  2  /

Session altered.

SCOTT@orcl_11g> create or replace procedure test_warning
  2  as
  3    v_count number;
  4  begin
  5    select count (*)
  6    into   v_count
  7    from   emp
  8    where  hiredate < hiredate + 1;
  9  end test_warning;
 10  /

SP2-0804: Procedure created with compilation warnings

SCOTT@orcl_11g> show errors
Errors for PROCEDURE TEST_WARNING:

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

8/30     PLW-07204: conversion away from column type may result in
         sub-optimal query plan

8/32     PLW-07204: conversion away from column type may result in
         sub-optimal query plan

SCOTT@orcl_11g> exec test_warning

PL/SQL procedure successfully completed.


-- without warning:
SCOTT@orcl_11g> alter session set plsql_warnings = 'DISABLE:ALL'
  2  /

Session altered.

SCOTT@orcl_11g> create or replace procedure test_warning
  2  as
  3    v_count number;
  4  begin
  5    select count (*)
  6    into   v_count
  7    from   emp
  8    where  hiredate < hiredate + 1;
  9  end test_warning;
 10  /

Procedure created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> exec test_warning

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>


Re: How to add days to a date without warnings? [message #310976 is a reply to message #310950] Thu, 03 April 2008 02:47 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Barbara,

I agree about the where clause in this case is totally meaningless. But that is not my point. If you see in my test case i have not inserted any records. All I am trying to say in my test case is if you do try to do a arithmetic operation on a date field (i.e) add 1 day to a date column , pl/sql compiler is coming up with warnings, indicating, due to the arithmetic performed on a date column it could lead to a subobtimal plan. I tried different combinations to avoid that warning but no success. Your suggestion has already been posted by Michel to disable the warning message.

I hope it explains.

Regards

Re: How to add days to a date without warnings? [message #311013 is a reply to message #310861] Thu, 03 April 2008 04:27 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
You could write your own function to do the arithmetic to avoid the warning(s):

SQL> CREATE TABLE ms_test
  2  (
  3  log_dtm	    DATE
  4  ) tablespace users ;

Table created.

SQL> create or replace function ms_add_days( ad_date DATE , an_number NUMBER ) RETURN DATE is
  2  begin
  3  	 return ad_date + an_number ;
  4  END ;
  5  /

Function created.

SQL> show errors
No errors.

SQL> create or replace PROCEDURE ms_proc_warning IS
  2    result NUMBER ;
  3  BEGIN
  4  
  5  	 select count(*)
  6  	 into result
  7  	 from ms_test
  8  	 where log_dtm < log_dtm + 1 ;
  9  
 10  end ms_proc_warning;
 11  /

SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE MS_PROC_WARNING:

LINE/COL ERROR                                                                  
-------- -----------------------------------------------------------------      
8/21     PLW-07204: conversie van kolomtype kan leiden tot minder optimaal zoekvraagplan                                                          
                                                                                
8/29     PLW-07204: conversie van kolomtype kan leiden tot minder optimaal zoekvraagplan                                                          
                                                                                
8/31     PLW-07204: conversie van kolomtype kan leiden tot minder optimaal zoekvraagplan                                                          
                                                                                
SQL> 
SQL> create or replace PROCEDURE ms_proc_nowarning IS
  2    result NUMBER ;
  3  BEGIN
  4  
  5  	 select count(*)
  6  	 into result
  7  	 from ms_test
  8  	 where log_dtm < ms_add_days( log_dtm , 1 ) ;
  9  
 10  end ms_proc_nowarning;
 11  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 
SQL> drop table ms_test purge ;

Table dropped.

SQL> drop function ms_add_days ;

Function dropped.

SQL> drop procedure ms_proc_warning ;

Procedure dropped.

SQL> drop procedure ms_proc_nowarning ;

Procedure dropped.



Sorry for the messages in Dutch Embarassed
Re: How to add days to a date without warnings? [message #311027 is a reply to message #311013] Thu, 03 April 2008 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think if you have no more a warning this is because you are no more in the case where "you may have suboptimal plan" and are now in the case "you have suboptimal plan". Razz

Regards
Michel
Re: How to add days to a date without warnings? [message #311031 is a reply to message #311027] Thu, 03 April 2008 04:56 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Thu, 03 April 2008 11:48
I think if you have no more a warning this is because you are no more in the case where "you may have suboptimal plan" and are now in the case "you have suboptimal plan". Razz

Regards
Michel



My example is a bit over-simplified, but if you use a variable of type DATE - or in this case SYSDATE, then you get the WARNING as well.

SQL> create or replace PROCEDURE ms_proc_warning IS
  2    result NUMBER ;
  3  BEGIN
  4  
  5  	 select count(*)
  6  	 into result
  7  	 from ms_test
  8  	 where log_dtm < sysdate + 1 ;
  9  
 10  end ms_proc_warning;
 11  /


In our shop we just moved to 10g and had the PL/SQL Warnings in place.
We got a lot of warnings (about 20 per procedure sometims, which is the limit) telling us "you may have suboptimal plan".
Most cases it had to do with DATE-arithmetic.
But disabling PL/SQL Warnings could hide warnings which could be important enough to have a look at.
Furthermore: after 20 warnings/errors compilation stops.



[Updated on: Thu, 03 April 2008 04:56]

Report message to a moderator

Re: How to add days to a date without warnings? [message #311053 is a reply to message #311031] Thu, 03 April 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I wanted to say is that as soon as you use a custom PL/SQL function you stop the optimizer in its optimizing work.
Maybe "date+1" generates an error but the optimizer knows what it means and so can handle it when "add_one_day(date)" can't be optimized and constrains the optimizer in some code paths.

Regards
Michel
Re: How to add days to a date without warnings? [message #311054 is a reply to message #311053] Thu, 03 April 2008 05:27 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Thu, 03 April 2008 12:25
What I wanted to say is that as soon as you use a custom PL/SQL function you stop the optimizer in its optimizing work.
Maybe "date+1" generates an error but the optimizer knows what it means and so can handle it when "add_one_day(date)" can't be optimized and constrains the optimizer in some code paths.

Regards
Michel



Thanks for clarifying !
Re: How to add days to a date without warnings? [message #311115 is a reply to message #310861] Thu, 03 April 2008 07:50 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
So.
Anybody knows how to add any number of days to a date without getting warnings?
I don't like the idea of using a function to do it or disabling that warning. It's kind of important because at work to gather statistics we have to add or subtract days, week or months.

If you think it's impossible please let me know.

Joaquin
Re: How to add days to a date without warnings? [message #311157 is a reply to message #311115] Thu, 03 April 2008 10:42 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I think I got one of the examples mixed up with the actual problem. So, if it is a variable, not a column, that you are adding a day to for comparison, then you can solve the problem by first selecting the variable plus one day into another variable of date datatype, then using that second variable in your select statement. I have provided a reproduction and solution below.

-- reproduction with warning:
SCOTT@orcl_11g> create or replace procedure test_warning
  2    (p_date in date)
  3  as
  4    v_count number;
  5    v_date  date;
  6  begin
  7    select count (*)
  8    into   v_count
  9    from   emp
 10    where  hiredate < p_date + 1;
 11  end test_warning;
 12  /

SP2-0804: Procedure created with compilation warnings

SCOTT@orcl_11g> show errors
Errors for PROCEDURE TEST_WARNING:

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

10/28    PLW-07204: conversion away from column type may result in
         sub-optimal query plan

10/30    PLW-07204: conversion away from column type may result in
         sub-optimal query plan

SCOTT@orcl_11g> exec test_warning (sysdate)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>


-- solution without warning:
SCOTT@orcl_11g> create or replace procedure test_warning
  2    (p_date in date)
  3  as
  4    v_count number;
  5    v_date  date;
  6  begin
  7    v_date := p_date + 1;
  8    select count (*)
  9    into   v_count
 10    from   emp
 11    where  hiredate < v_date;
 12  end test_warning;
 13  /

Procedure created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> exec test_warning (sysdate)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g>

Re: How to add days to a date without warnings? [message #311170 is a reply to message #311157] Thu, 03 April 2008 11:12 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
Thanks Barbara Smile.
It works flawlessly
Re: How to add days to a date without warnings? [message #311174 is a reply to message #311170] Thu, 03 April 2008 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Flawlessly I don't know, once again you hide something to the optimizer: the correlation between d.fechaalta and fecha_fin in your example.

Regards
Michel

Re: How to add days to a date without warnings? [message #311176 is a reply to message #311174] Thu, 03 April 2008 11:29 Go to previous messageGo to next message
xokas11
Messages: 28
Registered: January 2008
Location: Guadalajara, Spain
Junior Member
What do you mean by correlation?
I mean flawlessly because I don't get the warnings. Maybe because of what I do the optimizer doesn't optimize as well as it could.
Right now the code looks like this:
...
fecha_fin_mod date := fecha_fin +1;
  begin
  if num_pag != -1 and elementos_x_pag != -1 then
    select d.idusuario,d.fechaalta,d.nombre,d.apellidos
    bulk collect into res_bulk
    from (
      select idusuario,fechaalta,nombre,apellidos
      from datos_usuario
      where fechasolicitudbaja is null
      order by datos_usuario.fechaalta desc
    )d
    where d.fechaalta > fecha_inicio
    and   d.fechaalta < fecha_fin_mod
    and   rownum <= fin_paginacion;
...


Any ideas how to improve this code optimization wise?

Joaquin
Re: How to add days to a date without warnings? [message #311181 is a reply to message #311176] Thu, 03 April 2008 11:40 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correlation is "d.fechaalta < fecha_fin + 1" but now I see that fecha_fin was a variable which was not specified in your post and I thought it was a column.
In this case, it is really flawless.

Regards
Michel
Previous Topic: cursor for loop syntax issue
Next Topic: Performance Degrade when ported from 9i to 10g
Goto Forum:
  


Current Time: Thu Dec 08 04:25:24 CST 2016

Total time taken to generate the page: 0.16276 seconds