Passing parameter from Hashed to ODBC stage

[Seyed]

Hi all,
I have a problem with passing arguments from a hashed file stage to an ODBC stage. What I am trying to pass is a list of table names, and for each table name that should be passed from the hashed file stage to the ODBC stage, the following SQL select stagement should execute and retrun the result:

Code: Select all

select length(replace(replace(replace(dbms_metadata.get_ddl('TABLE','#SOURCE_TABLE_NAME#'),' ',''),chr(13), ''),chr(10),'')) FROM dual;

but it results in

Code: Select all

call to SQLExecute failed.
SQL statement:select length(replace(replace(replace(dbms_metadata.get_ddl('TABLE','#SOURCE_TABLE_NAME#'),' ',''),chr(13), ''),chr(10),'')) FROM dual 
SQLSTATE=S1000, DBMS.CODE=31603
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-31603: object "#SOURCE_TABLE_NAME#" of type TABLE not found in schema "DFFSSD"

I also tried substituting '#SOURCE_TABLE_NAME' with a '?', but that didn't make any difference either. Any ideas what is causing this error? The SOURCE_TABLE_NAME column is in the Hashed file is defined as VarChar with a length of 30.

Thanks for your input,

Seyed

[ray.wurlod]

Try "escaping" every single-quote character in your SQL with a backslash.

[Seyed]

Hi Ray,
I tried adding backslash, and it failed with the following Fatal error:

Code: Select all

Project:AgencyWideReports (s99dws01)
Job name:SchemaCompTablesPart2NEW
Event #:476
Timestamp:12/2/2013 3:05:46 PM
Event type:Fatal
User:OKDHS\U74981
Message:
SchemaCompTablesPart2NEW..GetSrcTblDDLBytes.IDENT1: |SchemaCompTablesPart2NEW..GetSrcTblDDLBytes.PassSrcTblToOrcl: DSD.BCIOpenW call to SQLPrepare failed.
select length(replace(replace(replace(dbms_metadata.get_ddl(\'TABLE\',\'?\'),\' \',\'\'),chr(13), \'\'),chr(10),\'\')) FROM dual 
SQLSTATE=37000, DBMS.CODE=911
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00911: invalid character|

Thanks for your help,

Seyed

[chulett]

Not a quote issue. Your error:

ORA-31603: object "#SOURCE_TABLE_NAME#" of type TABLE not found in schema "DFFSSD"

shows that your issue is the fact that the job parameter is not being recognized, so it remains untranslated. How are you building that select statement? You'll need to assemble it 'in pieces' so the parameter substitution can happen properly outside of the static strings.

[Seyed]

Hi Craig,

Thank you for your help. For additional debugging, I hard coded a table name that I know exists within the schema and now getting the following error message:

Code: Select all

SchemaCompTablesPart2NEW..Oracle_OCI_125: ORA-00903: invalid table name
SchemaCompTablesPart2NEW..Oracle_OCI_125.DSLink1: DSP.Open GCI $DSP.Open error -100.

I copied and pasted the following code in TOAD while using the same DB, and schema and it worked fine.

Code: Select all

select length(dbms_metadata.get_ddl('TABLE','PS2_INFO_SDF')) FROM dual;

Thanks again,

Seyed

[ray.wurlod]

Does your DataStage user use the same default schema name as does the TOAD user? Maybe you need a fully qualified table name.

[chulett]

It won't take a fully qualified table name and thus only works with an owner table. Unless, perhaps, you leverage a synonym?