asked on

I want to save a long type value in another table.

hello expert
I want to save a long type value in another table.
But that's a mistake.
how to fix this error.

In the 'RAD_SONUC' table, 'RAPORX' has the value 'LONG' of the column.

I want to insert the values of the 'raporx' column of the rad_sonuc table into the rad_tani table.

code is below

create or replace trigger raporxx
before  update  of RAPORX
   ON RAD_SONUC
FOR EACH ROW
declare
  VAR1 clob;
  VAR2 VARCHAR2(4000);
  var3 LONG;
begin
      var3:=:new.RAPORX;
      insert into rad_tani(istek_no,rowid1,yazi) values(:new.istek_no,:new.ROWID1,to_char(var3)); 

dbms_output.put_line('istek_no= '||:new.istek_no||' giris= '||VAR3);
end;

Open in new window

The error in the code is as follows.

ORA-04093: references to columns of type LONG are not allowed in triggers

Open in new window

how can i fix it.?
thanks

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ali şahin

ASKER

I work at the hospital automation company.
The client asked me to write a trigger like this.
I don't have the codes for the app.
I can't write code in practice.
I don't have access to codes.
since the application I use is an old application, the tables used are old oracle tables.
how can I solve this situation in another way.
thanks

johnsone

Your version of Oracle is obviously newer than when the data type was deprecated. Otherwise, it wouldn't know what a CLOB is.

What version (full version, all 4 numbers) is the server?

What is the datatype of RAD_TANI.YAZI?

Why copy the data to a new table? You are duplicating your storage, when you can just reference it in the other table.

Is it possible that RAD_SONUC.RAPORX ever contains more than 32K of information?

Just because someone tells you to do something doesn't mean it is a good idea or possible.

ali şahin

ASKER

thanks johnsone
you're right :)

johnsone

Well, I was working on trying to build a test case for this, but you closed the question.

My guess, is if you really want/need to do this, search for mutating trigger problem. The way you are going to have to solve it, I believe, is the same way you solve a mutating trigger problem. You are going to need multiple triggers and procedures. Your regular trigger puts the key into some sort of holding table (global temporary if available in your version). Then you need an after trigger that is statement level, not row level, to call a procedure that coverts the LONG to whatever type you need and inserts the new row. This may need to be an autonomous transaction, not sure, which may or may not be available in your version.