INSERT CLOB = ORA-01036: illegal variable name/number

[-=David=-]

Hi All,

I am trying to INSERT a record into a table that contains two CLOBs.

The TOracleQuery contains :-

BEGIN
INSERT INTO

(
,
...

)
VALUES
(
:V_COL1,
...
:V_COL84
)
RETURNING ,

INTO :V_COL75,
:V_COL76;
END;

In code I then do this :-

LOB1 := TLOBLocator.Create(, otCLOB);
LOB2 := TLOBLocator.Create(, otCLOB);

try

InsertRecord.SetVariable('V_COL1', );
...
InsertRecord.SetComplexVariable('V_COL75', LOB1);
InsertRecord.SetComplexVariable('V_COL76', LOB2);
...
InsertRecord.SetVariable('V_COL84', );
InsertRecord.Execute;

LOB1.AsString := 'TEST1';
LOB2.AsString := 'TEST2';

finally

LOB1.Free;
LOB2.Free;

end;

On the "InsertRecord.Execute" I then get the following error :-

ORA-01036: illegal variable name/number

I have triple checked all the variable names & types on the TOracleQuery and they all appear correct.

I've been on this for hours now and feel like banging my head against a wall!!!

Please could someone put me out of my misery?

Regards
David

 

[-=David=-]

Well I found it at last, but that error message is not even remotely helpful.

Basically I had included some columns in the list that shouldn't have been there (long story).

Anyway, I have removed them and now the "InsertRecord.Execute" produces this :-

ORA-03113: end-of-file on communication channel

The CLOB handling killing the server process?

Regards
David

 

[Marco Kalter]

I assume you passed an empty_clob() expression for both CLOB columns?

ORA-03113 usually means that the Oracle Server process for the current session has crashed. Can you check if a user trace dump file exist on the database server that corresponds to this ORA-03113 error? Usually you will see an ORA-00600 error here, with some error codes that may help us find the cause of the problem. If it does, can you send it to me?

 

[-=David=-]

Hi Marco,

Yes I passed in EMPTY_CLOB() for both columns.

I will be back in the office tomorrow so I will speak to the DBA's to see what we can find.

Regards
David

 

[-=David=-]

Hi Marco,

First off I should add we are running Oracle 10.1.0.3 on the server.

The DBA has found the dump file and the problem appears to be internal to Oracle...

ksedmp: internal or fatal error
ORA-07445: exception encountered

They are going to send it off to Oracle for analysis so I will post back when I have further news.

In the meantime, is there anything else you suspect it could be?

Regards
David

 

[Marco Kalter]

I have no immediate clues. The dump file may contain a clue though, so if you can send it to me, I can check it out.