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

[r96359]

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!

[r96359]

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

[VJR]

- 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 :).

[r96359]

@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/

[VJR]

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.

[r96359]

@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. 

[MisterH]

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

[r96359]

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: