1 2 Previous Next 15 Replies Latest reply: Nov 19, 2012 2:21 AM by Papai RSS

    Store The Current Session id in an Odi Variable

    Papai
      Hi,
      I want to store the current session id in an odi variable.how to do that?
      Please Ans
      Thanks

      Papai
        • 1. Re: Store The Current Session id in an Odi Variable
          Bhabani Ranjan
          SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL
          • 2. Re: Store The Current Session id in an Odi Variable
            Papai
            Bhabani wrote:
            SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL
            Hi Bhabani,
            do I need to write this code(SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL

            ) in refreshing tab of the variable.If yes then please tell me what will be the schema??
            (undefine,memory engine or sunopsis memory engine)
            • 3. Re: Store The Current Session id in an Odi Variable
              Sutirtha Roy
              Papai wrote:
              Bhabani wrote:
              SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL
              Hi Bhabani,
              do I need to write this code(SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL

              ) in refreshing tab of the variable.
              Yes
              If yes then please tell me what will be the schema??
              (undefine,memory engine or sunopsis memory engine)
              any Oracle schema will be fine
              • 4. Re: Store The Current Session id in an Odi Variable
                Papai
                Hi Sutirtha,
                I took Suopsis_Memory_Engine as schema.when I validate the query(green tick mark) it shows an error(ODI-20123) like this:
                java.sql.SQLException: unexpected token: <
                     at org.hsqldb.jdbc.Util.sqlException(Unknown Source)....

                and when I run this project it is executed successfully but the variable takes only 0.

                Please ans

                Error Details:
                java.sql.SQLException: unexpected token: <
                     at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
                     at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
                     at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
                     at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
                     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                     at java.lang.reflect.Method.invoke(Method.java:597)
                     at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter$OnDisconnectCommandExecutionHandler.invoke(OnConnectOnDisconnectDataSourceAdapter.java:200)
                     at $Proxy2.prepareStatement(Unknown Source)
                     at com.sunopsis.sql.SnpsQuery.getPreparedStatement(SnpsQuery.java:975)
                     at com.sunopsis.sql.SnpsQuery.getPreparedStatement(SnpsQuery.java:1016)
                     at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java:1850)
                     at com.sunopsis.sql.SnpsQuery.executeQuery(SnpsQuery.java:595)
                     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:6288)
                     at javax.swing.JComponent.processMouseEvent(JComponent.java:3267)
                     at java.awt.Component.processEvent(Component.java:6053)
                     at java.awt.Container.processEvent(Container.java:2041)
                     at java.awt.Component.dispatchEventImpl(Component.java:4651)
                     at java.awt.Container.dispatchEventImpl(Container.java:2099)
                     at java.awt.Component.dispatchEvent(Component.java:4481)
                     at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4577)
                     at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4238)
                     at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4168)
                     at java.awt.Container.dispatchEventImpl(Container.java:2085)
                     at java.awt.Window.dispatchEventImpl(Window.java:2478)
                     at java.awt.Component.dispatchEvent(Component.java:4481)
                     at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:643)
                     at java.awt.EventQueue.access$000(EventQueue.java:84)
                     at java.awt.EventQueue$1.run(EventQueue.java:602)
                     at java.awt.EventQueue$1.run(EventQueue.java:600)
                     at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                     at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:98)
                     at java.awt.EventQueue$2.run(EventQueue.java:616)
                     at java.awt.EventQueue$2.run(EventQueue.java:614)
                     at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)
                     at java.awt.EventQueue.dispatchEvent(EventQueue.java:613)
                     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)
                Caused by: org.hsqldb.HsqlException: unexpected token: <
                     at org.hsqldb.error.Error.parseError(Unknown Source)
                     at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
                     at org.hsqldb.ParserBase.checkIsIdentifier(Unknown Source)
                     at org.hsqldb.ParserDQL.readColumnOrFunctionExpression(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
                     at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
                     at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
                     at org.hsqldb.ParserCommand.compilePart(Unknown Source)
                     at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
                     at org.hsqldb.Session.compileStatement(Unknown Source)
                     at org.hsqldb.StatementManager.compile(Unknown Source)
                     at org.hsqldb.Session.execute(Unknown Source)
                     ... 51 more
                • 5. Re: Store The Current Session id in an Odi Variable
                  Papai
                  Hi,
                  If I refresh the variable it shows an error like this:

                  -5501 : 42501 : java.sql.SQLException: user lacks privilege or object not found: DUAL
                  java.sql.SQLException: user lacks privilege or object not found: DUAL
                       at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
                       at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
                       at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
                       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                       at java.lang.reflect.Method.invoke(Method.java:597)
                       at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter$OnDisconnectCommandExecutionHandler.invoke(OnConnectOnDisconnectDataSourceAdapter.java:200)
                       at $Proxy249.prepareStatement(Unknown Source)
                       at oracle.odi.runtime.agent.execution.sql.SQLCommand.doInitializeStatement(SQLCommand.java:83)
                       at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:117)
                       at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:111)
                       at oracle.odi.runtime.agent.execution.sql.RefreshVariableExecutor.execute(RefreshVariableExecutor.java:47)
                       at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)
                       at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)
                       at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.processTask(SnpSessTaskSqlV.java:410)
                       at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
                       at com.sunopsis.dwg.dbobj.SnpSessTaskSqlV.treatTask(SnpSessTaskSqlV.java:240)
                       at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:558)
                       at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:464)
                       at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2093)
                       at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
                       at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
                       at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
                       at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
                       at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
                       at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
                       at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
                       at java.lang.Thread.run(Thread.java:662)
                  Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: DUAL
                       at org.hsqldb.error.Error.error(Unknown Source)
                       at org.hsqldb.SchemaManager.getTable(Unknown Source)
                       at org.hsqldb.ParserDQL.readTableName(Unknown Source)
                       at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
                       at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
                       at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
                       at org.hsqldb.ParserCommand.compilePart(Unknown Source)
                       at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
                       at org.hsqldb.Session.compileStatement(Unknown Source)
                       at org.hsqldb.StatementManager.compile(Unknown Source)
                       at org.hsqldb.Session.execute(Unknown Source)
                       ... 28 more
                  • 6. Re: Store The Current Session id in an Odi Variable
                    JeromeFr
                    You cannot use the Sunopsis Memory Engine, you have to use an Oracle schema for this query.
                    • 7. Re: Store The Current Session id in an Odi Variable
                      Sutirtha Roy
                      Papai wrote:
                      Hi,
                      If I refresh the variable it shows an error like this:

                      -5501 : 42501 : java.sql.SQLException: user lacks privilege or object not found: DUAL
                      java.sql.SQLException: user lacks privilege or object not found: DUAL
                      This is coming as DUAL is an Oracle system table (owned by SYS and granted to PUBLIC)

                      You need to select an ORACLE schema for the variable refresh query
                      • 8. Re: Store The Current Session id in an Odi Variable
                        Papai
                        Hi,
                        Ok done that, but now I set my variable in refresh mode(In package) and In keep history param I set Recent Value.But it always return a old session id(the old session id is the id when I first refresh the variable while creating it).Why??

                        Edited by: Papai on Nov 15, 2012 5:41 AM
                        • 9. Re: Store The Current Session id in an Odi Variable
                          JeromeFr
                          In the history tab you have a refresh button. You should click on it to display the last value.
                          • 10. Re: Store The Current Session id in an Odi Variable
                            Papai
                            Jerome Fr wrote:
                            In the history tab you have a refresh button. You should click on it to display the last value.
                            Hi Jerome,
                            I have done that.Actually I want to store the recent value in a file using shell script.So I want to pass the recent value to the shell script.So in package,I put the the variable in refresh mode as first step and then the odioscommand link by ok.But it always return the old value that was created by clicking the refresh button at variable creation stage.
                            • 11. Re: Store The Current Session id in an Odi Variable
                              Bhabani Ranjan
                              Have you selected the LATEST VALUE ?
                              • 12. Re: Store The Current Session id in an Odi Variable
                                Papai
                                No Bhabani, I set the Recent value.
                                • 13. Re: Store The Current Session id in an Odi Variable
                                  JeromeFr
                                  Select Latest Value or All Values ;)
                                  • 14. Re: Store The Current Session id in an Odi Variable
                                    Bhabani Ranjan
                                    I dont see any recent value option here. what is your odi version ? If you can see the latest value then select it.

                                    Thanks
                                    Bhabani
                                    1 2 Previous Next