Home » SQL & PL/SQL » SQL & PL/SQL » How do you avoid this sql warning? (Oracle 10.2.0, linux, )
How do you avoid this sql warning? [message #381763] Mon, 19 January 2009 12:42 Go to next message
efachim
Messages: 42
Registered: July 2008
Member
Hi folks,

I would like to get rid of this sql code warning

(Warning(151,3): PLW-07202: bind type would result in conversion away from column type)


I get when I compile the code below. Basically Oracle does not like the idea of using a number in an expression with sysdate. eg (sampling_date < sysdate - p_n_value) where p_n_value is a number;


What is the best way to do this?

CREATE TABLE "USERDATA"."TBL_SESSION_SQL_WAIT_HISTORY" 
   (           "SESSION_SQL_WAIT_HIST_SEQ_ID" NUMBER, 
                "DBID" NUMBER, 
                "SID" NUMBER, 
                "SEQ#" NUMBER, 
                "EVENT" VARCHAR2(64 BYTE), 
                "P1TEXT" VARCHAR2(64 BYTE), 
                "P1" NUMBER, 
                "P1RAW" RAW(8), 
                "P2TEXT" VARCHAR2(64 BYTE), 
                "P2" NUMBER, 
                "P2RAW" RAW(8), 
                "P3TEXT" VARCHAR2(64 BYTE), 
                "P3" NUMBER, 
                "P3RAW" RAW(8), 
                "WAIT_TIME" NUMBER, 
                "SECONDS_IN_WAIT" NUMBER, 
                "STATE" VARCHAR2(19 BYTE), 
                "SAMPLING_DATE" DATE, 
                "SAMPLING_TIME" VARCHAR2(8 BYTE), 
                "SERIAL#" NUMBER, 
                "USERNAME" VARCHAR2(30 BYTE), 
                "OSUSER" VARCHAR2(30 BYTE), 
                "PADDR" RAW(4), 
                "LOGON_TIME" DATE, 
                "PROCESS" VARCHAR2(24 BYTE), 
                "SQL_HASH_VALUE" NUMBER, 
                "SADDR" RAW(4), 
                "MODULE" VARCHAR2(48 BYTE), 
                "ROW_WAIT_OBJ#" NUMBER, 
                "ROW_WAIT_FILE#" NUMBER, 
                "ROW_WAIT_BLOCK#" NUMBER, 
                "ROW_WAIT_ROW#" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERDATA" ;


  CREATE TABLE "USERDATA"."TBL_SQLTEXT_WAIT_HISTORY" 
   (           "SQLTEXT_SEQ_ID" NUMBER, 
                "DBID" NUMBER, 
                "ADDRESS" RAW(4), 
                "HASH_VALUE" NUMBER, 
                "SQL_ID" VARCHAR2(13 BYTE), 
                "COMMAND_TYPE" NUMBER, 
                "PIECE" NUMBER, 
                "SQL_TEXT_PIECE" VARCHAR2(64 BYTE), 
                "SQL_TEXT" VARCHAR2(1000 BYTE), 
                "DISK_READS" NUMBER, 
                "BUFFER_GETS" NUMBER, 
                "DIRECT_WRITES" NUMBER, 
                "PARSE_CALLS" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERDATA" ;

create or replace
PROCEDURE data_coll AS
 p_n_value userdata.tbl_options.n_value%type;  -- number
 
BEGIN

-- irrelevant code here 

  select n_value into p_n_value from tbl_options where name ='PURGE_DAY_COUNT';
  
  DELETE FROM tbl_sqltext_wait_history t where t.hash_value in 
    (select s.sql_hash_value from cc_session_sql_wait_history s 
    where s.sampling_date < sysdate  - p_n_value 
    and s.sql_hash_value = t.hash_value);

  DELETE FROM tbl_session_sql_wait_history
  WHERE sampling_date < sysdate - p_n_value;

END;


Thanks in advance for your feedback.

rgds, Efachim
Re: How do you avoid this sql warning? [message #381766 is a reply to message #381763] Mon, 19 January 2009 12:56 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I don't see a line 151 in the posting.
Numbers can be used in math against DATEs.
You need to show all the datatypes of all of your variables and columns, run the code and show us the output of your session.
Partial errors are of no help.
Re: How do you avoid this sql warning? [message #381767 is a reply to message #381763] Mon, 19 January 2009 12:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Do not depend upon or expect implicit data type conversions.
Use available functions to convert between different data types.
Use available functions to perform date arithmetic.
These functions are documented in the SQL Reference Manual found at http://tahiti.oracle.com

[Updated on: Mon, 19 January 2009 12:59]

Report message to a moderator

Re: How do you avoid this sql warning? [message #381774 is a reply to message #381763] Mon, 19 January 2009 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't get rid of this warning.

Regards
Michel
Re: How do you avoid this sql warning? [message #381851 is a reply to message #381766] Tue, 20 January 2009 02:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yuo can disable them with the PlSQL_WARNINGS You can set this for a System, a Session, or an individual stored procedure.

More details here
Previous Topic: procedure in another procedure
Next Topic: getting compilation errors
Goto Forum:
  


Current Time: Sat Dec 10 18:13:51 CST 2016

Total time taken to generate the page: 0.09059 seconds