How to insert data in the CLOB column (Oracle)

Question

I have table: TBL_SAMPLE

SAMPLE_ID - Primary Key
SAMPLE_CD - String
statusDateTime - DateTime
DATA_AREA - CLOB

I have this schema:

<schema>
<parameterizationId label="Samp Id" mapField="SAMPLE_ID" dataType="string"/>
<statusDateTime required="true" dataType="dateTime" default="%CurrentDateTime" mapField="STATUS_UPD_DTTM"/>
<user default="%CurrentUser" mapField="USER_ID" dataType="string"/>
<dataArea type="group" mapXML="DATA_AREA">
    <accountId dataType="string"/>
    <saId dataType="string"/>
    <saTypeCode dataType="string"/>
    <endDate dataType="date"/>
    <beginBalAmt dataType="number"/>
</dataArea>

I need to populate the DATE_AREA CLOB column and should look like this: (EXPECTED)

<accountId>0854215</accountId><saId>745152</saId><saTypeCode>TES</saTypeCode><endDate>16-Dec-2019</endDate><beginBalAmt>10525</beginBalAmt>

I have this existing SQL:

INSERT INTO TBL_DIFF
SELECT
SA.ACCT_ID,SA.SA_ID,SA.SA_TYPE_CD,'31-DEC-16',0
FROM CI_SA SA

I want to populate the DATA_AREA column using this sql statement with the expected output I provided above. How to do that?

see https://stackoverflow.com/questions/3890567/how-to-write-oracle-insert-script-with-one-field-as-clob — pifor, May 28 '20 at 06:04

Popeye · Answer 1 · 2020-05-28T07:08:42.417

0

You just need to use concatanation as follows:

INSERT INTO TBL_DIFF
    SELECT
        '<accountId>' || SA.ACCT_ID || '</accountId><saId>' || SA.SA_ID || '</saId><saTypeCode>' || SA.SA_TYPE_CD || '</saTypeCode><endDate>'
        || END_DATE || '</endDate><beginBalAmt>' || BEGINBALAMT || '</beginBalAmt>'
    FROM
        (
            SELECT
                SA.ACCT_ID,
                SA.SA_ID,
                SA.SA_TYPE_CD,
                '31-DEC-16' END_DATE,
                0 BEGINBALAMT
            FROM
                CI_SA SA
            WHERE
                SA.SA_TYPE_CD = 'LOAN'
        );

edited May 28 '20 at 07:08

answered May 28 '20 at 06:50

Popeye

35,427
4
10
31

Hi, what I need is to populate the DATA_AREA (CLOB) column using the result of the sql statement I've provided (see existing SQL part) and not the schema. – 7Eleven May 28 '20 at 07:00
Thanks Tejash! But I want it to update or insert the results of the SQL you've provided in the DATA_AREA column (CLOB) in my TBL_SAMPLE. Is that possible? – 7Eleven May 28 '20 at 07:45
Yes, I have written it in my answer, Use `TO_CLOB` to insert this select statement value into your table. – Popeye May 28 '20 at 08:14

How to insert data in the CLOB column (Oracle)

1 Answers1