work effectively with the deprecated LONG data type
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Working with LONGs
Generations of the Oracle Database ago the LONG data type was desupported and deprecated. Oracle told everyone to stop using it for new development: And some of us actually listened and did so. But the real world contains two types of applications that still use the LONG data type.
One is legacy applications that haven't been upgraded. The other, unfortunately us Oracle's Corps. own database where we must still suffer the use of LONGs for columns such as USER_TRIGGERS.TRIGGER_BODY and USER_CONSTRAINTS.SEARCH_CONDITION.

So we will use these default sources of LONG data to work some basic bits of functionality such as SUBSTRinging, JOINING, and searching with wild cards to develop techniques necessary to survive their painfully slow demise. We will start out by creating some sample data with which to work.
CREATE TABLE t1 (
col1 VARCHAR2(20),
col2 VARCHAR2(20));

ALTER TABLE t1
ADD CONSTRAINT cc_t1_col1
CHECK (col1 IN ('Yes', 'No', '?'));

CREATE OR REPLACE TRIGGER BIT_T1
BEFORE INSERT
ON t1

FOR EACH ROW

BEGIN
  :NEW.col1 := 'Yes';
END BIT_T1;
/

CREATE TABLE t2 (
col1 VARCHAR2(20),
col2 VARCHAR2(20));

ALTER TABLE t2
ADD CONSTRAINT cc_t2_col1
CHECK (col1 IN ('Yes', 'No', 'X'));

CREATE OR REPLACE TRIGGER BIT_T2
BEFORE INSERT
ON t2
FOR EACH ROW
BEGIN
  :NEW.col1 := 'No';
END BIT_T2;
/

SELECT trigger_name, trigger_body
FROM user_triggers;

SELECT constraint_name, search_condition
FROM user_constraints;
We will start our exploration with wild card searches. Lets say we are looking for all of the rows with the value 'Yes'. There should be one of them in USER_TRIGGERS.
desc user_triggers

-- none of these work

SELECT  trigger_name, trigger_body
FROM user_triggers
WHERE trigger_body LIKE '%Yes%';

SELECT trigger_name, trigger_body
FROM user_triggers
WHERE TO_CHAR(trigger_body) LIKE '%Yes%';

SELECT trigger_name, trigger_body
FROM user_triggers
WHERE TO_CLOB(trigger_body) LIKE '%Yes%';

SELECT trigger_name, trigger_body
FROM user_triggers
WHERE TO_LOB(trigger_body) LIKE '%Yes%';

DECLARE
 c CLOB;
BEGIN
  SELECT TO_LOB(trigger_body)
  INTO c
  FROM user_triggers
  WHERE rownum = 1;
END;
/

-- and yet this works
CREATE TABLE tlob (
testcol CLOB);

INSERT INTO tlob
SELECT TO_LOB(trigger_body)
FROM user_triggers;

SELECT * FROM tlob;
So how do we get only those values we want? Adding a WHERE clause with SUBSTR doesn't work. One obvious solution is to put all trigger bodies into a global temporary table and then select from there. For example:
DROP TABLE tlob PURGE;

CREATE GLOBAL TEMPORARY TABLE tlob (
testcol CLOB);

-- Step 1:
INSERT INTO tlob
SELECT TO_LOB(trigger_body)
FROM user_triggers;

-- Step 2:
SELECT *
FROM tlob
WHERE testcol LIKE '%Yes%';

or

DELETE FROM tlob
WHERE testcol NOT LIKE '%Yes%';
But is there a way to do this without inserting into a table? Unfortunately there isn't except under some very special, not general conditions so for our purposes we will say no. You must use LONG to LOB solely for purposes of version. And there is nothing you can do with LONG in a WHERE clause.

Simply put ... if you have a LONG, and you wish to do more than just display it, you must use TO_LOB to convert it during either an
INSERT INTO <table> or a CREATE TABLE <table_name> AS. Everything else is just wasting time.

Now if only we could get Oracle to stop using it.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved