topic Re: Issues Running SP to Load GTT and Select dta for Lakehouse Table in Same Session Using Copy activity in Data Engineering

Issues Running SP to Load GTT and Select dta for Lakehouse Table in Same Session Using Copy activity

AnamikaMish — Fri, 08 Nov 2024 20:27:06 GMT

I’m working on a procedure that loads data into a global temporary table in Oracle. While the loading works, I’m struggling to retrieve the data from the temp table within the same session for a copy activity to load into a lakehouse table.

Any insights on how to effectively select this data immediately after loading? Thanks for your help

Re: Issues Running SP to Load GTT and Select dta for Lakehouse Table in Same Session Using Copy activity

FabianSchut — Fri, 08 Nov 2024 22:31:22 GMT

Hi, the problem looks similar to this issue: https://community.fabric.microsoft.com/t5/Data-Pipeline/How-to-run-Multiple-SQL-commands-in-Fabric-Pipeline/m-p/4262667#M6121. I suggested there to use a function that returns a table instead of a stored procedure. With this method, you are able to run your code and retrieve a table with only one call. For Oracle that may look something like this: https://stackoverflow.com/questions/2829880/create-an-oracle-function-that-returns-a-table#answer-2830722

Re: Issues Running SP to Load GTT and Select dta for Lakehouse Table in Same Session Using Copy activity

Anonymous — Mon, 11 Nov 2024 06:36:55 GMT

Hi AnamikaMish,

Fabric data pipeline is reference from azure synapse features, so you can take a look at azure synapse temporary table usage limitations and T-sql limitations:

Use temporary tables in Synapse SQL - Azure Synapse Analytics | Microsoft Learn

T-SQL surface area - Microsoft Fabric | Microsoft Learn

In addition, you can also take a look the data pipeline usages if helps:

Data Factory limitations overview - Microsoft Fabric | Microsoft Learn

Regards,

Xiaoxin Sheng

Re: Issues Running SP to Load GTT and Select dta for Lakehouse Table in Same Session Using Copy activity

Anonymous — Thu, 14 Nov 2024 05:41:42 GMT

Hi AnamikaMish ,

Any update on this? Did these suggestions help for your scenario? If that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

Regards,

Xiaoxin Sheng