Tuesday, December 27, 2016

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.

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.

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

Saturday, December 24, 2016

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....

Sunday, December 18, 2016

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.

<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)

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

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.

<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

Add alias to SQL query return value.

For Ex:


Correct: added alias

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:

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)

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.

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