This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 19, 2012 12:21 AM by Papai RSS

Store The Current Session id in an Odi Variable

Papai Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    SELECT <%=odiRef.getSession("SESS_NO")%> FROM DUAL
  • 2. Re: Store The Current Session id in an Odi Variable
    Papai Newbie
    Currently Being Moderated
    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
    SutirthaRoy Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    SutirthaRoy Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Have you selected the LATEST VALUE ?
  • 12. Re: Store The Current Session id in an Odi Variable
    Papai Newbie
    Currently Being Moderated
    No Bhabani, I set the Recent value.
  • 13. Re: Store The Current Session id in an Odi Variable
    JeromeFr Expert
    Currently Being Moderated
    Select Latest Value or All Values ;)
  • 14. Re: Store The Current Session id in an Odi Variable
    Bhabani Ranjan Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points