Oracle Batch Execution script file

Here are the steps and methods for Oracle batch script file execution

1. Create script file (xx.sql):

For example, file createtable

Create table tb1 (ID varchar2 (), Name varchar2 (50)); commit;/

You can create multiple, but note that you want to end the statement with a semicolon, and note that there are no blank lines in the statement, because when the line is empty, you may get an error in bulk execution.

For example, when you create a table

CREATE TABLE t (ID varchar2, Name varchar2 ());

When this file is executed in bulk, it does not pass, prompting ")" as an illegal statement. It is ok to remove the empty line before the parenthesis.

For some SQL that needs to be committed, you should add a commit statement after execution.

Then there is the "/" for identification, the end of the file statement, optional.

2. Create a command file to execute the script

For example, I created the Excute.bat file, which functions as a call to execute the SQL file

Spool excutelog.logprompt   Start-------------------------------------------@ @CreateTable. sql;@@b.sql;@@c.sql ;p rompt  End----------------------------------------------spool off

Where spool is used to perform write log information, used in combination with spool off, and if there is no spool off, the log is not written to the file.

Prompt is the output information command.

Add the corresponding SQL file name after @@ 后面 ending with a semicolon.

3. Create call Sqlplus, log in and execute Excute.bat file

I created it as a start.bat file

Sqlplus username/[email protected]/fits @excute. Batpause

If the Oracle service is native, it can be used without IP. This is the same as login Sqlplus.

Oracle Batch Execution script file