Home » SQL & PL/SQL » SQL & PL/SQL » How to extract full text from clob where I have a distict select (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 SunOS ts0503 5.10 Generic_141445-09 i86pc i386 i86pc)
How to extract full text from clob where I have a distict select [message #585302] Thu, 23 May 2013 10:51 Go to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

I have the following query
As of now I am only able to extract only 4000 characters from the clob column "DESCRIPTION".
Could anyone pls suggest how to get more characters or max for that column with the same query concept?

Thanks

Jim


select distinct 
                o.id "Organization ID",
                en.entity_id "Contact ID",
                en.entity_cd "Note Entity Code",
                ed.entity_name "Note Entity",
                en.entity_id "Note Entity_id",
                en.note_dt "Note Date",
                en.create_dt "Create Date",
                en.update_dt "Update Date",
                FN_LOCALIZE_BI(nt.desc_id, 'EN', 'default') "Type",
                en.subject "Subject",
                replace(replace(dbms_lob.substr(en.description, 4000, 1), chr(10), '\n'), chr(13), '\n') "Content"
from 
        entity_notes en
        left join note_type nt on nt.type_cd = en.note_type
        left join entity_definition ed on en.entity_cd = ed.entity_cd
        inner join contact_owner co on co.contact_id = en.entity_id
        inner join organization o on o.id = co.organization_id
 where 
          (en.sync_src_cd = 'MANUAL' or en.sync_src_cd is null);

[Updated on: Thu, 23 May 2013 11:31]

Report message to a moderator

Re: How to extract full text from clob where I have a distict select [message #585304 is a reply to message #585302] Thu, 23 May 2013 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try:
                replace(replace(to_char(dbms_lob.substr(en.description, 4000, 1)),
                                chr(10),
                                '\n'),
                        chr(13),
                        '\n') "Content"

Regards
Michel
Re: How to extract full text from clob where I have a distict select [message #585308 is a reply to message #585304] Thu, 23 May 2013 11:45 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

Dear Michel,
Thanks for your quick response.
I tried this earlier. For the original query and for the query with "to_char" both returned the same value "4000" characters.

But if I am modifying your query as below like value more than 4000, for eg 4001 or 5000


replace(replace(to_char(dbms_lob.substr(en.description, 4001, 1)), chr(10), '\n'), chr(13), '\n') "Content"


I am getting the below error

>[Error] Script lines: 1-21 -------------------------
 ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


Thanks

Jim

[Updated on: Thu, 23 May 2013 12:13]

Report message to a moderator

Re: How to extract full text from clob where I have a distict select [message #585311 is a reply to message #585308] Thu, 23 May 2013 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot return more that 4000 bytes in a VARCHAR2 in SQL this is why you have the error.
If you want more then you have to use CLOB, but if you use CLOB then you can't use DISTINCT.
You have to make your choice, either DISTINCT either less than 4001 bytes.

Regards
Michel
Re: How to extract full text from clob where I have a distict select [message #585312 is a reply to message #585311] Thu, 23 May 2013 12:21 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

Michel,
Thanks for the reply. I even tried that way of concatenating using clob, but then distinct wont work, without distinct, the results are wrong and at the same time client needs to see more than 4000 characters for the column too. I am actually in a dilemma. My question is whether we can achieve this by custom function or something?

Regards

Jim
Re: How to extract full text from clob where I have a distict select [message #585314 is a reply to message #585312] Thu, 23 May 2013 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do all what you want in a custom function; you just have to find an algorithm to store and compare CLOB; I hope you have much and much RAM.

Quote:
without distinct, the results are wrong


It is then more likely that the query is wrong.
Why do you need a DISTINCT?

Regards
Michel

[Updated on: Thu, 23 May 2013 12:26]

Report message to a moderator

Re: How to extract full text from clob where I have a distict select [message #585315 is a reply to message #585312] Thu, 23 May 2013 12:29 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

select 
--distinct 
                o.id "Organization ID",
                en.entity_id "Contact ID",
                en.entity_cd "Note Entity Code",
                ed.entity_name "Note Entity",
                en.entity_id "Note Entity_id",
                en.note_dt "Note Date",
                en.create_dt "Create Date",
                en.update_dt "Update Date",
                FN_LOCALIZE_BI(nt.desc_id, 'EN', 'default') "Type",
                en.subject "Subject",
                replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 1)),chr(10),'\n'),chr(13),'\n') 
                ||''|| 
                replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 4001)),chr(10),'\n'),chr(13),'\n') 
                "Content"
