This tutorial demonstrates how to use Secure SQL Editor to execute Structured Query Language (SQL) and SQLPlus commands on your Instructional Oracle account ( If you do not have an Instructional Oracle account and would like to have one, go to Princeton University Instructional Oracle Account Facility ) . The following links provide detailed information on SQL and the Oracle client SQLPlus:
This tutorial is not intended to be a complete and exhaustive tutorial on Oracle SQL and SQLPlus commands. Instead the focus is to provide examples on how to perform common and typical operations on your Instructional Oracle account.
The SQL Editor is simple to use. You enter your OIT NetID, your Oracle account password, and a list of SQL and/or SQLPlus commands to be executed, and then click on the "Execute SQL" button. The commands and resulting output are then displayed in the lower frame. Oracle reports any error condition with an error label "ORA-nnnnn" and an error description. The last five characters of the error label is a five digit error code. To obtain more information on this error code, go to Oracle8 Error Messages.
1) SQL commands are not case sensitive. The only exceptions are text data which are used as values. If you need to create Oracle objects such as tables and columns with lowercase characters, you must enclosed the names with quotes. For example,
create table "New_Table" (
"First_Col" number,
"Second_Col" varchar2(1000));
In general, it is best not to create case-sensitive database objects.
2) A single SQL command consists of SQL keywords, object names (i.e. tablenames, column names), data values and is terminated by the ";" character. A single command can be divided into multiple lines. However the line division can not occur across the keywords, object names, and data values. You can specify multiple SQL commands as a single transaction to the SQL Editor. For example,
create table test_table(
age number,
full_name varchar2(64));
insert into test_table values(
21,'John Smith');
insert into test_table values(
22,'Mary Smith');
3)SQL commands which insert,update,delete rows in tables need to be "committed" if the desired changes need to be permanently stored in your Oracle account. Executing the "commit" command will cause all data updates from previous SQL statements to be permanently stored. The commit command can be issued at any time and as often as desired. In the following example:
insert into test_table values(
21,'John Smith');
commit;
commit;
insert into test_table values(
22,'Mary Smith');
only the first insertion is permanently stored.
However, there is one exception to this rule.
All SQL commands immediate before an Data Definition Language command ( command whichs which delete or create objects ) are automatically committed. For example
insert into test_table values(
23,'John Doe');
create table dummy_table ( id number);
The insertion will be automatically saved because it preceeds the DDL create table command.
4)SQL Editor only supports a subset of SQLPLus commands ( see SQL*Plus Quick Reference to get list of all commands ). SQL Editor does not support the following SQLPlus commands:
"accept","append","cancel","change","connect","copy","define","disconnect","edit",
"exit","get","host","input","interrupt","password","pause","prompt","run","runform",
"save","spool","sqlplus","start","store","whenever"
Examples of Useful SQL and SQLPlus Commands
select user from dual;
select sysdate from dual;
select power(4,3) from dual;
generates the following output:
USER ------------------------------ STORACLE SYSDATE --------- 22-JAN-99 POWER(4,3) ---------- 64
The SQLPlus command describe returns the definitions of tables and views. For example, information about your tables are stored in the table TABS. However, since you do know the columns names of that table, you can not query that table. The command
describe tabs;will produces that information
Name Null? Type ------------------------------- -------- ---- TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(10) INSTANCES VARCHAR2(10) CACHE VARCHAR2(5) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7)The first column is the list of column names. The second column is a list of Not Null indicators. The third column is the list of data types. This output reveals that the column name "TABLE_NAME" is not allowed to be null. Now to get an alphabetical list of all the tables in your account, use the command
select table_name from tabs order by table_name;You can use the describe command on the following views to obtain information on your account.
| View Name | Description |
| DICT | table names and table description |
| DICT_COLUMN | column names of table names and column description |
| CAT | names of all user's tables, views, synonyms, and sequences |
| OBJ | information on all objects in your account |
| TABS | table information on all user's tables |
| COLS | column information on all user's columns |
| USER_VIEWS | view information on all user's views |
| SYN | synonyms information on all user's synonyms |
| SEQ | sequence information on all user's sequences |
| USER_CONSTRAINTS | constraint information on user's constraints |
| USER_CONS_COLUMNS | column information on user's constraints |
| IND | index information on all user's indices |
| USER_IND_COLUMNS | column information on user's indices |
The output format for columns values are determined by the column data definition. For example, consider the table test_table which was created with the following command
Use "Column" To Set Column Output Length
create table test_table( age number, full_name varchar2(64));
The following example retrieves all the rows in this table and displays the output
select * from test_table;
AGE FULL_NAME
---------- ----------------------------------------------------------------
21 John Smith Junior
22 Miss Mary Smith
Since column FULL_NAME is defined to be 64 characters, the column output is set to 64. The following example sets the value to 24 and shows the resulting output
column full_name format a24;
select * from test_table;
AGE FULL_NAME
---------- ------------------------
21 John Smith Junior
22 Miss Mary Smith
SQL Editor is essentially a web interface to Oracle client SQLPlus. SQLPlus has interval variables that determine the behavior of SQLPlus. The command show all; will list all these internal variables and their current values. The Set command will reset an internal variable to a new value. For example, the default value for linesize is 80. This means output lines which have more than 80 characters will wrap and start a new line. The default value for numwidth is 10. This means output length of columns of data type number output lines is 10 characters. The follow example demonstrates the result of resetting numwidth to 2 and linesize to 20
select * from test_table; set numwidth 2; set linesize 20; select * from test_table;
Here is the resulting output
AGE FULL_NAME
---------- ----------------------------------------------------------------
21 John Smith Junior
22 Miss Mary Smith
AGE
---
FULL_NAME
--------------------
21
John Smith Junior
22
Miss Mary Smith
The truncate command will permanently delete all rows from a table. You do not need to issue a "commit" command after the truncate command. This command is useful when you need a quick method of clearing out all the rows from a table without knowing the table definition. The following example deletes all rows from the table test_table
truncate table test_table;
Use the Alter Table command to change the definition of a table. You can either add another column or change the definition of an existing column. In the first example, the new column department with length 24 is added to the table test_table. In the second example, the length definition of department has been changed to 32.
alter table test_table add (
department varchar2(24));
alter table test_table modify (
department varchar2(32));
Users can specify constraints on columns in tables. Row insertion and updates on tables will fail if these constraints are violated. Constraints can be specified either during or after table creation. Constraints can be disabled and re-enabled. Users can assign a name to a constraint. This practice of naming your constraints is highly recommended and valuable. If you do not assign a name, Oracle will assign a name for you. When a constraint is violated, Oracle reports the name of the constraint that has been violated. If you name your constraints, then you can easily identified what table and type of constraint.
There are four kinds of constraints, not null,, check value, unique, and foreign key. The "not null" constraint means this column can not have a null value. The "check value" constraint means the column must have values that are allowed by the check condition. The "unique" constraint means this column must have only unique values. The "foreign key" constraint means the values of a column must already exist as a value in a primary key column of another table. It is useful to follow a naming convention when assigning names to your constraints. One suggestion is use nn_ for not null constraints, chk_ for check constraints, unq_ for unique constraint, and fk_ for foreign key constraint. Another useful suggestion is to embed relevant table names and column names in the constraint name. The following example demonstrates how to specify constraints during table creation
create table salary_table (
fiscal_year varchar2(4) constraint constraint nn_salary_year not null,
salary number constraint chk_salary_salary check (salary > 0 ),
fullname varchar2(64) constraint unq_salary_fullname unique,
employee_id varchar2(12) constraint fk_salary_employee_id references employee_table(id));
There are four constraints nn_salary_year, chk_salary_salary, unq_salary_fullname, and fk_salary_employee_id specified in this table creation command.
The constraint nn_salary_year requires that the column fiscal_year can not have null values.
The constraint name "indicates" this is a constraint on the a table with the name salary ( i.e. the table salary_table ) and the column has the name year ( i.e. the column fiscal_year).
The constraint chk_salary_salary requires the check condition that the column salary must be greater than zero be satisfied.
The constraint name "indicates" this is a constraint on the table with the name salary ( i.e. the table salary_table ) and the column is salary.
The constraint unq_salary_fullname requires that fullname can only contain unique values.
The constraint name "indicates" this is a constraint on the table with the name salary ( i.e. the table salary_table ) and the column is fullname.
The constraint fk_salary_employee_id requires that the value of employee_id must already exist as a value in the primary key id in table employee_table.
The constraint name "indicates" this foreign key constraint is a constraint on table name salary ( i.e the table salary_table) onto table name employee ( i.e. the table employee_table) column name id.
To disable a constraint, use the alter table command. To enable a disabled constraint, again use the alter table command. The following examples disables and then re-enables the salary check condition
alter table salary_table disable constraint chk_salary_salary; alter table salary_table enable constraint chk_salary_salary;
You can specify a new constraint on an existing table via the alter table command. The following example defines a new not null constraint on the table test_table
describe test_table;
alter table test_table modify (
full_name varchar2(64) constraint nn_test_fullname not null);
describe test_table;
The resulting output is
Name Null? Type ------------------------------- -------- ---- AGE NUMBER FULL_NAME VARCHAR2(64) DEPARTMENT VARCHAR2(32) Table altered. Name Null? Type ------------------------------- -------- ---- AGE NUMBER FULL_NAME NOT NULL VARCHAR2(64) DEPARTMENT VARCHAR2(32)
You can specify what columns in a table should be the primary key for that table. The primary key is a unique label or identifier for the table. This means the set of columns must not be null and can only have unique values. You can think of the primary key specification as equivalent to having the not null constraint and the unique constraint applied to that set of columns. Consequently, when specifying primary keys in your table definitions, it is highly recommended and valuable that you name your primary key "constraint". The following example demonstrates how to specify the primary key on column id in the table company_table
create table company_table (
id varchar2(8) constraint pk_company_id primary key,
fullname varchar2(64));
The following example demonstrates how to designate two columns, question_id and answer_id, as the primary key in the table responses_table
create table responses_table (
question_id varchar2(12),
answer_id varchar2(12),
answer_text varchar2(1000),
constraint pk_responses_questid_answerid primary key(question_id,answer_id));