Re: CLOB data
Thanks Mladen for your wonderful explanation. Nancy From: Mladen Gogala <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: CLOB data Date: Thu, 16 Oct 2003 20:24:25 -0800 But the main point is valid. The phenomenon observed by Nancy is caused by the storage in the row. On 2003.10.16 19:59, Paul Drake wrote: how about using tablespaces UMDOTBS (instead of UNDOTBS) and SYTSEM? thanks for the laugh. Pd Mladen Gogala <[EMAIL PROTECTED]> wrote: This is because "ENABLE STORAGE IN ROW" is the default, which means that oracle will store the first 4000 bytes in the original block. The first part of a LOB is, essentially, identical to VARCHAR2(4000) or VARRAW(4000). If you say something like CREATE TABLE t ( ISCT_ID NUMBER(10), CONTENT CLOB ) TABLESPACE UNDOTBS LOB(CONTENT) STORE AS LOB_CONTENTS ( TABLESPACE SYSTEM DISABLE STORAGE IN ROW NOCACHE LOGGING) / You will not be able to do that any longer. You cannot, however, exceed 4000 characters (I'm not sure what happens if the characters are multibyte ones - may be Steve or Cary could help with that). - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). _ See when your friends are online with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: CLOB data
But the main point is valid. The phenomenon observed by Nancy is caused by the storage in the row. On 2003.10.16 19:59, Paul Drake wrote: how about using tablespaces UMDOTBS (instead of UNDOTBS) and SYTSEM? thanks for the laugh. Pd Mladen Gogala <[EMAIL PROTECTED]> wrote: This is because "ENABLE STORAGE IN ROW" is the default, which means that oracle will store the first 4000 bytes in the original block. The first part of a LOB is, essentially, identical to VARCHAR2(4000) or VARRAW(4000). If you say something like CREATE TABLE t ( ISCT_ID NUMBER(10), CONTENT CLOB ) TABLESPACE UNDOTBS LOB(CONTENT) STORE AS LOB_CONTENTS ( TABLESPACE SYSTEM DISABLE STORAGE IN ROW NOCACHE LOGGING) / You will not be able to do that any longer. You cannot, however, exceed 4000 characters (I'm not sure what happens if the characters are multibyte ones - may be Steve or Cary could help with that). - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: CLOB data
how about using tablespaces UMDOTBS (instead of UNDOTBS) and SYTSEM? thanks for the laugh. PdMladen Gogala <[EMAIL PROTECTED]> wrote: This is because "ENABLE STORAGE IN ROW" is the default, which meansthat oracle will store the first 4000 bytes in the original block.The first part of a LOB is, essentially, identical to VARCHAR2(4000)or VARRAW(4000). If you say something likeCREATE TABLE t(ISCT_ID NUMBER(10),CONTENT CLOB)TABLESPACE UNDOTBSLOB(CONTENT) STORE AS LOB_CONTENTS (TABLESPACE SYSTEMDISABLE STORAGE IN ROWNOCACHELOGGING)/You will not be able to do that any longer. You cannot, however, exceed 4000 characters (I'm not sure what happens if the characters are multibyte ones - may be Steve or Cary could help with that). Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: CLOB data
This is because "ENABLE STORAGE IN ROW" is the default, which means that oracle will store the first 4000 bytes in the original block. The first part of a LOB is, essentially, identical to VARCHAR2(4000) or VARRAW(4000). If you say something like CREATE TABLE t ( ISCT_ID NUMBER(10), CONTENT CLOB ) TABLESPACE UNDOTBS LOB(CONTENT) STORE AS LOB_CONTENTS ( TABLESPACE SYSTEM DISABLE STORAGE IN ROW NOCACHE LOGGING) / You will not be able to do that any longer. You cannot, however, exceed 4000 characters (I'm not sure what happens if the characters are multibyte ones - may be Steve or Cary could help with that). On 10/16/2003 04:54:26 PM, Nancy Hu wrote: I know Oracle provides us dbms_lob package to manipulate LOB data. However, I can use standard DML to manipulate CLOB data as following. Is there advantage to use dbms_lob package? SQL> CREATE TABLE t 2 ( 3ISCT_ID NUMBER(10), 4CONTENT CLOB 5 ); Table created. SQL> insert into t values(1, 'test'); 1 row created. SQL> insert into t values(2, NULL); 1 row created. SQL> update t set content='test2' where isct_id=2; 1 row updated. SQL> select * from t; ISCT_ID CONTENT --- 1 test 2 test2 SQL> _ Surf and talk on the phone at the same time with broadband Internet access. Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: CLOB
Tanel/Original poster, Let me add one more issue about LOBs - My understanding is that rollback information for LOB changes is allocated from the LOB segment itself, and not from system UNDO or RBS. This is controlled by the PCTVERSION parameter - read more about it in the Concepts and SQL Reference manuals... The offshoot is that the space consumed by LOBs will increase when there are updates, depending on PCTVERSION. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Tanel Poder [mailto:[EMAIL PROTECTED] >Sent: Tuesday, September 23, 2003 7:25 PM >To: Multiple recipients of list ORACLE-L >Subject: Re: CLOB > > >Ah, I forgot one important issue about LOB performance -> if >you select a >varchar or long, the data in those is returned to client >instantly, but if >you select a LOB, then only a pointer (locator) is returned >and it's up to >client whether it sends a reading request to server to get >actual contents >of LOB. This means additional sqlnet roundtrips for each LOB item. > >Tanel. > >- Original Message - >To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >Sent: Wednesday, September 24, 2003 5:04 AM > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: CLOB
Ah, I forgot one important issue about LOB performance -> if you select a varchar or long, the data in those is returned to client instantly, but if you select a LOB, then only a pointer (locator) is returned and it's up to client whether it sends a reading request to server to get actual contents of LOB. This means additional sqlnet roundtrips for each LOB item. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, September 24, 2003 5:04 AM > Hi! > > > Basically making a column type as Clob - instead of varchar2(4000) - how > > much extra overhead (as far as bytes/size) will it add to the table size? > > When creating table with clob columns, you can specify whether you can allow > storing of small lobs inline with the actual data row. The clob data maximum > inline limit is 3964 bytes (4000 bytes - 20 bytes for lob locator -16 bytes > for lob inode structure), anything larger will go to external lob segment. > Note that for varying-width characterset clobs, Oracle converts data to > 2-byte fixed character set, so with UTF-8, a letter 'X' will be stored as > hex value 00 58 or 58 00 to the datablock, depending on which platform you > are. If you store your 'X' into normal varchar field, it'll be stored just > as hex value 58, thus in one byte. > So, with variable-width charsets you can actually store only 1982 characters > inline with lob column. Note that nclobs are a bit different, they can have > even longer "byte-length". Also they always seem to be stored in little > endian format, even on Intel platform (that means 00 58 for 'X' not 58 00 > like with regular clob). > > Note that there's a difference between "enable storage in row" lob which is > forced to store its lob item out-of-line (because it's size >3964 bytes) and > regular "disable storage in row" lob. In first case, more space is used in a > row because inode structure, which points directly to physical lob location. > But native out-of-line lobs created with "disable storage in row" do have > only lob locator inline, thus needing additional sequential IOs for scanning > lob index which directs them to lob item physical location. In other words, > inline lobs don't normally need lob index scanning. > > > > > I have a table with 6000 rows, and about 500 of them have 1 column with > more > > that 4000 chars, so they need to go to a clob column. now I just want to > > know if I make a clob column and put the bigger column into clob for all > the > > 6000 rows, how much disk space would I waste by doing this, or would > Oracle > > use the space efficient enough on the clobs so that I shouldn't worry > about > > With so small table you don't actually have to worry about size at all. > Anyway, depending on your data and character set, your table might grow > because lob locator&inode structures are bigger than varchar2 control > structure (only 1 or 3 bytes stating it's length) and if using > variable-width charsets, your inline data will probably grow. On the other > hand, large lobs will be stored in lob segment. But: space in lob segments > is allocated in chunks, which are multiplies of Oracle blocks. Thus if you > have 8kB chunk size for lobs, then every out-of-line lob item will take at > least 8k. If lobs size is 9000 bytes, then it takes 16k and so on (situation > changes a bit with big lobs). > > So, overall size of your data will grow. > > > size ? Other question is it true that Clobs are generally slower even the > > data in them are small ? > > Querying inline clobs shouldn't be much slower, if you are using indexed > access to your table. But since you have very big rows, your buffer cache > might get saturated, since in normal envrionment you'd have tens to hundreds > of rows in a block, now you got only few. Full table scans and analyzing > will of course be slower, since table is much bigger. > > Sorry for answering a simple question with too long answer, I got carried > away... > Tanel. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: CLOB
Hi! > Basically making a column type as Clob - instead of varchar2(4000) - how > much extra overhead (as far as bytes/size) will it add to the table size? When creating table with clob columns, you can specify whether you can allow storing of small lobs inline with the actual data row. The clob data maximum inline limit is 3964 bytes (4000 bytes - 20 bytes for lob locator -16 bytes for lob inode structure), anything larger will go to external lob segment. Note that for varying-width characterset clobs, Oracle converts data to 2-byte fixed character set, so with UTF-8, a letter 'X' will be stored as hex value 00 58 or 58 00 to the datablock, depending on which platform you are. If you store your 'X' into normal varchar field, it'll be stored just as hex value 58, thus in one byte. So, with variable-width charsets you can actually store only 1982 characters inline with lob column. Note that nclobs are a bit different, they can have even longer "byte-length". Also they always seem to be stored in little endian format, even on Intel platform (that means 00 58 for 'X' not 58 00 like with regular clob). Note that there's a difference between "enable storage in row" lob which is forced to store its lob item out-of-line (because it's size >3964 bytes) and regular "disable storage in row" lob. In first case, more space is used in a row because inode structure, which points directly to physical lob location. But native out-of-line lobs created with "disable storage in row" do have only lob locator inline, thus needing additional sequential IOs for scanning lob index which directs them to lob item physical location. In other words, inline lobs don't normally need lob index scanning. > > I have a table with 6000 rows, and about 500 of them have 1 column with more > that 4000 chars, so they need to go to a clob column. now I just want to > know if I make a clob column and put the bigger column into clob for all the > 6000 rows, how much disk space would I waste by doing this, or would Oracle > use the space efficient enough on the clobs so that I shouldn't worry about With so small table you don't actually have to worry about size at all. Anyway, depending on your data and character set, your table might grow because lob locator&inode structures are bigger than varchar2 control structure (only 1 or 3 bytes stating it's length) and if using variable-width charsets, your inline data will probably grow. On the other hand, large lobs will be stored in lob segment. But: space in lob segments is allocated in chunks, which are multiplies of Oracle blocks. Thus if you have 8kB chunk size for lobs, then every out-of-line lob item will take at least 8k. If lobs size is 9000 bytes, then it takes 16k and so on (situation changes a bit with big lobs). So, overall size of your data will grow. > size ? Other question is it true that Clobs are generally slower even the > data in them are small ? Querying inline clobs shouldn't be much slower, if you are using indexed access to your table. But since you have very big rows, your buffer cache might get saturated, since in normal envrionment you'd have tens to hundreds of rows in a block, now you got only few. Full table scans and analyzing will of course be slower, since table is much bigger. Sorry for answering a simple question with too long answer, I got carried away... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: clob to text file
Yes, use DBMS_LOB and UTL_FILE. UTL_FILE has a 32k line length limit, so you will be required to output to multiple lines for CLOB's > 32k. If you don't have any restrictions on how this is done, use Perl, Java or C instead, and avoid the UTL_FILE limitations. Jared On Tuesday 12 November 2002 15:58, Sergei wrote: > Hello everyone, > > I am trying to offload data from multiple tables into one text file. > The problem is couple of the fields are clobs. Any advices how to deal > with clobs? > > Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Clob indexes
I also think that since those indexes are created by Oracle, Oracle knows them. I'll trace the dbms_stats and I'll look for the 'bitand(flag,)' --- Jonathan Lewis <[EMAIL PROTECTED]> a écrit : > It would make sense, > > I would expect Oracle to take a shortcut > with LOB Indexes, simply hard-coding the > fact that access to the LOB should always > be via the LOB index. Consequently there > would be no point in thinking about them > > You could run SQL_TRACE prior to the > dbms_stats call, and see if there is a > 'bitand(flag,)' line in the query that > identifies indexes that excludes LOB indexes. > > > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > Author of: > Practical Oracle 8i: Building Efficient Databases > > Next Seminar - Australia - July/August > http://www.jlcomp.demon.co.uk/seminar.html > > Host to The Co-Operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > > > -Original Message- > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > Date: 23 April 2002 22:52 > > > I've hit bug 1499329 > > As a workaround, I'm analysing the tables in the > staging environment then I'm doing an exchange > partition. > > I can analyse the tables/indexes without problem in > the staging environment. > My question is when creating a clob, Oracle creates > a > sys_...$$ indexes. When analysing the schema, those > sys_...$$ indexes do not have any statistics. Is > that > normal ? > > TIA > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jonathan Lewis > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Clob indexes
It would make sense, I would expect Oracle to take a shortcut with LOB Indexes, simply hard-coding the fact that access to the LOB should always be via the LOB index. Consequently there would be no point in thinking about them You could run SQL_TRACE prior to the dbms_stats call, and see if there is a 'bitand(flag,)' line in the query that identifies indexes that excludes LOB indexes. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 23 April 2002 22:52 I've hit bug 1499329 As a workaround, I'm analysing the tables in the staging environment then I'm doing an exchange partition. I can analyse the tables/indexes without problem in the staging environment. My question is when creating a clob, Oracle creates a sys_...$$ indexes. When analysing the schema, those sys_...$$ indexes do not have any statistics. Is that normal ? TIA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: CLOB or not?
- Original Message - From: "CC Harvest" <[EMAIL PROTECTED]> > Oracle has varchar2 limit of 4000. So basically for> any columns of over 4000 characters, we need to use> CLOB(BLOB). > > My question is : what's the advantage/disadvantage?> Any database design issue(I need to have a separate> tablespace). And those columns will also be accessed> from web via JDBC calls.> we access LOBs via JDBC extensively without any problems for us the only disadvantage was that u couldn't use normal varchar2 functions and operators on CLOBs but this seems to be resolved in 9i (see "SQL Semantics Support for LOBs" - http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a88879/adl07m12.htm#132015 ) I recommend that you take a look at the "Oracle9i Application Developer's Guide - Large Objects (LOBs)" docs for more clarifications hth, Marin "...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. "
RE: CLOB datatype
Rick, CLOBs been bery bery good to me. ;-)(8.1.6 on Win2k) We collect 50,000 - 200,000 documents per day and store them in out-of-line CLOBs in a partitioned table. The CLOB column has an interMedia Text index on it, which allows us to query up to about 12 million documents with great performance. CLOBs can be stored in separate segments from the "regular" data, allowing better storage and access control. CLOBs can be easily manipulated with the DBMS_LOB package. They suffer from none of the many problems caused by LONG columns. They're GRREAT! Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] (512)327-9068 -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 14, 2001 4:37 PM To: Multiple recipients of list ORACLE-L Does anyone have any opinions on using CLOB/NCLOB datatypes in Oracle? I am running 8.1.7.2 on Solaris. Oracle Docs state that CLOBs can handle up to 4 Gig. Are there any gotchas by using CLOBs? Thanks for any information, Rick Stephenson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: CLOB trigger problem
Is the transaction committed by the client prior to your using the CLOB PL/SQL? -Original Message- Sent: Wednesday, September 19, 2001 2:15 AM To: Multiple recipients of list ORACLE-L Dear listers, I am trying to copy an updated CLOB from a table in one database (let's call it A) to the equivalent table in another database (called B just to be original). I have written triggers and procedures to do this (one package on each database), and I am passing the contents of the CLOB to the remote update procedure as VARCHAR fields in a PL/SQL table. Everything works perfectly well when I run an update against table A. I am connecting via TOAD and am logged on to Oracle as the schema owner. The contents of the CLOB are retrieved, passed across and used to update the remote table. When the client updates the CLOB in table A, however, the select statement in my package on database A which 'locates' the CLOB, retrieves a CLOB of zero length. No exception occurs in the Select statement. The client's update succeeds on table A, and my package sends an empty PL/SQL table to the remote procedure (and the CLOB on the remote table is duly erased). The client is connecting through an ASP-driven web interface via IIS, which as far as I can tell is also logging on to Oracle as the schema owner. Anyone encountered this sort of behaviour before? I've just about run out of ideas. If I can't solve this by direct means I can probably do something kludgy like launch the remote update as a background process via DBMS_JOB.SUBMIT, but I'd far rather have it under transactional control. Oracle version is 8.1.6.3 on both databases. Can supply contents of triggers etc. if needed. Cheers, James Campbell [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Campbell, James INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Godlewski, Melissa INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: CLOB help
Use the supplied DBMS_LOB package. Paul Baumgartel MortgageSight Holdings, LLC [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 05, 2001 4:51 PM To: Multiple recipients of list ORACLE-L DBA's, I'm trying to help one of our developers without success. (developmentally challenged DBA;-) Does someone have a good example for inserting data larger than 4000 characters in a CLOB datatype. We tried initializing a row with empty_clob() and then doing an update, still haven't figured it out. The friendly manual is helpfull as usual, IOUG diddn't have anything we could use. We have data being loaded in a 8.1.6.2.1 database via ODBC from a hub(interface) engine. TIA ...JIM... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Howerton INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Paul Baumgartel INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Clob and TOAD
use dbms_lob.substr( clob_column, amount, offset ) to see it's portion. - Original Message - From: Apps Sol To: Multiple recipients of list ORACLE-L Sent: Tuesday, September 04, 2001 09:12 Subject: Clob and TOAD Is there any way to see CLOB data in TOAD?? And at the same time how do we map JAVA data type to CLOB data type in Oracle .. Looks like our developers are running to all kinds of problems.. reg this data type .. Any thoughts ??? TIA RK
Re: CLOB field question
On Thu, Aug 16, 2001 at 02:15:55AM -0800, Guy Hammond wrote: > I have another CLOB question, what is the best way to do a > search-and-replace? > > Something like a c/oldvalue/newvvalue/ on the SQL*Plus command line, or > $variable =~ s/oldvalue/newvalue/; in Perl. > > The only thing I can think of right now would be using INSTR to locate > the value I want, SUBSTR to extract the parts of the document either > side of it, then APPEND to create a new CLOB with first part, new value, > last part... but this seems a bit inelegant. My Oracle::OCI perl modules is close to being able to let you stream a LOB into client memory and stream it back to the server, possibly applying perl regular expressions along the way. Tim. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Bunce INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: CLOB field question
I have another CLOB question, what is the best way to do a search-and-replace? Something like a c/oldvalue/newvvalue/ on the SQL*Plus command line, or $variable =~ s/oldvalue/newvalue/; in Perl. The only thing I can think of right now would be using INSTR to locate the value I want, SUBSTR to extract the parts of the document either side of it, then APPEND to create a new CLOB with first part, new value, last part... but this seems a bit inelegant. Thanks, g -Original Message- Sent: Wednesday, August 15, 2001 10:21 PM To: Multiple recipients of list ORACLE-L Raj, Perfect!!! This is just what I needed! Thanks so much. Bill Bill Tantzen University of Minnesota Libraries 612-626-9949 [EMAIL PROTECTED] A computer program will always do what you tell it to do, but rarely what you want it to do. -> -Original Message- -> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jamadagni, -> Rajendra -> Sent: Wednesday, August 15, 2001 3:35 PM -> To: Multiple recipients of list ORACLE-L -> Subject: RE: CLOB field question -> -> -> Bill, -> -> How about -> -> select id -> from MyTable -> where DBMS_LOB.INSTR(YourClobColumn,UPPER(value_to_search_for)) > 0 -> and your_db_version = 8i -> -> HTH -> Raj -> __ -> Rajendra Jamadagni MIS, ESPN Inc. -> Rajendra dot Jamadagni at ESPN dot com -> Any opinion expressed here is personal and doesn't reflect that -> of ESPN Inc. -> -> QOTD: Any clod can have facts, but having an opinion is an art ! -> -> *1 -> -> This e-mail message is confidential, intended only for the named -> recipient(s) above and may contain information that is -> privileged, attorney work product or exempt from disclosure -> under applicable law. If you have received this message in -> error, or are not the named recipient(s), please immediately -> notify corporate MIS at (860) 766-2000 and delete this e-mail -> message from your computer, Thank you. -> -> *1 -> -> -- -> Please see the official ORACLE-L FAQ: http://www.orafaq.com -> -- -> Author: Jamadagni, Rajendra -> INET: [EMAIL PROTECTED] -> -> 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: [EMAIL PROTECTED] (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: Bill Tantzen INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Guy Hammond INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: CLOB field question
Raj, Perfect!!! This is just what I needed! Thanks so much. Bill Bill Tantzen University of Minnesota Libraries 612-626-9949 [EMAIL PROTECTED] A computer program will always do what you tell it to do, but rarely what you want it to do. -> -Original Message- -> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jamadagni, -> Rajendra -> Sent: Wednesday, August 15, 2001 3:35 PM -> To: Multiple recipients of list ORACLE-L -> Subject: RE: CLOB field question -> -> -> Bill, -> -> How about -> -> select id -> from MyTable -> where DBMS_LOB.INSTR(YourClobColumn,UPPER(value_to_search_for)) > 0 -> and your_db_version = 8i -> -> HTH -> Raj -> __ -> Rajendra Jamadagni MIS, ESPN Inc. -> Rajendra dot Jamadagni at ESPN dot com -> Any opinion expressed here is personal and doesn't reflect that -> of ESPN Inc. -> -> QOTD: Any clod can have facts, but having an opinion is an art ! -> -> *1 -> -> This e-mail message is confidential, intended only for the named -> recipient(s) above and may contain information that is -> privileged, attorney work product or exempt from disclosure -> under applicable law. If you have received this message in -> error, or are not the named recipient(s), please immediately -> notify corporate MIS at (860) 766-2000 and delete this e-mail -> message from your computer, Thank you. -> -> *1 -> -> -- -> Please see the official ORACLE-L FAQ: http://www.orafaq.com -> -- -> Author: Jamadagni, Rajendra -> INET: [EMAIL PROTECTED] -> -> 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: [EMAIL PROTECTED] (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: Bill Tantzen INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: CLOB field question
Bill, How about select id from MyTable where DBMS_LOB.INSTR(YourClobColumn,UPPER(value_to_search_for)) > 0 and your_db_version = 8i HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
