How to write a script: The script file is a text file which contains executable commands. For SQL script, it contains SQL statements which can be executed against a proper database. For our SQL script that runs against Oracle database. Therefore, we include some of Oracle specific SQLPlus commands that help us make output clear and understandable and allow us to save the output file.
Get the txt file below that contains SQL and SQLPlus commands. Spool commands are for getting the execution results. Set echo on or off are for getting executed commands printed on the screen before the execution result. Others are all SQLcommands. These commands should work as they are without modification.
There are a few simple suggested rules that can make your script robust.
Use remark, rem, to add comment or reminder in the script anything followed rem is not executed.
Spool on command should be the first command and followed
Set echo on should be before any execution commands begin.
Set echo off and spool off at the end of the file.
For SQL DDL commands. Drop the table before create it. this gives you clean database to begin with. The table created last should be dropped first.
For SQL DML, delete the records first before insert them into the table. This allow you begin with empty table. delete the records from child table first if primary key and foreign key constraints exist between the two tables. However, insert into parent table first then insert into child table. Don't forget the child record needs a reference key value from the parent table. Always end with commit command to save the data.
This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.