Hello Marco,
>>Sorry . I have placed this at the top of our >>list.
3 months is a long time for beeing on the top of the list.
maybe you could really look into this. i have written a short test programm. it creates a table with an varchar2(4000 char) field.
1) it inserts a record containing 1000 chars with bind variables -> sucess
2) it updates this field (now containing 2000 chars) -> success
3) it inserts another record containing 2000 chars without bind variables -> sucess
4) it tries to insert another record containing 2000 chars with bind variables -> error ora-01480
i really hope to get an answer / solution soon
Walter
We are using Oracle 9i Enterprise Server Version 9.2.0.4, Client 9.2.0.6.
DB Character Set: AL32UTF8
Client Setting:
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]
"ID"="0"
"ORACLE_GROUP_NAME"="Oracle - OraHome92"
"ORACLE_HOME_NAME"="OraHome92"
"ORACLE_HOME"="C:\\Programme\\OraHome92"
"NLS_LANG"="GERMAN_AUSTRIA.WE8MSWIN1252"
"NLS_NUMERIC_CHARACTERS"=".,"
"NLS_DATE_FORMAT"="YYYY-MM-DD HH24:MI:SS"
"NLS_TIME_FORMAT"="HH24:MI:SSXFF"
"NLS_TIMESTAMP_FORMAT"="YYYY-MM-DD HH24:MI:SSXFF"
"ORACLE_BUNDLE_NAME"="Enterprise"
"ORAMTS_CP_TRACE_LEVEL"="0"
"ORAMTS_CP_TRACE_DIR"="C:\\Programme\\OraHome92\\oramts\\Trace"
"ORAMTS_CONN_POOL_TIMEOUT"="120"
"ORAMTS_SESS_TXNTIMETOLIVE"="120"
"ORAMTS_NET_CACHE_MAXFREE"="5"
"ORAMTS_NET_CACHE_TIMEOUT"="120000"
"ORAMTS_OSCREDS_MATCH_LEVEL"="OS_AUTH_LOGIN"
procedure TestInsert(Sender: TObject);
var
Qry : TOracleQuery;
Db : TOracleSession;
begin
Db := TOracleSession.Create(nil);
Db.LogonUsername:='YOUR_USER';
Db.LogonPassword:='YOUR_PASSWORD';
Db.LogonDatabase:='YOUR_DATABASE';
Db.BytesPerCharacter := bcAutoDetect;
try
Db.LogOn;
Qry := TOracleQuery.Create(nil);
Qry.Session := Db;
Qry.SQL.Text := 'CREATE TABLE DOABUG(NR INTEGER NOT NULL,UNI4000 VARCHAR2(4000 CHAR) NULL)';
Qry.Execute;
try
// insert a 1000 varchar2 into uni4000 with bind variable
Qry.SQL.Text := 'INSERT INTO DOABUG (NR, UNI4000) VALUES(:NR, :UNI4000) ';
Qry.DeclareAndSet('NR',otInteger,1);
Qry.DeclareAndSet('UNI4000',otString,DupeString('A',1000));
Qry.Execute; //success
Qry.DeleteVariables;
// Update uni4000 field (now contains 2000 varchar2)
Qry.SQL.Text := 'UPDATE DOABUG SET UNI4000=UNI4000||UNI4000 WHERE NR=1';
Qry.Execute; //success
// insert a 2000 varchar2 into uni4000 without bind variable
Qry.SQL.Text := 'INSERT INTO DOABUG (NR, UNI4000) '+
' VALUES(:NR, '''+DupeString('A',2000)+ ''') ';
Qry.DeclareAndSet('NR',otInteger,2);
Qry.Execute; //success
// try to insert 2000 varchar2 into uni4000 with bind variable
Qry.SQL.Text := 'INSERT INTO DOABUG (NR, UNI4000) VALUES(:NR, :UNI4000) ';
Qry.DeclareAndSet('NR',otInteger,3);
Qry.DeclareAndSet('UNI4000',otString,DupeString('A',2000));
Qry.Execute; // fail -> error ora-01480
finally
Qry.DeleteVariables;
Qry.SQL.Text :='DROP TABLE DOABUG';
Qry.Execute;
end;
finally
Qry.Free;
Db.LogOff;
end;
end;