How To Pass Oracle Procedure Value using Tidal Oracle Database Job Definition to Tidal Variable

[Warren Tolentino]

how do i pass the parameter value from an oracle database tidal job to a tidal variable? for example i have this oracle db job that is defined to execute an oracle database procedure and i need to pass the parameter value to the tidal variable.

SQL tab:

begin
  procedure_get_user_info(<OracleUserVariable.1>);
end;
 

thanks,
warren
 

[Joe Fletcher]

Can you have a predecessor job that calls "tesmcmd varset" to define the variable?

[Warren Tolentino]

hi i'm not sure how to use the "tesmcmd varset" command. i guess if that command will be use in a predecessor job it will not work. because the value that the tidal variable needs will come from the oracle procedure. the oracle procedure needs to execute first and pass the value to the tidal variable.

thanks.

[Joe Fletcher]

tesmcmd is a binary that sits in your TIDAL master installation bin directory. It takes options, one of which is varset which let's you set variable values.

So you can run a system level script ( a unix example is given below) which can set values for group variables.

Looking at your example you need to find a way to define OracleUserVariable.1

Where does the value for this variable come from?

Sample variable set script:

GROUP_FILE_VAR=`echo $2 | sed -e 's/\.xml\.pgp/\.xml/'`
tesmcmd varset -i $1 -n GROUP_FILE_XML -v $GROUP_FILE_VAR
XSD_FILE_VAR=`echo $2 | sed -e 's/\.xml\.pgp/\.xsd/'`
tesmcmd varset -i $1 -n GROUP_FILE_XSD -v $XSD_FILE_VAR
 

And we call the job using

setvar.sh <JobID..p> <Group.REQUEST_FILE>

which are overrides from a file event.

[Warren Tolentino]

thank you. the value in this example for the tidal variable <OracleUserVariable.1> will come from the oracle database procedure.

[Joe Fletcher]

Looking at your example you have something that looks like it could be a group variable to which you wish to assign a value as the oracle job is called. Correct?

[Warren Tolentino]

i think about if the variable is a group variable or local job variable. i guess it does not matter as long as i can assign a value to the tidal variable pass by oracle procedure. thanks.