Execute SQL files for Oracle bat batch processing files

In real projects, you may need to use batch files to execute SQL files. The following describes how to use bat batch processing files to call independent SQL files and stored procedures in databases.

I. the BAT file calls the SQL File

First, create a simple SQL file (log. SQL ).

create table log(  PROBLEMID    VARCHAR2(40),  PROBLEMNAME  VARCHAR2(260),  PROBLEMLEVEL VARCHAR2(40),  PROBLEMORDER VARCHAR2(260),  PARENTID     VARCHAR2(40),  ROOTTYPEDESC VARCHAR2(260),  IFLEAF       VARCHAR2(40),  MEMO         VARCHAR2(1000),  IFVALID      VARCHAR2(40),  ROOTTYPE     VARCHAR2(20));exit;

In this SQL file, we create a table.

Next, we will create a bat batch processing file (log. BAT ).

@echo off sqlplus cssp/cssp@gxcssp @H:\bat_sql\log.sql > log.txtexit

In this batchfile, we use an absolute SQL file and output the execution process and result to the log.txt file.

At the end, run the batchfile to view the recorded content of the log.txt file.

SQL * Plus: Release 10.1.0.2.0-production on Thursday April 7 18:26:03 2011 copyright (c) 1982,200 4, Oracle. all rights reserved. connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, OLAP and Data Mining options table has been created. Disconnection from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-productionwith the partitioning, OLAP and Data Mining options

 

Through this log, we can see that the log table is successfully created. Go to the database and check whether the log table is created.

2. BAT files call stored procedures in Databases

This call process has the same principle as the above call process. Here is a simple description.

Assume that a stored procedure in the database is named produce_log.

You only need to modify the independent SQL file as follows:

Execute produce_log;
Exit;

Please try the specific execution result yourself.