HOW TO COMPARE LONG DATATYPE OR EXCLUDE IT DURING REFERENCING IN DATABASE TRIGGER

RESPECTED ORACLE GURUS
I HAVE MASTER  TABLE  IN WHICH ONE COLUMN  EMP_PHOTO DATATYPE IS LONG
NOW I WANT TO WRITE  BACK END TRIGGER FOR AUDIT PURPOSE BUT I AM GETTING
FOLLOWING ERROR

ERROR
TRIGGER VHMAIN.DBT_VHM_EMPLOYEE
On line:  0
PL/SQL: ORA-04093: references to columns of type LONG are not allowed in triggers


CREATE OR REPLACE TRIGGER "VHMAIN".DBT_VHM_EMPLOYEE
BEFORE INSERT OR DELETE OR UPDATE ON VHM_EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
BEGIN
<>
<>
<>
END;

rehman123Asked:
Who is Participating?
 
annamalai77Commented:
hi

well my friend, u cannot reference a long datatype or do a select directly on a long data type column.

u have first store the value in a varaible and then only reference it, something like the way i have mentioned below.

CREATE OR REPLACE TRIGGER "VHMAIN".DBT_VHM_EMPLOYEE
BEFORE INSERT OR DELETE OR UPDATE ON VHM_EMPLOYEE
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
tvar long;
BEGIN
  begin
      select <long data type field_name>
      into tvar
      from <table>
      where <condition>

<>
<>
<>
END;

i hope the above solution helps solve your problem.

regards
annamalai




0
 
orageniusCommented:
not sure if this helps or not but solution for similar problem can be found on

http://www.orafaq.com/usenet/comp.databases.oracle.server/2002/05/22/2653.htm
0
 
rehman123Author Commented:
whatever is mentioned in foloowing link is
http://www.orafaq.com/usenet/comp.databases.oracle.server/2002/05/22/2653.htm
is not useful for me .
0
 
schwertnerCommented:
You want to compare two long datatypes, but exclude trailing spaces  
in the comparison.  According to page 2-14 in the PL/SQL User's Guide  
and Reference Version 2.0, you cannot reference LONG columns in  
expressions, function calls or certain SQL clauses.  Is there another way?
 
Solution Description:  
=====================  
 
The only way to do this is to fetch these into another datatype which is a valid conversion for a long (see chart on pg 2-19 in the PL/SQL User's  Guide and Reference Version 2.0).  You can then use the functions like RTRIM against these datatypes.


Oracle provides several built-in SQL character functions to manipulate
character values, however, most return a VARCHAR2 datatype.
 
Although, Oracle allows using some of the built-in SQL functions
on LONG columns (for example: CONCAT, LENGTH, and LPAD),
they return VARCHAR2 values, and therefore, if your data is greater
than 4K (Oracle8), the data will be truncated and results returned
without an error message.
 
The best approach for manipulating LONG data is to use one of the
programmatic interfaces, such as Pro*C or Oracle Call Interface (OCI).
To obtain more functionality such as comparing, appending, erasing  
all or part of a character string with Oracle8, convert the LONG  
datatype to a LOB datatype and use the DBMS_LOB package.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.