I have an XML data in Oracle with ClOB as Datatype. I have to read that clob data as Varchar.
the following is the sample data.
"<POSLog>
<Transaction>
<UnitID>1101</UnitID>
<WorkstationID>1</WorkstationID>
<TransSequenceNumber>22142</TransSequenceNumber>
<TransactionType>1001</TransactionType>
<OperatorID>1</OperatorID>
<BusinessDayDate>2017-12-04</BusinessDayDate>
<BeginDateTime>2017-12-04T18:38:43-06:00</BeginDateTime>
<EndDateTime>2017-12-04T18:39:46-06:00</EndDateTime>
<TransStatus>COMPLETE</TransStatus>
<RetailTransaction>
<LineItem ItemType="2001" POSIDtype="1">
<RetailSequenceNumber>1</RetailSequenceNumber>
<EntryMethod>SCAN</EntryMethod>
<ActualSalesPrice>6.99</ActualSalesPrice>
<TotalAmount>15.28</TotalAmount>
<Amount>13.98</Amount>
<POSItemID>634392067282</POSItemID>
<Quantity>2</Quantity>
<RegularPrice>6.99</RegularPrice>
<SalesUnitOfMeasure>EA</SalesUnitOfMeasure>
<DTL_TransactionType>SALE</DTL_TransactionType>
<Tax TaxType="ZSTX">
<TaxSequencenumber>2</TaxSequencenumber>
<Amount>0.63</Amount>
</Tax>
<Tax TaxType="ZCUX">
<TaxSequencenumber>3</TaxSequencenumber>
<Amount>0.25</Amount>
</Tax>
<Tax TaxType="ZCYX">
<TaxSequencenumber>4</TaxSequencenumber>
<Amount>0.42</Amount>
</Tax></LineItem>
<LineItem ItemType="2001" POSIDtype="1">
<RetailSequenceNumber>3</RetailSequenceNumber>
<EntryMethod>SCAN</EntryMethod>
<ActualSalesPrice>4.99</ActualSalesPrice>
<TotalAmount>16.35</TotalAmount>
<Amount>14.97</Amount>
<POSItemID>037049923991</POSItemID>
<Quantity>3</Quantity>
<RegularPrice>4.99</RegularPrice>
<SalesUnitOfMeasure>EA</SalesUnitOfMeasure>
<DTL_TransactionType>SALE</DTL_TransactionType>
<Tax TaxType="ZSTX">
<TaxSequencenumber>2</TaxSequencenumber>
<Amount>0.67</Amount>
</Tax>
<Tax TaxType="ZCUX">
<TaxSequencenumber>3</TaxSequencenumber>
<Amount>0.26</Amount>
</Tax>
<Tax TaxType="ZCYX">
<TaxSequencenumber>4</TaxSequencenumber>
<Amount>0.45</Amount>
</Tax></LineItem>
<Tender TenderType="VOUCHER">
<Amount>31.63</Amount>
<AmountCurrency>USD</AmountCurrency>
<TenderSequenceNumber>4</TenderSequenceNumber>
<TenderSerialNumber>6299405030900342</TenderSerialNumber>
<TenderID>ISD_GIFT_CARD</TenderID>
</Tender>
</RetailTransaction>
</Transaction>
</POSLog>"
The maximum size of a varchar2 is 4,000 bytes*. So you need to call dbms_lob to get these first characters, which will return it as a varchar2:
create table t (
x clob
);
insert into t values ('<POSLog>
<Transaction>
<UnitID>1101</UnitID>
<WorkstationID>1</WorkstationID>
<TransSequenceNumber>22142</TransSequenceNumber>
<TransactionType>1001</TransactionType>
<OperatorID>1</OperatorID>
<BusinessDayDate>2017-12-04</BusinessDayDate>
<BeginDateTime>2017-12-04T18:38:43-06:00</BeginDateTime>
<EndDateTime>2017-12-04T18:39:46-06:00</EndDateTime>
<TransStatus>COMPLETE</TransStatus>
<RetailTransaction>
<LineItem ItemType="2001" POSIDtype="1">
<RetailSequenceNumber>1</RetailSequenceNumber>
<EntryMethod>SCAN</EntryMethod>
<ActualSalesPrice>6.99</ActualSalesPrice>
<TotalAmount>15.28</TotalAmount>
<Amount>13.98</Amount>
<POSItemID>634392067282</POSItemID>
<Quantity>2</Quantity>
<RegularPrice>6.99</RegularPrice>
<SalesUnitOfMeasure>EA</SalesUnitOfMeasure>
<DTL_TransactionType>SALE</DTL_TransactionType>
<Tax TaxType="ZSTX">
<TaxSequencenumber>2</TaxSequencenumber>
<Amount>0.63</Amount>
</Tax>
<Tax TaxType="ZCUX">
<TaxSequencenumber>3</TaxSequencenumber>
<Amount>0.25</Amount>
</Tax>
<Tax TaxType="ZCYX">
<TaxSequencenumber>4</TaxSequencenumber>
<Amount>0.42</Amount>
</Tax>
</LineItem>
<LineItem ItemType="2001" POSIDtype="1">
<RetailSequenceNumber>3</RetailSequenceNumber>
<EntryMethod>SCAN</EntryMethod>
<ActualSalesPrice>4.99</ActualSalesPrice>
<TotalAmount>16.35</TotalAmount>
<Amount>14.97</Amount>
<POSItemID>037049923991</POSItemID>
<Quantity>3</Quantity>
<RegularPrice>4.99</RegularPrice>
<SalesUnitOfMeasure>EA</SalesUnitOfMeasure>
<DTL_TransactionType>SALE</DTL_TransactionType>
<Tax TaxType="ZSTX">
<TaxSequencenumber>2</TaxSequencenumber>
<Amount>0.67</Amount>
</Tax>
<Tax TaxType="ZCUX">
<TaxSequencenumber>3</TaxSequencenumber>
<Amount>0.26</Amount>
</Tax>
<Tax TaxType="ZCYX">
<TaxSequencenumber>4</TaxSequencenumber>
<Amount>0.45</Amount>
</Tax>
</LineItem>
<Tender TenderType="VOUCHER">
<Amount>31.63</Amount>
<AmountCurrency>USD</AmountCurrency>
<TenderSequenceNumber>4</TenderSequenceNumber>
<TenderSerialNumber>6299405030900342</TenderSerialNumber>
<TenderID>ISD_GIFT_CARD</TenderID>
</Tender>
</RetailTransaction>
</Transaction>
</POSLog>');
commit;
select dump(dbms_lob.substr(x, 4000, 1))
from t;
DUMP(DBMS_LOB.SUBSTR(X,4000,1))
Typ=1 Len=2567: 60,80,79,83,76,111,103,62,10,32,32,60,84,114,97,... etc.Though if the document is larger than this, you'll mangle the XML...
* 32,767 in PL/SQL and in SQL from 12.1 and up if you have max_string_size=extended