From TJohnston@quallaby.com Wed, 14 Nov 2001 11:58:31 -0800 From: "Johnston, Tim" Date: Wed, 14 Nov 2001 11:58:31 -0800 Subject: RE: real newbie question (i think) Message-ID: MIME-Version: 1.0 Content-Type: text/plain Hi Dick... Here are some thoughts... 1) You can't use the like operator on the LONG datatype... You will get an inconsistent datatype error... 2) A bunch of reasons... a) LONG are going away someday b) You can store LOB data out of line with the table to improve performance c) You can more than one LOB per table d) You can partition a table with LOB's but not one with a LONG e) LOB's support random access to the data f) There a bunch of built in functions to access LOB's ( check out the DBMS_LOB package ) Tim -----Original Message----- Sent: Wednesday, November 14, 2001 2:23 PM To: Multiple recipients of list ORACLE-L Rich, Since no one has yet posted an answer, here is my opinion/questions. 1) Why do you want the 'new column'? What it looks like it that you just need to query the table for the keywords your interested in. The 'like' operator shoudl work just fine. 2) A long can contain 2GB worth of data, why on earth would you want to use a CLOB or BLOB? Dick Goulet ____________________Reply Separator____________________ Author: Rich Davidson Date: 11/14/2001 5:30 AM hey all, i'm in your typical situation of "we don't need a dba...oracle is an app so the bizapps guy can handle it"....right...now to reality. there is a column in our database (oracle is the back end of our crm package) that is defined as a long and they want to do keyword searches on it. i've found out that you can't use type long in a where clause. i guess i have a few questions and i'm going to assume the answers (if anyone answers...please oh please do) will be a matter of opinion. am i better off creating a new column in the existing table of the type clob or blob? should i create a new table to store this data? and what would be my best way to keep these columns in sync...every time someone updated the long column i would want my new column updated to reflect the change. right now we're on 8.0.6/win 2k...but could upgrade to 8.1.7 (means upgrading our crm package too) if it makes this process easier. thanks in advance for the help and not being a dba i hope what i wrote makes sense! *:-.,_,.-:*'``'*:-.,_,.-:*'``'*:-.,_,.-: rich davidson dynamicsoft Inc. east hanover, nj rdavidson@dynamicsoft.com t. +1-973-503-6169 f. +1-973-952-5050 how to extract text from LONG field
hey all,
 
i'm in your typical situation of "we don't need a dba...oracle is an app so the bizapps guy can handle it"....right...now to reality.  there is a column in our database (oracle is the back end of our crm package) that is defined as a long and they want to do keyword searches on it.  i've found out that you can't use type long in a where clause.
 
i guess i have a few questions and i'm going to assume the answers (if anyone answers...please oh please do) will be a matter of opinion.  am i better off creating a new column in the existing table of the type clob or blob?  should i create a new table to store this data?  and what would be my best way to keep these columns in sync...every time someone updated the long column i would want my new column updated to reflect the change.
 
right now we're on 8.0.6/win 2k...but could upgrade to 8.1.7 (means upgrading our crm package too) if it makes this process easier.
 
thanks in advance for the help and not being a dba i hope what i wrote makes sense!

*:-.,_,.-:*'``'*:-.,_,.-:*'``'*:-.,_,.-:
rich davidson
dynamicsoft Inc.
east hanover, nj

rdavidson@dynamicsoft.com
t. +1-973-503-6169
f. +1-973-952-5050

-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet@vicr.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnston, Tim INET: TJohnston@quallaby.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).