Saturday, August 2, 2008

LOV in Find Mode - Workaround for JDeveloper 11g TP4

During this week I have faced an issue with LOV functionality when form is switched to Find mode. I got reply from Oracle, this issue is a bug in JDeveloper 11g. However, for those who need to use LOV components in Find mode in your current project, I will provide temporary workaround. Temporary, I hope until next JDeveloper 11g build.

You can download sample application, where LOV in Find mode works with applied workaround - LOVFindMode.zip. Developed sample contains one form with LOV component.


Its possible to switch this form to Find mode by pressing Find button.


Before describing workaround, I want to remind you about one trick I have noticed with LOV read-only View objects. When you generate Model layer using ADF Business Components From Tables wizard, KeyAttributes property for read-only LOV View objects is not set for some reason. As a result, after value in LOV popup is selected:


Nothing is returned back:


In order to fix this, open your read-only View object you are using for LOV:


And specify key attributes, you can do this directly in XML or through wizard:


Ok, now let's back to main topic of this post - workaround for LOV when form is in Find mode. At first, you can ask - where is the problem? Its easy to answer - select value from LOV popup for first time:


Press LOV icon second time, in order to change selected value - oracle.jbo.SQLStmtException: JBO-27122 is thrown:


For some reason, ADF constructs SQL statement with :vc_temp_1 bind variable, and this variable is not initialized:

oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT Employees.EMPLOYEE_ID, Employees.FIRST_NAME, Employees.LAST_NAME, Employees.EMAIL, Employees.PHONE_NUMBER, Employees.HIRE_DATE, Employees.JOB_ID, Employees.SALARY, Employees.COMMISSION_PCT, Employees.MANAGER_ID, Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE (( (Employees.JOB_ID LIKE :vc_temp_1 ) ) AND Employees.EMPLOYEE_ID = :1)

java.sql.SQLException: Missing IN or OUT parameter at index:: 1

Its Oracle bug, and they will fix it next build, but for those who need to use LOV in Find mode with TP4, may be my workaround will be helpful.

While researching this problem, I have noticed that value from LOV is returned still correctly, even after exception is thrown. So, workaround is simple - just to hide generated exception. In 11g you can use centralized custom error handler class where reported exceptions can be managed.


This class should extend from DCErrorHandlerImpl class and should be registered in DataBindings.cpx file:


I'm catching generated exception in overrided reportException method and not reporting it, all other exceptions are reported as normal:


When this workaround is applied, you can open LOV popup when form is in Find mode as many times as you want - no error popup, and value from LOV is returned:


If you will check your server log, you will find entry that indicates information about catched but not reported exception:

7 comments:

  1. Hi,
    It's great. I’m wondering if that will hide the whole exception or just JBO-2712

    Thanks

    ReplyDelete
  2. Hi,

    In this case, it hides whole exception.

    Regards,
    Andrejus

    ReplyDelete
  3. Hi,
    LOV in Find Mode Exception
    LOV in Find Mode Exception
    LOV in Find Mode Exception
    <[weblogic.servlet.internal.WebAppServletContext@10eb552 - appName: 'LOVFindMode', name: 'LOVFindMode-ViewController-context-root', context-path: '/LOVFindMode-ViewController-context-root', spec-version: '2.5', request: weblogic.servlet.internal.ServletRequestImpl@18b8315[
    POST /LOVFindMode-ViewController-context-root/faces/main?_adf.ctrl-state=953938490_3 HTTP/1.1
    Accept: image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/x-shockwave-flash, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, */*
    Referer: http://127.0.0.1:7101/LOVFindMode-ViewController-context-root/faces/main?_adf.ctrl-state=953938490_3
    Accept-Language: en-us
    Content-Type: application/x-www-form-urlencoded
    UA-CPU: x86
    Accept-Encoding: gzip, deflate
    User-Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322)
    Content-Length: 2549
    Connection: Keep-Alive
    Cache-Control: no-cache
    Cookie: JSESSIONID=5nw4K3WDGYCjwk9VdXxrVGlsxwn9K4gdlWR9RB2cyfHmLcCMLfPb!-558683371

    ]] Root cause of ServletException.
    oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT Employees.EMPLOYEE_ID, Employees.FIRST_NAME, Employees.LAST_NAME, Employees.EMAIL, Employees.PHONE_NUMBER, Employees.HIRE_DATE, Employees.JOB_ID, Employees.SALARY, Employees.COMMISSION_PCT, Employees.MANAGER_ID, Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE (Employees.EMPLOYEE_ID = :1)
    at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:1015)
    at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:762)
    at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:5681)
    at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:1005)
    at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:1162)
    Truncated. see log file for complete stacktrace
    java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: vc_temp_1
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:116)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:177)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:233)
    at oracle.jdbc.driver.OraclePreparedStatement.setNullAtName(OraclePreparedStatement.java:4454)
    Truncated. see log file for complete stacktrace
    >
    How to catch These exception
    Thanks

    ReplyDelete
  4. Hi,
    I am working with JDeveloper 11.1.1.4.0 . I am just creation a normal LOV that is attached to the view object and this view object extends the BaseViewObject . I have bind variables nad values are also defined to these bind variables . But still i get this exception : Missing in and out parameter . Can you please advice me on this .
    I have also posted the issue in forum as well.. the link is :
    http://forums.oracle.com/forums/message.jspa?messageID=9501858#9501858

    ReplyDelete
  5. Hi,

    You will get this error if Bind Variable is set as Required and value is not assigned to Bind Variable on runtime.

    Andrejus

    ReplyDelete
  6. Hi,

    Since we are looking error code 27122, i think we will gracefully trap only that error. rest of the JBO exceptions will be shown to users, right ?

    ReplyDelete
  7. Hi I am also facing issue in my LOV but in my face in my loca deployment i am not getting any error but when i used same code in webcenter space, I am facing below error, I hav ecreated both VO on query based only and binding style of my vo is "OracleName"
    Please find blow my logs.



    <getDisplayMessage Called=oracle.jbo.SQLStmtException
    oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT * FROM (SELECT DISTINCT the_key, ProdLineKey,ProdLineDesc, ProdSegKey
    FROM (SELECT 1 the_key,
    TO_CHAR(trim(SRSRP2)) ProdLineKey,
    TO_CHAR(trim(SRDL02)) ProdLineDesc,
    TRIM (SRSRP1) ProdSegKey
    FROM F590001
    WHERE trim(SRSRP1) IS NOT NULL
    AND trim(SRSRP1) IN
    (SELECT TO_CHAR(trim(DRKY))
    FROM F0005
    WHERE trim (DRSY) = '41'
    AND trim(drrt) = 'S1'
    AND trim(drsphd) = 'CPP'
    )
    UNION ALL
    SELECT DISTINCT 2 the_key,
    TO_CHAR(trim(SRSRP2)) ProdLineKey,
    TO_CHAR(trim(SRDL02)) ProdLineDesc,
    NULL ProdSegKey
    FROM F590001
    WHERE trim(SRSRP1) IS NOT NULL
    AND trim(SRSRP1) IN
    (SELECT TO_CHAR(trim(DRKY))
    FROM F0005
    WHERE trim (DRSY) = '41'
    AND trim(drrt) = 'S1'
    AND trim(drsphd) = 'CPP'
    )
    )) QRSLT WHERE (:Bind_Prodsegkey is null and the_key = 2)or (:Bind_Prodsegkey is not null and the_key=1 and :Bind_Prodsegkey = ProdSegKey)
    at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:1293)
    at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:968)
    at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:7178)
    at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:1217)
    at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:1062)
    at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2810)
    at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2788)

    ReplyDelete