LONG problem (ORA-01461)

[krachyn]

I'm having a bit of trouble with one of my applications. I have a table with a LONG column and am using a TOracleDataSet to attach it to my form. I have added a persistent field for the column and it got created as a TMemoField. I have a TRzDBMemo attached to the field (Raize Components). If I enter text into the field and cause my dataset to post the data, I receive three errors:
ORA-01461, ORA-06512, ORA-04088 (the latter two are trigger messages)
I'm using an instead of update trigger because the original table has mixed case field names and I'd like everything to be done the oracle way (I'm planning on rebuilding the tables with proper names, soon).

So, as far as I can tell, everything is properly set up for my long field to be sent to my database, but it's not happening. Any ideas as to why this is happening or how to work around it?

Delphi 4 C/S

OA 3.3.3

------------------
Ken Rachynski

 

[Marco Kalter]

It seems that inside this trigger you are using a LONG variable, which is used for a non-LONG column in an insert or update statement.

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

 

[krachyn]

That is what it sounds like, but here is the trigger in its entirety:

create or replace trigger v_comms_ins_upd_row
instead of update on v_comms
for each row
declare
-- local variables here
begin
update comms
set "Contact ID" = :new.contact_id,
"Project Lead ID" = :new.lead_id,
"Topic" = :new.topic,
"Entry Date" = :new.date_entry,
"Entry By" = :new.user_entry,
"Notes" = :new.notes,
"Modified Date" = sysdate,
"Modified By" = user
where "Comm ID" = ld.comm_id;

end v_comms_ins_upd_row;

The LONG column is "Notes" and :new.notes. The other item that occurs to me this morning is that Topic is sent/returned via an edit field and may be the actual culprit here, everything else is added/updated via my code. I'll have to do some testing.

 

[krachyn]

Further testing has shown that I can duplicate this behaviour in PL/SQL Developer as well by using a non-empty string ('blah' will trigger this error in the trigger). If I update the table directly, I don't have the trigger problem so this makes it an Oracle issue. I wish I had the opportunity to redo the underlying tables so that I didn't have to use this trigger route.

------------------
Ken Rachynski

 

[krachyn]

Sigh. Caught again by a very basic and (I thought) well known rule: :NEW and :OLD cannot be used with LONG column.
Well, I'm off to test whether DOA can handle mixed case column names properly. I seem to recall that it couldn't which was why I had created the view and related triggers.

------------------
Ken Rachynski

 

[Marco Kalter]

If you just want to use the view to rename the mixed case column names, you don't really need an instead of trigger. Such a view can probably be updated without a problem.

The TOracleDataSet currently cannot handle mixed case columns.

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

 

[krachyn]

Well, okay, it's not just there to rename columns, there are multiple tables involved which means there's a join and rename and an instead of trigger *is* needed.

I have discovered that the dataset does not support mixed case columns so I'm trying something else with my views. Hopefully, I'll have this solved before too long.

Thanks for the replies.

------------------
Ken Rachynski

 

[DavidH]

I got that ORA-01641 (1461?) the other day...and my investigation showed that this is a problem related to Oracle, which seems to occur if your BLOB is larger than 4K (i think).

However, in Oracle 8.1.6 (which I just upgraded to), they've fixed this problem.

So then I could insert Blobs until they were larger than 32K, because then BDE crashed (AV's). So now I'm trying DOA for a few hours - and so far so good...

/ David