search

arthurblake / log4jdbc

log4jdbc is a Java JDBC driver that can log SQL and/or JDBC calls (and optionally SQL timing information) for other JDBC drivers using the Simple Logging Facade For Java (SLF4J) logging system.
516 stars 147 forks source link

Parameters are not (or wrongly) interpolated in a valid Oracle prepared statement #75

Closed alsha closed 9 years ago

alsha commented 9 years ago

Log-file output below shows the same SQL two times: not interpolated and interpolated. The second one is only partially interpolated, some parameters are set in wrong positions, e.g. here:

VS_FAHRZEUG.ID in (5367,5368,5402,5154,5144,5156,5322,5343,5158,1,0,0,1,0,1)

1,0,0,1,0,1 at the end is completely wrong. The parameter list is obviously somehow shifted starting from this point.

Here it is:

08:49:04.870  INFO [          ] audit,C3P0PooledConnectionPoolManager[identityToken->1hge1du9cpmcxen1009zcc|3175b70e]-HelperThread-#3 - 1. Connection.prepareStatement(select VS_FAHRZEUG.ID as D4843059,min(nvl(VS_FAHRZEUG.BAUMUSTN
|| '.' || nvl(VS_FAHRZEUG.LENKPOS,'?L')
|| '.' || (SELECT m.MOTORCODE FROM VS_MOTOR m join VS_FZG_MOTOR fm on m.ID = fm.MOTOR_ID WHERE fm.ID = VS_FAHRZEUG.FZG_MOTOR_ID)
|| '.' || (SELECT g.GETRTYP FROM VS_GETRIEBE g join VS_FZG_GETRIEBE fg on g.ID = fg.GETRIEBE_ID WHERE fg.ID = VS_FAHRZEUG.FZG_GETRIEBE_ID)
|| '.' || (SELECT nvl(VERTGETRTYPTYP,'4x2') FROM VS_ALLRAD WHERE ID = VS_FAHRZEUG.ALLRAD_ID)
|| '.' || decode(VS_FAHRZEUG.HAG_SERIE_ID,2,'Q11',nvl((SELECT TRANS_TEXT FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1020 AND TRANS_LANG = 'DE' AND TRANS_KEY = (SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)),(SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)))
|| '.' || VS_FAHRZEUG.TONNAGE*100
|| '.' || nvl(VS_FAHRZEUG.DACHFORM,'LH?')
|| '.' || nvl(VS_FAHRZEUG.ZULASSUNG, '00')
|| '.' || nvl(VS_FAHRZEUG.ABGASSTUFE, '000')
|| '.' || nvl(VS_FAHRZEUG.BLUEEFF, 'MX-')
|| '.' || nvl(VS_FAHRZEUG.AUFBAU, '000')
|| '.' || to_char(nvl(VS_FAHRZEUG.BASISFZGNUM,0),'00')
|| '.' || to_char(nvl(VS_FAHRZEUG.SAFZGNUM,0),'00'),VS_FAHRZEUG.ID)) as DN4843059,min(nvl(VS_FAHRZEUG.BAUMUSTN
|| '.' || nvl(VS_FAHRZEUG.LENKPOS,'?L')
|| '.' || (SELECT m.MOTORCODE FROM VS_MOTOR m join VS_FZG_MOTOR fm on m.ID = fm.MOTOR_ID WHERE fm.ID = VS_FAHRZEUG.FZG_MOTOR_ID)
|| '.' || (SELECT g.GETRTYP FROM VS_GETRIEBE g join VS_FZG_GETRIEBE fg on g.ID = fg.GETRIEBE_ID WHERE fg.ID = VS_FAHRZEUG.FZG_GETRIEBE_ID)
|| '.' || (SELECT nvl(VERTGETRTYPTYP,'4x2') FROM VS_ALLRAD WHERE ID = VS_FAHRZEUG.ALLRAD_ID)
|| '.' || decode(VS_FAHRZEUG.HAG_SERIE_ID,2,'Q11',nvl((SELECT TRANS_TEXT FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1020 AND TRANS_LANG = 'DE' AND TRANS_KEY = (SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)),(SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)))
|| '.' || VS_FAHRZEUG.TONNAGE*100
|| '.' || nvl(VS_FAHRZEUG.DACHFORM,'LH?')
|| '.' || nvl(VS_FAHRZEUG.ZULASSUNG, '00')
|| '.' || nvl(VS_FAHRZEUG.ABGASSTUFE, '000')
|| '.' || nvl(VS_FAHRZEUG.BLUEEFF, 'MX-')
|| '.' || nvl(VS_FAHRZEUG.AUFBAU, '000')
|| '.' || to_char(nvl(VS_FAHRZEUG.BASISFZGNUM,0),'00')
|| '.' || to_char(nvl(VS_FAHRZEUG.SAFZGNUM,0),'00'),VS_FAHRZEUG.ID)) as DL4843059,min(VS_FAHRZEUG.ID) as P7382905,min(nvl(VS_FAHRZEUG.BAUMUSTN
|| '.' || nvl(VS_FAHRZEUG.LENKPOS,'?L')
|| '.' || (SELECT m.MOTORCODE FROM VS_MOTOR m join VS_FZG_MOTOR fm on m.ID = fm.MOTOR_ID WHERE fm.ID = VS_FAHRZEUG.FZG_MOTOR_ID)
|| '.' || (SELECT g.GETRTYP FROM VS_GETRIEBE g join VS_FZG_GETRIEBE fg on g.ID = fg.GETRIEBE_ID WHERE fg.ID = VS_FAHRZEUG.FZG_GETRIEBE_ID)
|| '.' || (SELECT nvl(VERTGETRTYPTYP,'4x2') FROM VS_ALLRAD WHERE ID = VS_FAHRZEUG.ALLRAD_ID)
|| '.' || decode(VS_FAHRZEUG.HAG_SERIE_ID,2,'Q11',nvl((SELECT TRANS_TEXT FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1020 AND TRANS_LANG = 'DE' AND TRANS_KEY = (SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)),(SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)))
|| '.' || VS_FAHRZEUG.TONNAGE*100
|| '.' || nvl(VS_FAHRZEUG.DACHFORM,'LH?')
|| '.' || nvl(VS_FAHRZEUG.ZULASSUNG, '00')
|| '.' || nvl(VS_FAHRZEUG.ABGASSTUFE, '000')
|| '.' || nvl(VS_FAHRZEUG.BLUEEFF, 'MX-')
|| '.' || nvl(VS_FAHRZEUG.AUFBAU, '000')
|| '.' || to_char(nvl(VS_FAHRZEUG.BASISFZGNUM,0),'00')
|| '.' || to_char(nvl(VS_FAHRZEUG.SAFZGNUM,0),'00'),VS_FAHRZEUG.ID)) as P4844136,min(VS_RADREIFEN_VA.RAEDERTYP) as P6571745,min(VS_RADREIFEN_VA.ID) as D6571737,min(VS_RADREIFEN_VA.RADREIFENTYP) as P6571748,min((SELECT TRANS_TEXT || '/' || VS_RADREIFEN_VA.FLANKHOEHE FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1024 AND TRANS_KEY = VS_RADREIFEN_VA.REIFBZH)) as P6571751,min((SELECT tr.TRANS_TEXT || '/' || (SELECT ge.TRANS_TEXT FROM VS_TRANSLATIONS ge WHERE ge.TRANS_GROUP = 1022 AND ge.TRANS_KEY = VS_RADREIFEN_VA.GESCHWIDX) FROM VS_TRANSLATIONS tr WHERE tr.TRANS_GROUP = 1021 AND tr.TRANS_KEY = VS_RADREIFEN_VA.TRAGFIDX)) as P6571754,min((SELECT br.TRANS_TEXT || (SELECT fe.TRANS_TEXT FROM VS_TRANSLATIONS fe WHERE fe.TRANS_GROUP = 1003 AND fe.TRANS_KEY = VS_RADREIFEN_VA.FELGENBV) FROM VS_TRANSLATIONS br WHERE br.TRANS_GROUP = 1017 AND br.TRANS_KEY = VS_RADREIFEN_VA.BEREIFUNG)) as P6571757,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1004 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_VA.REIFBZV) or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_VA.REIFBZV is null))),VS_RADREIFEN_VA.REIFBZV)) as P6571760,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1023 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_VA.FELGENBH) or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_VA.FELGENBH is null))),VS_RADREIFEN_VA.FELGENBH)) as P6571763,min(VS_RADREIFEN_VA.TRAGF) as P6571766,min(VS_RADREIFEN_VA.TRAGFZ) as P6571769,min(VS_RADREIFEN_HA.RAEDERTYP) as P6571772,min(VS_RADREIFEN_HA.ID) as D6571741,min(VS_RADREIFEN_HA.RADREIFENTYP) as P6571775,min((SELECT TRANS_TEXT || '/' || VS_RADREIFEN_HA.FLANKHOEHE FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1024 AND TRANS_KEY = VS_RADREIFEN_HA.REIFBZH)) as P6571778,min((SELECT tr.TRANS_TEXT || '/' || (SELECT ge.TRANS_TEXT FROM VS_TRANSLATIONS ge WHERE ge.TRANS_GROUP = 1022 AND ge.TRANS_KEY = VS_RADREIFEN_HA.GESCHWIDX) FROM VS_TRANSLATIONS tr WHERE tr.TRANS_GROUP = 1021 AND tr.TRANS_KEY = VS_RADREIFEN_HA.TRAGFIDX)) as P6571781,min((SELECT br.TRANS_TEXT || (SELECT fe.TRANS_TEXT FROM VS_TRANSLATIONS fe WHERE fe.TRANS_GROUP = 1003  and fe.TRANS_LANG='DE' AND fe.TRANS_KEY = VS_RADREIFEN_HA.FELGENBV) FROM VS_TRANSLATIONS br WHERE br.TRANS_GROUP = 1017 and br.TRANS_LANG='DE' AND br.TRANS_KEY = VS_RADREIFEN_HA.BEREIFUNG)) as P6571784,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1004 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_HA.REIFBZV) or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_HA.REIFBZV is null))),VS_RADREIFEN_HA.REIFBZV)) as P6571787,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1023 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_HA.FELGENBH) or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_HA.FELGENBH is null))),VS_RADREIFEN_HA.FELGENBH)) as P6571790,min(VS_RADREIFEN_HA.TRAGF) as P6571793,min(VS_RADREIFEN_HA.TRAGFZ) as P6571796,min(VS_RADREIFEN_SW.RAEDERTYP) as P6571799,min(VS_RADREIFEN_SW.ID) as D6571733,min(VS_RADREIFEN_SW.RADREIFENTYP) as P6571802,min((SELECT TRANS_TEXT || '/' || VS_RADREIFEN_SW.FLANKHOEHE FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1024 AND TRANS_KEY = VS_RADREIFEN_SW.REIFBZH)) as P6571805,min((SELECT tr.TRANS_TEXT || '/' || (SELECT ge.TRANS_TEXT FROM VS_TRANSLATIONS ge WHERE ge.TRANS_GROUP = 1022 AND ge.TRANS_KEY = VS_RADREIFEN_SW.GESCHWIDX) FROM VS_TRANSLATIONS tr WHERE tr.TRANS_GROUP = 1021 AND tr.TRANS_KEY = VS_RADREIFEN_SW.TRAGFIDX)) as P6571808,min((SELECT br.TRANS_TEXT || (SELECT fe.TRANS_TEXT FROM VS_TRANSLATIONS fe WHERE fe.TRANS_GROUP = 1003 AND fe.TRANS_KEY = VS_RADREIFEN_SW.FELGENBV) FROM VS_TRANSLATIONS br WHERE br.TRANS_GROUP = 1017 AND br.TRANS_KEY = VS_RADREIFEN_SW.BEREIFUNG)) as P6571811,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1004 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_SW.REIFBZV) or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_SW.REIFBZV is null))),VS_RADREIFEN_SW.REIFBZV)) as P6571814,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1023 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_SW.FELGENBH) or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_SW.FELGENBH is null))),VS_RADREIFEN_SW.FELGENBH)) as P6571817,min(VS_RADREIFEN_SW.TRAGF) as P6571820,min(VS_RADREIFEN_SW.TRAGFZ) as P6571823,min((case
    when
        VS_FAHRZEUG.EOP < sysdate
    then
        ''
    when
        VS_FAHRZEUG.SOP > sysdate
        or
        VS_FAHRZEUG.SOP is null
    then
        decode(VS_FAHRZEUG.FREIGABE_BRL,null,'highlight_edit_dev','highlight_development')
    when
        VS_FAHRZEUG.FREIGABE_BRL is null
    then
        'highlight_editstate'
end)) as P6572834 from VS_FAHRZEUG,VS_VAG,VS_FZG_MOTOR,VS_MOTOR,VS_FZG_GETRIEBE,VS_ALLRAD,VS_AUFBAULAENGE,VS_HLK,VS_BETRBREMSE VS_BETRBREMSE_VA,VS_ELEKTRIK,VS_RADREIFEN VS_RADREIFEN_HA,VS_KRAFTSTOFF,VS_HAG,VS_LENKVERSTELLUNG,VS_PRITSCHE,VS_RADREIFEN VS_RADREIFEN_VA,VS_HAG VS_HAG_KURZ,VS_HEIZUNG,VS_RADREIFEN VS_RADREIFEN_SW,VS_ACHSE VS_ACHSE_VA,VS_KRAFTSTOFF_SCR_TANK,VS_ACHSE VS_ACHSE_HA,VS_BREMSGERAET,VS_GETRIEBE,VS_KRAFTSTOFF_NGT_TANK,VS_LENKUNG,VS_BETRBREMSE VS_BETRBREMSE_HA,VS_FESTSTELLBREMSE,VS_HAG VS_HAG_LANG,VS_BATTERIE,VS_FUELLUNG,VSDV_BR_DK_BRL_BRM where VS_FAHRZEUG.ID in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and (
VS_FAHRZEUG.ID <> 1
and
(
    1=?
    or
    1=?
    or
    1=?
    or
    1=?
    or
    1=?
    or
    1=?
    or
    (
        (
            1=?
            or
            1=?
        )
        and
        ? in (
            SELECT
                USERNAME
            FROM
                VS_BENUTZER
            WHERE
                DYN_ROLE_RULE in ('VV', 'VA', 'VE')
                or
                (
                    DYN_ROLE_RULE in ('VU', 'VC')
                    and
                    VS_FAHRZEUG.FREIGABE_BRL is not null
                )
            union
            SELECT
                USER_EFF_NAME
            FROM
                V_VSKD_KUNDEN_WORKFLOW
                join VS_FZG_MOTOR on
                    V_VSKD_KUNDEN_WORKFLOW.BAUREIHE_ID = VS_FZG_MOTOR.BAUREIHE_ID
            WHERE
                VS_FZG_MOTOR.ID = VS_FAHRZEUG.FZG_MOTOR_ID
                and
                (
                    DYN_ROLE_RULE in ('VV', 'VA', 'VE')
                    or
                    (
                        DYN_ROLE_RULE in ('VU', 'VC')
                        and
                        VS_FAHRZEUG.FREIGABE_BRL is not null
                    )
                )
                and
                STATUS in (20, 30)
        )
    )
    or
    (
        1=?
        and
        VS_FAHRZEUG.FREIGABE_BRL is not null
        and
        VS_FAHRZEUG.SOP < sysdate
    )
)
 ) and (VS_FZG_MOTOR.ID = VS_FAHRZEUG.FZG_MOTOR_ID
AND
VS_AUFBAULAENGE.ID = VS_FAHRZEUG.AUFBAULAENGE_ID
AND
VS_FZG_GETRIEBE.ID = VS_FAHRZEUG.FZG_GETRIEBE_ID
AND
VS_FZG_MOTOR.MOTOR_ID = VS_MOTOR.ID
AND
VS_FZG_MOTOR.BAUREIHE_ID = VSDV_BR_DK_BRL_BRM.ID) and VS_FAHRZEUG.VAG_ID=VS_VAG.ID and VS_FZG_MOTOR.MOTOR_ID=VS_MOTOR.ID and VS_FAHRZEUG.FZG_GETRIEBE_ID=VS_FZG_GETRIEBE.ID and VS_FAHRZEUG.ALLRAD_ID=VS_ALLRAD.ID and VS_FAHRZEUG.AUFBAULAENGE_ID=VS_AUFBAULAENGE.ID and VS_FAHRZEUG.HLK_ID=VS_HLK.ID and VS_FAHRZEUG.BETRBREMSE_VA_ID=VS_BETRBREMSE_VA.ID and VS_FAHRZEUG.ELEKTRIK_ID=VS_ELEKTRIK.ID and VS_FAHRZEUG.RADREIFEN_HA_ID=VS_RADREIFEN_HA.ID and VS_FAHRZEUG.KRAFTSTOFF_ID=VS_KRAFTSTOFF.ID and VS_FAHRZEUG.HAG_SERIE_ID=VS_HAG.ID and VS_FAHRZEUG.LENKVERSTELLUNG_ID=VS_LENKVERSTELLUNG.ID and VS_FAHRZEUG.PRITSCHE_ID=VS_PRITSCHE.ID and VS_FAHRZEUG.RADREIFEN_VA_ID=VS_RADREIFEN_VA.ID and VS_FAHRZEUG.HAG_KURZ_ID=VS_HAG_KURZ.ID and VS_FAHRZEUG.HEIZUNG_ID=VS_HEIZUNG.ID and VS_FAHRZEUG.FZG_MOTOR_ID=VS_FZG_MOTOR.ID and VS_FAHRZEUG.RADREIFEN_SW_ID=VS_RADREIFEN_SW.ID and VS_FAHRZEUG.ACHSE_VA_ID=VS_ACHSE_VA.ID and VS_FAHRZEUG.SCR_ID=VS_KRAFTSTOFF_SCR_TANK.ID and VS_FAHRZEUG.ACHSE_HA_ID=VS_ACHSE_HA.ID and VS_FAHRZEUG.BREMSGERAET_ID=VS_BREMSGERAET.ID and VS_FZG_GETRIEBE.GETRIEBE_ID=VS_GETRIEBE.ID and VS_FAHRZEUG.NGT_ID=VS_KRAFTSTOFF_NGT_TANK.ID and VS_FAHRZEUG.LENKUNG_ID=VS_LENKUNG.ID and VS_FAHRZEUG.BETRBREMSE_HA_ID=VS_BETRBREMSE_HA.ID and VS_FAHRZEUG.FESTSTELLB_ID=VS_FESTSTELLBREMSE.ID and VS_FAHRZEUG.HAG_LANG_ID=VS_HAG_LANG.ID and VS_FAHRZEUG.BATTERIE_ID=VS_BATTERIE.ID and VS_FAHRZEUG.FUELLUNG_ID=VS_FUELLUNG.ID group by VS_FAHRZEUG.ID) returned net.sf.log4jdbc.PreparedStatementSpy@68172a11

