Скачать презентацию How to log on to the RCRAInfo testing development Скачать презентацию How to log on to the RCRAInfo testing development

aa3ea27dba3624598ac9d3d06f6ad882.ppt

  • Количество слайдов: 24

How to log on to the RCRAInfo testing/development database 2 How to log on to the RCRAInfo testing/development database 2

SELECT DEFAULT FOLDER/WORKING LIBRARY • From SQL*Plus login • Select File/Open 3 SELECT DEFAULT FOLDER/WORKING LIBRARY • From SQL*Plus login • Select File/Open 3

BASIC QUERY SELECT … column name(s) FROM … table name(s) WHERE … (condition) GROUP BASIC QUERY SELECT … column name(s) FROM … table name(s) WHERE … (condition) GROUP BY … (grouping multiple rows into one row) ORDER BY … (specific order) Whenever you have a SELECT, you must also have a FROM clause 4

Creating Reports and Group Summaries column Penalty_Amt head 'Penalty|Amount' for $9, 999, 999. 99 Creating Reports and Group Summaries column Penalty_Amt head 'Penalty|Amount' for $9, 999, 999. 99 column penalty_type head 'Penalty|Type' format a 12 column activity_location head 'Activity|Location' format a 17 set linesize 80 set pagesize 200 ttitle 'Total Penalties based on Activity Location' break on activity_location on report spool penaltysum. txt Select activity_location, penalty_type, sum(penalty_amount) Penalty_Amt from cpenalty 3 group by activity_location, penalty_type / spool off Fri Jul 22 page 1 Total Penalties based on Activity Location Penalty Type Penalty Amount ------------------ AK FMP $17, 672, 082. 00 PMP $11, 903, 384. 00 FMP $28, 604, 528. 22 FSC $399, 723. 00 PMP $33, 236, 869, 382. 00 SCR $87, 678. 00 FMP $4, 006, 217. 40 FSC $928, 356. 16 PMP $14, 265, 026. 00 AL AR 5

REM LIST LAST INSPECTED column city format a 15 column county format a 15 REM LIST LAST INSPECTED column city format a 15 column county format a 15 column evaluation_type head 'EVAL|TYPE' format a 9 column handler_name format a 30 set linesize 100 set wrap off ttitle ' LAST DATE INSPECTED ' spool last_inspected. txt SELECT DISTINCT TO_CHAR(A. EVALUATION_START_DATE, 'DD-MON-YYYY') "EVAL DATE", A. EVALUATION_TYPE, A. AGENCY, HB. HANDLER_ID "FACILITY ID", HB. HANDLER_NAME "FACILITY NAME", H. LOCATION_CITY, C. COUNTY_NAME COUNTY FROM HBASIC HB, HHANDLER 2 H, CEVALUATION 3 A, LU_COUNTY C WHERE ( ((HB. STATE = '&&XSTATE') and (HB. HANDLER_ID = H. HANDLER_ID) and (HB. STATE = H. ACTIVITY_LOCATION) and (H. COUNTY_CODE = C. COUNTY_CODE) and (H. COUNTY_OWNER = C. OWNER) /*Rownum limits execution for testing */ and (ROWNUM < 20 ) ) and ((HB. HANDLER_ID = A. HANDLER_ID(+)) and (HB. STATE = A. ACTIVITY_LOCATION(+)) AND (EVALUATION_START_DATE < '&FDATE') AND EVALUATION_START_DATE = /*Selects the highest Inspection date */ (SELECT MAX(EVALUATION_START_DATE) FROM CEVALUATION 3 B WHERE A. ACTIVITY_LOCATION = '&&XSTATE' AND A. HANDLER_ID = B. HANDLER_ID))) ORDER BY HB. HANDLER_ID / undefine xstate spool off 6