from 
       entity_notes en
       left join note_type nt on nt.type_cd = en.note_type
       left join entity_definition ed on en.entity_cd = ed.entity_cd
       inner join contact_owner co on co.contact_id = en.entity_id
       inner join organization o on o.id = co.organization_id
where 
         (en.sync_src_cd = 'MANUAL' or en.sync_src_cd is null);



If we put distinct
We will get "ORA-00932: inconsistent datatypes: expected - got CLOB "

For getting full text, concatenation is the only possible solution in this context as per my knowledge and concatenation of string is having a limitation of 4K. So we have to convert it into LOB which is not having any limit. But LOB datatype won't work with Distinct in this context.
If we concatenate with out using to_clob we will come across "ORA-01489: result of string concatenation is too long " and without distinct the result is wrong Sad
Re: How to extract full text from clob where I have a distict select [message #585316 is a reply to message #585314] Thu, 23 May 2013 12:30 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

To avoid duplicates due to joins in the query
Re: How to extract full text from clob where I have a distict select [message #585317 is a reply to message #585311] Thu, 23 May 2013 12:31 Go to previous messageGo to next message
ThomasG
Messages: 3087
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Maybe some form of hashing the CLOB could be used to get the distinct CLOB rows, and then the CLOB data in a second step.

Of course if there are many rows that will only be feasable if the hashes are stored in the table, not computed on the fly.
Re: How to extract full text from clob where I have a distict select [message #585318 is a reply to message #585316] Thu, 23 May 2013 12:33 Go to previous messageGo to next message
ThomasG
Messages: 3087
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
jimmy_s_abraham@yahoo.com wrote on Thu, 23 May 2013 19:30
To avoid duplicates due to joins in the query


ok. if you have dpulicates because of joins, then Michel is on the right track. The joins are most likely wrong.
.
Re: How to extract full text from clob where I have a distict select [message #585319 is a reply to message #585316] Thu, 23 May 2013 12:33 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

For confirmation I can provide you the first 10 rows of both the outputs if you need
a. one which uses clob and without distinct (max characters we will get)
b. one which doesn't use clob and using distinct (only 4000 characters we will get)

Pls confirm
Thanks

Jim
Re: How to extract full text from clob where I have a distict select [message #585320 is a reply to message #585319] Thu, 23 May 2013 12:36 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

I think RAM is not an issue. even if ram is a constraint, it will affect performance only, right?
I think I can bargain with client for desired result or desired performance.


Re: How to extract full text from clob where I have a distict select [message #585321 is a reply to message #585316] Thu, 23 May 2013 12:43 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jimmy_s_abraham@yahoo.com wrote on Thu, 23 May 2013 19:30
To avoid duplicates due to joins in the query


So maybe some join conditions are missing.

Regards
Michel
Re: How to extract full text from clob where I have a distict select [message #585322 is a reply to message #585319] Thu, 23 May 2013 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jimmy_s_abraham@yahoo.com wrote on Thu, 23 May 2013 19:33
For confirmation I can provide you the first 10 rows of both the outputs if you need
a. one which uses clob and without distinct (max characters we will get)
b. one which doesn't use clob and using distinct (only 4000 characters we will get)

Pls confirm
Thanks

Jim


No post the CREATE TABLE statement for each table (without any storage or tablespace clause) along with all constraints (primary key, unique, referential).
And the specification of what the query should do.
This is the only way to valid the query.

Regards
Michel
Re: How to extract full text from clob where I have a distict select [message #585323 is a reply to message #585320] Thu, 23 May 2013 12:45 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

Actually this script is developed by the senior developer and I am a DBA with less development skills.
When they tried to put value more than 4000 and when it errored out, I was brought into picture for the solution. They are calling this select script from xml. what I update you in my previous replies are my findings and I am seeking help from senior sql experts like you for the solutions if possible
Thanks all for your inputs

Regards

Jim
Re: How to extract full text from clob where I have a distict select [message #585324 is a reply to message #585320] Thu, 23 May 2013 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jimmy_s_abraham@yahoo.com wrote on Thu, 23 May 2013 19:36
I think RAM is not an issue. even if ram is a constraint, it will affect performance only, right?
I think I can bargain with client for desired result or desired performance.


If you have 100G of CLOB to compare I doubt you can afford 100G of PGA for your process.
So not only the performances will be awful but you will just end with an error.

Regards
Michel

Re: How to extract full text from clob where I have a distict select [message #585326 is a reply to message #585323] Thu, 23 May 2013 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jimmy_s_abraham@yahoo.com wrote on Thu, 23 May 2013 19:45
Actually this script is developed by the senior developer and I am a DBA with less development skills.
When they tried to put value more than 4000 and when it errored out, I was brought into picture for the solution. They are calling this select script from xml. what I update you in my previous replies are my findings and I am seeking help from senior sql experts like you for the solutions if possible
Thanks all for your inputs

Regards

Jim


Post what I asked to validate the query and I bet join conditions are missing.
And I will add, given how the query is written, your senior developer is not so skilled; outer joins must always be done in the end after all inner joins.

Regards
Michel

[Updated on: Thu, 23 May 2013 12:50]

Report message to a moderator

Re: How to extract full text from clob where I have a distict select [message #585330 is a reply to message #585326] Thu, 23 May 2013 13:09 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

CREATE TABLE "CFI_V53_UAT0427"."ENTITY_NOTES" 
   (	"ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ENTITY_CD" VARCHAR2(20) NOT NULL ENABLE, 
	"ENTITY_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"NOTE_DT" DATE NOT NULL ENABLE, 
	"CREATE_DT" DATE NOT NULL ENABLE, 
	"UPDATE_DT" DATE, 
	"CREATE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"UPDATE_ID" NUMBER(10,0), 
	"USER_ONBEHALFOF_ID" NUMBER(10,0) DEFAULT ((0)), 
	"DESCRIPTION" CLOB DEFAULT ' ', 
	"SYNC_ID" VARCHAR2(50), 
	"SYNC_SRC_CD" VARCHAR2(20), 
	"SUBJECT" VARCHAR2(250) DEFAULT 'N/A', 
	"NOTE_TYPE" VARCHAR2(10), 
	"REPORT_NOTE" NUMBER(3,0) DEFAULT '0', 
	"IS_ACTIVE" NUMBER(1,0) DEFAULT '1', 
	"VISIBILITY_LEVEL" NUMBER(10,0), 
	"VISIBILITY_USER_ROLE_CD" VARCHAR2(10), 
	"IS_PRIVATE" NUMBER(1,0) DEFAULT 0, 
	 CONSTRAINT "PK__ENTITY_NOTES__464936E5" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
 ENABLE, 
	 CONSTRAINT "FK_NOTETYPES" FOREIGN KEY ("NOTE_TYPE")
	  REFERENCES "CFI_V53_UAT0427"."NOTE_TYPE" ("TYPE_CD") ENABLE, 
	 CONSTRAINT "FK__ENTITY_NO__ENTIT__473D5B1E" FOREIGN KEY ("ENTITY_CD")
	  REFERENCES "CFI_V53_UAT0427"."ENTITY_DEFINITION" ("ENTITY_CD") ENABLE
   ) 


SQL> desc ENTITY_NOTES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        	NOT NULL NUMBER(10)
 ENTITY_CD                                 NOT NULL VARCHAR2(20)
 ENTITY_ID                                 NOT NULL NUMBER(10)
 NOTE_DT                                   NOT NULL DATE
 CREATE_DT                                 NOT NULL DATE
 UPDATE_DT                                          DATE
 CREATE_ID                                 NOT NULL NUMBER(10)
 UPDATE_ID                                          NUMBER(10)
 USER_ONBEHALFOF_ID                                 NUMBER(10)
 DESCRIPTION                                        CLOB
 SYNC_ID                                            VARCHAR2(50)
 SYNC_SRC_CD                                        VARCHAR2(20)
 SUBJECT                                            VARCHAR2(250)
 NOTE_TYPE                                          VARCHAR2(10)
 REPORT_NOTE                                        NUMBER(3)
 IS_ACTIVE                                          NUMBER(1)
 VISIBILITY_LEVEL                                   NUMBER(10)
 VISIBILITY_USER_ROLE_CD                            VARCHAR2(10)
 IS_PRIVATE                                         NUMBER(1)


CREATE TABLE "CFI_V53_UAT0427"."NOTE_TYPE" 
   (	"TYPE_CD" VARCHAR2(10) NOT NULL ENABLE, 
	"DESC_ID" VARCHAR2(40) NOT NULL ENABLE, 
	"SORT_ORDER" NUMBER(5,0), 
	"IS_ACTIVE" NUMBER(1,0), 
	"UI_BF" NUMBER(5,0) DEFAULT '0', 
	"BUS_BF" NUMBER(5,0) DEFAULT '0', 
	 CONSTRAINT "PK_NOTE_TYPE" PRIMARY KEY ("TYPE_CD")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 


SQL> desc NOTE_TYPE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TYPE_CD                                   NOT NULL VARCHAR2(10)
 DESC_ID                                   NOT NULL VARCHAR2(40)
 SORT_ORDER                                         NUMBER(5)
 IS_ACTIVE                                          NUMBER(1)
 UI_BF                                              NUMBER(5)
 BUS_BF                                             NUMBER(5)




CREATE TABLE "CFI_V53_UAT0427"."ENTITY_DEFINITION" 
   (	"ENTITY_CD" VARCHAR2(20) NOT NULL ENABLE, 
	"ENTITY_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	 CONSTRAINT "PK__ENTITY_DEFINITIO__108C44B2" PRIMARY KEY ("ENTITY_CD")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
  

SQL> desc ENTITY_DEFINITION
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENTITY_CD                                 NOT NULL VARCHAR2(20)
 ENTITY_NAME                               NOT NULL VARCHAR2(100)



CREATE TABLE "CFI_V53_UAT0427"."CONTACT_OWNER" 
   (	"ID" NUMBER(10,0) NOT NULL ENABLE, 
	"ORGANIZATION_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"CONTACT_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"IS_ACTIVE" NUMBER(1,0) DEFAULT ((1)) NOT NULL ENABLE, 
	"UI_BF" NUMBER(10,0), 
	"START_DT" DATE, 
	"STOP_DT" DATE, 
	"CREATE_DT" DATE NOT NULL ENABLE, 
	"CREATE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"UPDATE_DT" DATE, 
	"UPDATE_ID" NUMBER(10,0), 
	 CONSTRAINT "PK__CONTACT_OWNER__5BAE681F" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
ENABLE, 
	 CONSTRAINT "UNQ_CONTACT_OWNER" UNIQUE ("CONTACT_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
 ENABLE, 
	 CONSTRAINT "FK__CONTACT_O__CONTA__5D96B091" FOREIGN KEY ("CONTACT_ID")
	  REFERENCES "CFI_V53_UAT0427"."CONTACT" ("ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING



SQL> desc CONTACT_OWNER
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 ORGANIZATION_ID                           NOT NULL NUMBER(10)
 CONTACT_ID                                NOT NULL NUMBER(10)
 IS_ACTIVE                                 NOT NULL NUMBER(1)
 UI_BF                                              NUMBER(10)
 START_DT                                           DATE
 STOP_DT                                            DATE
 CREATE_DT                                 NOT NULL DATE
 CREATE_ID                                 NOT NULL NUMBER(10)
 UPDATE_DT                                          DATE
 UPDATE_ID                                          NUMBER(10)



CREATE TABLE "CFI_V53_UAT0427"."ORGANIZATION" 
   (	"ID" NUMBER(10,0) NOT NULL ENABLE, 
	"TYPE_CD" VARCHAR2(10) NOT NULL ENABLE, 
	"STATUS_CD" VARCHAR2(15) NOT NULL ENABLE, 
	"ORG_CODE" VARCHAR2(20), 
	"START_DT" DATE, 
	"STOP_DT" DATE, 
	"CREATE_DT" DATE NOT NULL ENABLE, 
	"CREATE_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"UPDATE_DT" DATE, 
	"UPDATE_ID" NUMBER(10,0), 
	"SYNC_ID" VARCHAR2(60), 
	"SYNC_SRC_CD" VARCHAR2(20), 
	"SYNC_UPDATE_DT" DATE, 
	"CM_ID" VARCHAR2(20), 
	 CONSTRAINT "PK__ORGANIZATION__2A170C8B" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS 
 ENABLE, 
	 CONSTRAINT "FK__ORGANIZAT__STATU__2BFF54FD" FOREIGN KEY ("STATUS_CD")
	  REFERENCES "CFI_V53_UAT0427"."ORGANIZATION_STATUS" ("STATUS_CD") ENABLE, 
	 CONSTRAINT "FK__ORGANIZAT__TYPE___2B0B30C4" FOREIGN KEY ("TYPE_CD")
	  REFERENCES "CFI_V53_UAT0427"."ORGANIZATION_TYPE" ("TYPE_CD") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  
  

SQL> desc ORGANIZATION
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 TYPE_CD                                   NOT NULL VARCHAR2(10)
 STATUS_CD                                 NOT NULL VARCHAR2(15)
 ORG_CODE                                           VARCHAR2(20)
 START_DT                                           DATE
 STOP_DT                                            DATE
 CREATE_DT                                 NOT NULL DATE
 CREATE_ID                                 NOT NULL NUMBER(10)
 UPDATE_DT                                          DATE
 UPDATE_ID                                          NUMBER(10)
 SYNC_ID                                            VARCHAR2(60)
 SYNC_SRC_CD                                        VARCHAR2(20)
 SYNC_UPDATE_DT                                     DATE
 CM_ID                                              VARCHAR2(20)

Re: How to extract full text from clob where I have a distict select [message #585331 is a reply to message #585330] Thu, 23 May 2013 13:25 Go to previous messageGo to next message
ThomasG
Messages: 3087
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hmmmm... the joins seem to be on primary keys, so there shouldn't be. any duplicates there.

Can you add entity_notes.id to the query without the distinct to see if there are perhaps duplicate entries in entity_notes to begin with?
Re: How to extract full text from clob where I have a distict select [message #585332 is a reply to message #585330] Thu, 23 May 2013 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the relations between the following? one to one? one to many? many to one? many to many?
entity_notes <-> contact_owner
contact_owner <-> organization

Note that there is no defined foreign key from contact_owner(organization_id) to organization(id), missing in the definition?
In the same way, the join condition between entity_notes(entity_id) and contact_owner(contact_id) is quite weird for an external eye (but we don't what is an entity and what is a contact [owner]). Note that there is unique constraint on entity_notes(entity_id) so one of these values may return several contact_owner rows.

Regards
Michel
Re: How to extract full text from clob where I have a distict select [message #585333 is a reply to message #585331] Thu, 23 May 2013 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ThomasG wrote on Thu, 23 May 2013 20:25
Hmmmm... the joins seem to be on primary keys, so there shouldn't be. any duplicates there.


They seem at first sight but they are not at second one. Smile

Regards
Michel

Re: How to extract full text from clob where I have a distict select [message #585337 is a reply to message #585333] Thu, 23 May 2013 14:27 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

As u know there is a BIG gap between people when asking for logic.

I added the column suggested by Thomas and found no difference with distinct and without distinct.

select 
--distinct 
                o.id "Organization ID",
                en.id "Entity Note id",
                en.entity_id "Contact ID",
                en.entity_cd "Note Entity Code",
                ed.entity_name "Note Entity",
                en.entity_id "Note Entity_id",
                en.note_dt "Note Date",
                en.create_dt "Create Date",
                en.update_dt "Update Date",
                FN_LOCALIZE_BI(nt.desc_id, 'EN', 'default') "Type",
                en.subject "Subject",
--                replace(replace(dbms_lob.substr(en.description, 4000, 1), chr(10), '\n'), chr(13), '\n') "Content"
--                replace(replace(to_char(dbms_lob.substr(en.description, 4001, 1)), chr(10), '\n'), chr(13), '\n') "Content"
replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 1)),chr(10),'\n'),chr(13),'\n') 
||''|| 
replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 4001)),chr(10),'\n'),chr(13),'\n') 
"Content"            
from 
        entity_notes en
        left join note_type nt on nt.type_cd = en.note_type
        left join entity_definition ed on en.entity_cd = ed.entity_cd
        inner join contact_owner co on co.contact_id = en.entity_id
        inner join organization o on o.id = co.organization_id
 where 
          (en.sync_src_cd = 'MANUAL' or en.sync_src_cd is null)
order by 2


I didnt get much help from the Sr. Developer.

I know only few (<100) among the millions have characters greater than 4000.
So is there any way to keep the original script as it is without distinct and union it with a view which is having more than 4000 characters?

Thanks

Jim
Re: How to extract full text from clob where I have a distict select [message #585338 is a reply to message #585337] Thu, 23 May 2013 14:35 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

As per my calculation max length of the clob column is 154266. Concatenating means I have to add almost 39 lines for the concatenation itself in the main code which seems to be ugly for the main code while audit comes
Re: How to extract full text from clob where I have a distict select [message #585339 is a reply to message #585338] Thu, 23 May 2013 14:53 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

Like the data for the maxlength of clob column less than 4001 UNION data for the maxlength of clob column greater than 4001
Re: How to extract full text from clob where I have a distict select [message #585340 is a reply to message #585339] Thu, 23 May 2013 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer to my questions.

Regards
Michel
Re: How to extract full text from clob where I have a distict select [message #585341 is a reply to message #585338] Thu, 23 May 2013 15:22 Go to previous messageGo to next message
ThomasG
Messages: 3087
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
jimmy_s_abraham@yahoo.com wrote on Thu, 23 May 2013 21:35
As per my calculation max length of the clob column is 154266. Concatenating means I have to add almost 39 lines for the concatenation itself in the main code which seems to be ugly for the main code while audit comes


While that is true, the problem lies in the fact that a CLOB is normally passed to the client application, and then handled on the client side of the procces. A "Sr" developer that has to ask a DBA to split a CLOB in the SQL is a somewhat weird situation. (but I have been in weird situations myself Smile )
Re: How to extract full text from clob where I have a distict select [message #585371 is a reply to message #585341] Fri, 24 May 2013 07:01 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

I believe the relationship is many to many and abt the logic still I didnt get a proper reply from the Sr. Dev.
As per him he is getting his output except the full text on CLOB
Re: How to extract full text from clob where I have a distict select [message #585386 is a reply to message #585371] Fri, 24 May 2013 09:57 Go to previous messageGo to next message
jimmy_s_abraham@yahoo.com
Messages: 14
Registered: May 2013
Location: Toronto, Canada
Junior Member

This is my findings and the solution I am going to give to the team with my limited knowledge in development. Pls have a look and suggest


Total no of description which is not null= 2966986

No of description
<4001 	= 2962477
>4000 	= 4509 
>8000 	= 1262
>12000 	= 698
>16000 	= 409
>20000 	= 265
>24000 	= 177
>28000 	= 112
>32000 	= 79
>36000 	= 53
>40000 	= 36
>44000 	= 30
>48000 	= 25
>52000 	= 15
>56000 	= 11
>60000 	= 10
>64000 	= 8
>68000 	= 8
>72000 	= 8
>76000 	= 8
>80000 	= 7
>84000 	= 3
>100000  = 3



Lowest Length of DESCRIPTION = 18
Highest length of DESCRIPTION = 154266


Create 2 views as per conditions

create view VIEWNAME1_V as
select 
		o.id "Organization ID",
		en.id "Entity Note id",
		en.entity_id "Contact ID",
		en.entity_cd "Note Entity Code",
		ed.entity_name "Note Entity",
		en.entity_id "Note Entity_id",
		en.note_dt "Note Date",
		en.create_dt "Create Date",
		en.update_dt "Update Date",
		FN_LOCALIZE_BI(nt.desc_id, 'EN', 'default') "Type",
		en.subject "Subject",
		replace(replace(dbms_lob.substr(en.description, 4000, 1), chr(10), '\n'), chr(13), '\n') "Content"
from 
		entity_notes en
		left join note_type nt on nt.type_cd = en.note_type
		left join entity_definition ed on en.entity_cd = ed.entity_cd
		inner join contact_owner co on co.contact_id = en.entity_id
		inner join organization o on o.id = co.organization_id
 where 
		(en.sync_src_cd = 'MANUAL' or en.sync_src_cd is null) and
		DBMS_LOB.GETLENGTH(description)<=4000




create view VIEWNAME2_V as
select 
		o.id "Organization ID",
		en.id "Entity Note id",
		en.entity_id "Contact ID",
		en.entity_cd "Note Entity Code",
		ed.entity_name "Note Entity",
		en.entity_id "Note Entity_id",
		en.note_dt "Note Date",
		en.create_dt "Create Date",
		en.update_dt "Update Date",
		FN_LOCALIZE_BI(nt.desc_id, 'EN', 'default') "Type",
		en.subject "Subject",
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 1)),chr(10),'\n'),chr(13),'\n') ||''|| 
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 4001)),chr(10),'\n'),chr(13),'\n') ||''|| 
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 8001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 12001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 16001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 20001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 24001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 28001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 32001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 36001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 40001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 44001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 48001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 52001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 56001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 60001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 64001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 68001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 72001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 76001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 80001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 84001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 88001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 92001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 96001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 100001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 104001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 108001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 112001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 116001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 120001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 124001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 128001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 132001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 136001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 140001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 144001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 148001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 152001)),chr(10),'\n'),chr(13),'\n') ||''||
		replace(replace(to_clob(dbms_lob.substr(en.description, 4000, 156001)),chr(10),'\n'),chr(13),'\n') 
                --May need to add more concatenation lines as per the max length of the description field
		"Content"            
from 
		entity_notes en
		left join note_type nt on nt.type_cd = en.note_type
		left join entity_definition ed on en.entity_cd = ed.entity_cd
		inner join contact_owner co on co.contact_id = en.entity_id
		inner join organization o on o.id = co.organization_id
 where 
		(en.sync_src_cd = 'MANUAL' or en.sync_src_cd is null) and
		DBMS_LOB.GETLENGTH(description)>4000






Final script replacing existing script

select 
	* 
from 
	VIEWNAME1_V
union
	VIEWNAME2_V





Re: How to extract full text from clob where I have a distict select [message #585387 is a reply to message #585386] Fri, 24 May 2013 10:24 Go to previous message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this work?
Isn't it better to fix the error than to hide it?

Regards
Michel
Previous Topic: dbms_lock.sleep
Next Topic: Find and trace custom view/procedure...
Goto Forum:
  


Current Time: Tue Jul 22 06:52:41 CDT 2014

Total time taken to generate the page: 0.14779 seconds