08:49:04.872  INFO [   XXXXXXX] sqlonly,http-bio-8080-exec-18 - select VS_FAHRZEUG.ID as D4843059,min(nvl(VS_FAHRZEUG.BAUMUSTN || '.' || nvl(VS_FAHRZEUG.LENKPOS,'5392L') 
|| '.' || (SELECT m.MOTORCODE FROM VS_MOTOR m join VS_FZG_MOTOR fm on m.ID = fm.MOTOR_ID WHERE 
fm.ID = VS_FAHRZEUG.FZG_MOTOR_ID) || '.' || (SELECT g.GETRTYP FROM VS_GETRIEBE g join VS_FZG_GETRIEBE 
fg on g.ID = fg.GETRIEBE_ID WHERE fg.ID = VS_FAHRZEUG.FZG_GETRIEBE_ID) || '.' || (SELECT nvl(VERTGETRTYPTYP,'4x2') 
FROM VS_ALLRAD WHERE ID = VS_FAHRZEUG.ALLRAD_ID) || '.' || decode(VS_FAHRZEUG.HAG_SERIE_ID,2,'Q11',nvl((SELECT 
TRANS_TEXT FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1020 AND TRANS_LANG = 'DE' AND TRANS_KEY 
= (SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)),(SELECT HAGTYP FROM VS_HAG 
WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID))) || '.' || VS_FAHRZEUG.TONNAGE*100 || '.' || nvl(VS_FAHRZEUG.DACHFORM,'LH5361') 
|| '.' || nvl(VS_FAHRZEUG.ZULASSUNG, '00') || '.' || nvl(VS_FAHRZEUG.ABGASSTUFE, '000') || 
'.' || nvl(VS_FAHRZEUG.BLUEEFF, 'MX-') || '.' || nvl(VS_FAHRZEUG.AUFBAU, '000') || '.' || to_char(nvl(VS_FAHRZEUG.BASISFZGNUM,0),'00') 
|| '.' || to_char(nvl(VS_FAHRZEUG.SAFZGNUM,0),'00'),VS_FAHRZEUG.ID)) as DN4843059,min(nvl(VS_FAHRZEUG.BAUMUSTN 
|| '.' || nvl(VS_FAHRZEUG.LENKPOS,'5359L') || '.' || (SELECT m.MOTORCODE FROM VS_MOTOR m join 
VS_FZG_MOTOR fm on m.ID = fm.MOTOR_ID WHERE fm.ID = VS_FAHRZEUG.FZG_MOTOR_ID) || '.' || (SELECT 
g.GETRTYP FROM VS_GETRIEBE g join VS_FZG_GETRIEBE fg on g.ID = fg.GETRIEBE_ID WHERE fg.ID = 
VS_FAHRZEUG.FZG_GETRIEBE_ID) || '.' || (SELECT nvl(VERTGETRTYPTYP,'4x2') FROM VS_ALLRAD WHERE 
ID = VS_FAHRZEUG.ALLRAD_ID) || '.' || decode(VS_FAHRZEUG.HAG_SERIE_ID,2,'Q11',nvl((SELECT TRANS_TEXT 
FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1020 AND TRANS_LANG = 'DE' AND TRANS_KEY = (SELECT 
HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)),(SELECT HAGTYP FROM VS_HAG WHERE ID 
= VS_FAHRZEUG.HAG_SERIE_ID))) || '.' || VS_FAHRZEUG.TONNAGE*100 || '.' || nvl(VS_FAHRZEUG.DACHFORM,'LH5491') 
|| '.' || nvl(VS_FAHRZEUG.ZULASSUNG, '00') || '.' || nvl(VS_FAHRZEUG.ABGASSTUFE, '000') || 
'.' || nvl(VS_FAHRZEUG.BLUEEFF, 'MX-') || '.' || nvl(VS_FAHRZEUG.AUFBAU, '000') || '.' || to_char(nvl(VS_FAHRZEUG.BASISFZGNUM,0),'00') 
|| '.' || to_char(nvl(VS_FAHRZEUG.SAFZGNUM,0),'00'),VS_FAHRZEUG.ID)) as DL4843059,min(VS_FAHRZEUG.ID) 
as P7382905,min(nvl(VS_FAHRZEUG.BAUMUSTN || '.' || nvl(VS_FAHRZEUG.LENKPOS,'5486L') || '.' 
|| (SELECT m.MOTORCODE FROM VS_MOTOR m join VS_FZG_MOTOR fm on m.ID = fm.MOTOR_ID WHERE fm.ID 
= VS_FAHRZEUG.FZG_MOTOR_ID) || '.' || (SELECT g.GETRTYP FROM VS_GETRIEBE g join VS_FZG_GETRIEBE 
fg on g.ID = fg.GETRIEBE_ID WHERE fg.ID = VS_FAHRZEUG.FZG_GETRIEBE_ID) || '.' || (SELECT nvl(VERTGETRTYPTYP,'4x2') 
FROM VS_ALLRAD WHERE ID = VS_FAHRZEUG.ALLRAD_ID) || '.' || decode(VS_FAHRZEUG.HAG_SERIE_ID,2,'Q11',nvl((SELECT 
TRANS_TEXT FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1020 AND TRANS_LANG = 'DE' AND TRANS_KEY 
= (SELECT HAGTYP FROM VS_HAG WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID)),(SELECT HAGTYP FROM VS_HAG 
WHERE ID = VS_FAHRZEUG.HAG_SERIE_ID))) || '.' || VS_FAHRZEUG.TONNAGE*100 || '.' || nvl(VS_FAHRZEUG.DACHFORM,'LH5405') 
|| '.' || nvl(VS_FAHRZEUG.ZULASSUNG, '00') || '.' || nvl(VS_FAHRZEUG.ABGASSTUFE, '000') || 
'.' || nvl(VS_FAHRZEUG.BLUEEFF, 'MX-') || '.' || nvl(VS_FAHRZEUG.AUFBAU, '000') || '.' || to_char(nvl(VS_FAHRZEUG.BASISFZGNUM,0),'00') 
|| '.' || to_char(nvl(VS_FAHRZEUG.SAFZGNUM,0),'00'),VS_FAHRZEUG.ID)) as P4844136,min(VS_RADREIFEN_VA.RAEDERTYP) 
as P6571745,min(VS_RADREIFEN_VA.ID) as D6571737,min(VS_RADREIFEN_VA.RADREIFENTYP) as P6571748,min((SELECT 
TRANS_TEXT || '/' || VS_RADREIFEN_VA.FLANKHOEHE FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1024 
AND TRANS_KEY = VS_RADREIFEN_VA.REIFBZH)) as P6571751,min((SELECT tr.TRANS_TEXT || '/' || (SELECT 
ge.TRANS_TEXT FROM VS_TRANSLATIONS ge WHERE ge.TRANS_GROUP = 1022 AND ge.TRANS_KEY = VS_RADREIFEN_VA.GESCHWIDX) 
FROM VS_TRANSLATIONS tr WHERE tr.TRANS_GROUP = 1021 AND tr.TRANS_KEY = VS_RADREIFEN_VA.TRAGFIDX)) 
as P6571754,min((SELECT br.TRANS_TEXT || (SELECT fe.TRANS_TEXT FROM VS_TRANSLATIONS fe WHERE 
fe.TRANS_GROUP = 1003 AND fe.TRANS_KEY = VS_RADREIFEN_VA.FELGENBV) FROM VS_TRANSLATIONS br 
WHERE br.TRANS_GROUP = 1017 AND br.TRANS_KEY = VS_RADREIFEN_VA.BEREIFUNG)) as P6571757,min(nvl((select 
/*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1004 and TRANS_LANG 
= 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_VA.REIFBZV) or (VS_TRANSLATIONS.TRANS_KEY 
is null and VS_RADREIFEN_VA.REIFBZV is null))),VS_RADREIFEN_VA.REIFBZV)) as P6571760,min(nvl((select 
/*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1023 and TRANS_LANG 
= 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_VA.FELGENBH) or (VS_TRANSLATIONS.TRANS_KEY 
is null and VS_RADREIFEN_VA.FELGENBH is null))),VS_RADREIFEN_VA.FELGENBH)) as P6571763,min(VS_RADREIFEN_VA.TRAGF) 
as P6571766,min(VS_RADREIFEN_VA.TRAGFZ) as P6571769,min(VS_RADREIFEN_HA.RAEDERTYP) as P6571772,min(VS_RADREIFEN_HA.ID) 
as D6571741,min(VS_RADREIFEN_HA.RADREIFENTYP) as P6571775,min((SELECT TRANS_TEXT || '/' || 
VS_RADREIFEN_HA.FLANKHOEHE FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1024 AND TRANS_KEY = VS_RADREIFEN_HA.REIFBZH)) 
as P6571778,min((SELECT tr.TRANS_TEXT || '/' || (SELECT ge.TRANS_TEXT FROM VS_TRANSLATIONS 
ge WHERE ge.TRANS_GROUP = 1022 AND ge.TRANS_KEY = VS_RADREIFEN_HA.GESCHWIDX) FROM VS_TRANSLATIONS 
tr WHERE tr.TRANS_GROUP = 1021 AND tr.TRANS_KEY = VS_RADREIFEN_HA.TRAGFIDX)) as P6571781,min((SELECT 
br.TRANS_TEXT || (SELECT fe.TRANS_TEXT FROM VS_TRANSLATIONS fe WHERE fe.TRANS_GROUP = 1003 
and fe.TRANS_LANG='DE' AND fe.TRANS_KEY = VS_RADREIFEN_HA.FELGENBV) FROM VS_TRANSLATIONS br 
WHERE br.TRANS_GROUP = 1017 and br.TRANS_LANG='DE' AND br.TRANS_KEY = VS_RADREIFEN_HA.BEREIFUNG)) 
as P6571784,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where 
TRANS_GROUP = 1004 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_HA.REIFBZV) 
or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_HA.REIFBZV is null))),VS_RADREIFEN_HA.REIFBZV)) 
as P6571787,min(nvl((select /*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where 
TRANS_GROUP = 1023 and TRANS_LANG = 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_HA.FELGENBH) 
or (VS_TRANSLATIONS.TRANS_KEY is null and VS_RADREIFEN_HA.FELGENBH is null))),VS_RADREIFEN_HA.FELGENBH)) 
as P6571790,min(VS_RADREIFEN_HA.TRAGF) as P6571793,min(VS_RADREIFEN_HA.TRAGFZ) as P6571796,min(VS_RADREIFEN_SW.RAEDERTYP) 
as P6571799,min(VS_RADREIFEN_SW.ID) as D6571733,min(VS_RADREIFEN_SW.RADREIFENTYP) as P6571802,min((SELECT 
TRANS_TEXT || '/' || VS_RADREIFEN_SW.FLANKHOEHE FROM VS_TRANSLATIONS WHERE TRANS_GROUP = 1024 
AND TRANS_KEY = VS_RADREIFEN_SW.REIFBZH)) as P6571805,min((SELECT tr.TRANS_TEXT || '/' || (SELECT 
ge.TRANS_TEXT FROM VS_TRANSLATIONS ge WHERE ge.TRANS_GROUP = 1022 AND ge.TRANS_KEY = VS_RADREIFEN_SW.GESCHWIDX) 
FROM VS_TRANSLATIONS tr WHERE tr.TRANS_GROUP = 1021 AND tr.TRANS_KEY = VS_RADREIFEN_SW.TRAGFIDX)) 
as P6571808,min((SELECT br.TRANS_TEXT || (SELECT fe.TRANS_TEXT FROM VS_TRANSLATIONS fe WHERE 
fe.TRANS_GROUP = 1003 AND fe.TRANS_KEY = VS_RADREIFEN_SW.FELGENBV) FROM VS_TRANSLATIONS br 
WHERE br.TRANS_GROUP = 1017 AND br.TRANS_KEY = VS_RADREIFEN_SW.BEREIFUNG)) as P6571811,min(nvl((select 
/*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1004 and TRANS_LANG 
= 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_SW.REIFBZV) or (VS_TRANSLATIONS.TRANS_KEY 
is null and VS_RADREIFEN_SW.REIFBZV is null))),VS_RADREIFEN_SW.REIFBZV)) as P6571814,min(nvl((select 
/*+ ordered_predicates */(TRANS_TEXT) from VS_TRANSLATIONS where TRANS_GROUP = 1023 and TRANS_LANG 
= 'DE' and ((VS_TRANSLATIONS.TRANS_KEY=VS_RADREIFEN_SW.FELGENBH) or (VS_TRANSLATIONS.TRANS_KEY 
is null and VS_RADREIFEN_SW.FELGENBH is null))),VS_RADREIFEN_SW.FELGENBH)) as P6571817,min(VS_RADREIFEN_SW.TRAGF) 
as P6571820,min(VS_RADREIFEN_SW.TRAGFZ) as P6571823,min((case when VS_FAHRZEUG.EOP < sysdate 
then '' when VS_FAHRZEUG.SOP > sysdate or VS_FAHRZEUG.SOP is null then decode(VS_FAHRZEUG.FREIGABE_BRL,null,'highlight_edit_dev','highlight_development') 
when VS_FAHRZEUG.FREIGABE_BRL is null then 'highlight_editstate' end)) as P6572834 from VS_FAHRZEUG,VS_VAG,VS_FZG_MOTOR,VS_MOTOR,VS_FZG_GETRIEBE,VS_ALLRAD,VS_AUFBAULAENGE,VS_HLK,VS_BETRBREMSE 
VS_BETRBREMSE_VA,VS_ELEKTRIK,VS_RADREIFEN VS_RADREIFEN_HA,VS_KRAFTSTOFF,VS_HAG,VS_LENKVERSTELLUNG,VS_PRITSCHE,VS_RADREIFEN 
VS_RADREIFEN_VA,VS_HAG VS_HAG_KURZ,VS_HEIZUNG,VS_RADREIFEN VS_RADREIFEN_SW,VS_ACHSE VS_ACHSE_VA,VS_KRAFTSTOFF_SCR_TANK,VS_ACHSE 
VS_ACHSE_HA,VS_BREMSGERAET,VS_GETRIEBE,VS_KRAFTSTOFF_NGT_TANK,VS_LENKUNG,VS_BETRBREMSE VS_BETRBREMSE_HA,VS_FESTSTELLBREMSE,VS_HAG 
VS_HAG_LANG,VS_BATTERIE,VS_FUELLUNG,VSDV_BR_DK_BRL_BRM where VS_FAHRZEUG.ID in (5367,5368,5402,5154,5144,5156,5322,5343,5158,1,0,0,1,0,1) 
and ( VS_FAHRZEUG.ID <> 1 and ( 1=0 or 1=1 or 1='XXXXXXX' or 1=1 or 1=? or 1=? or ( ( 1=? or 
1=? ) and ? in ( SELECT USERNAME FROM VS_BENUTZER WHERE DYN_ROLE_RULE in ('VV', 'VA', 'VE') 
or ( DYN_ROLE_RULE in ('VU', 'VC') and VS_FAHRZEUG.FREIGABE_BRL is not null ) union SELECT 
USER_EFF_NAME FROM V_VSKD_KUNDEN_WORKFLOW join VS_FZG_MOTOR on V_VSKD_KUNDEN_WORKFLOW.BAUREIHE_ID 
= VS_FZG_MOTOR.BAUREIHE_ID WHERE VS_FZG_MOTOR.ID = VS_FAHRZEUG.FZG_MOTOR_ID and ( DYN_ROLE_RULE 
in ('VV', 'VA', 'VE') or ( DYN_ROLE_RULE in ('VU', 'VC') and VS_FAHRZEUG.FREIGABE_BRL is not 
null ) ) and STATUS in (20, 30) ) ) or ( 1=? and VS_FAHRZEUG.FREIGABE_BRL is not null and VS_FAHRZEUG.SOP 
< sysdate ) ) ) and (VS_FZG_MOTOR.ID = VS_FAHRZEUG.FZG_MOTOR_ID AND VS_AUFBAULAENGE.ID = VS_FAHRZEUG.AUFBAULAENGE_ID 
AND VS_FZG_GETRIEBE.ID = VS_FAHRZEUG.FZG_GETRIEBE_ID AND VS_FZG_MOTOR.MOTOR_ID = VS_MOTOR.ID 
AND VS_FZG_MOTOR.BAUREIHE_ID = VSDV_BR_DK_BRL_BRM.ID) and VS_FAHRZEUG.VAG_ID=VS_VAG.ID and 
VS_FZG_MOTOR.MOTOR_ID=VS_MOTOR.ID and VS_FAHRZEUG.FZG_GETRIEBE_ID=VS_FZG_GETRIEBE.ID and VS_FAHRZEUG.ALLRAD_ID=VS_ALLRAD.ID 
and VS_FAHRZEUG.AUFBAULAENGE_ID=VS_AUFBAULAENGE.ID and VS_FAHRZEUG.HLK_ID=VS_HLK.ID and VS_FAHRZEUG.BETRBREMSE_VA_ID=VS_BETRBREMSE_VA.ID 
and VS_FAHRZEUG.ELEKTRIK_ID=VS_ELEKTRIK.ID and VS_FAHRZEUG.RADREIFEN_HA_ID=VS_RADREIFEN_HA.ID 
and VS_FAHRZEUG.KRAFTSTOFF_ID=VS_KRAFTSTOFF.ID and VS_FAHRZEUG.HAG_SERIE_ID=VS_HAG.ID and VS_FAHRZEUG.LENKVERSTELLUNG_ID=VS_LENKVERSTELLUNG.ID 
and VS_FAHRZEUG.PRITSCHE_ID=VS_PRITSCHE.ID and VS_FAHRZEUG.RADREIFEN_VA_ID=VS_RADREIFEN_VA.ID 
and VS_FAHRZEUG.HAG_KURZ_ID=VS_HAG_KURZ.ID and VS_FAHRZEUG.HEIZUNG_ID=VS_HEIZUNG.ID and VS_FAHRZEUG.FZG_MOTOR_ID=VS_FZG_MOTOR.ID 
and VS_FAHRZEUG.RADREIFEN_SW_ID=VS_RADREIFEN_SW.ID and VS_FAHRZEUG.ACHSE_VA_ID=VS_ACHSE_VA.ID 
and VS_FAHRZEUG.SCR_ID=VS_KRAFTSTOFF_SCR_TANK.ID and VS_FAHRZEUG.ACHSE_HA_ID=VS_ACHSE_HA.ID 
and VS_FAHRZEUG.BREMSGERAET_ID=VS_BREMSGERAET.ID and VS_FZG_GETRIEBE.GETRIEBE_ID=VS_GETRIEBE.ID 
and VS_FAHRZEUG.NGT_ID=VS_KRAFTSTOFF_NGT_TANK.ID and VS_FAHRZEUG.LENKUNG_ID=VS_LENKUNG.ID and 
VS_FAHRZEUG.BETRBREMSE_HA_ID=VS_BETRBREMSE_HA.ID and VS_FAHRZEUG.FESTSTELLB_ID=VS_FESTSTELLBREMSE.ID 
and VS_FAHRZEUG.HAG_LANG_ID=VS_HAG_LANG.ID and VS_FAHRZEUG.BATTERIE_ID=VS_BATTERIE.ID and VS_FAHRZEUG.FUELLUNG_ID=VS_FUELLUNG.ID 
group by VS_FAHRZEUG.ID
arthurblake commented 9 years ago

The algorithm that log4jdbc uses simply finds question marks in the SQL in order and replaces them according to the bind parameter index. It really is not perfect at all and can break down sometimes as you found. I see early in your SQL you have question marks embedded in strings. That's probably what is throwing it off. Unfortunately there is not a good solution to the problem without a complete parsing of the SQL which is beyond the scope of what log4jdbc can do at this time. One way you might be able to work around the problem is to modify your SQL by using escape sequences to specify the question marks embedded in the strings instead of using actual question marks. There may be a simple JDBC escape sequence that could be used too.

alsha commented 9 years ago

Yes, you are right - question marks in string literals are the reason for this misbehaviour. I understand, that fixing that is not a trivial task, I will try to work around it using your suggestion. Thank you!