Register External Candidate Without Resume Errors with JBO-27122; ORA-20103 (Doc ID 416619.1)

Last updated on MAY 29, 2017

Applies to:

Oracle iRecruitment - Version 11.5.10.0 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
Version 11.5.10.x to 12.x.x


Symptoms

Within iRecruitment, when attempting to register an external candidate who does not have a resume, instead of seeing the resume templates at step four, the following error is seen instead:

Error Page
You have encountered an unexpected error. Please contact the System Administrator for assistance.
Click here for exception details.

The 'here' link reveals the following exception details:

Error Page
Exception Details:

oracle.apps.fnd.framework.OAException:
oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.
Statement: select nvl2(:1, irc_jps_generator.GenerateJPS(:2,:3), null) as Resume,'Resume.htm' as FileName, :4 as RESUME_STYLE from dual
....
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:865)
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:988)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:750)
at oracle.apps.per.irc.candidateSelfService.webui.AplRegCreateResumeCO.processRequest(AplRegCreateResumeCO.java:48)
....
## Detail 0 ## java.sql.SQLException:
ORA-20103: Null input is not allowed ORA-06512: at "APPS.XSLPROCESSOR", line 34
ORA-06512: at "APPS.XSLPROCESSOR", line 329
ORA-06512: at "APPS.IRC_JPS_GENERATOR", line 411
ORA-06512: at "APPS.IRC_JPS_GENERATOR", line 428
ORA-06512: at line 1

OR

Exception Details.
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: select nvl2(:1, irc_jps_generator.GenerateJPS(:2,:3), null) as Resume
,'Resume.htm' as FileName,
:4 as RESUME_STYLE
from dual
at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:996)
## Detail 0 ##
java.sql.SQLException: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-28759: failure to open file
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-28759: failure to open file
ORA-06512: at "APPS.IRC_JPS_GENERATOR", line 414
ORA-06512: at "APPS.IRC_JPS_GENERATOR", line 428
ORA-06512: at line 1

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        OR
      
        -- Error
        ORA-01400: cannot insert NULL into ("HR"."IRC_DOCUMENTS"."MIME_TYPE") in Package irc_document_swi Procedure create_document

 

The issue can be reproduced at will with the following steps:

1. Start on the IrcVisitor.jsp page and choose the "Register Today" button to access the main registration page.
2. Enter the details for the Registration page and then click on Submit to move to the "My Account" page.
3. Enter the details for creating an account, but most importantly, select the option 'No, I do not have a resume' and click on Continue.
4. Enter Personal information and click on Next (step 1 of 4).
5. Enter some details for 'Register:Add Qualifications and Skills' and then click on Next (step 2 of 4).
6. At the 'Register:Enter Preferences' screen, click on Next (step 3 of 4).  Instead of seeing the resume templates, the above error is seen.

Changes

Possible changes that can cause this:
1. Implementing for the first time.
2. Added SSL to an external tier either directly or via a reverse proxy or loadbalancer.
3. Added a firewall or other network change that blocks the database from making URL calls.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms