JDBC Tutorials - Herong's Tutorial Examples - Version 3.00, by Dr. Herong Yang

SQL*Plus - Oracle Command Line Interface

This section describes how to use Oracle command line interface, SQL*Plus.

Oracle 11g XE server not only offers a Web interface, but it also offers a command line interface called SQL*Plus. The program file of SQL*Plus is located at \local\orablexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe.

Here is what I did in a command window with "sqlplus", to obtain a list of all users and checking privileges of user "Herong":

C:\herong>cd \local\orablexe\app\oracle\product\11.2.0\server\bin\
C:\...>sqlplus /nolog

SQL*Plus: Release Production on Tue Aug 11 16:39:24 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect SYS/HerongY@ng AS SYSDBA
ORA-12154: TNS:could not resolve the connect identifier specified

SQL> connect SYS/anything AS SYSDBA

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
XS$NULL                        2147483638 27-AUG-11
APEX_040000                            47 27-AUG-11
APEX_PUBLIC_USER                       45 27-AUG-11
FLOWS_FILES                            44 27-AUG-11
HR                                     43 27-AUG-11
MDSYS                                  42 27-AUG-11
ANONYMOUS                              35 27-AUG-11
XDB                                    34 27-AUG-11
CTXSYS                                 32 27-AUG-11
OUTLN                                   9 27-AUG-11
SYSTEM                                  5 27-AUG-11
SYS                                     0 27-AUG-11
12 rows selected.

This tutorial shows a number of interesting behaviors of Oracle 11.2:

  • Use the "/nolog" option with the "sqlplus" command so it will be started without login, which give you a chance to run the "CONNECT" or other commands.
  • "SYS" is a very special built-in user account in Oracle server. It has the password set to the value provided during the installation process.
  • When correct user name and password "SYS/HerongY@ng" is provided, Oracle 11.2 server returns with the "ORA-12154: TNS" error, which could be related to some settings I missed during the installation.
  • However, when incorrect user name and password "SYS/anything" is provided, Oracle 11.2 server provides me the connect. This is very funny. After some searching on the Internet, I think Oracle has a logic to default incorrect user name and password combination to use Windows user group authentication to establish the connection if "AS SYSDBA" is specified.
  • Application Express users are not included in the "ALL_USERS" table in Oracle 11.2. But in Oracle 10.2, the user added in Application Express is listed in the "ALL_USERS" table.

Last update: 2015.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Installing and Running Java DB - Derby

 Derby (Java DB) JDBC Driver

 Derby (Java DB) JDBC DataSource Objects

 Java DB (Derby) - DML Statements

 Java DB (Derby) - ResultSet Objects of Queries

 Java DB (Derby) - PreparedStatement

 MySQL Installation on Windows

 MySQL JDBC Driver (MySQL Connector/J)

 MySQL - PreparedStatement

 MySQL - Reference Implementation of JdbcRowSet

 MySQL - JBDC CallableStatement

 MySQL CLOB (Character Large Object) - TEXT

 MySQL BLOB (Binary Large Object) - BLOB

Oracle Express Edition Installation on Windows

 Oracle Database 11g Express Edition (XE) Installation

 Accessing Oracle Server through Web Interface

 Creating Oracle Database Users

SQL*Plus - Oracle Command Line Interface

 SQL*Plus - Create New User and Login

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Microsoft SQL Server 2005 Express Edition

 Microsoft JDBC Driver for SQL Server - sqljdbc42.jar

 Microsoft JDBC Driver - Query Statements and Result Sets

 Microsoft JDBC Driver - DatabaseMetaData Object

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Summary of JDBC Drivers and Database Servers

 Additional Tutorial Notes to Be Added

 Outdated Tutorials


 PDF Printing Version

SQL*Plus - Oracle Command Line Interface - Updated in 2015, by Dr. Herong Yang