How to automatically generate batches for batch execution of SQL scripts
This article mainly introduces how to automatically generate batch processing for batch execution of SQL scripts, which is very detailed and has a certain reference value. Friends who are interested must read it!
Scene:
DBA exported to me all the storage, functions and other object creation scripts, there are thousands of files.
Now you need to import these object creation scripts into another library. What can I do about it?
It is obviously not realistic to execute one by one manually.
So a batch is written manually, all the files are formed into a .SQL script, and finally imported into the target library in the form of @ generated .SQL script.
OS environment: WINDOWS xp
The script reads as follows:
The code is as follows:
@ echo off
If exist list.sql del list.sql / q
: input
Cls
Set input=:
Set / p input= Please enter the path you want to judge:
Set "input=%input:" =% "
:: the above sentence is to determine whether quotation marks exist in% input%, and if so, remove them.
If "% input%" = ":" goto input
If not exist "input%" goto input
For / f "delims="% I in ('dir / b / amurd / s "% input%") do echo @ @% ~ fnxi > > list.sql
If not exist list.sql goto no_file
Start list.sql
Exit
: no_file
Cls
An error occurred in echo cur_dir% and the list.sql script was not generated successfully!
Pause
Use:
After saving as a .bat type file, double-click to execute.
Enter the path to your script:
For example, under the CRY folder of my E disk, there are the following types of files:
TEST.PRC
..
123\ TEST1.FNC (with subfolders 123)
..
TEST2.VW
.
After executing the batch, the contents of the final generated list.sql script file are as follows (the file is automatically opened with the default editor after the batch is executed):
@ @ E:\ cry\ TEST.PRC
..
@ @ E:\ cry\ 123\ TEST1.FNC
..
@ @ E:\ cry\ TEST2.VW
..
Open SQLPLUS to specify the user to log in to the database, and then execute: (my script file is generated on the d disk)
@ D:\ list.sql
In this way, all objects are automatically generated under the specified user.
-
Note: many of the objects generated in this way are invalid, because they are not generated sequentially, so it is recommended to compile all invalid objects after automatic execution.
The above is all the contents of the article "how to automatically generate batches for batch execution of SQL scripts". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
