?
Solved

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

Posted on 2004-08-12
6
Medium Priority
?
2,022 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 1000 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 1000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.
Suggested Courses

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