Synapse ADF error with Oracle Dynamic partitioning

Question

Synapse ADF error with Oracle Dynamic partitioning

JLopez 61

Hi, I am trying to configure a copy activity with dynamic partition for an Oracle dataset in Synapse pipelines but I get the following error:

Error code 9602
ERROR [07001] [Microsoft][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 1.
ERROR [07001] [Microsoft][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 2.
ERROR [07001] [Microsoft][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 3.
ERROR [07001] [Microsoft][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 4.
Activity ID: e57f0525-25bb-46e9-bb82-ac28a6acfed8

The query I am executing is:

SELECT
*
FROM
schema.table
WHERE ?AdfRangePartitionColumnName <= ?AdfRangePartitionUpbound AND ?AdfRangePartitionColumnName >= ?AdfRangePartitionLowbound

The error pops up using the etl and even in the preview data. The same code is working in Azure Data Factory

Any idea about this issue?

MartinJaffer-MSFT 26,161 Reputation points

2022-12-27T18:53:51.493+00:00
Hello @JLopez and welcome to Microsoft Q&A.

As I understand you are using Copy Activity in Synapse, with Oracle.

If your code really is the same between Data Factory and Synapse, but behaving differently, then this discrepancy is something the product team would want to know about. In that case I'd give you a support ticket so they can dig into the issue.

That said, your query seems a little odd to me. This might be because I'm not an Oracle expert. I assume the words starting with ? are parameters, and Oracly is expecting them to be defined somewhere. I don't see where the values are given. I also assume schema.table isn't the actual object, just something you replaced for privacy.

I would expect to see your query like:

SELECT * FROM schema.table WHERE @{pipeline().parameters.AdfRangePartitionColumnName} <= @{pipeline().parameters.AdfRangePartitionUpbound} AND @{pipeline().parameters.AdfRangePartitionColumnName} >= @{pipeline().parameters.AdfRangePartitionLowbound}

where the pipeline has parameters by those names. Then Data Factory would make the substitutions into the query before sending it.

As yours is currently, using ? , no substitutions happen.

Does this make sense?
JLopez 61 Reputation points

2022-12-29T09:37:34.85+00:00

Hi Martin, thanks for your comment.

Yes, the name of schema and table is part of the example. You can change it with your own code.

The query in Oracle is the same as in TSQL, from my understanding the ? suffixed are the parameters that Synapse/ADF should replace in execution time due to Dynamic Partition configuration.

My query is following the info provided in the Dynamic Partition Help:

So the values in the 3 parameters in the screenshot (Partition Column Name, Partition Upper bound, Partition lower bound) should replace the ? suffixed parameters in the query in execution time because the Dynamic config calculates the ranges. The values in the original etl are not empty.

Thanks for your help.
MartinJaffer-MSFT 26,161 Reputation points

2022-12-29T21:09:32.487+00:00

@JLopez You are correct. My apologies, I forgot about the dynamic partition syntax, and did not give due attention to that.

Okay, so the query looks correct. The error complains about missing the parameters.

Validation prevents publishing from the studio without these parameters. So unless you created the pipeline outside of the studio, by, say Powershell or SDK, this shouldn't be a case of triggering an old version and forgetting to publish the corrections.

(Synapse) Debug also ran validation and stopped me from running without parameters.

So I'm not sure how a run could happen without parameters...

Wait, what if you parameterized the parameters. Err, I mean like below. And then either forgetting to provide a value or having a bad value.

That case is not covered by validation. I could see upstream failure making bad values for the copy activity making this error.

Is yours like that, or do you put in numbers directly?

If directly, then I'll give you support ticket so someone can dig into the logs and get to the bottom of this.
JLopez 61 Reputation points

2022-12-29T21:35:56.94+00:00

Hi Martin, no apoligies, I can only say thank you for your help.

The pipeline is developed in the synapse studio and deployed with devops, the studio is configured with git integration.

My first pipeline version calculates in a previous activity the partition ranges and the incremental column. To do the troubleshooting I hardcoded the values for the Partition values so I have tested both versions with the same result.

I hope this info helps.
MartinJaffer-MSFT 26,161 Reputation points

2023-01-03T17:00:30.597+00:00

Since both hardcoded and dynamic versions produced the same result, I'll send you instructions for support ticket @JLopez
Afzal 0 Reputation points

2023-07-25T11:19:44.9433333+00:00

Hey guys, I am facing the same problem while running this query with dynamic partitions in ADF. Any resolutions so far?

Your answer

MartinJaffer-MSFT 26,161 Reputation points

2022-12-27T18:53:51.493+00:00

Hello @JLopez and welcome to Microsoft Q&A.

As I understand you are using Copy Activity in Synapse, with Oracle.

If your code really is the same between Data Factory and Synapse, but behaving differently, then this discrepancy is something the product team would want to know about. In that case I'd give you a support ticket so they can dig into the issue.

That said, your query seems a little odd to me. This might be because I'm not an Oracle expert. I assume the words starting with ? are parameters, and Oracly is expecting them to be defined somewhere. I don't see where the values are given. I also assume schema.table isn't the actual object, just something you replaced for privacy.

I would expect to see your query like:

SELECT * FROM schema.table WHERE @{pipeline().parameters.AdfRangePartitionColumnName} <= @{pipeline().parameters.AdfRangePartitionUpbound} AND @{pipeline().parameters.AdfRangePartitionColumnName} >= @{pipeline().parameters.AdfRangePartitionLowbound}

where the pipeline has parameters by those names. Then Data Factory would make the substitutions into the query before sending it.

As yours is currently, using ? , no substitutions happen.

Does this make sense?
JLopez 61 Reputation points

2022-12-29T09:37:34.85+00:00

Hi Martin, thanks for your comment.

Yes, the name of schema and table is part of the example. You can change it with your own code.

The query in Oracle is the same as in TSQL, from my understanding the ? suffixed are the parameters that Synapse/ADF should replace in execution time due to Dynamic Partition configuration.

My query is following the info provided in the Dynamic Partition Help:

So the values in the 3 parameters in the screenshot (Partition Column Name, Partition Upper bound, Partition lower bound) should replace the ? suffixed parameters in the query in execution time because the Dynamic config calculates the ranges. The values in the original etl are not empty.

Thanks for your help.
MartinJaffer-MSFT 26,161 Reputation points

2022-12-29T21:09:32.487+00:00

@JLopez You are correct. My apologies, I forgot about the dynamic partition syntax, and did not give due attention to that.

Okay, so the query looks correct. The error complains about missing the parameters.

Validation prevents publishing from the studio without these parameters. So unless you created the pipeline outside of the studio, by, say Powershell or SDK, this shouldn't be a case of triggering an old version and forgetting to publish the corrections.

(Synapse) Debug also ran validation and stopped me from running without parameters.

So I'm not sure how a run could happen without parameters...

Wait, what if you parameterized the parameters. Err, I mean like below. And then either forgetting to provide a value or having a bad value.

That case is not covered by validation. I could see upstream failure making bad values for the copy activity making this error.

Is yours like that, or do you put in numbers directly?

If directly, then I'll give you support ticket so someone can dig into the logs and get to the bottom of this.
JLopez 61 Reputation points

2022-12-29T21:35:56.94+00:00

Hi Martin, no apoligies, I can only say thank you for your help.

The pipeline is developed in the synapse studio and deployed with devops, the studio is configured with git integration.

My first pipeline version calculates in a previous activity the partition ranges and the incremental column. To do the troubleshooting I hardcoded the values for the Partition values so I have tested both versions with the same result.

I hope this info helps.
MartinJaffer-MSFT 26,161 Reputation points

2023-01-03T17:00:30.597+00:00

Since both hardcoded and dynamic versions produced the same result, I'll send you instructions for support ticket @JLopez
Afzal 0 Reputation points

2023-07-25T11:19:44.9433333+00:00

Hey guys, I am facing the same problem while running this query with dynamic partitions in ADF. Any resolutions so far?

Share via

Synapse ADF error with Oracle Dynamic partitioning

Your answer