1 Reply Latest reply: Mar 26, 2013 10:44 AM by Stacy Carpenter RSS

    Testing VOs that have positional bind variables with BC4J Tester

    Stacy Carpenter
      Hi

      I am doing some OAF development for eBusiness 12.1.3 and would like to understand whether VOs that have positional bind variables can be tested using the BC4J Tester in JDeveloper 10.1.3 (the latest version of JDev compatible with OAF development). It looks like it should work as when you choose "Oracle Positional" in the VO wizard Binding Style it adds another text box to the to the Bind Variables page called Bind Positions, I assume that this is so you can create variables that you can populate in the Tester that will relate name to position but I get an error regarding column index when I use the BC4J Tester:

      oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation. Statement: SELECT CASE
      WHEN ph.closed_code IN ('CANCELLED', 'CLOSED','FINALLY CLOSED')
      THEN 'Expired Item'
      WHEN prl.unit_price <> pl.unit_price
      THEN 'Price Update'
      ELSE ''
      END item_status
      FROM po_lines pl,
      po_headers ph,
      po_requisition_lines prl
      WHERE ph.po_header_id = pl.po_header_id
      AND prl.blanket_po_header_id = pl.po_header_id
      AND prl.blanket_po_line_num = pl.line_num
      AND pl.po_header_id = :1
      AND pl.line_num = :2
      AND prl.requisition_line_id = :3
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:833)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:666)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3655)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4560)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:742)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:891)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:805)
           at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:799)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3575)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)
           at oracle.jbo.jbotester.VariableDialog.save(VariableDialog.java:144)
           at oracle.jbo.jbotester.GenericDialog.actionPerformed(GenericDialog.java:219)
           at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
           at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
           at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
           at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
           at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:234)
           at java.awt.Component.processMouseEvent(Component.java:5488)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
           at java.awt.Component.processEvent(Component.java:5253)
           at java.awt.Container.processEvent(Container.java:1966)
           at java.awt.Component.dispatchEventImpl(Component.java:3955)
           at java.awt.Container.dispatchEventImpl(Container.java:2024)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
           at java.awt.Container.dispatchEventImpl(Container.java:2010)
           at java.awt.Window.dispatchEventImpl(Window.java:1774)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
           at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:153)
           at java.awt.Dialog$1.run(Dialog.java:515)
           at java.awt.Dialog.show(Dialog.java:536)
           at oracle.jbo.jbotester.JTDialog.show(JTDialog.java:121)
           at oracle.jbo.jbotester.JboTesterUtil.editBindParameters(JboTesterUtil.java:169)
           at oracle.jbo.jbotester.SimpleForm.setIterator(SimpleForm.java:65)
           at oracle.jbo.jbotester.SimpleForm.<init>(SimpleForm.java:51)
           at oracle.jbo.jbotester.VOTreeNode.showForm(VOTreeNode.java:115)
           at oracle.jbo.jbotester.BaseTree.processTreeMouseClicked(BaseTree.java:471)
           at oracle.jbo.jbotester.BaseTree.mav$processTreeMouseClicked(BaseTree.java:45)
           at oracle.jbo.jbotester.BaseTree$TreeMouseListener.mouseClicked(BaseTree.java:125)
           at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:212)
           at java.awt.Component.processMouseEvent(Component.java:5491)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
           at java.awt.Component.processEvent(Component.java:5253)
           at java.awt.Container.processEvent(Container.java:1966)
           at java.awt.Component.dispatchEventImpl(Component.java:3955)
           at java.awt.Container.dispatchEventImpl(Container.java:2024)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3901)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
           at java.awt.Container.dispatchEventImpl(Container.java:2010)
           at java.awt.Window.dispatchEventImpl(Window.java:1774)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
           at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
           at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)
      ## Detail 0 ##
      java.sql.SQLException: Invalid column index
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:240)
           at oracle.jdbc.driver.OraclePreparedStatement.setNUMBERInternal(OraclePreparedStatement.java:5891)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7674)
           at oracle.jdbc.driver.OraclePreparedStatement.setCustomDatumInternal(OraclePreparedStatement.java:7410)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8178)
           at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8166)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParamValue(BaseSQLBuilderImpl.java:1847)
           at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3890)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)
           at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:13759)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:801)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:666)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3655)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4560)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:742)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:891)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:805)
           at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:799)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3575)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)
           at oracle.jbo.jbotester.VariableDialog.save(VariableDialog.java:144)
           at oracle.jbo.jbotester.GenericDialog.actionPerformed(GenericDialog.java:219)
           at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
           at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
           at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
           at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
           at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:234)
           at java.awt.Component.processMouseEvent(Component.java:5488)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
           at java.awt.Component.processEvent(Component.java:5253)
           at java.awt.Container.processEvent(Container.java:1966)
           at java.awt.Component.dispatchEventImpl(Component.java:3955)
           at java.awt.Container.dispatchEventImpl(Container.java:2024)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
           at java.awt.Container.dispatchEventImpl(Container.java:2010)
           at java.awt.Window.dispatchEventImpl(Window.java:1774)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
           at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:153)
           at java.awt.Dialog$1.run(Dialog.java:515)
           at java.awt.Dialog.show(Dialog.java:536)
           at oracle.jbo.jbotester.JTDialog.show(JTDialog.java:121)
           at oracle.jbo.jbotester.JboTesterUtil.editBindParameters(JboTesterUtil.java:169)
           at oracle.jbo.jbotester.SimpleForm.setIterator(SimpleForm.java:65)
           at oracle.jbo.jbotester.SimpleForm.<init>(SimpleForm.java:51)
           at oracle.jbo.jbotester.VOTreeNode.showForm(VOTreeNode.java:115)
           at oracle.jbo.jbotester.BaseTree.processTreeMouseClicked(BaseTree.java:471)
           at oracle.jbo.jbotester.BaseTree.mav$processTreeMouseClicked(BaseTree.java:45)
           at oracle.jbo.jbotester.BaseTree$TreeMouseListener.mouseClicked(BaseTree.java:125)
           at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:212)
           at java.awt.Component.processMouseEvent(Component.java:5491)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
           at java.awt.Component.processEvent(Component.java:5253)
           at java.awt.Container.processEvent(Container.java:1966)
           at java.awt.Component.dispatchEventImpl(Component.java:3955)
           at java.awt.Container.dispatchEventImpl(Container.java:2024)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3901)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
           at java.awt.Container.dispatchEventImpl(Container.java:2010)
           at java.awt.Window.dispatchEventImpl(Window.java:1774)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
           at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
           at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)

      ----- LEVEL 1: DETAIL 0 -----
      java.sql.SQLException: Invalid column index
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
           at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:240)
           at oracle.jdbc.driver.OraclePreparedStatement.setNUMBERInternal(OraclePreparedStatement.java:5891)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:7674)
           at oracle.jdbc.driver.OraclePreparedStatement.setCustomDatumInternal(OraclePreparedStatement.java:7410)
           at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8178)
           at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8166)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParamValue(BaseSQLBuilderImpl.java:1847)
           at oracle.jbo.server.OracleSQLBuilderImpl.bindParamValue(OracleSQLBuilderImpl.java:3890)
           at oracle.jbo.server.BaseSQLBuilderImpl.bindParametersForStmt(BaseSQLBuilderImpl.java:3335)
           at oracle.jbo.server.ViewObjectImpl.bindParametersForCollection(ViewObjectImpl.java:13759)
           at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:801)
           at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:666)
           at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3655)
           at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(Unknown Source)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4560)
           at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:742)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:891)
           at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:805)
           at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:799)
           at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3575)
           at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:439)
           at oracle.jbo.jbotester.VariableDialog.save(VariableDialog.java:144)
           at oracle.jbo.jbotester.GenericDialog.actionPerformed(GenericDialog.java:219)
           at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
           at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
           at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
           at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
           at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:234)
           at java.awt.Component.processMouseEvent(Component.java:5488)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
           at java.awt.Component.processEvent(Component.java:5253)
           at java.awt.Container.processEvent(Container.java:1966)
           at java.awt.Component.dispatchEventImpl(Component.java:3955)
           at java.awt.Container.dispatchEventImpl(Container.java:2024)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
           at java.awt.Container.dispatchEventImpl(Container.java:2010)
           at java.awt.Window.dispatchEventImpl(Window.java:1774)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
           at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:153)
           at java.awt.Dialog$1.run(Dialog.java:515)
           at java.awt.Dialog.show(Dialog.java:536)
           at oracle.jbo.jbotester.JTDialog.show(JTDialog.java:121)
           at oracle.jbo.jbotester.JboTesterUtil.editBindParameters(JboTesterUtil.java:169)
           at oracle.jbo.jbotester.SimpleForm.setIterator(SimpleForm.java:65)
           at oracle.jbo.jbotester.SimpleForm.<init>(SimpleForm.java:51)
           at oracle.jbo.jbotester.VOTreeNode.showForm(VOTreeNode.java:115)
           at oracle.jbo.jbotester.BaseTree.processTreeMouseClicked(BaseTree.java:471)
           at oracle.jbo.jbotester.BaseTree.mav$processTreeMouseClicked(BaseTree.java:45)
           at oracle.jbo.jbotester.BaseTree$TreeMouseListener.mouseClicked(BaseTree.java:125)
           at java.awt.AWTEventMulticaster.mouseClicked(AWTEventMulticaster.java:212)
           at java.awt.Component.processMouseEvent(Component.java:5491)
           at javax.swing.JComponent.processMouseEvent(JComponent.java:3126)
           at java.awt.Component.processEvent(Component.java:5253)
           at java.awt.Container.processEvent(Container.java:1966)
           at java.awt.Component.dispatchEventImpl(Component.java:3955)
           at java.awt.Container.dispatchEventImpl(Container.java:2024)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
           at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3901)
           at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
           at java.awt.Container.dispatchEventImpl(Container.java:2010)
           at java.awt.Window.dispatchEventImpl(Window.java:1774)
           at java.awt.Component.dispatchEvent(Component.java:3803)
           at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
           at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:242)
           at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
           at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
           at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)

      Is there some other configuration I need to do when using positional Bind Variables? If I switch the query to use named parameters and replace :1 with :var1 for example it works fine in the Tester. The reason I am not using named bind variables is that Oracle recommend using positional for eBus, probably because it was predominantly built on older versions of JDev.

      Thanks in advance

      Stacy

      Edited by: Stacy Carpenter on 26-Mar-2013 06:40
        • 1. Re: Testing VOs that have positional bind variables with BC4J Tester
          Stacy Carpenter
          There are actually 2 issues here. The first is that bind positions start at 0 (not 1) and that will resolve the invalid column index issue above. However it seems there is a relatively unknown error in JDeveloper 10.1.3 that keeps resetting the bind position of all bind variables each time you update any of them. So if I create var1 with a position of 0 and then create var2 with a position of 1 then the position of var1 will also be overwritten with 1. I tried listing 0,1 for both to see whether the the IDE would intuitively sort out what was supposed to be where but all that happens is that var2 ends up populating positions 0 and 1, which unless the values are meant to be the same is obviously no good. It seems the only way to get around this in this version of JDeveloper is to close the IDE, edit the VO xml to put the variables against their correct positions, and then reopen. Opening the VO wizard will reset them all to the same again so do not do this (unknowingly) and simply right click the AM and run the BC4J Tester which should now let you edit the bind variables and produce the expected results.

          Cheers

          Stacy