cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mauslyon
Regular Visitor

Execute ORACLE SQL scripts and save the data

Hi All,

I'm trying to implement a flow as follow:

 

Upon reception of an email with one or more SQL scripts attached, the flow should:

A) create a folder on the company sharepoint (DONE)

B) for each SQL script attached

    B1) save the SQL script with a given naming convention (DONE)

    B2) Connect to the ORACLE database (parameters are fixed)

    B3) Execute the query

    B4) Save the query results in a file (CSV is fine)

C) email back with the results as attached files

 

Is this possible? maybe using some sort of bot?

 

Thanks in advance

M

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @mauslyon 

 

Your mindset is the correct one. Automation is critical here, so not be a bottleneck. 

 

Your idea of forwarding to a ticketing system is a good workaround so that everything doesn't stop on you.

 

It's not the solution you wanted, but it's a solution.

 

Anything else please let me know.

 

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

Cheers
Manuel

View solution in original post

3 REPLIES 3
manuelstgomes
Super User II
Super User II

Hi @mauslyon 

 

I don't think that's possible. You can call a stored procedure. I check the Oracle forums to see if you could create a stored procedure that would run a script for you but got recommendations not to do it:

https://community.oracle.com/thread/3578459

 

Hopefully, someone has a cool workaround, but I think you cannot run "random" scripts via Power Automate. If you think about it, it's also a potential security risk since you would be able to run scripts that could have severe impacts on your database just by dropping a file in a folder.

 

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

Cheers
Manuel

Hi Manuel,

You are right about the security risk but I did not mention that the connection to the DB  is READ-ONLY and the query are made by a very limited number of developers.

The ultimate goal is to avoid the delay caused by the current (nonsensical) procedure:

if a consultant developer needs a certain extraction then he needs to send me an email with the request and the SQL script.

Now, while I may manage the request by myself - since I have DB access - but more likely I will forward his email to our internal call center that will subsequently open a ticket, then the ticket is routed to our DB support group, where it is queued and eventually executed . The results are then sent to me and I forward them to the developer.

You can easily see that if I'm not available the process stops and - ina ny case - my contribution is nil

 

So I was thinking  if it was possible to speed up the process bu automating the whole deal; however maybe it isn't possible

 

Anyway thanks for your help

M

Hi @mauslyon 

 

Your mindset is the correct one. Automation is critical here, so not be a bottleneck. 

 

Your idea of forwarding to a ticketing system is a good workaround so that everything doesn't stop on you.

 

It's not the solution you wanted, but it's a solution.

 

Anything else please let me know.

 

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

Cheers
Manuel

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (2,105)