Error loading CSV into Oracle using Copy Data Activity

Tim Burke 20

Trying to load a CSV file into Oracle. I am able to import the schema and map the source to the target. When I go to run the pipeline, I get the following error:

UserErrorOdbcOperationFailed

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]General error. setStmtType failed

ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error in SQL Parser.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]General error. setStmtType failed

ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error in SQL Parser.,Source=msora28.dll,'

Anyone else see this error and how did you resolve it? Thanks!

phemanth 14,220 Reputation points Microsoft External Staff

2024-12-03T18:09:51.3266667+00:00

Hi @Tim Burke

Thanks for reaching out to Microsoft Q&A.

To resolve this, try adding the following to the additional connection properties. Property Name: WorkArounds Value: 536870912

refer the thread https://learn.microsoft.com/en-us/answers/questions/1372157/copy-activity-azure-sql-to-oracle

For more details on the WorkArounds values, you can refer to this documentation: WorkArounds and WorkArounds2 options."

https://docs.progress.com/bundle/datadirect-hybrid-data-pipeline-46/page/WorkArounds-and-WorkArounds2-options.html

Hope this helps. Do let us know if you any further queries.
phemanth 14,220 Reputation points Microsoft External Staff

2024-12-04T17:35:20.0166667+00:00
Hi @Tim Burke @Tim Burke (DJCS) Thanks for the additional details! please check these troubleshooting steps:

Check CSV Formatting: Ensure that your CSV file is properly formatted. Each value should be enclosed in quotes if it contains commas or special characters. For example:

"value1","value2","value3"

ODBC Driver Settings: Sometimes, specific settings in the ODBC driver can cause issues. You might want to try adjusting the WorkArounds2 property as well. For example:

Property Name: WorkArounds2

Value: 2

SQL*Loader: If the issue persists, consider using Oracle’s SQL*Loader tool, which is specifically designed for loading data from CSV files into Oracle tables. Here’s a basic example of how to use it:

Control File (load.ctl):

LOAD DATA INFILE 'yourfile.csv' INTO TABLE your_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (column1, column2, column3)

Command:

sqlldr username/password@database control=load.ctl

Data Type Mismatch: Ensure that there are no data type mismatches between your CSV file and the Oracle table. Even though both are VARCHAR2, sometimes hidden characters or encoding issues can cause problems.

Log Files: Check the detailed error logs generated by Azure Data Factory and the ODBC driver.

I hope the above steps will resolve the issue, please do let us know if issue persists.
Thank you
phemanth 14,220 Reputation points Microsoft External Staff

2024-12-05T18:09:43.11+00:00

@Tim Burke We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Tim Burke 20 Reputation points

2024-12-05T20:01:54.9633333+00:00

@phemanth ... I figured out what was causing the problem, it was a permissions issue with trying to do a truncate table call in the "pre-copy script" attribute. I removed it and put "DELETE FROM FOX_STG','STG_XML_FILE" and it worked fine.

So I have a package that we use to truncate tables. I can run it in SQL Developer. When I put it in the "pre-copy script" attribute - CALL FOX_STG.PKG_DB_UTIL.SP_TRUNCATE_TABLE('FOX_STG','STG_XML_FILE'); - and run it, I get a new error:

Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00933: SQL command not properly ended

,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [42000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-00933: SQL command not properly ended

,Source=msora28.dll,'

I'll add the Workaround2 value you mentioned above, but is there another value I should add for this new error?

Accepted answer

phemanth 14,220 Reputation points Microsoft External Staff

2024-12-06T06:15:29.7033333+00:00

@Tim Burke I'm glad to hear that you were able to resolve the issue by removing the truncate table call and using the "DELETE" statement instead.

Regarding the new error you are encountering with the "pre-copy script" attribute, it seems to be related to a syntax error in the SQL command. Specifically, the error message "ORA-00933: SQL command not properly ended" indicates that there is a problem with the syntax of the SQL command.

To resolve this issue, you may want to double-check the syntax of the SQL command and make sure that it is properly formatted. You can also try running the SQL command directly in Oracle SQL Developer to see if it produces any errors or warnings.

If the issue persists, you can try adjusting the WorkArounds2 property as I mentioned earlier. The value for WorkArounds2 that you can try is 2.

Hope this helps. Please do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.
Tim Burke 20 Reputation points

2024-12-06T17:01:24.7433333+00:00

@phemanth , thanks for your help. I got it working with playing around with the format. It's working now!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

Tim Burke 20 Reputation points

2024-12-03T20:40:01.6966667+00:00

Hi @phemanth

I'm still getting the same error after adding the WorkArounds Value: 536870912.

Now, I'm trying to load a CSV file with all string values into an Oracle Table that has all VARCHAR2 columns. Not sure if this matters.

Thanks for your help!
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Error loading CSV into Oracle using Copy Data Activity

1 additional answer

Your answer