Solved

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

Posted on 2004-08-12
6
2,015 Views
Last Modified: 2012-06-27
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;

0
Comment
Question by:rehman123
6 Comments
 
LVL 8

Accepted Solution

by:
annamalai77 earned 250 total points
ID: 11782137
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
 
LVL 4

Expert Comment

by:oragenius
ID: 11782139
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
 

Author Comment

by:rehman123
ID: 11782487
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 250 total points
ID: 11782950
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question