Skip to Main Content
  • Questions
  • Extracting text from long column using substr and instr


Question and Answer

Tom Kyte

Thanks for the question, Scott.

Asked: March 26, 2001 - 6:19 pm UTC

Last updated: February 03, 2004 - 11:41 am UTC

Version: 8.1.6

Viewed 50K+ times! This question is

You Asked

I am trying to extract data from a long datatype column that is being used for multiple purposes. I am trying to use the following SQL statement to extract data in the string held between the following placeholders - <SHORT_START> and <SHOR_END> -

select substr(t.text_data,instr(t.text_data,'<SHORT_START>'),instr(t.text_data,'<SHORT_END>'))
FROM text_storage t, product p
where p.product_code = 'ADJ' and
t.text_number = p.tech_notes_number

I know that the statement isn't yet complete, but I can't get beyond this stage as I keep getting a ORA-00932 error at the first t.text_data portion of the substr function. The text_data column is set as LONG.

and Tom said...

You cannot perform functions on LONGS.

In Oracle8.0 and up, you should be using CLOBS instead, then you could:

select ...
dbms_lob.substr( clob_col, ..dbms_lob.instr(clob_col,...) .. )

If the long is ALWAYS 32k or less, you can use the method outlined in:

</code> <code>

whereby you create a procedure that takes a ROWID in, fetches the long out into a 32k variable, parses it and returns upto 4000 characters of it to SQL.

That also shows you how to use DBMS_SQL to access the long piecewise as well if need be.


  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment


A reader, March 27, 2001 - 4:16 am UTC

What if the LONG datatype is a view?

A Reader, February 21, 2003 - 7:06 pm UTC

What if we cannot convert the datatype and still want to use function like substr? Example user_views:
select view_name, substr(text,1, 10)
from user_views;

We want to get the first 10 characters in the long field. We may copy the contents of the base table into another table and convert that table's datatype. Is there any esasier ways?

Thank you very much.

Why Oracle don't use CLOB but use LONG in it's dictionary tables?

A Reader, February 22, 2003 - 3:18 pm UTC

Just want to know the reason. Thanks.

Tom Kyte
February 22, 2003 - 3:36 pm UTC

legacy reasons.

when you make a change, you affect yourself.

when we make a change, we affect -- well, more then just ourselves. Changing the datatypes in the dictionary will be a rather large change for us. Backwards compatability is a hard thing to achieve. The smallest changes can have large ripples.

How to use a Clob datatype as a variable

Balaji, November 15, 2003 - 3:59 pm UTC

I am forming a dynamic sql in a proc.
the length of this sql goes more than 32 k.
so how am i to declare a clob variable and assign the query to it??

i also need to return the clob as output.

Tom Kyte
November 16, 2003 - 10:35 am UTC

well, you would use dbms_lob.createtemporary and dbms_lob.writeappend to write to it, BUT you will not be using native dynamic sql on it, you'll have to use dbms_sql which allows you to call parse with a plsql table of strings -- each string can be 255 characters but the strings are glued together to create an entire sql statement.

Long into Varchar2

Dan Clamage, November 17, 2003 - 2:27 pm UTC

If you're doing this one row at a time, AND the Long value is under 32K, you can fetch the Long into a PL/SQL VARCHAR2 and then use the standard string functions.

Tom Kyte
November 21, 2003 - 7:25 am UTC

(that was part of the original answer!)

Why Oracle don't use CLOB but use LONG in it's dictionary tables?

Vladimir Andreev, February 03, 2004 - 11:41 am UTC

That's a very good question.
Tom, your first answer to it ("Legacy reasons") doesn't tell us why the *new* data dictionary columns are of type LONG. I mean, there cannot be any backward compatibility concerns for "user_ind_expressions.column_expression", for example -- LOBs were present and recommended at the time when function-based indexes came around.
I have a grudge with that particular view, since it prevents me from using a simple read-only script to tell me if a column is indexed -- and it is a rare occasion when I am allowed to create a stored PL/SQL function in a production database, where every change is strictly controlled (as it should be).


More to Explore


Complete documentation on Securefiles and Large Objects here