SQL*Plus Script File

1. SQL*Plus Script File • SQL*Plus editor can have only one SQL command • Insert command inserts only one record at a time • Use Oracle script file • A program in SQL

2. Creating SQL*Plus Script File • DO NOT use Oracle editor to create script file • Use any Test editor such as NotePad or .NET File extension: *.sql DOS file name conversion: no space Multiple SQL commands Use semicolon at the end of each command

3. Change the Working Folder • SQL*Plus has a default working folder We may not have full rights in the folder • Change the working folder File menu Open… Browse to the folder (J:\CS 3630\Oracle) Select a file (Lab7.sql) Open • This does not execute the script file You could get an error message Enter to get SQL*Plus prompt

4. Execute Script File • Make sure the script file is in the working folder • Execute SQL script file @Lab7 @Lab7.SQL • Do not use command run!

5. Dropping Tables before Creating • Cannot create a table if a table with the same name exists in the database Drop table test1; -- You may get an error message -- Since no table exists Create table test1 ( …

6. Parent and Child Tables Drop table test2; Drop table test1; create table test1( c1 char(4) primary key, …); Create Table test2( b1 char(4) references test1(c1), b2 integer);

7. Script File: Style • File Header Full Name UserName Date Course • Add some comments • Blank lines to separate commands • Each line is not too long • Alignment • Different clauses on separate lines

8. Example OraclePlus.sql SQL*Plus command Pause Enter to continue