Hi,
I've got a mysterious problem with Oracle-DBD (v. 1.12) and DBI 1.32 against an Oracle 8.1.7.3 database.
An execute fails with ORA-904 ("invalid column name") although the column names are correct and the prepare (it's an INSERT statement) succeeded!
=============================================================
1. the error:
=============================================================
DBD::Oracle::st execute failed: ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute) at ... line 2542.
=============================================================
2. line 2542 and predecessors (the binding):
=============================================================
$sth_itmarkt_i_log->bind_param(1,$einzelne_datei);
$sth_itmarkt_i_log->bind_param(2,$import_nummer);
$sth_itmarkt_i_log->bind_param(3,$h_kunde{'Bestellzeichen'} ne ''?$h_kunde{'Bestellzeichen'}:undef);
$sth_itmarkt_i_log->bind_param(4,$h_zusatzinfo{'Bestell-ID'} ne ''?$h_zusatzinfo{'Bestell-ID'}:undef);
$sth_itmarkt_i_log->bind_param(5,$h_zusatzinfo{'Bestelldatum'} ne ''?$h_zusatzinfo{'Bestelldatum'}:undef);
$sth_itmarkt_i_log->bind_param(6,$todo_kunde_updaten eq '1'?'i':(($todo_auftrag_kunde_anlegen eq '1')||($todo_
nur_kunde_anlegen eq '1'))?'a':'0');
$sth_itmarkt_i_log->bind_param(7,(($todo_auftrag_kunde_anlegen eq '1')||($todo_nur_auftrag_anlegen eq '1'))?'1':'0');
$sth_itmarkt_i_log->bind_param(8,$h_kunde{'T3_Kundennummer'} ne ''?$h_kunde{'T3_Kundennummer'}:undef);
$sth_itmarkt_i_log->bind_param(9,$kundennummer ne ''?$kundennummer:undef);
$sth_itmarkt_i_log->bind_param(10,$h_kunde{'Kundenkennung'} ne ''?$h_kunde{'Kundenkennung'}:undef); $sth_itmarkt_i_log->bind_param(11,(defined($h_kunde{'Bemerkung_Kunde'}))?$h_kunde{'Bemerkung_Kunde'}:undef); $sth_itmarkt_i_log->bind_param(12,(defined($h_kunde{'Bemerkung'}))?$h_kunde{'Bemerkung'}:undef); $sth_itmarkt_i_log->bind_param(13,undef);
$sth_itmarkt_i_log->bind_param(14,undef);
$sth_itmarkt_i_log->bind_param(15,undef);
$sth_itmarkt_i_log->bind_param(16,undef);
$sth_itmarkt_i_log->bind_param(17,undef);
$sth_itmarkt_i_log->execute || die "$DBI::errstr\n";
============================================================= 3. the prepare (where I'd expected the error to happen if at all) ============================================================= $stm_itmarkt_i_log=<<EOINSERT_LOG INSERT INTO VAS.ITMARKT_IMPORT_LOG (DATUM_IMPORT, PGP_DATEI, IMPORT_NUMMER, BESTELLZEICHEN, BESTELL_ID, BESTELL_ID_DATUM, KUNDENANLAGE, AUFTRAGSANLAGE, T3_KUNDENNUMMER, KUNDENNUMMER, KUNDENKENNUNG, BEMERKUNG_KUNDE, BEMERKUNG, artikel, menge, preiscode, abrechnungsrhythmus, t3_preis ) VALUES (sysdate, ?, ?, ?, ?, ?, ?, ?, ?,?, ?,?,?, ?,?,?,?,? ) EOINSERT_LOG ; #--- $sth_itmarkt_i_log = $dbh->prepare(qq[$stm_itmarkt_i_log]);
=============================================================
4. the trace (level 4) output:
=============================================================
---------------------------------------
a) prepare
---------------------------------------
-> prepare for DBD::Oracle::db (DBI::db=HASH(0x8436db4)~0x840cb20 'INSERT INTO VAS.ITMARKT_IMPORT_L
OG
(DATUM_IMPORT, PGP_DATEI, IMPORT_NUMMER,
BESTELLZEICHEN, BESTELL_ID, BESTELL_ID_DATUM,
KUNDENANLAGE,
AUFTRAGSANLAGE,
T3_KUNDENNUMMER, KUNDENNUMMER,
KUNDENKENNUNG, BEMERKUNG_KUNDE, BEMERKUNG,
artikel, menge, preiscode, abrechnungsrhythmus, t3_preis
)
VALUES
(sysdate, ?, ?,
?, ?, ?,
?,
?,
?,?,
?,?,?,
?,?,?,?,?
)
')
New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x840cb20), id=)
dbih_setup_handle(DBI::st=HASH(0x818e210)=>DBI::st=HASH(0x847aaa8), DBD::Oracle::st, 818e228, Null!
)
dbih_make_com(DBI::db=HASH(0x840cb20), DBD::Oracle::st, 208) thr#(nil)
dbd_preparse scanned 17 distinct placeholders
dbd_st_prepare'd sql INSERT
dbd_describe skipped for INSERT
<- prepare= DBI::st=HASH(0x818e210) at Web2VASTEST line 846 via /opt/local/oracle/bin/Web2VASTEST l
ine 4563
---------------------------------------
b) error
---------------------------------------
...
-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x818e210)~0x847aaa8 9 ' T_Kd-Nr')
bind :p9 <== ' T_Kd-Nr' (type 0)
bind :p9 <== ' T_Kd-Nr' (size 8/9/0, ptype 4, otype 1)
bind :p9 <== ' T_Kd-Nr' (size 8/8, otype 1, indp 0, at_exec 1)
bind :p9 done with ftype 1
<- bind_param= 1 at Web2VASTEST line 2533 via /opt/local/oracle/bin/Web2VASTEST line 4594
...
-> execute for DBD::Oracle::st (DBI::st=HASH(0x818e210)~0x847aaa8)
dbd_st_execute INSERT (out0, lob0)...
in ':p1' [0,0]: len 24, ind 0
in ':p4' [0,0]: len 10, ind 0
in ':p5' [0,0]: len 8, ind 0
in ':p6' [0,0]: len 1, ind 0
in ':p7' [0,0]: len 1, ind 0
in ':p8' [0,0]: len 10, ind 0
in ':p9' [0,0]: len 8, ind 0
OCIErrorGet after OCIStmtExecute (er1:ok): -1, 904: ORA-00904: invalid column name
!! ERROR: 904 'ORA-00904: invalid column name (DBD ERROR: OCIStmtExecute)'
<- execute= undef at Web2VASTEST line 2542 via /opt/local/oracle/bin/Web2VASTEST line 4594
-> rollback for DBD::Oracle::db (DBI::db=HASH(0x8436db4)~0x840cb20)
<- rollback= 1 at Web2VASTEST line 4601
-> disconnect for DBD::Oracle::db (DBI::db=HASH(0x8436db4)~0x840cb20)
<- disconnect= 1 at Web2VASTEST line 4703
-- DBI::END
============================================================= 5. the table description: ============================================================= SQL> desc itmarkt_import_log Name Null? Typ ----------------------------------------- -------- ------------------- DATUM_IMPORT DATE IMPORT_NUMMER NUMBER BESTELLZEICHEN VARCHAR2(20) BESTELL_ID VARCHAR2(32) BESTELL_ID_DATUM DATE T3_KUNDENNUMMER VARCHAR2(16) KUNDENNUMMER VARCHAR2(8) KUNDENKENNUNG CHAR(2) BEMERKUNG_KUNDE VARCHAR2(2000) KUNDENANLAGE CHAR(1) AUFTRAGSANLAGE CHAR(1) BEMERKUNG VARCHAR2(2000) ARTIKELNUMMER VARCHAR2(13) MENGE NUMBER PREISCODE CHAR(1) ABRECHNUNGSRHYTHMUS CHAR(1) T3_PREIS NUMBER(8,2) PGP_DATEI VARCHAR2(32)
So I'm at a loss where to look further / what to do next. Someone with a hint for me, please?
Regards, Frank.
-- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH & Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538