Tue Jul 26 page 1 LAST DATE INSPECTED EVAL DATE TYPE A FACILITY ID Tue Jul 26 page 1 LAST DATE INSPECTED EVAL DATE TYPE A FACILITY ID FACILITY NAME CITY --------- - --------------------------10 -DEC-2003 CEI E FLD 003952033 MOSAIC FERTILIZER LLC BARTOW 02 -JUL-2002 CAV S FLD 040867699 G E GENERATORS PENSACOLA LLC PENSACOLA 17 -JUL-2000 CEI X FLD 057512741 HORDIS BROTHERS INC TAMPA 08 -DEC-2003 CEI E FLD 064696107 MOSAIC FERTILIZER LLC TAMPA 18 -JUL-2000 CEI E FLR 000017459 FUTRONIX INC HOMOSASSA 05 -JUL-2002 CEI S FLR 000021659 BODREE PRINTING CO INC PENSACOLA 07 -SEP-1999 SNN S FLR 000031161 SOVEREIGN AMERICA INC PORT RICHEY 09 -SEP-2002 SNN S FLR 000032938 FLORIDA MIRROR TAMARAC 24 -NOV-2003 CSE S FLR 000046508 BEST USED PARTS II ORLANDO 05 -JUL-2002 SNN S FLR 000088443 MUSIC MASTERS TALLAHASSEE 10 rows selected. 7

JOINS Combines columns and data from two or more tables. The tables are listed JOINS Combines columns and data from two or more tables. The tables are listed in the FROM clause and the relationship between the tables in specified in the WHERE clause. Select … from table_1, table_2 where table_1. column_name = table_2. column_name; If you omit the where clause you will create a Cartesian product. A Cartesian product always generates a result of: (number of rows from tbl 1) * (number of rows from tbl 2) Example: tbl 1 has 200 rows tbl 2 has 200 rows Cartesian product result = 200 * 200 = 40, 000 rows An INNER JOIN only selects data where the record exists in both tables. WHERE (H. HANDLER_ID = C. HANDLER_ID) An OUTER JOIN will select data where the record exists in both tables or when the JOIN from record exists but the to side record does not exist. WHERE (H. HANDLER_ID = C. HANDLER_ID(+)) 8

Inner Join Examples column viol_type_own format a 13 column former_citation format a 25 column Inner Join Examples column viol_type_own format a 13 column former_citation format a 25 column determined_date format a 15 column violation_type_desc format a 55 set linesize 150 spool inner_joint. txt select distinct v 3. handler_id, v 3. violation_type_owner, v 3. violation_type, vt. violation_type_desc, v 3. former_citation, v 3. determined_date from cviolation 3 v 3, lu_violation_type vt where v 3. violation_type_owner = vt. owner and v 3. violation_type = vt. violation_type order by handler_id; spool off HANDLER_ID VIOLATION_TYPE_DESC FORMER_CITATION DETERMINED_DATE --------------------------------- -------- AK 0000007906 262. A Standards Applicable to Generators of HW: General AK 0000007922 262. A Standards Applicable to Generators of HW: General FR - 40 CFR 261. 5(g)(3) 21 -SEP-04 AK 0000007922 262. A Standards Applicable to Generators of HW: General FR - 40 CFR 279. 22 21 -SEP-04 AK 0000007922 262. A Standards Applicable to Generators of HW: General AK 0000007922 262. C Standards Applicable to Generators of HW: Pre-Transport 20 -SEP-91 26 -AUG-93 FR - 40 CFR 273. 15(c) 21 -SEP-04 Requirements AK 0000009852 262. A Standards Applicable to Generators of HW: General 16 -NOV-93 AK 0000010447 262. A Standards Applicable to Generators of HW: General 15 -AUG-90 AK 0000033902 262. A Standards Applicable to Generators of HW: General 15 -SEP-93 AK 0000033910 262. A Standards Applicable to Generators of HW: General 22 -SEP-93 AK 0000075812 262. A Standards Applicable to Generators of HW: General 17 -DEC-91 AK 0000084020 262. A Standards Applicable to Generators of HW: General 28 -FEB-94 AK 0000084020 262. A Standards Applicable to Generators of HW: General 28 -APR-94 AK 0000094888 262. A Standards Applicable to Generators of HW: General FR - 40 CFR 270. 1(c) 02 -AUG-00 AK 0000094888 262. A Standards Applicable to Generators of HW: General FR - 40 CFR 279. 12(a) 02 -AUG-00 AK 0000094888 262. A Standards Applicable to Generators of HW: General FR - 40 CFR 279. 12(b) 02 -AUG-00 AK 0000094888 262. A Standards Applicable to Generators of HW: General FR - 40 CFR 279. 61(a) 02 -AUG-00 9

