DSXchange

We have a job failing as double quotes (") are being used to populate the SQL statement. Testing the statement via sqlplus returns an ora-00984 error. Changing the double quotes to single in sqlplus solves the error. How can I change it in datastage?

Datastage error:
ExtTASAudit_Test..Transformer_33.UptTable: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO SCOWNER.AU_TABLE_AUDIT(AUDIT_ID, AUDIT_DATE, TABLE_NAME, TABLE_KEY_1) VALUES (?,TO_DATE(?,'YYYY-MM-DD HH24:MI:SS'),?,?,?)
0 Rows affected.

AUDIT_ID = 132423753
AUDIT_DATE = "2019-02-04 09:43:18"
TABLE_NAME = "PERSONAL"
TABLE_KEY_1 = "4056406"

The last three values need single rather than double quotes.
Thanks

Clarify a couple of things for us, please.

1) Is this truly a Server job?
2) What stage are you using? Or is this a custom routine?

For Oracle I would expect to see OCI syntax via a native stage but that looks like ODBC so suspecting a routine. Best to understand how exactly you're doing this before people start making suggestions.

Thanks for the replies and apologies for lack of detail, I'm new to datastage.

This is a server job, it takes data from an ODBC source, goes through a transformer and outputs to a file and to an oracle database via ODBC.

The ODBC Stage Inputs tab give the database name and table, with update action set to "Insert new or update existing rows"

The View SQL tab shows the insert and update commands. If I copy and paste the sql into sqlplus and add values enclosed in single quotes it works. Using double quotes fails with the same error seen when the datastage job fails.

Ray, thanks for the suggestion - how would I use that code? Convert the Update Action to User-defined SQL and add it there?

I asked about the job type so I could move this to the proper forum... and here we are. Had to double-check because the vast majority of questions here are for Parallel jobs.

I'm going to have to assume Ray jumped in early thinking it was a routine. That's not going to solve your problem with the ODBC stage. One thing, though, I'm wondering why your posted SQL statement needs four column values but the statement is using five bind variables? I'm assuming you've passed five columns into the stage, yes? One last clarification, please - are you using the "ODBC stage" or the "ODBC Connector stage"? The latter is the preferred stage now that they are part of the product, btw. FWIW, still think you're better off using a native Oracle stage.

Is quote handling part of the .odbc.ini file configuration? Don't recall.

You could put it in the Transformer stage to derive the columns heading to Oracle. However the target ODBC stage should be adding single quotes to non-numeric fields anyway. Check the properties in this stage. Or, as Craig suggested, the DSN definition in .odbc.ini.