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. |
|
