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  |
|
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 #585308 is a reply to message #585304] |
Thu, 23 May 2013 11:45   |
|
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 #585312 is a reply to message #585311] |
Thu, 23 May 2013 12:21   |
|
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 #585315 is a reply to message #585312] |
Thu, 23 May 2013 12:29   |
|
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
|
|
|
|
|
|
|
|
|
|
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   |
|
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 #585326 is a reply to message #585323] |
Thu, 23 May 2013 12:48   |
 |
Michel Cadot
Messages: 68765 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
jimmy_s_abraham@yahoo.com wrote on Thu, 23 May 2013 19:45Actually 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   |
|
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 #585337 is a reply to message #585333] |
Thu, 23 May 2013 14:27   |
|
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 #585386 is a reply to message #585371] |
Fri, 24 May 2013 09:57   |
|
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
|
|
|
|
Goto Forum:
Current Time: Tue Aug 19 23:04:06 CDT 2025
|