![Page 1: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/1.jpg)
Dr. Chen, Business Database Systems (Oracle)
Instruction on Creatingsql files and SPOOL
Jason C.H. Chen, Ph.D.
Professor of MIS
School of Business, Gonzaga University
Spokane, WA 99258 USA
![Page 2: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/2.jpg)
Dr. Chen, Business Database Systems (Oracle)2
HW• Create a *.sql script file using Notepad for the Hands-
On Assignments (#3,5,7,9 and 1) on pp. 54-55 (why #1 at the end?)
• Name your file as Oracle_Ch2_Lname_Fname.sql (not .txt). For example, Oracle_Ch2_chen_Jason.sql
• Make sure the file extension should be *.sql not *.txt, otherwise, you can’t do other commands in the future. How? See detailed instruction on the next slide.
• Note that you need to copy and paste each sql file to SQL> (SQL Plus) and make sure each one is working properly.
![Page 3: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/3.jpg)
Dr. Chen, Business Database Systems (Oracle)
Instruction on Creating a Script file and save as *.sql file
Step 1. Create SQL commands using Notepad
Step 2. Click File then Save As..
![Page 4: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/4.jpg)
Dr. Chen, Business Database Systems (Oracle)
Instruction on Creating a Script file and save as *.sql file (cont.)
Step 3. “Save AS” … Screen
Step 4. Change from “Text Document ( *.txt) to “All Files (*.*)” on “Save as type” box
![Page 5: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/5.jpg)
Dr. Chen, Business Database Systems (Oracle)
Instruction on Creating a Script file and save as *.sql file (cont.)
Step 5. Enter a right file name (Oracle_Ch2_Chen_Jason.sql)
![Page 6: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/6.jpg)
Dr. Chen, Business Database Systems (Oracle)6
HW (cont.)
• Then spool it onto the file of Oracle_ch2_Spool_Chen_Jason.txt• How?• See next slide• Please note that the “SPOOLed” file should include both your source sql commands and the result from the Oracle.
![Page 7: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/7.jpg)
Dr. Chen, Business Database Systems (Oracle)7
How to Spool your Script and Output Files
After you tested the script file of Oracle_ch2_Lname_Fname.sql successfully, follow the instructions below to spool both script and output files:
Step 0. Run the following two script files from SQL*Plus (since you have created JLDB tables)– Start c:\oradata\chapter2\JLDB_Drop.sql– Start c:\oradata\chapter2\JLDB_Build.sql
• 1. type the following on SQL>– Spool c:\oradata\Oracle_ch2_Spool_Lname_Fname.txt (make sure your name is entered)
• 2. open Oracle_ch2_Lname_Fname.sql that you already tested• 3. copy and paste all the SQL commands (including all comments) to the
SQL*PLUS • 4. type Spool Off on the SQL>The output should contain your personal information, all SQL commands and
their solution on the .txt file and saved in C: drive (oradata\ folder)
Upload the spooled file (*.txt) to the Bb (under “Assignments & Projects”) by the deadline.
![Page 8: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/8.jpg)
Dr. Chen, Business Database Systems (Oracle)
Hint for #1:Advanced SQL Commands
clear screen;column <fieldname> format a15 wrapcolumn <fieldname> format a15 wordcolumn <fieldname> format a20 heading ‘description for the field’column <fieldname> format a15 truncatecolumn <fieldname> format $999.99-- note: default is wrap
-- formatting output (try it on the SQL>)-- chapter 2, Figure 2-14(b); p. 41clear screenSET LINESIZE 80SET PAGESIZE 45column title format a12 heading 'Book Title' WORDcolumn PubID format 99 heading 'ID'column Cost format $999.99column retail format $999.99
SELECT * FROM books;
![Page 9: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/9.jpg)
Dr. Chen, Business Database Systems (Oracle)
ISBN Book Title PUBDATE ID COST RETAIL DISCOUNT CATEGORY---------- ------------ --------- --- -------- -------- ---------- ------------1059831198 BODYBUILD IN 21-JAN-05 4 $18.75 $30.95 FITNESS 10 MINUTES A DAY0401140733 REVENGE OF M 14-DEC-05 1 $14.20 $22.00 FAMILY LIFE ICKEY4981341710 BUILDING A C 18-MAR-06 2 $37.80 $59.95 3 CHILDREN AR WITH TOOT HPICKS8843172113 DATABASE IMP 04-JUN-03 3 $31.40 $55.95 COMPUTER LEMENTATION3437212490 COOKING WITH 28-FEB-04 4 $12.50 $19.95 COOKING MUSHROOMS3957136468 HOLY GRAIL O 31-DEC-05 3 $47.25 $75.95 3.8 COMPUTER F ORACLE1915762492 HANDCRANKED 21-JAN-05 3 $21.80 $25.00 COMPUTER COMPUTERS9959789321 E-BUSINESS T 01-MAR-06 2 $37.90 $54.50 COMPUTER HE EASY WAY2491748320 PAINLESS CHI 17-JUL-04 5 $48.00 $89.95 4.5 FAMILY LIFE LD-REARING0299282519 THE WOK WAY 11-SEP-04 4 $19.00 $28.75 COOKING TO COOK8117949391 BIG BEAR AND 08-NOV-05 5 $5.32 $8.95 CHILDREN LITTLE DOVE0132149871 HOW TO GET F 11-NOV-06 4 $17.85 $29.95 1.5 SELF HELP ASTER PIZZA9247381001 HOW TO MANAG 09-MAY-03 1 $15.40 $31.95 BUSINESS E THE MANAGE RISBN Book Title PUBDATE ID COST RETAIL DISCOUNT CATEGORY---------- ------------ --------- --- -------- -------- ---------- ------------2147428890 SHORTEST POE 01-MAY-05 5 $21.85 $39.95 LITERATURE MS 14 rows selected.
Here is a better output after formatting output:
![Page 10: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/10.jpg)
Dr. Chen, Business Database Systems (Oracle)
Summary on Oracle_Ch2
- see spool instruction on the next slideHW (Odd numbers only (pp.54-55), but do #1 at the end as it will affects the rest of problems)
- run the following two commands:Start c:\oradata\chapter2\JLDB_Drop.sqlStart c:\oradata\chapter2\JLDB_Build.sqlOutput should be formatted in a very good manner.
Scripting file name: Oracle_ch2_Lname_Fname.sql Spooled file name: Oracle_ch2_Spool_Lname_Fname.txt
Upload the spooled file (*.txt) to the Bb (under “Assignments & Projects”) by the deadline.
![Page 11: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/11.jpg)
Dr. Chen, Business Database Systems (Oracle)
Homework - Hands-On Assignments
Read and Practice all examples on Chapters 2• 1. Run two script files: JLDB_Drop.sql and
JLDB_Build.sql• 2. Read Oracle assignment and create a script file
Oracle_ch2_Lname_Fname.sql for odd number questions on “Hands-on Assignments”. Use appropriate COLUMN statements to produce readable outputs
• 3. Execute and test one problem a time (do #1 at the end, why?) and make sure they are all running successfully. Include class and personal information.
• 4. When you done, spool the script files (see next slide for spooling instructions) and upload the spooled file (Oracle_ch2_Spool_Lname_Fname.txt) to Bb by midnight this coming Sunday. Turn in a hardcopy to me in the class.
Upload the spooled file (*.txt) to the Bb (under “Assignments & Projects”) by the deadline.
![Page 12: Dr. Chen, Business Database Systems (Oracle) Instruction on Creating sql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga](https://reader036.vdocuments.us/reader036/viewer/2022062321/56649ec75503460f94bd391b/html5/thumbnails/12.jpg)
Dr. Chen, Business Database Systems (Oracle)12
How to Spool your Script and Output Files
After you tested the script file of Oracle_ch2_Lname_Fname.sql successfully, follow the instructions below to spool both script and output files:
Step 0. Run the following two script files from SQL*Plus (since you have created JLDB tables)– Start c:\oradata\chapter2\JLDB_Drop.sql– Start c:\oradata\chapter2\JLDB_Build.sql
• 1. type the following on SQL>– Spool c:\oradata\Oracle_ch2_Spool_Lname_Fname.txt (make sure your name is entered)
• 2. open Oracle_ch2_Lname_Fname.sql that you already tested• 3. copy and paste all the SQL commands (including all comments) to the
SQL*PLUS • 4. type Spool Off on the SQL>The output should contain your personal information, all SQL commands and
their solution on the .txt file and saved in C: drive (\oradata\ folder)
Upload the spooled file (*.txt) to the Bb (under “Assignments & Projects”) by the deadline.