Home » SQL & PL/SQL » SQL & PL/SQL » Referering Long value in triger (10g)
Referering Long value in triger [message #440078] Wed, 20 January 2010 23:09 Go to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
Hi guys,

is there any to refer the long values in trigger? any work around?

thanks
Re: Referering Long value in triger [message #440079 is a reply to message #440078] Wed, 20 January 2010 23:16 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please see the Trigger Restrictions on LONG and LONG RAW DatatypesHere

sriram Smile
Re: Referering Long value in triger [message #440081 is a reply to message #440078] Wed, 20 January 2010 23:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Referering Long value in triger [message #440082 is a reply to message #440079] Wed, 20 January 2010 23:48 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
hi sriram, thanks for the link. does it mean there is no work around available to refer the long values in triger?
Re: Referering Long value in triger [message #440083 is a reply to message #440078] Wed, 20 January 2010 23:57 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member

from link Sriram posted.

Quote:

Trigger Restrictions on LONG and LONG RAW Datatypes

LONG and LONG RAW datatypes in triggers are subject to the following restrictions:

A SQL statement within a trigger can insert data into a column of LONG or LONG RAW datatype.
If data from a LONG or LONG RAW column can be converted to a constrained datatype (such as CHAR and VARCHAR2), then a LONG or LONG RAW column can be referenced in a SQL statement within a trigger. The maximum length for these datatypes is 32000 bytes.
Variables cannot be declared using the LONG or LONG RAW datatypes.
:NEW and :PARENT cannot be used with LONG or LONG RAW columns.



so there is no workaround.

Re: Referering Long value in triger [message #440088 is a reply to message #440082] Thu, 21 January 2010 00:29 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
you will get
ORA-04093: references to columns of type LONG are not allowed in triggers


Quote:
If data from a LONG or LONG RAW column can be converted to a constrained datatype (such as CHAR and VARCHAR2), then a LONG or LONG RAW column can be referenced in a SQL statement within a trigger. The maximum length for these datatypes is 32000 bytes.
you can also use lobs.

sriram Smile
Re: Referering Long value in triger [message #440094 is a reply to message #440088] Thu, 21 January 2010 00:53 Go to previous message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
yea sriram, you are right thats the error i m getting. even i cannot change the long to clob as it would b a drastic change for us.Sad i was looking for any other way to do it.
anyway thank you all for the help and support.

regards,
Kashif

Previous Topic: Does the value is set to null when exception raised?
Next Topic: Problem with varray type out parameter (splitted from "xmltype conversion" topic)
Goto Forum:
  


Current Time: Tue Feb 11 09:24:26 CST 2025