How to use script files to manage Oracle databases

If you can reduce the input of the command, if you can save the debug test through the command to facilitate the next time you need to use, such as to avoid errors on the input and so on. Although the Oracle database provides a Sql*plus program editing tool that provides a degree of realism, editing, and modification of SQL buffer commands, its ability to edit commands is very small and less convenient to manipulate than script files. So many database management experts like to make a number of commonly used commands into script files. When you need to use it later, call the script file directly. As I now save the useful script files are nearly hundred. When you need to use, as long as the script file to get over, some can be directly executed, and some just a little bit according to the actual situation to modify, you can use.

So, I think the script file is an indispensable tool in Oracle database management, in fact, the use of script files to manage Oracle database is not as difficult as you think. Through this article, perhaps you can eliminate this fear.

First step: Create the script file.

In the Oracle database, there are many ways to create script files. If you can create a script file directly in Notepad, you can also create it directly from the Sql*plus tool. However, the author's opinion is that it is troublesome to create script files directly in Sql*plus. This is mainly because the command editing capability is very effective in the Sql*plus tool. If you cannot use the arrow keys on the keyboard to locate the input, you cannot use the DEL key to delete the input and so on. And it's also difficult to read.

I like the practice is, now some editors, such as text files to write the script statement, and then directly copied to the Sql*plus tool for testing. If the test passes, you can save it for the next time you need to use it. Therefore, the use of Third-party scripting statement editing tools, there is a flaw is not able to directly test the statement. It needs to be manually copied and pasted into an Oracle environment for testing. But this is still worth the effort to order the editors. After all, editing a script statement in a Third-party tool is a lot simpler than editing a script in Sql*plus. I am also not clear, such as Oracle database management software, why not develop a decent script statement editor.

Another point to note is that in the Sql*plus statement, you can save script statements that are not tested or have errors. But it would only be a waste of time to do so. In order to improve the usability of the script statement, the author is strongly recommended that the database administrator should not be too confident, after the completion of scripting statements, be sure to test. It is only after testing that the script statement is useful that it can be archived.

Second, if you use Notepad and other tools to write script statements, in order to be able to call this script file directly in an Oracle environment, it is best to change the extension of the script file to SQL. This is an Oracle database-recognized script file extension.

Also, for the database administrator who first contacted the script file, take a little notice of the format of the script file. In general, similar to ordinary SQL statements. There are only two areas to draw attention to. First, in the last line of the script file, be sure to add the "/" symbol. The function of this symbol is that the notice database can now execute the statement. The second is in the last statement of the SQL statement, do not add ";" Resolution Otherwise, the next time you run this script statement, an error occurs.

Finally, when writing a script file, pay attention to the variable problem of the script file. If the script file requires external incoming parameters, the Sql*plus tool, when executed, replaces the replacement parameters in the script file with these externally passed values, so the life of the substitution parameters in the script file is a little different from the way variables are declared in the Oracle database for functions and procedures. In a script file, you do not need to declare it in advance when you use a replacement parameter. But in the use of the time, directly using &1 &2, etc. to express.

Step two: Edit the script file.

When we use the script file, we often need to adjust it slightly according to the actual situation. There are also two ways to edit a script file. One is through the Sql*plus tool, and the other is the third party independent script editing software.

For some of the less-tuned script files, we can open them directly using the Sql*plus tool, and then edit them. However, for script files that require a lot of modification, the author recommends that the database administrator adopt a third party independent script editing software. The reason is simple, as mentioned above, the Sql*plus tool script command editing function is very weak. If you use this tool to edit ready-made script statements, you may be able to rewrite the workload or rework a light. Therefore, the database administrator to the actual situation, select the appropriate script editing tools.

In addition, in the editing process, pay attention to the correctness of the grammar. In particular, be aware that it is not possible to change its intrinsic format. If you do not accidentally delete the last "/" sign end, and so on. I now like to use Microsoft's own Notepad as a script editing tool. He is more convenient, and no longer need to go to the Internet to find a professional script editing tools. That is, easy, but also to our database administrator's regular needs, why not do it!