column viol_type_own format a 13 column former_citation format a 25 column handler_name format a column viol_type_own format a 13 column former_citation format a 25 column handler_name format a 40 column violation_type_desc format a 55 set linesize 150 spool inner_join 2. txt select distinct v 3. handler_id, hu 3. handler_name, v 3. violation_type, vt. violation_type_desc, v 3. former_citation from cviolation 3 v 3, lu_violation_type vt, hreport_univ 3 hu 3 where v 3. violation_type_owner = vt. owner and v 3. violation_type = vt. violation_type and v 3. handler_id = hu 3. handler_id and v 3. activity_location = hu 3. activity_location order by handler_id / spool off HANDLER_ID HANDLER_NAME FORMER_CITATION VIOLATION_TYPE_DESC --------------------------------------AK 0000007906 ALASKA ST OF LEGISLATIVE AFFAIRS PRINTIN 262. A Standards Applicable to Generators of HW: General AK 0000007922 ALASKA RAILROAD CORP FAIRBANKS YD FR - 40 CFR 261. 5(g)(3) 262. A Standards Applicable to Generators of HW: General AK 0000007922 ALASKA RAILROAD CORP FAIRBANKS YD FR - 40 CFR 279. 22 262. A Standards Applicable to Generators of HW: General AK 0000007922 ALASKA RAILROAD CORP FAIRBANKS YD Transport FR - 40 CFR 273. 15(c) 262. C Standards Applicable to Generators of HW: Pre- AK 0000009852 REFUGE COVE MARINA 262. A Standards Applicable to Generators of HW: General AK 0000010447 USDOT CG JUNEAU STA 262. A Standards Applicable to Generators of HW: General 10

Outer Join Example Can evaluations with no violations be shown also? select e. handler_id, Outer Join Example Can evaluations with no violations be shown also? select e. handler_id, e. activity_location, e. evaluation_ seq, e. evaluation_start_date, e. agency, e. evaluation_type, e. found_violation, l. violation_handler_id, l. violation_activity_location, l. violation_ seq, l. violation_determined_by_agency from cevaluation 3 e, cln_evaluation_violation 3 l where e. HANDLER_ID = 'ALD 000622464' and e. evaluation_start_date >= '01 -Jan-2003' and e. handler_id = l. evaluation_handler_id(+) and e. activity_location = l. evaluation_activity_location(+) and e. evaluation_seq = l. evaluation_seq(+) and e. evaluation_start_date = l. evaluation_start_date(+) and e. agency = l. evaluation_agency(+); 11

Fri Jul 22 page 1 Evaluations with NO Violations HANDLER_ID AC EVALUATIO A EVA Fri Jul 22 page 1 Evaluations with NO Violations HANDLER_ID AC EVALUATIO A EVA FOUND_VIOLATION_HA VI VIOLATION_SEQ V ------ -- ----------- -- ------- ALD 000622464 AL 001 25 -MAR-03 X CEI Y ALD 000622464 AL 44 X ALD 000622464 AL 001 25 -MAR-03 X CEI Y ALD 000622464 AL 45 X ALD 000622464 AL 001 25 -MAR-03 X CEI Y ALD 000622464 AL 46 X ALD 000622464 AL 001 26 -MAR-03 S CEI Y ALD 000622464 AL 157 S ALD 000622464 AL 001 26 -MAR-03 S CEI Y ALD 000622464 AL 158 S ALD 000622464 AL 001 26 -MAR-03 S CEI Y ALD 000622464 AL 159 S ALD 000622464 AL 001 26 -MAR-03 S CEI Y ALD 000622464 AL 160 S ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 161 S ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 162 S ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 163 S ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 164 S ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 165 S ALD 000622464 AL 001 30 -MAY-03 S NRR N ALD 000622464 AL 001 07 -AUG-03 S NRR N ALD 000622464 AL 001 02 -SEP-03 S NRR N ALD 000622464 AL 001 04 -SEP-03 S NRR N ALD 000622464 AL 001 19 -JAN-04 S NRR N ALD 000622464 AL 001 30 -JAN-04 S FRR N ALD 000622464 AL 001 17 -FEB-04 S NRR N ALD 000622464 AL 001 08 -MAR-04 S NRR N ALD 000622464 AL 001 22 -MAR-04 S NRR N 12

