Hi Experts,
i have a script file which contains XML data and it tries to load into CLOB table.
If I am running this through SQL developer it is working fine but the same thing I am trying to execute from command prompt it is erroring out.
I am using oracle 12c R2. Please share your thoughts and approaches
The table desc is as below:
Name Null? Type
------------------ ----- --------------
MESSAGE_ID NUMBER(10)
NAME VARCHAR2(4000)
APPLICATIONNAME VARCHAR2(400)
SUBJECT VARCHAR2(4000)
FROMEMAIL VARCHAR2(4000)
FROMNAME VARCHAR2(4000)
BCCEMAIL VARCHAR2(4000)
TEMPLATE CLOB
TEMPLATE_ENCRYPTED CLOB
TEMPLATE_BLOB BLOB
SET DEFINE OFF;
Insert into ALERTMESSAGESTAGING (MESSAGE_ID,NAME,APPLICATIONNAME,SUBJECT,FROMEMAIL,FROMNAME,BCCEMAIL,TEMPLATE,TEMPLATE_ENCRYPTED,TEMPLATE_BLOB) values (null,'OAO_PENDING_APPLICATION','AccountOpening','Your application is pending',null,null,null,TO_CLOB('<!DOCTYPE html>
<html xmlns="http://www.w3.org/1/xhtml">
<head><meta charset="utf-8">
<!-- utf-8 works for most cases -->
<meta name="viewport" content="width=device-width">
<!-- Forcing initial-scale shouldn't be necessary -->
<meta http-equiv="X-UA-Compatible" content="IE=edg')
|| TO_CLOB('e"><!-- Use the latest (edge) version of IE rendering engine -->
<meta name="x-apple-disable-message-reformatting">
<!-- Disable auto-scale in iOS 10 Mail entirely -->
<title>Bankname</title>
<!-- The title tag shows in email notifications, like Android 4.4. -->
</head>
<body wid')
|| TO_CLOB('th="100%" bgcolor="gray" style="width:100%; background:white; margin: 0 !important; padding:0 !important; mso-line-height-rule: exactly;">
<center style="width: 100%; background: white; margin:0; padding:0;">
<!-- Visually Hidden Preheader Text : BEGIN -->
<div style="display:none;font-size:1px;line-height:1px;max-height:0px;max-width:0px;opacity:0;overflow:hidden;mso-hide:all;font-family: sa')
|| TO_CLOB('ns-serif;">
Bankname
</div>
<!-- Visually Hidden Preheader Text : END -->
<!--
Set the email width. Defined in two places:
1. max-width for all clients except Desktop Windows Outlook, allowing the email to squish on narrow but never go wider than 600px.
')
|| TO_CLOB(' 2. MSO tags for Desktop Windows Outlook enforce a 600px width.
-->
<div style="width: 100%; max-width:600px; margin: 0; padding:0; text-align:left">
<!--[if mso]>
<table role="presentation" cellspacing="0" cellpadding="0" border="0" width="600" align="center">
<tr>
')
|| TO_CLOB(' <td>
<![endif]-->
<!-- Email Header : BEGIN
<table role="presentation" cellspacing="0" cellpadding="20" border="0" align="center" width="100%" style="max-width: 600px;">
<tr>
<td style=" text-align: center">
<img src="https://dl.dropboxuserc')
|| TO_CLOB('ontent.com/u/2063685/zenmonics_stacked_white.png" width="200" height="100" alt="Zenmonics" border="0" style="height: 100; width:200; font-family: sans-serif; font-size: 24px; line-height: 32px; color: #ffffff;">
</td>
</tr>
</table>
<!-- Email Header : END -->
<!-- Email Header : BEGIN ')
|| TO_CLOB('-->
<!-- Email Header : END -->
<!-- Email Body : BEGIN -->
<br><table role="presentation" cellspacing="0" cellpadding="0" border="0" align="center" width="600" style="max-width: 600px; width:600px; padding:0px; background:white; ">
<tr>
<td style="padding:15px;">
')
|| TO_CLOB(' <img src={0}
width="200" height="auto" alt="BankName" border="0" align="center" style="width:200px; max-width:200px; height: auto; font-family: sans-serif; font-size: 36px; line-height: 48px; color: #555555; margin:0px;">
</td>
</tr>
<!-- Hero Image, Flush : END -->
<!-- Email Body: BEGIN -->
')
|| TO_CLOB(' <tr >
<td style="" bgcolor="#f6f6f6">
<table style="border-left:4px solid #0E857D; padding:40px;" role="presentation" cellspacing="0" cellpadding="0" border="0" width="100%" >
<!-- copy section : START -->
<tr>
<td style="padding-bottom:')
|| TO_CLOB(' 40px; font-family: sans-serif; font-size: 15px; line-height: 20px; color: #555555;">
Dear {1},
<!-- Paragragh separation : BEGIN -->
<br>
<br>
<!-- Paragragh separation : END -->
')
|| TO_CLOB(' Thank you for choosing {2}. Your new account application has been received and you will be contacted by a {3} representative to finalize the process. Your application id is {4}.
<!-- Paragragh separation : BEGIN -->
<br>
<br>
<!-- Paragragh separation : END -')
|| TO_CLOB('->
If you have any questions, please call customer service at {5}.
<!-- Paragragh separation : BEGIN -->
<br>
<br>
<!-- Paragragh separation : END -->
Thank you for banking with us.
')
|| TO_CLOB(' </td>
</tr>
<!-- copy section : END -->
<!-- signature section : START -->
<tr>
<td style="padding:0; font-family: sans-serif; font-size: 15px; line-height: 20px; color: #555555;">
Sincerely,
')
|| TO_CLOB(' <br>
{7}
</td>
</tr>
<!-- signature section : END -->
</table>
</td>
</tr>
</table>
')
|| TO_CLOB(' <!-- Email Body : END -->
<!-- Email Footer : BEGIN -->
<table role="presentation" cellspacing="0" cellpadding="0" border="0" align="center" width="100%" style="max-width: 600px;">
<tr>
<td style="padding: 15px 15px 40px 15px; width: 100%;font-size: 12px; font-family: sans-serif; line-height:18px; text-align: left; color: #bbb;">
')
|| TO_CLOB(' All products and services are subject to approval, including credit approval. This email automatically generated; please do not reply directly.
</td>
</tr>
</table>
<!-- Email Footer : END -->
<!--[if mso]>
</td>
</tr>
')
|| TO_CLOB(' </table>
<![endif]-->
</div>
</center>
</body>
</html>'), null, EMPTY_BLOB());