[TOraSQL] Uninitialized parameters with Null value causing EConvertError in Direct mode

[pcz]

Hello

I have no idea if this case is connected with viewtopic.php?f=5&t=33825

If yes - just ignore it

Exception "Invalid argument to date encode" happens during execution in Direct mode when lines

Code: Select all

:DataOd := NULL;
:DataDo := NULL;

are commented (code sample below)

Code: Select all

declare
  vIleRozlMag integer;
  vIleRozlMagWtoku integer;
  vIleZamkniete integer;
begin
  select count(1)
  into vIleRozlMag
  from inw_rozl_mag rm;

--  :DataOd := NULL;
--  :DataDo := NULL;
  
  if(vIleRozlMag = 0) then
    :WYJ := -3;
  end if;
  
  if(vIleRozlMag > 0) then
    select count(1)
    into vIleRozlMagWtoku
    from inw_rozl_mag rm
    where rm.status = 'W TOKU';
    
    if(vIleRozlMagWtoku > 0) then
      select rm.data_do, rm.data_od
      into :DataOd, :DataDo
      from inw_rozl_mag rm
      where rm.status = 'W TOKU'
      and rownum <= 1;      
      :WYJ := 1;
    else
      select count(1)
      into vIleZamkniete
      from
      (
        select rm.data_do, rm.data_od, nvl(nk.pfk_numer, 0) pfk_numer, nvl(nk.suma_zaplat, 0) suma_zaplat, rank() over (order by rm.data_do desc) rn
        from inw_rozl_mag rm
             left join
             zap_noty_ksiegowe nk
             on (nk.numer = rm.znk_numer)
      ) a
      where a.rn = 1
      and 
      (
        a.pfk_numer <> '0'
        or
        a.suma_zaplat <> 0
      );
      
      if(vIleZamkniete = 0) then
        :WYJ := 2;
      else
        :WYJ := -2;
      end if;  
    end if;
  end if;
end;

Regards
P.C.

[AlexP]

Hello,

Please send us the script for creating the table.

[pcz]

This is shorter version of SQL that causes problem:

Code: Select all

declare
  vIleRozlMag integer;
  vIleRozlMagWtoku integer;
  vIleZamkniete integer;
begin
  select count(1)
  into vIleRozlMag
  from inw_rozl_mag rm
  where 1=2;

--  :DataOd := NULL;  uncomment these 2 lines to avoid EConvertError
--  :DataDo := NULL;
  
  if(vIleRozlMag = 0) then
    :WYJ := -3;
  end if;
  
  if(vIleRozlMag > 0) then
    select count(1)
    into vIleRozlMagWtoku
    from inw_rozl_mag rm
    where rm.status = 'W TOKU';
    
      select rm.data_do, rm.data_od
      into :DataOd, :DataDo
      from inw_rozl_mag rm
      where rm.status = 'W TOKU'
      and rownum <= 1;      
  end if;
end;

Exception class:
EConvertError

Scripts:

Code: Select all

create  table INW_ROZL_MAG
(
  ID                                 NUMBER(12, 0)   NOT NULL ,
  ZNK_NUMER                          VARCHAR2(17)   ,
  DATA_OD                            DATE            NOT NULL ,
  DATA_DO                            DATE            NOT NULL ,
  KON_NR_KONTRAHENTA                 NUMBER(8, 0)    NOT NULL ,
  AK_ID                              NUMBER(10, 0)   NOT NULL ,
  M_SYMBOL                           VARCHAR2(3)     NOT NULL ,
  CZY_FRANCZYZA                      VARCHAR2(1)     DEFAULT 'N' NOT NULL ,
  CZY_ZAMKNIETE                      VARCHAR2(1)     DEFAULT 'N' NOT NULL ,
  ILE_INWENT                         NUMBER          DEFAULT 0 NOT NULL ,
  ILE_KI_PLUS                        NUMBER          DEFAULT 0 NOT NULL ,
  ILE_KI_MINUS                       NUMBER          DEFAULT 0 NOT NULL ,
  ILE_JME_KI_PLUS                    NUMBER          DEFAULT 0 NOT NULL ,
  ILE_JME_KI_MINUS                   NUMBER          DEFAULT 0 NOT NULL ,
  WART_KI_PLUS                       NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  WART_KI_MINUS                      NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  SALDO                              NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  OBROT                              NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  DOP_STRATY                         NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  UTWORZONY_PRZEZ                    VARCHAR2(30)    NOT NULL ,
  DATA_UTWORZENIA                    DATE            NOT NULL ,
  MODYFIKOWANY_PRZEZ                 VARCHAR2(30)   ,
  DATA_MODYFIKACJI                   DATE           ,
  SALDO_OSTATECZNE                   NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  UWAGI                              VARCHAR2(256)  ,
  STATUS                             VARCHAR2(9)     DEFAULT 'W TOKU')
