SQLExecute Failed

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
cst
Participant
Posts: 2
Joined: Thu Apr 04, 2019 1:05 am

SQLExecute Failed

Post by cst »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Code: Select all

Convert('"', "'", InLink.TheString)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
cst
Participant
Posts: 2
Joined: Thu Apr 04, 2019 1:05 am

Post by cst »

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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply