Introduction to SQLPlus

1
Introduction to SQLPlus
2
Agenda

• SQLPlus Overview
• SQLPlus User Interface
• Commands and Environment
• Running SQLPlus Commands
• Writing Scripts
• Editing Scripts
• Running Scripts
• Formatting Results
• Creating a Flat File
• Generating HTML Reports
• iSQLPlus
• Creating Dynamic Reports
• iSQLPlus DBA

3
SQLPlus Overview
4
What is SQLPlus?

• Interactive and batch query tool Dynamic
• Installed with every Oracle database
• Command-line interface
• Windows GUI
• Web-based interface
• Own commands and environment

5
SQLPlus Uses

• Issue DDL statements (CREATE, ALTER, DROP)
• Issue DML statements (SELECT, INSERT, DELETE)
• Issue DCL statements (GRANT, REVOKE)
• Issue TCL statements (COMMIT, SAVEPOINT)
• Perform database administration
• Develop and run batch scripts
• Output results to a file, screen or HTML file
• Generate reports dynamically, interactively or
  batch
• Send messages to and receive responses from users

6
SQLPlus User Interface
7
Command-Line
Open terminal or command prompt At the
prompt, type sqlplus
When prompted, enter your Oracle username and
password Enter username hr Enter password hr
8
Command-Line - Starting

• sqlplus username
• sqlplus username/password
• sqlplus username/password_at_system_identifier
• sqlplus username_at_system_identifier
• sqlplus / AS SYSDBA or SYSOPER
• sqlplus username/password AS SYSDBA or SYSOPER

9
Windows Graphical User Interface
Click Start gt Programs gt
Oracle-OraHomeName gt Application
Development gt SQL Plus
10
Windows Graphical User Interface
Enter your username hr Enter your password
hr Enter host string if connecting to a different
datbase otherwise leave blank - orcl
SQLPlus Windows GUI opens with the Log On box
11
Windows Graphical User Interface
SQLPlus starts and start entering commands
12
Windows Graphical User Interfacevia Command-Line
Click Start gt Programs gt
Accessoriesgt Command Prompt
13
Windows Graphical User Interfacevia Command-Line

• Open terminal or command prompt
• At the prompt, type SQLPLUSW

14
Windows Graphical User Interfacevia Command-Line
Enter your username hr Enter your password
hr Enter host string if connecting to a different
datbase otherwise leave blank - orcl
SQLPlus Windows GUI opens with the Log On box
15
Windows Graphical User Interfacevia Command-Line
SQLPlus starts and start entering commands
16
Windows Graphical User Interfacevia Command-Line
(A Shortcut)
At the prompt, type sqlplusw
username/password _at_connect_identifier
17
Windows Graphical User Interfacevia Command-Line
SQLPlus starts and start entering commands
18
Commands and Environment
19
Commonly Used Commands
20
Setting Environment
21
Running SQLPlus Commands
22
Types of Commands
1. (command for formatting results)
COLUMN SALARY FORMAT 99,999 HEADING MONTHLY
SALARY 2. (commands to work with
database) SELECT EMPLOYEE_ID, LAST_NAME,
JOB_ID, SALARY FROM EMP_DETAILS_VIEW
WHERE SALARY gt 12000
23
Types of Commands
3. PL/SQL Blocks
DECLARE x NUMBER 100
BEGIN FOR i IN 1..10
LOOP IF MOD (i,2) 0 THEN
INSERT INTO temp
VALUES (i,x, i is even)
ELSE THEN INSERT INTO
temp VALUES (i,x, i is odd)
END IF x x 100
END LOOP END
24
Writing Scripts
25
Steps
1. Define editor
2. Name your file
26
Steps
3. Write your query
4. Save your query
27
Editing Scripts
28
SQLPlus Editing Commands
29
Editing Example
Query with error
Error message
Use the CHANGE command to correct the error
Use the RUN command to run your query again
30
Running Scripts
31
START Command
Use the START command Followed by the filename
32
Running a script as you login

• Open terminal window
• Type sqlplus username/password _at_ltfilenamegt

33
Nesting Scripts or Batch Process
Q1SALES .SQL
Q2SALES .SQL
Q3SALES .SQL
Q4SALES .SQL
YEAREND .SQL
Q1SALES
Q2SALES
Q3SALES
START SALESREPORT.SQL
Q4SALES
YEAREND
34
Formatting Results
35
Basic query ordered by Department_ID
36
Results grouped by Department ID suppressing
duplicate values BREAK command
37
Results grouped by Department ID with 2 spaces
SKIP command
38
Results grouped by Department ID with each
department on separate page
39
Query grouping by Department ID and Job ID
40
Results grouped by Department ID and Job ID
41

• Query groups by Department ID
• Calculates sub-total and total COMPUTE command

42
Results grouped by Department ID with sub-totals
by department and grand total
43

• Query groups by Department ID computing
  sub-totals and grand total
• Sets environment variables for report SET
  commands
• Includes report title (top and bottom of
  report) - BTITLE/TTITLE/CENTER commands
• Formats column titles COLUMN command

