How to ignore the escape character in oracle query?

Question

I am facing one issue where I want to copy data from an oracle select query to Azure Data Lake, my select query contains some column details from an oracle view, for example below is my oracle select query (below is formed inside of a for each loop):

SELECT "Update","Name" from T.DATA

Issue that I am facing currently is whenever it is passed inside the copy data activity it is passed as escaped character before the double quotes as below:

"SELECT "Update","Name" from T.DATA

(Not able to give example here but can share screenshot if required)

and above is throwing an error in oracle database, not sure why? I tried using replace function as well to get rid off "", but it does not replace as well, please let me know if there are ways through which we can achieve this?

Answer 1

It could probably help if you specify the environment where this is happening. From your tags, I would guess it is in Azure Data Factory since you've added that tag. Then again, you also have the SQL Server tag, and I don't really see the connection there.

And, yes, people who will want to help you will sure appreciate a screenshot.

Answer 2

Problem was in the parquet file which I was seleting as the destination, the data which I was extracting was an oracle view hence it contains underscores and colon in the column names, hence I was invalid query error so I was looking at the oracle query and thinking what is wrong but the problem is that you cannot insert data into the parquet file having column names with whitespaces, that I found later on this page: https://learn.microsoft.com/en-us/azure/data-factory/connector-troubleshoot-parquet#no-enum-constant

Answer 3

Hi Anmol Ganju ,

Thankyou for posting your query on Microsoft Q&A platform.

As per my understanding, you are facing error when your since the query has double quotes which is not getting escaped.

You can try using the escape character '' before the double quotes and see if that helps.

For better understanding, kindly checkout this video, although it passes the value to dataflow, however, same approach can be followed in your case : Write dynamic expression for SQL with single quotes around parameter value

Kindly check if it helps. If you have any further questions, kindly share more details by posting screenshot of your copy activity configurations.

Answer 4

@Anmol Ganju,

There is no need to use double quotes for the Oracle db table column names. Double quotes in Oracle make column names case sensitive.

So, your query would be as follows:

SELECT Update, Name from T.DATA