Skip to Content
avatar image
Former Member

Datastore session parameter

Hi,

I'm trying to use this ver. 12 feature to change an environment variable value at database connection starts.

To do it I need to execute a package passing a varchar parameter.

I tried to put the below statement in Additional session parameters of the Oracle datastore.

'DBMS_PACK.TRY({$Company})'

This package set up an Oracle variable used in some views to filter the result by company.

I did a test job with a script to setup the $Company variable and a dataflow which simply read one of such views and copy the result to a template table.

Running it no data are read from the source view as if the variables was not initialized.

If I try the "View Data" option on the same view an "ORA-00900 invalid SQL statement " error is shown.

I think that's due to the global variable in the statement.

Any clue about why no errors occurs at run time but the call seems not to work ?

Versions used are

Dataservices 12.2.2.2

Oracle 10.2

Thanks in advance

Achille

Edited by: Achille Masserano on Sep 14, 2010 7:14 PM

Edited by: Achille Masserano on Sep 14, 2010 7:15 PM

Edited by: Achille Masserano on Sep 14, 2010 7:16 PM

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Sep 14, 2010 at 10:39 PM

    I don't think view data will work if the session parameter is initialized using a variable, since Designer doesn't know anything about the global varaible

    have you tried passing a constant value to see if it actually works, the statement that you pass in the session parameter are usually ALTER SESSION etc, if you are trying to call a stored procedure I don't think DI engine will recognize that from the Session Parameters, try usign the syntax that you would use from SQL Plus

    the other thing that you can try is, use a variable as session paraemter value in Datastore for example $SESS_PARAM_STR and initilize this with the command in a script as first thing in the job and see if that works

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      global varaible will not work for the session parameter, you can use substitution parameter, but I think you will not be able to initialize that in a script, but will be able to overwrite from the command line

      in your case since you need to reset before DF execution in a job, the workaround you are using is better

      the other thing with session parameter is, the SQL specified in that will be executed eveytime a connection is done to the database, and at run time DI will open multiple connections at different times depending on the transforms, so you have to be careful using any kind of SQL through session parameter