UTL_SMTP: Random Exclamations

Recently I noticed an issue with random exclamations points(!) in the HTML email notification, generated using UTL_SMTP package in PLSQL.

These random exclamations points were not supposed to be part of the message, but strangely appears in the message body. After analyzing for some time, I found out the cause. Here is my analysis.


Cause:
This issue appears only with HTML email notification exceeding 1000 characters(includes the CR and LF) per line. As per RFC#2822 Section 2.1.1, no line can be longer than 998 characters without a line break.


Fix:
Solution is simple. Concatenate each line with CHR(10) or line breaks, so that each line will not exceed 1000 characters mark.

SQL Developer: DBA

Recently, I came across DBA Navigator feature in SQL Developer, that really helps DBA or Developers to administer Oracle Database right from your desktop.

How to access DBA Navigator:
Open SQL Developer --> Go to View --> DBA

What are all features, it enables to users:

• View database instance overall status using Instance Viewer(IO, wait events, storage, log switches, and processes)
• Access ADDM/AWR/ASH reports
• Security(Users/Roles) Administration
• Storage(Datafiles/Tablespaces) Administration
• Many more....

JDK Upgrade: JavaMail Exception

Recently I upgraded one of SOA Suite 11g environments to use latest JDK(7u121) and I received below error from UMS driver, when I restarted the complete SOA domain.

Error:

<Nov 25, 2016 2:26:53> <Error> <oracle.sdp.messaging.driver.email> <SDP-26123> <Could not initialize Email Store for user ABC@XYZ.COM >

<Nov 25, 2016 2:26:53> <Error> <oracle.sdp.messaging.driver.email> <SDP-25700> <An unexpected exception was caught.

javax.mail.MessagingException: * BYE JavaMail Exception: java.net.SocketTimeoutException: Read timed out;

  nested exception is:

        com.sun.mail.iap.ConnectionException: * BYE JavaMail Exception: java.net.SocketTimeoutException: Read timed out

        at com.sun.mail.imap.IMAPStore.protocolConnect(IMAPStore.java:569)

        at javax.mail.Service.connect(Service.java:288)

        at javax.mail.Service.connect(Service.java:169)

Cause:

As a part of latest JDK release, plain text authentication is no longer supported.

Fix:

Disable Plain-text Authentication in Email Driver Configuration

i.e. Check ImapAuthPlainDisable checkbox as shown below.

BPM: XPathFunctionException

Recently I faced below XPathFunctionException error, when I tried to build and execute SQL query in BPM process Script activity.

Error:
<Dec 12, 2016 6:47:14> <Error> <oracle.fabric.common.xml.xpath> <BEA-000000> <XPath function failed with error:oracle.xml.sql.OracleXMLSQLException: Character ')' is not allowed in an XML tag name.
oracle.fabric.common.xml.xpath.XPathFunctionException: oracle.xml.sql.OracleXMLSQLException: Character ')' is not allowed in an XML tag name.

<Dec 12, 2016 6:47:14> <Error> <oracle.soa.bpel.engine.xml> <BEA-000000> <
javax.xml.xpath.XPathExpressionException: internal xpath error


Fix:
Add alias to SQL query return value.

For Ex:

Incorrect: 
SELECT GET_STATUS('1234') FROM DUAL

Correct: added alias
SELECT GET_STATUS('1234') STATUS FROM DUAL

BPM: XPath ClassCastException

Recently I faced below Class Casting error, when I tried to invoke a BPEL service from BPM process.

Error 1:

<Dec 11, 2016 8:37:49> <Error> <oracle.fabric.common.xml.xpath> <BEA-000000> <XPath function failed with error:java.lang.ClassCastException: oracle.xml.parser.v2.XMLElement cannot be cast to java.lang.String

oracle.fabric.common.xml.xpath.XPathFunctionException: java.lang.ClassCastException: oracle.xml.parser.v2.XMLElement cannot be cast to java.lang.String


Error 2:
ORABPEL-11211

DOM Parsing Exception in translator.
DOM parsing exception in inbound XSD translator while parsing InputStream.
Please make sure that the xml data is valid.

at oracle.tip.adapter.jms.inbound.JmsConsumer.translateFromNative(JmsConsumer.java:591)
        at oracle.tip.adapter.jms.inbound.JmsConsumer.sendInboundMessage(JmsConsumer.java:407)
        at oracle.tip.adapter.jms.inbound.JmsConsumer.send(JmsConsumer.java:1185)
        at oracle.tip.adapter.jms.inbound.JmsConsumer.run(JmsConsumer.java:1065)
Caused by: oracle.xml.parser.v2.XMLParseException; lineNumber: 2; columnNumber: 82; Namespace prefix 'xsi' used but not declared.
        at oracle.xml.parser.v2.XMLError.flushErrors1(XMLError.java:323)
        at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:409)
        at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:232)

Cause:

By default, BPM process variable refers to oracle.xml.parser.v2.XMLElement type and you need to convert it to String, before pass on to BPEL service.

Fix:

Surround input from BPM to BPEL process with string() function.