How can I use Execute SQL Statement to run an Oracle SQL script? Any other ideas?

(0) Share

Report

Posted on by r96359

228

Hello,

I am creating an automation that includes processing an Oracle SQL script into an Oracle database.

I can connection, execute, and close using a standard sql query.

I very much need to run a SQL script in the same mannor.

Using SQL developer, it's a simple step to paste the sql script into the sql window and click on the run script button instead or run statement.

I would rather not have to copy and paste data into SQL developer to automate this.

Would much rather find a way to use the Execute SQL Statement""action.

I have everything prepared, just need to execute this script.

Is this possible?

If not, any preferred method or guidance?

Many thanks!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (7)

Answers (0)

r96359 228 on at

Like (0)

Report
Copy link

Link copied!

@VJR Hi VJR, any recommendations for what to do here to run an Oracle SQL Script from PAD? Much appreciated.

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report
Copy link

Link copied!

- Create a connection string with the Oracle database

https://www.connectionstrings.com/oracle/

- And then use the Execute Sql statement

If this is not what you were asking then I do not know the answer to this :).

Was this reply helpful? Yes No
r96359 228 on at

Like (0)

Report
Copy link

Link copied!

@VJR Thanks for the reply.

Run Scripts refers to oracle pl/sql. This a good high level overview. It's sql and instructions combined. Execute sql statement won't send this or won't return the results.
www.geeksforgeeks.org/difference-between-sql-and-plsql/amp/

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report
Copy link

Link copied!

Can it work by having those scripts inside Oracle and then calling them from PAD by using "EXEC xxx" statements?

Something like @MisterH did here for Access although that's not fully the same you are looking for but similar.

Was this reply helpful? Yes No
r96359 228 on at

Like (0)

Report
Copy link

Link copied!

@VJR @Thanks for the reply.
The script is calling procedures from the server in addition to a sql insert statement that is built dynamically based on results from PAD. Don't think the sql statement can be preloaded on the server. In total the PL/Sql saves a log, calls a procedure, and processes sql statement. This is all in a string that is passed to the pad execute statement action that doesn't seem to be working.

Was this reply helpful? Yes No
MisterH 51 on at

Like (0)

Report
Copy link

Link copied!

The commands 'PROCEDURE' and 'CALL' seem to also have some functionality in PAD depending of the database being used. I have not had a chance to test against Oracle, so I am not sure which ones will work.

FWIW - even on an Access (accdb) file the performance increase is noticeable

Was this reply helpful? Yes No
r96359 228 on at

Like (0)

Report
Copy link

Link copied!

I have added a few pictures to show you the script. Does this help understand what I am sending and if it's possible?

PL/SQL:

Expected Return:

Was this reply helpful? Yes No