Solved

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

Posted on 2004-08-12
6
2,008 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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

770 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