Modify Schema Name in OCI9

[ar_shriks]

I have a job with Query Type : "Fully Generated SQL Query" reading from an Oracle database. The SQL query looks like :

SELECT FIELD1, FIELD2
FROM USER1.TABLE_NAME;

Note that the the schema name is USER1.

Now, when I have to run the job from production database the schema name changes to USER2 and the query should look like:

SELECT FIELD1, FIELD2
FROM USER2.TABLE_NAME;

Is it possible to modify the job to read from USER2.TABLE_NAME without changing the Query Type to "User Defined SQL Query"?

Thanks & Regards
Shrikanth

[sb_akarmarkar]

Hi,


Create parameter for database schema... Like

SELECT FIELD1, FIELD2 FROM #SchemaName#.TABLE_NAME;


Thanks,
Anupam

[ray.wurlod]

That is, include a job parameter reference as the schema name in the Table Name field. Make sure there is a job parameter of that name.

[ar_shriks]

Thanks. But when you create the query by clicking on the 'Build' command button and selecting the table name, the displayed table name in the 'Selected tables' list is read only and in the format schemaname.table_name. Is it possible to list the table name without the schema name so that my SELECT command will look like :

SELECT FIELD1, FIELD2 FROM TABLE_NAME;
instead of
SELECT FIELD1, FIELD2 FROM schema_name.TABLE_NAME;

I am aware that I can copy the generated SQL Query and modify it by changing the Query Type to "User Defined Query". I am just probing the option of leaving the query as a "Fully generated SQL Query", but take care of schema name changes when running the job in production environment.

[chulett]

Yuck... Fully Generated. You don't need to switch to User Defined, but to the other generation option, whatever it is called in your version (the name changed): 'Column Generated' or the ever so long 'Generate SELECT list clause from column list; enter other clauses'. You'll be fine there using the parameter as noted.

Me, I personally wouldn't touch Fully Generated (now known as 'Use SQL Builder Tool') with 39 and a half foot pole.