Hi Guys ..
I have a table which has a Column with CLOB data and the table is a base table means this data is required for implementing the product. I need to put this in text file and send it to implementation team .
There are some rows which has data length more than 4000 characters and to export that in insert scripts are not possbile .
One way left is I have to write pl/sql script to insert it but we have around 5000 rows for each row writing a PL/sql block is not feasible .
is there any out where I can generate the data as part of insert and update like below .
Here is the sample script how we put in the text file . First we put the insert stmt and next the update stmt , because the insert can nt accomdate more than 4000 characters.
Any thoughts and idea ??
Oracle Verion is :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
DDL
create table CORXPRADM.MOCK_ENTRIES
(
ENTRY_ID NUMBER(31) not null,
REQUEST_NAME NVARCHAR2(128),
REQUEST_NAMESPACE NVARCHAR2(256),
ENABLE_REQUEST_RGX NUMBER,
RESPONSE_TEMPLATE CLOB,
constraint PK_MOCK_ENTRIES primary key (ENTRY_ID)
);
--The generation scripts I need in the below format
BEGIN
INSERT INTO mock_entries ( entry_id , request_name , request_namespace , enable_request_rgx , response_template )
VALUES (21, 'AcctInqRq', 'com.fnf.acctinq.v2_0', 1, NULL);
g_clob := '#if ($AcctType == "INV")
<com.fnf.acctinq.v2_0:AcctInqRs xmlns="http://www.ifxforum.org/IFX_150" xmlns:com.fnf.acctinq.v2_0="dummy"
<Status>
<StatusCode>0</StatusCode>
<Severity>Info</Severity>
<StatusDesc>Success</StatusDesc>
</Status>
<RqUID>$Service-RqUID</RqUID>
<DepAcctId>
<AcctId>$AcctId</AcctId>
<AcctType>INV</AcctType>
<BankInfo/>
</DepAcctId>
<UpDt>2008-11-06T09:30:47.0Z</UpDt>
<BankAcctRec>
<DepAcctId>
<AcctId>$AcctId</AcctId>
<AcctType>INV</AcctType>
<BankInfo>
<RefInfo>
<RefType>Cost Center</RefType>
<RefId>1059</RefId>
</RefInfo>
</BankInfo>
</DepAcctId>
<BankAcctInfo>
<CurCode>USD</CurCode>
<ContactInfo>
<PostAddr>
<Addr1>123 main st</Addr1>
<City>Tampa Bay</City>
<StateProv>FL</StateProv>
<PostalCode>33647</PostalCode>
<Country>USA</Country>
<AddrType>Mailing</AddrType>
</PostAddr>
</ContactInfo>
</BankAcctInfo>
<BankAcctStatus>
<BankAcctStatusCode>OP</BankAcctStatusCode>
<StatusDesc/>
<StatusModBy/>
</BankAcctStatus>
</BankAcctRec>
<com.fnf.acctinq.v2_0:DepAcctRec>
<com.fnf:DepAcctInfo>
<com.fnf:AccountTitle>Sandy Sanders</com.fnf:AccountTitle>
<OpenDt>2004-09-21</OpenDt>
<TINInfo>
<TINType>SSN</TINType>
<TaxId>222334444</TaxId>
</TINInfo>
<com.fnf:IntAcctInfo>
<SPName/>
</com.fnf:IntAcctInfo>
<PersonInfo>
<NameAddrType/>
<FullName>Sandy Sanders</FullName>
<ContactInfo>
<PostAddr>
<Addr1>9625 Northwest Blvd</Addr1>
<Addr2>Suite 101</Addr2>'
;
update mock_entries set response_template =g_clob
WHERE entry_id=21;
END;
/