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

[AnamikaMish]

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

[v-shex-msft]

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

[v-shex-msft]

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

[FabianSchut · Accepted solution]

Hi, the problem looks similar to this issue: https://community.fabric.microsoft.com/t5/Data-Pipeline/How-to-run-Multiple-SQL-commands-in-Fabric-P.... 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-28...