/

create  table ZAP_NOTY_KSIEGOWE
(
  NUMER                              VARCHAR2(17)    NOT NULL ,
  TYP_NOTY                           VARCHAR2(2)     NOT NULL ,
  SYMBOL_Z_FK                        VARCHAR2(20)   ,
  DATA_WYST_DOK                      DATE            NOT NULL ,
  TERMIN_PLATNOSCI                   DATE           ,
  WRT_DO_ZAPLATY                     NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  SUMA_ZAPLAT                        NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  MIEJSCE_UTWORZENIA                 VARCHAR2(2)     NOT NULL ,
  SFP_SYMBOL                         VARCHAR2(13)    NOT NULL ,
  KON_NR_KONTRAHENTA                 NUMBER(8, 0)    NOT NULL ,
  M_SYMBOL                           VARCHAR2(3)     NOT NULL ,
  DATA_CZAS_UTWORZENIA               DATE            NOT NULL ,
  UTWORZONY_PRZEZ                    VARCHAR2(30)    NOT NULL ,
  DATA_UTWORZENIA                    DATE            NOT NULL ,
  MODYFIKOWANY_PRZEZ                 VARCHAR2(30)   ,
  DATA_MODYFIKACJI                   DATE           ,
  UWAGI                              VARCHAR2(240)  ,
  PCE_NUMER                          VARCHAR2(12)    DEFAULT '0' NOT NULL ,
  PFK_NUMER                          VARCHAR2(12)    DEFAULT '0' NOT NULL ,
  SKO_NAZWA                          VARCHAR2(5)     DEFAULT NULL NOT NULL ,
  SWA_SYMBOL                         VARCHAR2(3)     DEFAULT 'PLN' NOT NULL ,
  SUMA_ZAPLAT_WAL                    NUMBER(12, 2)   DEFAULT 0 NOT NULL ,
  WRT_DO_ZAPLATY_WAL                 NUMBER(12, 2)   DEFAULT 0 NOT NULL )
/

Happens only in Direct mode

[AlexP]

Your query returns an error (even if you don't uncomment the lines):

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 15

if you comment the lines

Code: Select all

  --if(vIleRozlMag = 0) then
--    :WYJ := -3;
--  end if;

and uncomment

Code: Select all

  :DataOd := NULL;  --uncomment these 2 lines to avoid EConvertError
  :DataDo := NULL;

errors don't occur after query execution

[pcz]

Your query returns an error (even if you don't uncomment the lines):

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 15

ORA-06502 error

disappeared after declaring parameter types...
WYJ => Integer
DataOd, DataDo => Date

But the EConvertError happened only in direct mode


Maybe uninitialized param with Date type had some initial value that cannot be converted into date and this is not a NULL...

[pcz]

I have updated ODAC to 9.7.25 and it seems that problem disappeared

Your query returns an error (even if you don't uncomment the lines):

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 15

Sometimes parameter types have to be declared, sometimes not...

[AlexP]

If to set types for all parameters, then your query works with no errors