Oracle reads and writes operating system files

In SQL * Plus, you can perform simple read/write access to text files in the operating system. Store the SQL statement or PLSQL block code in a text file before calling the text file.

In SQL * Plus, you can perform simple read/write access to text files in the operating system. Store the SQL statement or PL/SQL block code in a text file before calling the text file.

In SQL * Plus, you can perform simple read/write access to text files in the operating system.
For example, store the SQL statement or PL/SQL block code in a text file, and then transfer the text file to the buffer for execution.
You can also save the content in the current buffer to a file, or save the execution results of SQL statements and PL/SQL blocks to the file.

1. commands involved in File Reading include @, get, start, and so on.

1.1 @ command is used to read the content of the specified text file to the buffer and execute it. A text file can be a local file or a file on a remote server.
For a local file, the Command Format of @ command is @ file name.
The file name must specify the complete path. The default extension is. SQL. If the script file uses the default extension, the extension can be omitted in the @ command.
For a remote file, you must store it on a web server and access it through HTTP or FTP. At this time, the command execution format of @ command is (taking HTTP as an example): @ server/file name

When you use the @ command to read a file, the file can contain multiple SQL statements, each statement ends with a semicolon; or it can contain a PL/SQL block.
After the file is read into the buffer, SQL * Plus executes the code in the file in sequence and outputs the execution result to the display.
For example, assume that there is a file named a. SQL under the/home/Oracle directory. The file content is:
SELECT ename FROM emp WHERE empno = 7902; SELECT dname FROM dept WHERE deptno = 10;
Now you want to read this file to the buffer using the @ command. The command execution format is as follows: SQL> @/home/oracle/

@ Command also has a usage, that is, when starting SQL * Plus, read the specified file into the buffer and execute it.
At this time, the @ command and the file name are used as the command line parameters of SQL * Plus in the following format: sqlplus user name/password @ file name
Note that this format is similar to the previous format for using network services,
Sqlplus user name/password @ file name
Sqlplus username/password @ network service name
But there are still differences. Please observe:
Sqlplus username/password @ network service name because both the file name and Network Service name are represented as strings, it is impossible to distinguish whether the file name is used or the network service name.
The difference between the two lies in that there is a space after the user name/password in the first format. In this case, the following parameters are interpreted as a file and the file is loaded into the buffer zone.
In the second format, there is no space after the user name/password, then the following parameters are interpreted as the network service name.

1.2 The get command is similar to the @ command, but it only loads the file into the buffer and does not directly execute it.
The execution format of the get command is: get file name option.
The default file name extension is. SQL, which can be omitted in the get command. Currently, the get command only supports local operating system files.
Two options are available: LIST and NOLIST.
The LIST option specifies that the file content is read to the buffer and output on the display. This is the default option.
The NOLIST option makes the file content not output on the display.
When using the get command, note that only one SQL statement can be contained in a text file and cannot end with a semicolon. You can also include only one PL/SQL block. The block ends with a semicolon.

Pay attention to the differences in these formats when using the @ and get commands. For example, assume that there is a file named B. SQL under the/home/oracle directory. The file content is:
SELECT ename FROM emp WHERE empno = 7902 now read it into the buffer using the get command, and then execute/command to execute it:
SQL> get/home/oracle/B 1 * SELECT ename FROM emp WHERE empno = 7902 SQL>/ENAME -------- FORD

The 1.3 start command is equivalent to the @ command, which is not described here.

2. commands involved in file writing include save and spool.
The save command is used to write the content in the current buffer to an operating system file, and the spool command is used to output the command execution result to an operating system file.

2.1 The format of the save command is: SQL> save file name Option
The option specifies the method in which the file is written. You can use the following three options:
CREATE if the file does not exist, it is created. Otherwise, the command fails to be executed.
APPEND is created if the file does not exist. Otherwise, append the object to the end.
REPLACE is created if the file does not exist. Otherwise, delete the original file and recreate it.
If the complete path is not specified, the file is generated in the current directory. The default file extension is. SQL.
For example, if there is a SELECT statement in the current buffer, you can use the save command to write this statement to the file:
SQL> list 1 * SELECT * FROM emp
SQL> save/home/oracle/aa

2.2 The spool command uses the off-line technology to write SQL * Plus output to a file. It has the following usage:
Spool gets the status of the current spool, which is unavailable by default.
The spool file name starts spool and opens the specified file.
Spool off spool and write SQL * Plus output to the file.
Spool out close spool, write SQL * Plus output to the file, and send it to the printer at the same time.

If you run the spool command in SQL * Plus using a command line, you can start from executing the spool command and opening the file, and all subsequent output, including the error information and the user's keyboard input, will be written to the specified file until "spool off" or "spool out" is encountered ".
However, the write of the information is completed at one time, that is, the information is written to the file only once when "spool off" or "spool out" is executed, including the last executed "spool off" or "spool out" command itself.
The default file extension is. LST, and the default path is the current directory.

The common usage of the spool command is to generate a report.
First, store the well-designed SQL statement in a file, add the spool command before and after the output statement, and then read the file to the buffer for execution.
In this way, only the command execution results are written to the file, excluding the SQL statement itself.

For example, assume that there is a file named c. SQL in the current directory. Its content is:
Spool cc SELECT ename, sal FROM emp WHERE deptno = 10; spool off now reads this file into the buffer and executes it. The execution result is as follows:
SQL> @ c
Ename sal clark 2450 KING 5000 MILLER 1300
The execution results of SQL statements in the file are displayed on the screen, and the cc. LST file is generated in the current directory. The content of the file is exactly the same as that displayed on the screen.