Solved

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

Posted on 2004-08-12
6
2,019 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 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