2 Replies Latest reply: Nov 20, 2012 2:41 AM by Sutirtha Roy RSS

    Part 2:Store The Current Session id in an Odi Variable

    Papai
      Hi,
      I want to store the current session id in an odi variable.So I write the code bellow in refreshing tab of the variable.

      SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL

      I took a oracle schema which points to oracle target data server logical schema.
      When I validate the query(click on green tick mark) it shows an error(ORA-00936: missing expression) like this:

      java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
      at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
      at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
      at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
      at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
      at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
      at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
      at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
      at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:645)
      at com.sunopsis.graphical.frame.edit.EditFrameSnpVar.handleTestVariableQueryAction(EditFrameSnpVar.java:1095)
      at com.sunopsis.graphical.frame.edit.EditFrameSnpVar.access$3(EditFrameSnpVar.java:1074)
      at com.sunopsis.graphical.frame.edit.EditFrameSnpVar$4.actionPerformed(EditFrameSnpVar.java:492)
      at com.sunopsis.graphical.component.toolbar.OdiTextAreaToolbarAction.executeAction(OdiTextAreaToolbarAction.java:125)

      at com.sunopsis.graphical.component.toolbar.AbstractOdiToolbarAction.actionPerformed(AbstractOdiToolbarAction.java:203)

      at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
      at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
      at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
      at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
      at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:273)
      at java.awt.Component.processMouseEvent(Component.java:6297)
      at javax.swing.JComponent.processMouseEvent(JComponent.java:3275)
      at java.awt.Component.processEvent(Component.java:6062)
      at java.awt.Container.processEvent(Container.java:2039)
      at java.awt.Component.dispatchEventImpl(Component.java:4660)
      at java.awt.Container.dispatchEventImpl(Container.java:2097)
      at java.awt.Component.dispatchEvent(Component.java:4488)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
      at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4236)
      at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
      at java.awt.Container.dispatchEventImpl(Container.java:2083)
      at java.awt.Window.dispatchEventImpl(Window.java:2489)
      at java.awt.Component.dispatchEvent(Component.java:4488)
      at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:668)
      at java.awt.EventQueue.access$400(EventQueue.java:81)
      at java.awt.EventQueue$2.run(EventQueue.java:627)
      at java.awt.EventQueue$2.run(EventQueue.java:625)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
      at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
      at java.awt.EventQueue$3.run(EventQueue.java:641)
      at java.awt.EventQueue$3.run(EventQueue.java:639)
      at java.security.AccessController.doPrivileged(Native Method)
      at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
      at java.awt.EventQueue.dispatchEvent(EventQueue.java:638)
      at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
      at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
      at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)


      So please help and let me know what will be the correct query.
      Thanks
      Papai

      Edited by: Papai on Nov 20, 2012 12:30 AM
        • 1. Re: Part 2:Store The Current Session id in an Odi Variable
          JeromeFr
          Hi Papai.

          I think you cannot validate this kind of query since it is not pure SQL (you have some substitution function from ODI API).

          Set the hystorical type to Keep All Values and try to refresh it inside a package instead :).



          Hope it helps.


          Regards,
          JeromeFr
          • 2. Re: Part 2:Store The Current Session id in an Odi Variable
            Sutirtha Roy
            Papai wrote:
            Hi,
            I want to store the current session id in an odi variable.So I write the code bellow in refreshing tab of the variable.

            SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL

            I took a oracle schema which points to oracle target data server logical schema.
            When I validate the query(click on green tick mark) it shows an error(ORA-00936: missing expression) like this:
            Validate button is only for SQL statement.
            API call can not be validated using validate button .

            java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
            at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
            at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
            at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
            at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
            at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:548)
            at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:217)
            at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:947)
            at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1441)
            at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3769)
            at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3823)
            at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1671)
            at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:645)
            at com.sunopsis.graphical.frame.edit.EditFrameSnpVar.handleTestVariableQueryAction(EditFrameSnpVar.java:1095)
            at com.sunopsis.graphical.frame.edit.EditFrameSnpVar.access$3(EditFrameSnpVar.java:1074)
            at com.sunopsis.graphical.frame.edit.EditFrameSnpVar$4.actionPerformed(EditFrameSnpVar.java:492)
            at com.sunopsis.graphical.component.toolbar.OdiTextAreaToolbarAction.executeAction(OdiTextAreaToolbarAction.java:125)

            at com.sunopsis.graphical.component.toolbar.AbstractOdiToolbarAction.actionPerformed(AbstractOdiToolbarAction.java:203)

            at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995)
            at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318)
            at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387)
            at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242)
            at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236)
            at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:273)
            at java.awt.Component.processMouseEvent(Component.java:6297)
            at javax.swing.JComponent.processMouseEvent(JComponent.java:3275)
            at java.awt.Component.processEvent(Component.java:6062)
            at java.awt.Container.processEvent(Container.java:2039)
            at java.awt.Component.dispatchEventImpl(Component.java:4660)
            at java.awt.Container.dispatchEventImpl(Container.java:2097)
            at java.awt.Component.dispatchEvent(Component.java:4488)
            at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4575)
            at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4236)
            at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4166)
            at java.awt.Container.dispatchEventImpl(Container.java:2083)
            at java.awt.Window.dispatchEventImpl(Window.java:2489)
            at java.awt.Component.dispatchEvent(Component.java:4488)
            at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:668)
            at java.awt.EventQueue.access$400(EventQueue.java:81)
            at java.awt.EventQueue$2.run(EventQueue.java:627)
            at java.awt.EventQueue$2.run(EventQueue.java:625)
            at java.security.AccessController.doPrivileged(Native Method)
            at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
            at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
            at java.awt.EventQueue$3.run(EventQueue.java:641)
            at java.awt.EventQueue$3.run(EventQueue.java:639)
            at java.security.AccessController.doPrivileged(Native Method)
            at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
            at java.awt.EventQueue.dispatchEvent(EventQueue.java:638)
            at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)
            at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)
            at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)
            at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)
            at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)
            at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)


            So please help and let me know what will be the correct query.
            Thanks
            Papai

            Edited by: Papai on Nov 20, 2012 12:30 AM