Forum Discussion
Data Extraction from Oracle
Hi Ira_27 ,
Thanks for the clarification. I understand you are using notebook only to read the .sql file and pass it to Copy activity.To avoid notebook, one option you can try is storing the SQL query in a config table and reading it using Lookup activity in pipeline. This way you can directly pass it to Copy activity and remove the HC notebook dependency.
If maintaining queries in table is difficult, another option is to store the SQL files in OneLake and use pipeline activities like Lookup/Get Metadata + read content to read the file instead of notebook. This helps you keep the files separate and also avoids the 5 connection limit from HC notebooks.
Please check if this works for you. Let us know if you need any help on this.
Regards,
Community Support Teams.
Appreciate your responses however this is a third party system that uses Oracle and in order to use Mirroring they need to enable golden gate which is not an option at the moment hence the approach that we are taking.
Given that mirroring is not an option, would love to get additional feedback on this approach.
Hi Ira_27 ,
all right.So that means you’re not able to configure anything on the Oracle server itself?
In that case, your only real options are the Copy Activity or the Copy Job.You can, of course, work with watermarks to ensure that only new or previously uncopied data is loaded. CDC would be an alternative, but that would require configuration either on the server or within the database.
Another advantage of using a Copy Activity or Copy Job is that it avoids the overhead associated with notebooks.
I can imagine the following approach, though it would need to be tested in practice:
- Configuration table (Lakehouse Tables or the Fabric Warehouse)
- Pipeline that reads the configuration table row by row
- ForEach activity with dynamic parallelization
- Copy Activity that loads data from Oracle → Lakehouse Tables
Later, you can use Notebooks to further process the data.
Would that be an option for you?
Best regards
René
Thank you spaceman127 for your response and that is exactly what i am doing right now. The only caviat is that since my Oracle source queries are stored in .sql file i am using notebook to read the content of the file and passing it as a variable in the copy activity. I initially thought of saving the source queries as TEXT in a table but then it add's overhead when someone has to review or update the source queries. Do you have any other recommendation?
Hi Ira_27 ,
Thanks for the clarification. I understand you are using notebook only to read the .sql file and pass it to Copy activity.To avoid notebook, one option you can try is storing the SQL query in a config table and reading it using Lookup activity in pipeline. This way you can directly pass it to Copy activity and remove the HC notebook dependency.
If maintaining queries in table is difficult, another option is to store the SQL files in OneLake and use pipeline activities like Lookup/Get Metadata + read content to read the file instead of notebook. This helps you keep the files separate and also avoids the 5 connection limit from HC notebooks.
Please check if this works for you. Let us know if you need any help on this.
Regards,
Community Support Teams.
- Configuration table (Lakehouse Tables or the Fabric Warehouse)
