ORA-01461 Error on Oracle 9I on a Sun Solaris

[bkmckenzie]

I have a table with two CLOB fields. The application runs fine on most of my clients Oracle databases, both 8I and 9I.

However I have once Client That has Oracle 9I on Solaris that does not work and when the insert to the tables is processed I get the following Error:

ORA-01461 Can bind a long value only for insert to a long column.

I downloaded the 3.4.6.4 to see if this clears it up With no success.

The Code for the insert is below:

{PREP}

procedure Tdrc_TxnMgrDAL_DOAORA.InitTxnInsert;
const
cVarDecl: array[0..23] of Tdrc_DeclareRec = (
(VariableName: 'TransID'; VariableType: otInteger),
(VariableName: 'DetailID'; VariableType: otInteger),
(VariableName: 'dTime'; VariableType: otDate),
(VariableName: 'SessionID'; VariableType: otInteger),
(VariableName: 'UserName'; VariableType: otString),
(VariableName: 'Action'; VariableType: otString),
(VariableName: 'ActionLevel'; VariableType: otString),
(VariableName: 'Version'; VariableType: otString),
(VariableName: 'VersionID'; VariableType: otInteger),
(VariableName: 'Hier'; VariableType: otString),
(VariableName: 'HierID'; VariableType: otInteger),
(VariableName: 'NodeAbbrev'; VariableType: otString),
(VariableName: 'NodeID'; VariableType: otInteger),
(VariableName: 'NodeDescr'; VariableType: otString),
(VariableName: 'PropAbbrev'; VariableType: otString),
(VariableName: 'PropID'; VariableType: otInteger),
(VariableName: 'LeafFlag'; VariableType: otInteger),
(VariableName: 'Descr'; VariableType: otString),
(VariableName: 'OriginatingID'; VariableType: otInteger),
(VariableName: 'OriginatingDetailID'; VariableType: otInteger),
(VariableName: 'FromOrigin'; VariableType: otString),
(VariableName: 'ToOrigin'; VariableType: otString),
(VariableName: 'FromValue'; VariableType: otCLOB),
(VariableName: 'ToValue'; VariableType: otCLOB)
);
var
sSQL: string;
inCounter: integer;
begin
if FTxnInsert nil then Exit;
FTxnInsert := TOracleQuery.Create(nil);
sSQL :=
'insert into \\transaction_history (' +
'i_transaction_id, i_detail_id, d_timestamp, i_session_id, c_username, '+
'c_action, c_action_level, c_version_abbrev, i_version_id, c_hierarchy_abbrev, '+
'i_hierarchy_id, c_node_abbrev, i_node_id, c_node_descr, c_property_abbrev, '+
'i_property_id, b_leaf_flag, c_action_description, c_from_value, c_to_value, '+
'i_originating_id, i_originating_detail_id, c_from_origin, c_trigin) '+
'values TransID, etailID, :dTime, :SessionID, :UserName, :Action, :ActionLevel, ' +
':Version, :VersionID, :Hier, :HierID, :NodeAbbrev, :NodeID, :NodeDescr, ropAbbrev, ' +
'ropID, :LeafFlag, escr, empty_clob(), empty_clob(), :OriginatingID, :OriginatingDetailID, ' +
':FromOrigin, :ToOrigin) ' +
'returning c_from_value, c_to_value into :FromValue, :ToValue';
InitQry(FTxnInsert, sSQL);
for inCounter := 0 to 23 do
FTxnInsert.DeclareVariable(cVarDecl[inCounter].VariableName, cVarDecl[inCounter].VariableType);
FFromValue := TLOBLocator.Create(FSession, otCLOB);
FToValue := TLOBLocator.Create(FSession, otCLOB);
FTxnInsert.SetComplexVariable('FromValue', FFromValue);
FTxnInsert.SetComplexVariable('ToValue', FToValue);
end;

{PROCESS}
procedure Tdrc_TxnMgrDAL_DOAORA.InsertTxn(const ATxnRec: Tdrc_DALTxnRec);
var
inTryCount: integer;
blSuccess: boolean;
iTransID: integer;
begin
if FDBDisconnect then Exit;
InitTxnInsert;
iTransID := ATxnRec.TxnID;
FTxnInsert.SetVariable('TransID', iTransID);
FTxnInsert.SetVariable('DetailID', ATxnRec.DetailID);
FTxnInsert.SetVariable('dTime', StrToDateTime(ATxnRec.TxnDate));
FTxnInsert.SetVariable('SessionID', ATxnRec.SessionID);
FTxnInsert.SetVariable('UserName', ATxnRec.UserName);
FTxnInsert.SetVariable('Action', ATxnRec.Action);
FTxnInsert.SetVariable('ActionLevel', ATxnRec.ActionLevel);
FTxnInsert.SetVariable('Version', ATxnRec.Version);
FTxnInsert.SetVariable('VersionID', ATxnRec.VersionID);
FTxnInsert.SetVariable('Hier', ATxnRec.Hier);
FTxnInsert.SetVariable('HierID', ATxnRec.HierID);
FTxnInsert.SetVariable('NodeAbbrev', ATxnRec.Node);
FTxnInsert.SetVariable('NodeID', ATxnRec.NodeID);
FTxnInsert.SetVariable('NodeDescr', ATxnRec.NodeDescr);
FTxnInsert.SetVariable('PropAbbrev', ATxnRec.PropAbbrev);
FTxnInsert.SetVariable('PropID', ATxnRec.PropID);
FTxnInsert.SetVariable('LeafFlag', Ord(ATxnRec.LeafFlag));
FTxnInsert.SetVariable('Descr', ATxnRec.Descr);
FTxnInsert.SetVariable('OriginatingID', ATxnRec.OriginatingID);
FTxnInsert.SetVariable('OriginatingDetailID', ATxnRec.OriginatingDetailID);
FTxnInsert.SetVariable('FromOrigin', ATxnRec.FromOrigin);
FTxnInsert.SetVariable('ToOrigin', ATxnRec.ToOrigin);
inTryCount := 1;
blSuccess := false;
repeat
try
FTxnInsert.Execute;
blSuccess := true;
except
Inc(inTryCount);
if inTryCount > 10 then
raise;
end;
until blSuccess;
FFromValue.AsString := ATxnRec.FromValue;
FToValue.AsString := ATxnRec.ToValue;
end;

[This message has been edited by bkmckenzie (edited 04 August 2003).]

[This message has been edited by bkmckenzie (edited 04 August 2003).]

 

[Marco Kalter]

If this is a multibyte character set database, then it may be necessary to set TOracleSession.BytesPerCharacter to bcAutoDetect.

------------------
Marco Kalter
Allround Automations

 

[bkmckenzie]

It is a Multibyte. I finally got ahold of the DBA and they are using UTF8.

I have modified the code to use auto detect and will have them test it. I will post the results here soon.

Is there any performance issue with using the Autodetect since most of my clients use single byte character sets>

Thanks

 

[bkmckenzie]

The Auto Detect setting worked well.

Thanks