Home » SQL & PL/SQL » Client Tools » Automate sql executions
Automate sql executions [message #684513] Mon, 21 June 2021 14:43 Go to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
Hello,

Any tool that you can recommend for executing scripts on the databases?

For example, we have daily modifications of tables, inserts in other tables and so on...

And we have 700-800 DBs in total.

Something that could be scheduled beforehand even by an operator.

Thank you
Re: Automate sql executions [message #684521 is a reply to message #684513] Tue, 22 June 2021 09:38 Go to previous messageGo to next message
EdStevens
Messages: 1350
Registered: September 2013
Senior Member
Quote:
And we have 700-800 DBs in total.
What is your definition of 'database'? Methinks you are using the mssql definition, which in oracle terms is a schema within a database. I just have a hard time imagining an organization having 700-800 _oracle_ databases --- and I used to work for the US government.

As for the basic question, my preference is to schedule the script through either the OS scheduling system, or within the database, using dbms_scheduler.

If it has to be on demand, and you want it submitted by an essentially non-technical "operator", then no matter what you use there will be some training involved; not only in how to submit it, but how to recognize errors and respond to them. So to that, there is a point at which it doesn't really matter the tool, because it's going to require training anyway.
Re: Automate sql executions [message #684527 is a reply to message #684521] Wed, 23 June 2021 08:55 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
My definition of a database is a cluster database with 2-4 instances each.

I work for a bank, and we create a new DB for each app, not just a schema, a DB in Exadata nodes, then inside we create a schema.

We have in house scripts for running scripts, but they are so complicated to use that we're looking for some other alternatives...
Re: Automate sql executions [message #684528 is a reply to message #684527] Wed, 23 June 2021 10:25 Go to previous messageGo to next message
EdStevens
Messages: 1350
Registered: September 2013
Senior Member
Quote:
We have in house scripts for running scripts, but they are so complicated to use that we're looking for some other alternatives...
We know nothing about your scripts, or your 'scripts for running scripts'. We don't know what it is that makes them complicated to use. Just like trying to debug code you cannot see, it's impossible to recommend improvements on a process you cannot see.
Re: Automate sql executions [message #684541 is a reply to message #684513] Thu, 24 June 2021 15:39 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
The point of this post is not tell you about our scripts, just to ask for recommendations.

Thanks

[Updated on: Thu, 24 June 2021 15:41]

Report message to a moderator

Re: Automate sql executions [message #684544 is a reply to message #684541] Fri, 25 June 2021 10:34 Go to previous messageGo to next message
EdStevens
Messages: 1350
Registered: September 2013
Senior Member
JackBauer wrote on Thu, 24 June 2021 15:39
The point of this post is not tell you about our scripts, just to ask for recommendations.

Thanks
One cannot make valid recommendations without knowing the starting point and the actual problem with that starting point.

I'll waste no more of my time on this thread.
Re: Automate sql executions [message #684546 is a reply to message #684544] Sun, 27 June 2021 15:03 Go to previous messageGo to next message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
Let's say we have nothing as our scripts are very bad.
Re: Automate sql executions [message #684804 is a reply to message #684546] Mon, 23 August 2021 10:36 Go to previous messageGo to next message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

JackBauer wrote on Sun, 27 June 2021 23:03
Let's say we have nothing as our scripts are very bad.
There are different products in the market, most of them cost money and all of them require training.

I suggest that you start building your own script repository.
That will also make you familiar with options and techniques to implement them.

Start with a main variable script that defines which database you work on, and the credentials such as username, connection string ( preferably no plain text passwords )

Then write a script to execute scripts on the location and other conditions, according to the variables set.

When you have something that is working to your satisfaction you can try to implement it in Windows task scheduler, or Oracle's dbms_scheduler

Step by step... good luck

Andrey
Re: Automate sql executions [message #684805 is a reply to message #684513] Mon, 23 August 2021 10:47 Go to previous message
JackBauer
Messages: 24
Registered: February 2021
Junior Member
THanks
Previous Topic: Oracle instance Shutdown
Next Topic: Unable to connect to database
Goto Forum:
  


Current Time: Sun Dec 05 05:56:44 CST 2021