DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
cst
Participant



Joined: 04 Apr 2019
Posts: 2

Points: 33

Post Posted: Thu Apr 04, 2019 1:15 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Server
OS: Windows
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42950
Location: Denver, CO
Points: 221500

Post Posted: Thu Apr 04, 2019 10:14 am Reply with quote    Back to top    

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

Now I've got that feeling once again, I can't explain; you would not understand
this is now who I am. I have become comfortably numb.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54514
Location: Sydney, Australia
Points: 295617

Post Posted: Thu Apr 04, 2019 11:55 am Reply with quote    Back to top    

Code:
Convert('"', "'", InLink.TheString)

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
Rate this response:  
Not yet rated
cst
Participant



Joined: 04 Apr 2019
Posts: 2

Points: 33

Post Posted: Thu Apr 04, 2019 8:52 pm Reply with quote    Back to top    

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?
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42950
Location: Denver, CO
Points: 221500

Post Posted: Fri Apr 05, 2019 1:01 am Reply with quote    Back to top    

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

Now I've got that feeling once again, I can't explain; you would not understand
this is now who I am. I have become comfortably numb.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54514
Location: Sydney, Australia
Points: 295617

Post Posted: Sat Apr 06, 2019 8:06 pm Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours