DSXchange

My sequence has User variables activity followed by a Job Activity. I have set the job parameter of the Job activity using User variable set in the previous step.

Var1 = UserVariables_Activity_1.Var1

I am using this variable in the source sql of the job as below:

Select #Var1# as Variable1 from dual;

I am getting below error when I run the job.

Oracle_Connector_3,0: The following SQL statement failed: Select as Variable1 from Dual.
Oracle_Connector_3,0: The OCI function OCIStmtExecute returned status -1. Error code: 936, Error message: ORA-00936: missing expression. (CC_OraStatement::executeSelect, file CC_OraStatement.cpp, line 3,900)

Where am I doing wrong?

Your SQL expression is missing a column name. Select WHAT as Variable1 from Dual?

My SQL is "Select #Var1# as Variable1 from dual;" where Var1 is the job parameter (derived from User variable activity). Can't we use job parameter in select SQL?

To start with, print the values in the log and see how the query looks like after the variable has been passed?

May be space issue, or missing values .......

Once you are happy then use it in the job.

In other words, you can use job parameters in select SQL. Since it is resolving as empty, you must have passed in a null value... that or not really passed it to the Job Activity stage correctly.

The SQL statement that the Oracle Connector reported as execution was Select as Variable1 from Dual.

This implies that the value of job parameter Var1 had not been set (or had not been properly set).

Note that #Var1# is NOT a reference to a variable from a User Variables activity. You cannot use a User Variables variable reference in an SQL statement; however you could build the entire SQL statement in a User Variables variable.