1 / 12

Instruction on Creating sql files and SPOOL

Instruction on Creating sql files and SPOOL. Jason C.H. Chen , Ph.D. Professor of MIS School of Business, Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu. HW.

mercer
Download Presentation

Instruction on Creating sql files and SPOOL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Instruction on Creatingsql files and SPOOL Jason C.H. Chen, Ph.D. Professor of MIS School of Business, Gonzaga University Spokane, WA 99258 USA chen@jepson.gonzaga.edu

  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.

  3. 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..

  4. 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

  5. Instruction on Creating a Script file and save as *.sql file (cont.) Step 5. Enter a right file name (Oracle_Ch2_Chen_Jason.sql)

  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.

  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) Email me the spooled file (.txt) with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch2 (or bmis441-02_Oracle_ch2 )

  8. Hint for #1:Advanced SQL Commands clear screen; column <fieldname> format a15 wrap column <fieldname> format a15 word column <fieldname> format a20 heading ‘description for the field’ column <fieldname> format a15 truncate column <fieldname> format $999.99 -- note: default is wrap -- formatting output (try it on the SQL>) -- chapter 2, Figure 2-14(b); p. 41 clear screen SET LINESIZE 80 SET PAGESIZE 45 column title format a12 heading 'Book Title' WORD column PubID format 99 heading 'ID' column Cost format $999.99 column retail format $999.99 SELECT * FROM books;

  9. Here is a better output after formatting output: ISBN Book Title PUBDATE ID COST RETAIL DISCOUNT CATEGORY ---------- ------------ --------- --- -------- -------- ---------- ------------ 1059831198 BODYBUILD IN 21-JAN-05 4 $18.75 $30.95 FITNESS 10 MINUTES A DAY 0401140733 REVENGE OF M 14-DEC-05 1 $14.20 $22.00 FAMILY LIFE ICKEY 4981341710 BUILDING A C 18-MAR-06 2 $37.80 $59.95 3 CHILDREN AR WITH TOOT HPICKS 8843172113 DATABASE IMP 04-JUN-03 3 $31.40 $55.95 COMPUTER LEMENTATION 3437212490 COOKING WITH 28-FEB-04 4 $12.50 $19.95 COOKING MUSHROOMS 3957136468 HOLY GRAIL O 31-DEC-05 3 $47.25 $75.95 3.8 COMPUTER F ORACLE 1915762492 HANDCRANKED 21-JAN-05 3 $21.80 $25.00 COMPUTER COMPUTERS 9959789321 E-BUSINESS T 01-MAR-06 2 $37.90 $54.50 COMPUTER HE EASY WAY 2491748320 PAINLESS CHI 17-JUL-04 5 $48.00 $89.95 4.5 FAMILY LIFE LD-REARING 0299282519 THE WOK WAY 11-SEP-04 4 $19.00 $28.75 COOKING TO COOK 8117949391 BIG BEAR AND 08-NOV-05 5 $5.32 $8.95 CHILDREN LITTLE DOVE 0132149871 HOW TO GET F 11-NOV-06 4 $17.85 $29.95 1.5 SELF HELP ASTER PIZZA 9247381001 HOW TO MANAG 09-MAY-03 1 $15.40 $31.95 BUSINESS E THE MANAGE R ISBN Book Title PUBDATE ID COST RETAIL DISCOUNT CATEGORY ---------- ------------ --------- --- -------- -------- ---------- ------------ 2147428890 SHORTEST POE 01-MAY-05 5 $21.85 $39.95 LITERATURE MS 14 rows selected.

  10. Summary on Oracle_Ch2 - see spool instruction on the next slide HW (Oddnumbers 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.sql Start c:\oradata\chapter2\JLDB_Build.sql Output 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 Email me with the spooled file using attachment to: chen@jepson.gonzaga.edu with subject title of bmis441_Oracle_ch2

  11. Summary on Oracle_Ch2 (cont.) Email me with one attachment (Oracle_ch2_Spool_Lname_Fname.) to: chen@jepson.gonzaga.edu with subject title of bmis441_Oracle_ch2 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 email the file (Oracle_ch2_Spool_Lname_Fname.txt) to me by the midnight this coming Sunday. Turn in a hardcopy to me in the class.

  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) Email me the spooled file (.txt) with attachment to: chen@jepson.gonzaga.edu with subject title of Bmis441-01_Oracle_ch2 (or bmis441-02_Oracle_ch2)

More Related