44
(No Transcript)
45
Creating a Flat File
46
Create a SQL script SPOOL ONOFF command
47
Run SQL script
48
CSV File as defined in script
49
Create a SQL script and define separators
50
Run SQL script
CSV File with comma delimited
51
Generating HTML Reports
52
SET ECHO OFF - does not display output
on screen SET PAGESIZE -
of lines to print on a page SET NEWPAGE action
to take when page
break occurs SET MARKUP - begins the
command HTML ON - enables HTML output HEAD
lttitlegtlt/tttlegt - displays the title which
will appear on
your
browser ltlink hrefgt - references an external CSS
file which contains the
formatting for the
report BODY - use the CSS file to format the
body of the report. TABLE
classdetail specifies the
formatting which will be used for
the table by assigning the class
created in the CSS file
.detail ENTMAP OFF prevents special characters
from being replaced by HTML
named characters SPOOL ON
specifies SQLPlus to write the opening
and closing tags to the start and end of
each file created by the SPOOL command.
report.sql
53
CSS Style Sheet style.css
54
report.html generated by SPOOL ON command
beginning of HTML file
end of HTML file
55
Run SQL script to produce HTML file

• Log into SQLPlus
• Run SQL Script

56

• Type address of created HTML report
• Open browser

57
iSQLPlus
58
iSQLPlus Architecture
HTTP TCP/IP
Oracle Net
Oracle Database
Web Browser (Client)
Application Server
The application server coordinates resources
between the client and the database
59
iSQLPlus Application Server
Start a terminal window. Make sure you are in
your ORACLE_HOME directory Enter
ORACLE_HOME\BIN\isqlplusctl start
60
iSQLPlus Application Server
iSQLPlus has started
61
iSQLPlus Application Server - TEST
Open a web browser. Type the iSQLPlus URL
http//machine_name.domainport/isqlplus
Login screen appears
62
iSQLPlus using Enterprise Manager
63
Enterprise Manager Login
Click Start gt Programs gt
Oracle-OraHomeName gt Database Control
- orcl
64
Enterprise Manager Login
Enter username sys Enter password
orcl Connect As SYSDBA Click Login
65
Enterprise Manager Login
66
Under Related Links gt iSQLPlus
67

• Select a Role
• Click Continue

68
Enter username hr Enter password hr Enter
Connect Identifier leave blank for default
database Click Login
69
iSQLPlus Workspace
70
iSQLPlus Workspace
71
What Can You Do?

• Enter, execute and cancel scripts
• Load and save scripts
• View, save and print output
• Display history of previously executed scripts
  within the same session
• Access preferences screens
• Get help and log out

72

• Enter statements
• One statement or multiple statements
• Click Execute

73
RESULTS
74
History Screen
75
Preferences Screen
76
Saving Scripts
Click Save Script
77
Save dialog box appears
78
Save in desired location
79
Loading Scripts
Click Load Script
80

• Click Browse
• Select script file
• Click Load

81

• Back to your workspace
• Click Execute

82
Creating Dynamic Reports
83
Key Point
The SQL file MUST be saved on your Application
Server. C\oracle\product\10.2.0\ohs\Apache\Apach
e\htdocs Reason A configuration parameter
,DocumentRoot, specifies where the
HTTP Server should look for documents.
84
Step 1 Write SQL Script

• Set your environment variables
• Define a prompt to instruct the user for
  input
• Define the parameter/variable for the value

Prompt
Parameter
85
Step 2 Write HTML File

• Starts iSQLPlus ACTION
• Automatically logs the user to iSQLPlus and
  the database HIDDEN USERID
• Runs the SQL script with the parameter/variable
  HIDDEN SCRIPT
• Displays the Search box INPUT TYPE SUBMIT

dynamic.html
86

• Open web browser
• Type address of the HTML file

Web page
87
Results

• Displays the columns in our SELECT statement
• Displays the prompt written in our script
• Displays the results based on our user input

88
iSQLPlus DBA
89
You Can / Must

• Use iSQLPlus to perform database
  administration and run DBA commands
• Use iSQLPlus DBA URL http//ltmachine.name.do
  mainportgt/isqlplus/dba
• Authenticate with the Application Server
• Authenticate with the database
• Have SYSDBA and SYSOPER privileges

90
iSQLPlus DBA Configuration

• ORACLE_HOME\oc4j\j2ee\isqlplus\application-deplo
  yments\isqlplus
• set ORACLE_HOMEltyour-oracle-homegt
• set JAVA_HOMEORACLE_HOME\jdk
• cd ORACLE_HOME\oc4j\j2ee\isqlplus\application-d
  eployments\isqlplus
• JAVA_HOME\bin\java Djava.security.properties
• ORACLE_HOME\oc4j\j2ee\home\config\jazn.sec
  urity.props jar
• ORACLE_HOME\oc4j\j2ee\home\jazn.jar user
  iSQLPlus DBA/admin
• password welcome adduser iSQLPlus DBA
  iplusdba iplusdba
• 6. JAVA_HOME\bin\java Djava.security.properti
  es
• ORACLE_HOME\oc4j\j2ee\home\config\jazn.sec
  urity.props jar
• ORACLE_HOME\oc4j\j2ee\home\jazn.jar user
  iSQLPlus DBA/admin
• password welcome grantrole webDba
  iSQLPlus DBA iplusdba

91
iSQLPlus DBA Configuration

• 7. Test URL access, enter the iSQLPlus DBA URL
  in your web browser
• http//ltmachine.name.domainportgt/isqlplus/d
  ba
• Enter the username and password you created
  above.

92
iSQLPlus DBA Configuration
9. iSQLPlus login screen. Enter the username
and password you created above and set the
privilege to SYSDBA. Click LOGIN
93
iSQLPlus DBA Configuration
10. You will see the Workspace
94
Resources

• Oracle Technology Network
• http//otn.oracle.com/tech/sql_plus/
• SQLPlus Discussion Forum
• http//www.oracle.com/forums
• Oracle Documentation Library
• http//otn.oracle.com/documentation
• Oracle SQLPlus by Jonathon Gennick (OReilly)

95
The End