cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Is it possible to fetch data using oracle stored procedure from flow and populate the data into Power APP

Hi 

 

Is there any possibility to fetch data using oracle store procedure (using oracle DB Connection) and populate the fetched data into PowerAPPS 

 

As we have thousands of records , i am having performance issues. 

Please let me know your thoughts. Quick responses are highly appreciated.

 

 

Thanks

Lohitha

14 REPLIES 14
Highlighted
Dual Super User III
Dual Super User III

Yes, it would be possible, but I don't think it would solve your performance issues since Power Automate uses the same connectors that Power Apps does.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted

@Pstork1 

 

Thank you for your quick response.

Can you please give me some directions how to achieve this.

 

Thanks

Lohitha

Highlighted

Sorry, I just looked into the details and the Oracle connector itself doesn't support the use of stored procedures.  So it won't work in Power Automate or Power Apps.  You can retrieve data in a flow and pass it back to Power Apps, but can't use stored procedures in Oracle.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted

@Pstork1 

 

Thank you for your quick response

you mentioned "You can retrieve data in a flow and pass it back to Power Apps using stored procedure"

What do  u exactly mean by "  can't use stored procedures in Oracle" ,Can you please elaborate it.

 

I have a below requirement not sure what is the exact way to achieve it. Can you please give ur thoughts to it and direction to achieve it.

 

I have a requirement to fetch data from oracle database multiple tables

Populate the data automatically based on the above selected values in drop down. Can you please help me how to achieve this.

 

 

For Example

Below is the screen shot from power apps

Please see pic 1

Based on the selection of org name – org id ,legal entity name, legal entity id, invoice currency should be autopopulated ( data source for this I have choosen is share point list, Records are one- one mapping)

 

Another one is below . Please see pic 2

Based on supplier GSL Number – Supplier name, vendor site code, address line1 ,address line2, city,state,country should be autopopulated ( This supplier GSL number should old the values which has been selected on Org name)

Below is my oracle sql query

SELECT PS.SEGMENT1, PS.VENDOR_NAME, SSS.VENDOR_SITE_ID, SSS.VENDOR_SITE_CODE, SSS.ATTRIBUTE14 AS SCX_CODE,

SSS.ADDRESS_LINE1, SSS.ADDRESS_LINE2, SSS.CITY, SSS.STATE, SSS.COUNTRY,

SSS.PAYMENT_METHOD_LOOKUP_CODE, OU.ORG_ID, OU.SHORT_NAME

--OU.SHORT_NAME,

FROM APPS.AP_SUPPLIER_SITES_ALL SSS, APPS.AP_SUPPLIERS PS

, APPS.GETS_INV_OP_UNIT_INFO OU

WHERE

PS.VENDOR_ID=SSS.VENDOR_ID

AND OU.ORG_ID=SSS.ORG_ID

AND OU.SHORT_NAME IN ('GEINIME','GEINIMT','GEINIDL','GERTIO')

AND nvl (sss.inactive_date,sysdate+1) >= sysdate

AND PS.ENABLED_FLAG='Y';

 

Here one thing  need to maintained The selected value of org name/short_name need to maintained thru out of all the columns

Data has to be cascaded based of further above fields,if you see sql query I have lot of and conditions

 

As im newbie to powerapps ,Can you please give me your thoughts how to achieve this.  Quick response is highly appreciated

 

 

Thanks

Lohitha

Highlighted

Check my reply again.  I said you can retrieve data using flow and pass it back to Power Apps, but you CAN NOT use stored procedures.

 

Check this article on the Oracle connector.

https://powerapps.microsoft.com/en-us/blog/connecting-to-oracle-database-from-powerapps-flow-and-log...

In the Known issues section you will find the following:

4. Stored Procedures are currently not supported.  We are looking at adding this functionality in the future.  We would like you to vote for it in the community forum if you are interested.

 

That connector does not support the use of stored procedures whether you are in Power Apps or Power Automate.  You can retrieve the data in either Power Apps or Power Automate using a query as you described, but you can't use a stored procedure.  So there really isn't any benefit to doing it in Power Automate.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted

@Pstork1 

 

https://powerapps.microsoft.com/en-us/blog/connecting-to-oracle-database-from-powerapps-flow-and-log...

 

I have already seen this article , the above does not match to my requirement , as i have connected to oracle DB

 

Please see the attached pic .. where it shows "Execute Stored Procedure" - in case if power apps doesn't support. Then i which case we can use this in flow/power apps?

 

"You can retrieve the data in either Power Apps or Power Automate using a query as you described, but you can't use a stored procedure."

 

If i can retrieve data using query.  below is my query how to pass this query to Power apps or Power Automate .Please give me some directions

 

SELECT PS.SEGMENT1, PS.VENDOR_NAME, SSS.VENDOR_SITE_ID, SSS.VENDOR_SITE_CODE, SSS.ATTRIBUTE14 AS SCX_CODE,

SSS.ADDRESS_LINE1, SSS.ADDRESS_LINE2, SSS.CITY, SSS.STATE, SSS.COUNTRY,

SSS.PAYMENT_METHOD_LOOKUP_CODE, OU.ORG_ID, OU.SHORT_NAME

--OU.SHORT_NAME,

FROM APPS.AP_SUPPLIER_SITES_ALL SSS, APPS.AP_SUPPLIERS PS

, APPS.GETS_INV_OP_UNIT_INFO OU

WHERE

PS.VENDOR_ID=SSS.VENDOR_ID

AND OU.ORG_ID=SSS.ORG_ID

AND OU.SHORT_NAME IN ('GEINIME','GEINIMT','GEINIDL','GERTIO')

AND nvl (sss.inactive_date,sysdate+1) >= sysdate

AND PS.ENABLED_FLAG='Y';

 

 

Thanks

Lohitha

 

Highlighted

OK, I think we are going around in circles.  Evidently stored procedure support has been added to the Oracle connector.  But that support should be available in either Power Apps or Power Automate. Are you saying you can't do it in Power Apps and therefore want to use Power Automate? Or are you saying its slow in Power Apps?  If its too slow in Power Apps it will be just as slow in Power Automate since it uses the same connector.  Offloading the data retrieval will NOT improve performance.  In fact it will make it slower because in addition to retrieving the data you now need to pass it back to Power Apps.

 

So to get back to the original question:

1) Yes, it is possible to retrieve data from Oracle in a flow and pass it back to Power Apps.

2) No, it won't improve performance.  So I wouldn't recommend it if that is your primary reason for doing it.

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted

@Pstork1

If yes, can u pls give me some directions

Thanks
Lohitha
Highlighted

Take a look at the following YouTube demo.  It shows how to invoke a flow from a Power App, run a SQL query and return the results to Power Apps.  Other than using the oracle action to run the stored procedure the process would be the same.

https://www.youtube.com/watch?v=K_H5r0nzwy4&feature=youtu.be

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (12,890)