Fri Jul 22 page 2 Evaluations with NO Violations HANDLER_ID AC EVALUATIO A EVA Fri Jul 22 page 2 Evaluations with NO Violations HANDLER_ID AC EVALUATIO A EVA FOUND_VIOLATION_HA VI VIOLATION_SEQ V ------ -- ----------- -- ------- ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 166 S ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 167 S ALD 000622464 AL 001 06 -APR-04 S CEI Y ALD 000622464 AL 168 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 169 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 170 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 171 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 172 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 173 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 174 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 175 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 176 S ALD 000622464 AL 001 30 -NOV-04 S CEI Y ALD 000622464 AL 177 S ALD 000622464 AL 001 15 -APR-04 S FRR N ALD 000622464 AL 001 22 -APR-04 S OAM N ALD 000622464 AL 001 30 -APR-04 S NRR N ALD 000622464 AL 001 17 -MAY-04 S NRR N ALD 000622464 AL 001 28 -JUN-04 S NRR N ALD 000622464 AL 001 13 -AUG-04 S FCI N ALD 000622464 AL 001 24 -SEP-04 S FRR N ALD 000622464 AL 002 30 -MAY-03 S NRR N ALD 000622464 AL 002 17 -FEB-04 S NRR N 13

Fri Jul 22 page 3 Evaluations with NO Violations HANDLER_ID AC EVALUATIO A EVA Fri Jul 22 page 3 Evaluations with NO Violations HANDLER_ID AC EVALUATIO A EVA FOUND_VIOLATION_HA VI VIOLATION_SEQ V ------ -- ----------- -- ------- ALD 000622464 AL 002 30 -APR-04 S NRR N ALD 000622464 AL 002 17 -MAY-04 S NRR N ALD 000622464 AL 003 30 -MAY-03 S NRR N ALD 000622464 AL 003 17 -FEB-04 S NRR N ALD 000622464 AL 004 30 -MAY-03 S NRR N ALD 000622464 AL 004 17 -FEB-04 S NRR N ALD 000622464 AL 005 30 -MAY-03 S NRR N ALD 000622464 AL 005 17 -FEB-04 S NRR N ALD 000622464 AL 006 30 -MAY-03 S NRR N ALD 000622464 AL 007 30 -MAY-03 S NRR N ALD 000622464 AL 312 08 -MAR-04 S NRR N 53 rows selected. 14

Handler Id ALD 000622464 column act_loc format a 7 column eval_seq format a 7 Handler Id ALD 000622464 column act_loc format a 7 column eval_seq format a 7 column eval_start_date format a 12 column eval_type format a 9 spool not_exists. txt select e. handler_id, e. activity_location act_loc, e. evaluation_seq eval_seq, e. evaluation_start_date eval_start_date, e. agency, e. evaluation_type eval_type from cevaluation 3 e where e. HANDLER_ID = 'ALD 000622464' and e. evaluation_start_date >= '01 -Jan-2003' and not exists (select * from cln_evaluation_violation 3 l where e. handler_id = l. evaluation_handler_id and e. activity_location = l. evaluation_activity_location and e. evaluation_seq = l. evaluation_seq and e. evaluation_start_date = l. evaluation_start_date and e. agency = l. evaluation_agency) / spool off 15

HANDLER_ID ------ALD 000622464 ALD 000622464 ALD 000622464 ALD 000622464 ALD 000622464 ALD 000622464 ACT_LOC HANDLER_ID ------ALD 000622464 ALD 000622464 ALD 000622464 ALD 000622464 ALD 000622464 ALD 000622464 ACT_LOC ------AL AL AL AL AL AL EVAL_SE ------001 001 001 001 002 002 003 EVAL_START_D ------30 -MAY-03 07 -AUG-03 02 -SEP-03 04 -SEP-03 19 -JAN-04 30 -JAN-04 17 -FEB-04 08 -MAR-04 22 -MAR-04 15 -APR-04 22 -APR-04 30 -APR-04 17 -MAY-04 28 -JUN-04 13 -AUG-04 24 -SEP-04 30 -MAY-03 17 -FEB-04 30 -APR-04 17 -MAY-04 30 -MAY-03 A S S S S S S EVAL_TYPE ----NRR NRR NRR FRR OAM NRR NRR FCI FRR NRR NRR NRR FOUND_VIOLATION -------N N N N N N HANDLER_ID ------ALD 000622464 ALD 000622464 ACT_LOC ------AL AL EVAL_SE ------003 004 005 006 007 312 EVAL_START_D ------17 -FEB-04 30 -MAY-03 08 -MAR-04 A S S S S EVAL_TYPE ----NRR NRR FOUND_VIOLATION -------N N N N 29 rows selected. 16

