Am I getting this error when there is special character in a CLOB that I am trying to retrieve? If yes, is there any way of getting around it. I have " - " and " / " in my CLOB.
Query:
SELECT /*+ index (audit_v3 IX_AUDIT_V3_AK1) */
audit_v3.adt_id,
audit_v3.app_id,
audit_evt_type_v3.adt_evt_typ_name,
audit_v3.appl_adt_evt_ts,
audit_v3.xml_dtd_id,
audit_v3.subj_key_val_txt,
audit_v3.subj_ownr_key_val_txt,
audit_v3.host_server_name,
audit_v3.adt_evt_stat_code,
audit_v3.appl_userid,
audit_v3.adt_xml_desc,
audit_v3.group_name
FROM audit_v3,
audit_evt_type_v3
WHERE audit_v3.adt_evt_typ_id = audit_evt_type_v3.adt_evt_typ_id AND
XMLTYPE(audit_v3.adt_xml_desc).extract('/EVENT/UPDATE_USER/USER/ORG_LIST/ORG').extract('/ORG/ID/text()').getStringVal() = '16178' AND
XMLTYPE(audit_v3.adt_xml_desc).extract('/EVENT/UPDATE_USER/USER/SEC_GRP_LIST/SEC_GRP').extract('/SEC_GRP/NAME/text()').getStringVal() = 'cspinquiry'
CLOB Sample:
<EVENT>
<UPDATE_USER>
<ADDED_BY>
<USER_ID>X951XXX</USER_ID>
</ADDED_BY>
<USER>
<FIRST>Steven</FIRST>
<LAST>Heroly</LAST>
<STATUS>Active</STATUS>
<ORG_LIST>
<ORG>
<ID>16178</ID>
<NAME>All</NAME>
</ORG>
</ORG_LIST>
<SEC_GRP_LIST>
<SEC_GRP><NAME>cspupdatecustpayee</NAME><DESCRIPTION>Add/Update/Delete CSP Customer Payees</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatepayment</NAME><DESCRIPTION>Add/Update/Delete CSP Payment</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>adminreports</NAME><DESCRIPTION>Administrate Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>billopscsr</NAME><DESCRIPTION>Bill Operations CSR</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>bccsr</NAME><DESCRIPTION>Business centre CSR</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>bcsa</NAME><DESCRIPTION>Business centre security administrator high</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspapprovefundacct</NAME><DESCRIPTION>CSP Approve Funding Account</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspCIBcustmaint</NAME><DESCRIPTION>CSP CIB Customer Maintenance</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspinquiry</NAME><DESCRIPTION>CSP CSR Inquiry</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspcustpasswdchg</NAME><DESCRIPTION>CSP Customer Password Change</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspfundacctmgmtmvnt</NAME><DESCRIPTION>CSP Funding Account Management - Oracle</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatebill</NAME><DESCRIPTION>CSP Update Bill</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspviewbill</NAME><DESCRIPTION>CSP View Bill</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>metaorgadmin</NAME><DESCRIPTION>Oracle Org Administrator</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>ofxcspinquirylogviewerclient</NAME><DESCRIPTION>OFX CSP Inquiry and Log Viewer - Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>orderclosedcustomercd</NAME><DESCRIPTION>Order Closed Customer CD</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>refundCSPfees</NAME><DESCRIPTION>Refund CSP Fees</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspreleasesmarttrans</NAME><DESCRIPTION>Release Smart Balance Trans</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sahighassignCSPsecgrps</NAME><DESCRIPTION>SA High Assign CSP</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sahighassignCSPInquirysecgrp</NAME><DESCRIPTION>SA High Assign CSP Inquiry Only</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sahighassignviewreports</NAME><DESCRIPTION>SA High Assign View Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>salowviewreports</NAME><DESCRIPTION>SA Low View Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sa</NAME><DESCRIPTION>Security administrator high</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecustidentity</NAME><DESCRIPTION>Update CSP Customer Identity</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecustidentityclient</NAME><DESCRIPTION>Update CSP Customer Identity Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>updatemetorgtranlimit</NAME><DESCRIPTION>Update Oracle Org Tran Limit</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>updateorganizationtranlimit</NAME><DESCRIPTION>Update Organization Tran Limit</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecust</NAME><DESCRIPTION>Update/Deactivate CSP Customer</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecustclient</NAME><DESCRIPTION>Update/Deactivate CSP Customer Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>report</NAME><DESCRIPTION>View Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>rptsecbusctr</NAME><DESCRIPTION>View Security Reports - Bus Ctr</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>rptsecclient</NAME><DESCRIPTION>View Security Reports - Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>rptsecinfosec</NAME><DESCRIPTION>View Security Reports - Info Sec</DESCRIPTION></SEC_GRP>
</SEC_GRP_LIST>
<CHANGE>
<VALUE>
<PRIOR>CSP Funding Account Management - Oracle/Update CSP Customer Identity/CSP Approve Funding Account/CSP CSR Inquiry/CSP CIB Customer Maintenance/Administrate Reports/Update/Deactivate CSP Customer/View Security Reports - Client/Update Organization Tran Limit/SA Low View Reports/Update Oracle Org Tran Limit/View Security Reports - Bus Ctr/Add/Update/Delete CSP Payment/OFX CSP Inquiry and Log Viewer - Client/Update/Deactivate CSP Customer Client/CSP Customer View Update/Business centre CSR/Security administrator high/Bill Operations CSR/Update CSP Customer Identity Client/Order Closed Customer CD/SA High Assign CSP Inquiry Only/View Reports/Refund CSP Fees/Oracle Org Administrator/View Security Reports - Info Sec/Add/Update/Delete CSP Customer Payees/CSP View Bill/CSP Customer Password Change/SA High Assign CSP/Release Smart Balance Trans/Business centre security administrator high/CSP Update Bill/SA High Assign View Reports</PRIOR>
<NEW>CSP Funding Account Management - Oracle/CSP Approve Funding Account/Update CSP Customer Identity/CSP CSR Inquiry/CSP CIB Customer Maintenance/Administrate Reports/Update/Deactivate CSP Customer/View Security Reports - Client/Update Organization Tran Limit/SA Low View Reports/Update Oracle Org Tran Limit/View Security Reports - Bus Ctr/Add/Update/Delete CSP Payment/Update/Deactivate CSP Customer Client/OFX CSP Inquiry and Log Viewer - Client/Business centre CSR/Bill Operations CSR/Security administrator high/Update CSP Customer Identity Client/Order Closed Customer CD/View Reports/SA High Assign CSP Inquiry Only/Refund CSP Fees/Oracle Org Administrator/View Security Reports - Info Sec/Add/Update/Delete CSP Customer Payees/CSP View Bill/CSP Customer Password Change/SA High Assign CSP/Release Smart Balance Trans/Business centre security administrator high/CSP Update Bill/SA High Assign View Reports</NEW>
<DESCRIPTION>Security Groups</DESCRIPTION></VALUE>
</CHANGE>
</USER>
</UPDATE_USER>
<CONTACT_HISTORY>
<USER>
<USER_ID>X951XXX</USER_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>Heroly</LAST_NAME>
</USER>
</CONTACT_HISTORY>
</EVENT>
Could anyone offer some immediate help? Thank you.
I tried to see if I can retrieve some infomation from the CLOB and I ran this. The second dbms_output gives me the correct value, but when I run the First dbms_output, I get
ORA-20000: ORU-10028: line length overflow; limit of 255 chars per line.
Sample Code:
declare
xml xmltype ;
begin
xml := xmltype('<EVENT>
<UPDATE_USER>
<ADDED_BY>
<USER_ID>X951XXX</USER_ID>
</ADDED_BY>
<USER>
<FIRST>Steven</FIRST>
<LAST>Heroly</LAST>
<STATUS>Active</STATUS>
<ORG_LIST>
<ORG>
<ID>16178</ID>
<NAME>All</NAME>
</ORG>
</ORG_LIST>
<SEC_GRP_LIST>
<SEC_GRP><NAME>cspupdatecustpayee</NAME><DESCRIPTION>Add/Update/Delete CSP Customer Payees</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatepayment</NAME><DESCRIPTION>Add/Update/Delete CSP Payment</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>adminreports</NAME><DESCRIPTION>Administrate Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>billopscsr</NAME><DESCRIPTION>Bill Operations CSR</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>bccsr</NAME><DESCRIPTION>Business centre CSR</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>bcsa</NAME><DESCRIPTION>Business centre security administrator high</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspapprovefundacct</NAME><DESCRIPTION>CSP Approve Funding Account</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspCIBcustmaint</NAME><DESCRIPTION>CSP CIB Customer Maintenance</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspinquiry</NAME><DESCRIPTION>CSP CSR Inquiry</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspcustpasswdchg</NAME><DESCRIPTION>CSP Customer Password Change</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspfundacctmgmtmvnt</NAME><DESCRIPTION>CSP Funding Account Management - Oracle</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatebill</NAME><DESCRIPTION>CSP Update Bill</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspviewbill</NAME><DESCRIPTION>CSP View Bill</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>metaorgadmin</NAME><DESCRIPTION>Oracle Org Administrator</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>ofxcspinquirylogviewerclient</NAME><DESCRIPTION>OFX CSP Inquiry and Log Viewer - Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>orderclosedcustomercd</NAME><DESCRIPTION>Order Closed Customer CD</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>refundCSPfees</NAME><DESCRIPTION>Refund CSP Fees</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspreleasesmarttrans</NAME><DESCRIPTION>Release Smart Balance Trans</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sahighassignCSPsecgrps</NAME><DESCRIPTION>SA High Assign CSP</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sahighassignCSPInquirysecgrp</NAME><DESCRIPTION>SA High Assign CSP Inquiry Only</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sahighassignviewreports</NAME><DESCRIPTION>SA High Assign View Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>salowviewreports</NAME><DESCRIPTION>SA Low View Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>sa</NAME><DESCRIPTION>Security administrator high</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecustidentity</NAME><DESCRIPTION>Update CSP Customer Identity</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecustidentityclient</NAME><DESCRIPTION>Update CSP Customer Identity Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>updatemetorgtranlimit</NAME><DESCRIPTION>Update Oracle Org Tran Limit</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>updateorganizationtranlimit</NAME><DESCRIPTION>Update Organization Tran Limit</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecust</NAME><DESCRIPTION>Update/Deactivate CSP Customer</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>cspupdatecustclient</NAME><DESCRIPTION>Update/Deactivate CSP Customer Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>report</NAME><DESCRIPTION>View Reports</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>rptsecbusctr</NAME><DESCRIPTION>View Security Reports - Bus Ctr</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>rptsecclient</NAME><DESCRIPTION>View Security Reports - Client</DESCRIPTION></SEC_GRP>
<SEC_GRP><NAME>rptsecinfosec</NAME><DESCRIPTION>View Security Reports -Info Sec</DESCRIPTION></SEC_GRP>
</SEC_GRP_LIST>
<CHANGE>
<VALUE>
<PRIOR>CSP Funding Account Management - Oracle/Update CSP Customer Identity/CSP Approve Funding Account/CSP CSR Inquiry/CSP CIB Customer Maintenance/Administrate Reports/Update/Deactivate CSP Customer/View Security Reports - Client/Update Organization Tran Limit/SA Low View Reports/Update Oracle Org Tran Limit/View Security Reports - Bus Ctr/Add/Update/Delete CSP Payment/OFX CSP Inquiry and Log Viewer - Client/Update/Deactivate CSP Customer Client/CSP Customer View Update/Business centre CSR/Security administrator high/Bill Operations CSR/Update CSP Customer Identity Client/Order Closed Customer CD/SA High Assign CSP Inquiry Only/View Reports/Refund CSP Fees/Oracle Org Administrator/View Security Reports - Info Sec/Add/Update/Delete CSP Customer Payees/CSP View Bill/CSP Customer Password Change/SA High Assign CSP/Release Smart Balance Trans/Business centre security administrator high/CSP Update Bill/SA High Assign View Reports</PRIOR>
<NEW>CSP Funding Account Management - Oracle/CSP Approve Funding Account/Update CSP Customer Identity/CSP CSR Inquiry/CSP CIB Customer Maintenance/Administrate Reports/Update/Deactivate CSP Customer/View Security Reports - Client/Update Organization Tran Limit/SA Low View Reports/Update Oracle Org Tran Limit/View Security Reports - Bus Ctr/Add/Update/Delete CSP Payment/Update/Deactivate CSP Customer Client/OFX CSP Inquiry and Log Viewer - Client/Business centre CSR/Bill Operations CSR/Security administrator high/Update CSP Customer Identity Client/Order Closed Customer CD/View Reports/SA High Assign CSP Inquiry Only/Refund CSP Fees/Oracle Org Administrator/View Security Reports - Info Sec/Add/Update/Delete CSP Customer Payees/CSP View Bill/CSP Customer Password Change/SA High Assign CSP/Release Smart Balance Trans/Business centre security administrator high/CSP Update Bill/SA High Assign View Reports</NEW>
<DESCRIPTION>Security Groups</DESCRIPTION></VALUE>
</CHANGE>
</USER>
</UPDATE_USER>
<CONTACT_HISTORY>
<USER>
<USER_ID>X951XXX</USER_ID>
<FIRST_NAME>Steven</FIRST_NAME>
<LAST_NAME>Heroly</LAST_NAME>
</USER>
</CONTACT_HISTORY>
</EVENT>') ;
dbms_output.put_line(xml.extract('/EVENT/UPDATE_USER/USER/SEC_GRP_LIST/SEC_GRP/NAME/text()').getStringVal()) ;
--dbms_output.put_line(xml.extract('/EVENT/UPDATE_USER/USER/ORG_LIST/ORG/ID/text()').getStringVal()) ;
end ;