Bulk execution of SQL statements in Sql*plus

Bulk execution of SQL statements in Sql*plus

Today because of the needs of the work, in order to execute a large number of SQL statements in CMD, for the Oracle learning is still in the introductory stage, I can only bite the bullet around to find information (mainly web materials, including their own ebook), and finally all the SQL statements executed once.

First, all the SQL statements that will be executed are written to one of the SQL files. e.g. F:\test.sql;

Then, connect to the database by using Sqlplus, such as:

sql> sqlplus test/[email protected];

Then execute the following command:

Sql> @f:\test.sql; @+ Address

... ...

It was so simple to carry out the success.

In addition, in the execution of an INSERT statement in Oracle, the string contains an escape character, such as &, is not directly manipulated, after the & will be treated as a variable, and therefore does not meet our requirements. It is specifically:

To insert data into table A (id,name), if the data you want to insert is: (1, ' tom&jim '). The following statement cannot be directly at this time:

Sql>insert into A values (' 1 ', ' tom&jim ');

There are two ways to meet our requirements:

1. Replace & with ASCII characters, such as:

Sql>insert into A Values (' 1 ', ' Tom ' | | Chr (38) | | Jim ');

2. Set the define in Oracle to OFF, as follows:

Sql>set define off;

Sql>insert into A values (' 1 ', ' tom&jim ');

This can also be done successfully.

Bulk execution of SQL statements in Sql*plus