SUBQUERIES A query used within another query. Data from subquery is not displayed. Select SUBQUERIES A query used within another query. Data from subquery is not displayed. Select … from … where … (subquery …) Subqueries always performed first and results are passed back to main query. 17

select ceg. cnt ceg, sqg. cnt sqg, lqg. cnt lqg from (select count(*) cnt select ceg. cnt ceg, sqg. cnt sqg, lqg. cnt lqg from (select count(*) cnt from (select handler_id, handler_name, genstatus from hreport_univ 3 where genstatus = 'CEG') ) ceg, (select count(*) cnt from (select handler_id, handler_name, genstatus from hreport_univ 3 where genstatus = 'SQG') ) sqg, (select count(*) cnt from (select handler_id, handler_name, genstatus from hreport_univ 3 where genstatus = 'LQG') ) lqg / CEG ----171009 SQG ----179747 LQG ----34284 select count(*) from (select distinct h. region, e. fk_hbasichandler_id id, e. responsible_agency from aevent e, hreport_univ 3 h where e. fk_lu_ca_event_code like 'CA 725%' and to_number(to_char(e. actual_date, 'J')) = (select max(to_number(to_char(e 2. actual_date, 'J'))) from aevent e 2 where e 2. fk_hbasichandler_id = e. fk_hbasichandler_id and e 2. fk_lu_ca_event_code = e. fk_lu_ca_event_code) and e. responsible_agency in ('E', 'S') -- join to hr and e. fk_hbasichandler_id = h. handler_id and e. activity_location = h. activity_location and gpra_ca = 'Y') / COUNT(*) -------1820 18

select e. handler_id, e. activity_location, e. enforcement_seq, e. enforcement_date, e. agency, e. enforcement_type_owner, e. select e. handler_id, e. activity_location, e. enforcement_seq, e. enforcement_date, e. agency, e. enforcement_type_owner, e. enforcement_type from cenforcement 3 e where ( ( (e. enforcement_type between '0' and '399') or (e. enforcement_type between '500' and '699') or (e. enforcement_type between '800' and '809') or 400 series (Civil/Judicial Referrals ) – enforcement sensitive and will only be released once the violations they are linked to have a civil/judicial action (500 series indicates that the case has been filed in court. (e. enforcement_type between '811' and '999') or (e. enforcement_type between '700' and '799' and exists (select p. handler_id, p. activity_location, p. enforcement_seq, p. enforcement_date, p. agency, p. penalty_type_owner, p. penalty_type from cpenalty 3 p where e. handler_id=p. handler_id and e. activity_location=activity_location and e. enforcement_seq=p. enforcement_seq and e. enforcement_date=p. enforcement_date and e. agency =p. agency and p. penalty_type='FA') )) ) and exists (select t. owner, t. enforcement_type from lu_enforcement_type t where t. owner = e. enforcement_type_owner and t. enforcement_type = e. enforcement_type and t. usage in (‘ 0’, '1', '3', '5', '7‘, ’ 9’) ) and exists (select h. handler_id from hbasic h where e. handler_id = h. handler_id and h. extract_flag='X') 19

HANDLER_ID ACT_LOC ENF_SEQ ENF_DATE A ENF_TYPE_OW ENF_TYPE ------- ----- ---- TXD 982759797 TX 003 HANDLER_ID ACT_LOC ENF_SEQ ENF_DATE A ENF_TYPE_OW ENF_TYPE ------- ----- ---- TXD 982759797 TX 003 10 -NOV-04 S TX 211 TXD 982758930 TX 003 04 -OCT-04 S TX 211 TXD 980808778 TX 003 18 -JUN-03 S TX 211 TXD 091882035 TX 004 23 -DEC-03 S TX 211 TXD 070133319 TX 003 22 -JUL-04 S TX 211 TXR 000032656 TX 004 15 -JUL-04 S TX 211 TXR 000014779 TX 003 03 -SEP-04 S TX 211 TXR 000012013 TX 003 12 -AUG-04 S TX 211 TXR 000010678 TX 003 06 -JUL-04 S TX 211 TXD 987992690 TX 011 03 -AUG-04 S TX 211 TXD 980864078 TX 005 30 -AUG-04 S TX 211 TXR 000004986 TX 003 30 -AUG-04 S TX 211 TXD 988064804 TX 003 17 -SEP-03 S TX 211 TXD 091882035 TX 005 23 -DEC-03 S TX 211 TXD 080271398 TX 003 02 -JUL-04 S TX 211 TXD 070133319 TX 004 22 -JUL-04 S TX 211 TXD 067285973 TX 019 30 -AUG-04 S TX 211 TX 3213820738 TX 001 14 -APR-04 S TX 211 TXD 000820274 TX 001 12 -JAN-04 S TX 211 TXR 000027508 TX 001 24 -FEB-04 S TX 211 TXR 000032656 TX 003 15 -JUL-04 S TX 211 TXD 987992690 TX 009 03 -AUG-04 S TX 211 TXD 055141378 TX 092 14 -JUN-04 S TX 211 TXD 000835157 TX 003 02 -APR-04 S TX 211 TXD 040407736 TX 001 04 -DEC-03 S TX 211 20

Some Tips Use table aliases in your query. Different queries can return the same Some Tips Use table aliases in your query. Different queries can return the same result. Experiment for best performance. Only ask for what you need. Know the characteristics (size, number of records) of your tables. Style is important. If it is readable, it will be reusable. 21

Editing Commands Command Description APPEND text Adds text to the end of the current Editing Commands Command Description APPEND text Adds text to the end of the current line. CHANGE/ old / new / Changes old text to new in the current line. DEL Deletes current line. INPUT Inserts an indefinite number of lines. INPUT text Inserts a line consisting of text. LIST Lists all lines in the SQL buffer. N Specifies the line to make the current line. 0 text Inserts a line before line 1. File Commands Command Description SAVE filename. ext Save contents of SQL buffer to a file. If no file name is specified, the buffer is saved to afied. buf. START filename. ext Runs a previously saved file. RUN Displays and runs the current SQL statement in the buffer. "@filename. ext" Runs a previously saved file. EDIT filename. ext Invokes the editor to edit contents of a saved file. If no file name is specified, the contents of the SQL buffer is saved and then edited in file afiedt. buf. Environment Commands Command Description SET ECHO {ON|OFF} Display SQL statement along with SQL statement results. SET FEEDBACK {ON|OFF} Display the number of records returned by a query. SET HEADING {ON|OFF} Display column headings with SQL statement results. SET LINESIZE n Set the number characters per line to n. SET PAGESIZE n Set the number of lines per page to n. SET TERMOUT {ON|OFF} Display output on screen SET VERIFY {ON|OFF} Display the text of a command before and after it replaces a substitution variable with values. 22

Input/Output Commands Command Description SPOOL filename. ext Send output to a file. SPOOL {OFF|OUT} Input/Output Commands Command Description SPOOL filename. ext Send output to a file. SPOOL {OFF|OUT} OFF closes the SPOOL file. OUT closes the spool file and sends the output to the system printer. &user_variable If user_variable does not exist, SQL*Plus will prompt for a value. SQL*Plus immediately discards it when used. &&user_variable value for reuse. If user_variable does not exist, SQL*Plus will prompt for a value only once and will store the DEFINE Display all user variables. DEFINE variable = value Creates a CHAR type user variable and assigns the value to it. ACCEPT variable {PROMPT text} Prompts the user with text then accepts the user input and assigns the value to variable. UNDEFINE variable Discards the specified variable. VARIABLE variable datatype Creates a variable of the type datatype. PRINT variable Displays the value of variable. Format Commands Command Description COLUMN Displays settings for all columns. COLUMN column CLEAR Clears all settings for the specified column. COLUMN column {HEADING text} Sets the column heading and justification. Specifies a format. {JUSTIFY align} {FORMAT format} for the column results and text to be displayed in place of null values. {NULL text} TTITLE text Specifies text as a header to be placed at the top of each page. BTITLE text Specifies text as a footer to appear at the bottom of each page. BREAK Displays breaks for all columns. CLEAR BREAK Clears all breaks. BREAK {ON report_element} Set break to suppress display of duplicate values for the specified report_element. Other Commands Command HOST command Description 23 Executes an operating system command from within SQL*Plus.

QUESTIONS? 24 QUESTIONS? 24