Search for string in LONG datatype

[benjamenus]

I wish to search for a string of text (e.g. like '%search%') but the field is long datatype so it will not allow.

I believe it is possible to amend the SQL so it will display the first 2000 characters. I also belileve you can write a function to convert the datatype.

Does anyone know how to do this or have any other solution?

Cheers

 

[parbhani]

HI there ,

I do not know which version of Oracle you are using.
You cannot do such thing with LONG column, unless you write a pro*C program, get the data in a c-veriable and find with strstr function or use Java in similiar way.

But with Oracle 8i and onwards you can do all such thing with LOB column. Also if you have data from old LONG column, you can transfer that data to a CLOB column.

See the "Application developer's Guide" and "PL/SQL supplied packages reference" for concepts and the details of DBMS_LOB package.

Regards

 

[benjamenus]

Thanks for that. I did find this which sort of does the trick:

set serveroutput on
/
DECLARE
v_32k VARCHAR2 (32767);
BEGIN
FOR i IN (SELECT scriptno, scripttext FROM pcm03)
LOOP
v_32k := i.scripttext;
if instr(i.scripttext, 'SEARCH STRING') > 0 then
DBMS_OUTPUT.put_line (i.scriptno || ' contains "search string"');
end if;
END LOOP;
END;
/

 

[sem]

Benjamenus, not quite clean solution, because the first chunk may be ended with SEARCH and the second one - started with STRING.

Regards, Dima

http://seminihin.narod.ru

 

[parbhani]

Hi there ,

I may look like a fool , but ,

v_32k VARCHAR2 (32767);

I cannot understand this. I never tested the limits of varchar2, but assume that it should not grow beyond 4000.

Please explain.
Also convey the Oracle version you are on.

regards

 

[Turkbear]

Hi,
From the docs:
( Basically as a database column it is limited to 4K, but as a Pl/Sql variable it is 32K)

You use the VARCHAR2 datatype to store variable-length character data. How the data is represented internally depends on the database character set. The VARCHAR2 datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:

VARCHAR2(maximum_size)


You cannot use a constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

The VARCHAR2 datatype involves a trade-off between memory use and efficiency. For a VARCHAR2(>= 2000) variable, PL/SQL dynamically allocates only enough memory to hold the actual value. However, for a VARCHAR2(< 2000) variable, PL/SQL preallocates enough memory to hold a maximum-size value. So, for example, if you assign the same 500-byte value to a VARCHAR2(2000) variable and to a VARCHAR2(1999) variable, the latter uses 1499 bytes more memory.

Remember, you specify the maximum size of a VARCHAR2 variable in bytes, not characters. So, if a VARCHAR2 variable stores multi-byte characters, its maximum size is less than n characters. The maximum width of a VARCHAR2 database column is 4000 bytes. Therefore, you cannot insert VARCHAR2 values longer than 4000 bytes into a VARCHAR2 column.

You can insert any VARCHAR2 value into a LONG database column because the maximum width of a LONG column is 2**31 bytes. However, you cannot retrieve a value longer than 32767 bytes from a LONG column into a VARCHAR2 variable.



Hope it helps clear thing up...

 

[SantaMufasa]

Benjamenus,

You have some great flexibility with Oracle 9 since it fully supports CLOBs (Character Large Objects). You can treat a CLOB as you would any other character column. You cannot, however, treat LONGs with such flexibility. If you have a LONG column in a table, there should be no downside in converting the LONG to a CLOB:

ALTER TABLE <table_name> MODIFY <long-column> CLOB;

Once you complete the conversion, you can do anything you want to the CLOB. Notice the following character-based manipulations of a CLOB;

SQL> desc clobber
 Name                          Null?    Type
 ----------------------------- -------- ------
 ID                                     NUMBER
 LONG_THANG                             CLOB

SQL> select instr(long_thang,'benjamenus') from clobber
  2  where long_thang like '%benjamenus%';

INSTR(LONG_THANG,'BENJAMENUS')
------------------------------
                       1027073
SQL> select substr(long_thang,1027073) from clobber
  2  where long_thang like '%benjamenus%';

SUBSTR(LONG_THANG,1027073)
-------------------------------------------------------
benjamenus

Notice that 'benjamenus' is more than a million characters into LONG_THANG, and that I can substr(), like, instr(), et cetera to it.

Let me know if this helps resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:44 (09Jun04) UTC (aka "GMT" and "Zulu"), 12:44 (09Jun04) Mountain Time)

 

[benjamenus]

Thanks for all the info everyone. Unfortunately the datatype is dictated by our DBAs, but it's always good to have the knowledge )

 

[SantaMufasa]

Benjamenus,

First, I am a DBA...I am not a dictator. I must have good business and technical reasons for implementing the structures I implement. I would be fascinated to hear your DBA's rationale for using LONG versus CLOB.

Here are some questions (which answers I would enjoy hearing) that you can pose to your DBA regarding the use of LONGs:

Q1) What are the advantages of using LONG that prevent our using CLOB in our application(s)?
My A1) There are none. LONG columns are similar to the character Lennie in John Steinbeck's classic, "Of Mice and Men"...Big and hopelessly functionally retarded. You cannot do any data manipulation to a LONG; you cannot query a LONG in concert with other columns. With CLOB, you can do all types of data manipulation that is typical with a VARCHAR2 column.

Q2) What are the disadvantages or risks of converting from a LONG column to a CLOB.
My A2) There are none. Oracle allows "ALTER TABLE <name> MODIFY <LONG-column> CLOB;" without incident. The disadvantages/risks of remaining with LONG columns are that you must come up with some very convoluted method of trying to "turn Lennie into a college graduate". There are tons of business risks to your trying to contrive normal functionality for LONGs. I know, because I've had to attempt that project on old versions of Oracle that lack CLOB functionality. The solution was a programmatical hodge-podge with very limited success for the 4K/32K limitations that you, Parbhani, and Turkbear already presented, above.

So, all-in-all, your DBAs should have much better overriding business/technical reasons for restricting you to the use of LONGS (versus CLOB) than the lame (but typical) reason: "Because we said so." In this case, there is proof that remaining with LONG (versus CLOB) can be a costly business/technical decision.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:52 (10Jun04) UTC (aka "GMT" and "Zulu"), 08:52 (10Jun04) Mountain Time)

 

[benjamenus]

Mufasa

It appears I may have caused some offence. I assure you none was intended.

Thanks again for the advice/info.

 

[SantaMufasa]

Benjamenus,

Absolutely no offense/offence taken. My rather vehement reply was more iconoclastic. In fact, unfortunately, I recognise/recognize and acknowledge your situation totally. My thesis is: In this day and age, we should be moving away from the iron-handed, god-like DBA whose word us unquestioned scripture. ("Power corrupts and absolute power corrupts absolutely.") The design of a system can be "corrupted" (read: degraded) by blind faith in the almighty (and unquestioned) laws of a control-freak DBA.

Every rule or standard in an organization/organisation should be backed up with reasoning that contributes to an improved "bottom-line" in the organization/organisation. If someone cannot show me how a rule/standard either directly or indirectly increases revenue or decreases expenses or alternative rules/standards, then the rule/standard is suspect. That was my point.

...And I certainly was not offended by your comments, Benjamenus. I am just trying to give you "armor/armour to fight the valiant battle" against dictatorial DBAs.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:25 (10Jun04) UTC (aka "GMT" and "Zulu"), 09:25 (10Jun04) Mountain Time)