Teach Yourself SQL in 21 Days, Second Edition
![Previous chapter](../buttonart/previous.gif) ![Next chapter](../buttonart/next.gif)
- Day 21 -
Common SQL Mistakes/Errors and Resolutions
Objectives
Welcome to Day 21. By the end of today, you will have become familiar with the
following:
- Several typical errors and their resolutions
- Common logical shortcomings of SQL users
- Ways to prevent daily setbacks caused by errors
Introduction
Today you will see various common errors that everyone--from novice to pro--makes
when using SQL. You will never be able to avoid all errors and/or mistakes, but being
familiar with a wide range of errors will help you resolve them in as short a time
as possible.
NOTE: We used Personal Oracle7 for our
examples. Your particular implementation will be very similar in the type of error,
but could differ in the numbering or naming of the error. We ran our SQL statements
using SQL*PLUS and set ECHO and FEEDBACK to on to see
the statement.
Keep in mind that some mistakes will actually yield error messages, whereas others
may just be inadequacies in logic that will inevitably cause more significant errors
or problems down the road. With a strict sense of attention to detail, you can avoid
most problems, although you will always find yourself stumbling upon errors.
Common Errors
This section describes many common errors that you will receive while executing
all types of SQL statements. Most are simple and make you want to kick yourself on
the hind side, whereas other seemingly obvious errors are misleading.
Table or View Does Not Exist
When you receive an error stating that the table you are trying to access does
not exist, it seems obvious; for example:
INPUT:
SQL> @tables.sql
OUTPUT:
SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || table_name
2 from sys.dba_table
3 where owner = 'SYSTEM'
4 order by table_name
5 /
from sys.dba_table
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> spool off
SQL>
ANALYSIS:
Notice the asterisk below the word table. The correct table name is sys.dba_tables.
An s was omitted from the table name.
But what if you know the table exists and you still receive this error? Sometimes
when you receive this error, the table does in fact exist, but there may be a security
problem--that is, the table exists, but you do not have access to it. This error
can also be the database server's way of saying nicely, "You don't have permission
to access this table!"
TIP: Before you allow panic to set in,
immediately verify whether or not the table exists using a DBA account, if available,
or the schema account. You will often find that the table does exist and that the
user lacks the appropriate privileges to access it.
Invalid Username or Password
INPUT:
SQL*Plus: Release 3.2.3.0.0 - on Sat May 10 11:15:35 1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter user-name: rplew
Enter password:
OUTPUT:
ERROR: ORA-01017: invalid username/password; logon denied
Enter user-name:
This error was caused either by entering the incorrect username or the incorrect
password. Try again. If unsuccessful, have your password reset. If you are sure that
you typed in the correct username and password, then make sure that you are attempting
to connect to the correct database if you have access to more than one database.
FROM Keyword Not Specified
INPUT:
SQL> @tblspc.sql
OUTPUT:
SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15) a,
2 substrfile_name, 1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
substrfile_name, 1,45) c, bytes
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> spool off
SQL>
ANALYSIS:
This error can be misleading. The keyword FROM is there, but you are
missing a left parenthesis between substr and file_name on line
2. This error can also be caused by a missing comma between column names in the SELECT
statement. If a column in the SELECT statement is not followed by a comma,
the query processor automatically looks for the FROM keyword. The previous
statement has been corrected as follows:
SQL> select substr(tablespace_name,1,15) a,
2 substr(file_name,1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
Group Function Is Not Allowed Here
INPUT:
SQL> select count(last_name), first_name, phone_number
2 from employee_tbl
3 group by count(last_name), first_name, phone_number
4 /
OUTPUT:
group by count(last_name), first_name, phone_number
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL>
ANALYSIS:
As with any group function, COUNT may not be used in the GROUP BY
clause. You can list only column and nongroup functions, such as SUBSTR,
in the GROUP BY clause.
TIP: COUNT is a function that
is being performed on groups in the query.
The previous statement has been corrected using the proper syntax:
SQL> select count(last_name), first_name, phone_number
2 from employee_tbl
3 group by last_name, first_name, phone_number;
Invalid Column Name
INPUT:
SQL> @tables.sql
OUTPUT:
SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || tablename
2 from sys.dba_tables
3 where owner = 'SYSTEM'
4 order by table_name
5 /
select owner|| '.' || tablename
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> spool off
SQL>
ANALYSIS:
In line 1 the column tablename is incorrect. The correct column name
is table_name. The underscore was omitted. To see the correct columns, use
the DESCRIBE command. This error can also occur when trying to qualify a
column in the SELECT statement by the wrong table name.
Missing Keyword
INPUT:
SQL> create view emp_view
2 select * from employee_tbl
3 /
OUTPUT:
select * from employee_tbl
*
ERROR at line 2:
ORA-00905: missing keyword
SQL>
ANALYSIS:
Here the syntax is incorrect. This error occurs when you omit a mandatory word
with any given command syntax. If you are using an optional part of the command,
that option may require a certain keyword. The missing keyword in this example is
as. The statement should look like this:
SQL> create view emp_view as
2 select * from employee_tbl
3 /
Missing Left Parenthesis
INPUT:
SQL> @insert.sql
OUTPUT:
SQL> insert into people_tbl values
2 '303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
3 /
'303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
*
ERROR at line 2:
ORA-00906: missing left parenthesis
SQL>
ANALYSIS:
On line 2 a parenthesis does not appear before the Social Security number. The
correct syntax should look like this:
SQL> insert into people_tbl values
2 ('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
3 /
Missing Right Parenthesis
INPUT:
SQL> @tblspc.sql
OUTPUT:
SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15 a,
2 substr(file_name, 1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
select substr(tablespace_name,1,15 a,
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> spool off
SQL>
ANALYSIS:
On line 1 the right parenthesis is missing from the substr. The correct
syntax looks like this:
SQL> select substr(tablespace_name,1,15) a,
2 substr(file_name,1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
Missing Comma
INPUT:
SQL> @ezinsert.sql
OUTPUT:
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into office_tbl values
2 ('303785523' 'SMITH', 'OFFICE OF THE STATE OF INDIANA, ADJUTANT GENERAL')
3 /
('303785523' 'SMITH', 'OFFICE OF THE STATE OF INDIANA, ADJUTANT GENERAL')
*
ERROR at line 2:
ORA-00917: missing comma
SQL> spool off
SQL>
ANALYSIS:
On line 2 a comma is missing between the Social Security number and SMITH.
Column Ambiguously Defined
INPUT:
SQL> @employee_tbl
OUTPUT:
SQL> spool employee.lst
SQL> set echo on
SQL> set feedback on
SQL> select p.ssn, name, e.address, e.phone
2 from employee_tbl e,
3 payroll_tbl p
4 where e.ssn =p.ssn;
select p.ssn, name, e.address, e.phone
*
ERROR at line 1:
ORA-00918: column ambigously defined
SQL> spool off
SQL>
ANALYSIS:
On line 1 the column name has not been defined. The tables have been given aliases
of e and p. Decide which table to pull the name from and define
it with the table alias.
SQL Command Not Properly Ended
INPUT:
SQL> create view emp_tbl as
2 select * from employee_tbl
3 order by name
4 /
OUTPUT:
order by name
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL>
ANALYSIS:
Why is the command not properly ended? You know you can use a / to end
an SQL statement. Another fooler. An ORDER BY clause cannot be used in a
CREATE VIEW statement. Use a GROUP BY instead. Here the query processor
is looking for a terminator (semicolon or forward slash) before the ORDER BY
clause because the processor assumes the ORDER BY is not part of the CREATE
VIEW statement. Because the terminator is not found before the ORDER BY,
this error is returned instead of an error pointing to the ORDER BY.
Missing Expression
INPUT:
SQL> @tables.sql
OUTPUT:
SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || table,
2 from sys.dba_tables
3 where owner = 'SYSTEM'
4 order by table_name
5 /
from sys.dba_tables
*
ERROR at line 2:
ORA-00936: missing expression
SQL> spool off
SQL>
ANALYSIS:
Notice the comma after table on the first line; therefore, the query
processor is looking for another column in the SELECT clause. At this point,
the processor is not expecting the FROM clause.
Not Enough Arguments for Function
INPUT:
SQL> @tblspc.sql
OUTPUT:
SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15) a,
2 decode(substr(file_name,1,45)) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
decode(substr(file_name,1,45)) c, bytes
*
ERROR at line 2:
ORA-00938: not enough arguments for function
SQL> spool off
SQL>
ANALYSIS:
There are not enough arguments for the DECODE function. Check your implementation
for the proper syntax.
Not Enough Values
INPUT:
SQL> @ezinsert.sql
OUTPUT:
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into employee_tbl values
2 ('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE')
3 /
insert into employee_tbl values
*
ERROR at line 1:
ORA-00947: not enough values
SQL> spool off
SQL>
ANALYSIS:
A column value is missing. Perform a DESCRIBE command on the table to
find the missing column. You can insert the specified data only if you list the columns
that are to be inserted into, as shown in the next example:
INPUT:
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into employee_tbl (ssn, last_name, first_name, mid_name, sex)
2 values ('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE')
3 /
Integrity Constraint Violated--Parent Key Not Found
INPUT:
SQL> insert into payroll_tbl values
2 ('111111111', 'SMITH', 'JOHN')
3 /
OUTPUT:
insert into payroll_tbl values
*
ERROR at line 1:
ORA-02291: integrity constraint (employee_cons) violated - parent
key not found
SQL>
ANALYSIS:
This error was caused by attempting to insert data into a table without the data
existing in the parent table. Check the parent table for correct data. If missing,
then you must insert the data into the parent table before attempting to insert data
into the child table.
Oracle Not Available
INPUT:
(sun_su3)/home> sqlplus
SQL*Plus: Release 3.2.3.0.0 - Production on Sat May 10 11:19:50 1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter user-name: rplew
Enter password:
OUTPUT:
ERROR: ORA-01034: ORACLE not available
ORA-07318: smsget: open error when opening sgadef.dbf file.
ANALYSIS:
You were trying to sign on to SQL*PLUS. The database is probably down. Check status
of the database. Also, make sure that you are trying to connect to the correct database
if you have access to multiple databases.
Inserted Value Too Large for Column
INPUT:
SQL> @ezinsert.sql
OUTPUT:
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into office_tbl values
2 ('303785523', 'SMITH', 'OFFICE OF THE STATE OF INDIANA, ADJUTANT GENERAL')
3 /
insert into office_tbl values
*
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL> spool off
SQL>
ANALYSIS:
One of the values being inserted is too large for the column. Use the DESCRIBE
command on the table for the correct data length. If necessary, you can perform an
ALTER TABLE command on the table to expand the column width.
TNS:listener Could Not Resolve SID Given in Connect Descriptor
INPUT:
SQLDBA> connect rplew/xxxx@database1
OUTPUT:
ORA-12505: TNS:listener could not resolve SID given in connect descriptor
SQLDBA> disconnect
Disconnected.
SQLDBA>
ANALYSIS:
This error is very common in Oracle databases. The listener referred to in the
preceding error is the process that allows requests from a client to communicate
with the database on a remote server. Here you were attempting to connect to the
database. Either the incorrect database name was typed in or the listener is down.
Check the database name and try again. If unsuccessful, notify the database administrator
of the problem.
Insufficient Privileges During Grants
INPUT:
SQL> grant select on people_tbl to ron;
OUTPUT:
grant select on people_tbl to ron
*
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
SQL>
INPUT:
SQL> grant select on demo.employee to ron;
OUTPUT:
grant select on demo.employee to ron
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
This error occurs if you are trying to grant privileges on another user's table
and you do not have the proper privilege to do so. You must own the table to be able
to grant privileges on the table to other users. In Oracle you may be granted a privilege
with the Admin option, which means that you can grant the specified privilege on
another user's table to another user. Check your implementation for the particular
privileges you need to grant a privilege.
Escape Character in Your Statement--Invalid Character
Escape characters are very frustrating when trying to debug a broken SQL statement.
This situation can occur if you use the backspace key while you are entering your
SQL statement in the buffer or a file. Sometimes the backspace key puts an invalid
character in the statement depending upon how your keys are mapped, even though you
might not be able see the character.
Cannot Create Operating System File
This error has a number of causes. The most common causes are that the associated
disk is full or incorrect permissions have been set on the file system. If the disk
is full, you must remove unwanted files. If permissions are incorrect, change them
to the correct settings. This error is more of an operating system error, so you
may need to get advice from your system administrator.
Common Logical Mistakes
So far today we have covered faults in SQL statements that generate actual error
messages. Most of these errors are obvious, and their resolutions leave little to
the imagination. The next few mistakes are more (or less) logical, and they may cause
problems later--if not immediately.
Using Reserved Words in Your SQL statement
INPUT:
SQL> select sysdate DATE
2 from dual;
OUTPUT:
select sysdate DATE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ANALYSIS:
In this example the query processor is not expecting the word DATE because
it is a reserved word. There is no comma after the pseudocolumn SYSDATE;
therefore, the next element expected is the FROM clause.
INPUT:
SQL> select sysdate "DATE"
2 from dual;
OUTPUT:
DATE
--------
15-MAY-97
ANALYSIS:
Notice how the reserved word problem is alleviated by enclosing the word DATE
with double quotation marks. Double quotation marks allow you to display the literal
string DATE as a column alias.
NOTE: Be sure to check your specific database
documentation to get a list of reserved words, as these reserved words will vary
between different implementations.
You may or may not have to use double quotation marks when naming a column alias.
In the following example you do not have to use double quotation marks because TODAY
is not a reserved word. To be sure, check your specific implementation.
INPUT:
SQL> select sysdate TODAY
2 from dual;
OUTPUT:
TODAY
--------
15-MAY-97
SQL>
The Use of DISTINCT When Selecting Multiple Columns
INPUT:
SQL> select distinct(city), distinct(zip)
2 from address_tbl;
OUTPUT:
select distinct(city), distinct(zip)
*
ERROR at line 1:
ORA-00936: missing expression
SQL>
ANALYSIS:
A city can have more than one ZIP code. As a rule, you should use the DISTINCT
command on only one selected column.
Dropping an Unqualified Table
Whenever dropping a table, always use the owner or schema. You can have
duplicate table names in the database. If you don't use the owner/schema name, then
the wrong table could be dropped.
The risky syntax for dropping a table:
SYNTAX:
SQL> drop table people_tbl;
The next statement is much safer because it specifies the owner of the table you
want to drop.
SYNTAX:
SQL> drop table ron.people_tbl;
WARNING: Qualifying the table when dropping
it is always a safe practice, although sometimes this step may be unnecessary. Never
issue the DROP TABLE command without first verifying the user id by which
you are connected to the database.
The Use of Public Synonyms in a Multischema Database
Synonyms make life easier for users; however, public synonyms open tables that
you might not want all users to see. Use caution when granting public synonyms especially
in a multischema environment.
The Dreaded Cartesian Product
INPUT:
SQL> select a.ssn, p.last_n
2 from address_tbl a,
3 people_tbl p;
OUTPUT:
SSN LAST_NAME
--------- ---------------
303785523 SMITH
313507927 SMITH
490552223 SMITH
312667771 SMITH
420001690 SMITH
303785523 JONES
313507927 JONES
490552223 JONES
312667771 JONES
420001690 JONES
303785523 OSBORN
313507927 OSBORN
490552223 OSBORN
312667771 OSBORN
420001690 OSBORN
303785523 JONES
313507927 JONES
490552223 JONES
312667771 JONES
420001690 JONES
16 rows selected.
This error is caused when you do not join the tables in the WHERE clause.
Notice how many rows were selected. Both of the preceding tables have 4 rows; therefore,
we wanted 4 rows returned instead of the 16 rows that we received. Without the use
of a join in the WHERE clause, each row in the first table is matched up
with each row in the second. To calculate the total number of rows returned, you
would multiple 4 rows by 4 rows, which yields 16. Unfortunately, most of your tables
will contain more than 4 rows of data, with some possibly exceeding thousands or
millions of rows. In these cases don't bother doing the multiplication, for your
query is sure to become a run-away query.
Failure to Enforce Input Standards
Assuring that input standards are adhered to is commonly known as quality assurance
(QA). Without frequent checks on the data entered by data entry clerks, you run a
very high risk of hosting trash in your database. A good way to keep a handle on
quality assurance is to create several QA reports using SQL, run then on a timely
basis, and present their output to the data entry manager for appropriate action
to correct errors or data inconsistencies.
Failure to Enforce File System Structure Conventions
You can waste a lot of time when you work with file systems that are not standardized.
Check your implementation for recommended file system structures.
Allowing Large Tables to Take Default Storage Parameters
Default storage parameters will vary with implementations, but they are usually
rather small. When a large or dynamic table is created and forced to take the default
storage, serious table fragmentation can occur, which can severely hinder database
performance. Good planning before table creation will help to avoid this. The following
example uses Oracle's storage parameter options.
INPUT:
SQL> create table test_tbl
2 (ssn number(9) not null,
3 name varchar2(30) not null)
4 storage
5 (initial extent 100M
6 next extent 20M
7 minextents 1
8 maxextents 121
9 pctincrease 0};
Placing Objects in the System Tablespace
The following statement shows a table being created in the SYSTEM tablespace.
Although this statement will not return an error, it is likely to cause future problems.
INPUT:
SQL> create table test_tbl
2 (ssn number(9) not null,
3 name varchar2(30) not null)
4 tablespace SYSTEM
5 storage
6 (initial extent 100M
7 next extent 20M
8 minextents 1
9 maxextents 121
10 pctincrease 0};
The next example corrects this so-called problem:
INPUT:
SQL> create table test_tbl
2 (ssn number(9) not null,
3 name varchar2(30) not null)
4 tablespace linda_ts
5 (initial extent 100M
6 next extent 20M
7 minextents 1
8 maxextents 121
9 pctincrease 0};
ANALYSIS:
In Oracle, the SYSTEM tablespace is typically used to store SYSTEM
owned objects, such as those composing the data dictionary. If you happen to place
dynamic tables in this tablespace and they grow, you run the risk of corrupting or
at least filling up the free space, which in turn will probably cause the database
to crash. In this event the database may be forced into an unrecoverable state. Always
store application and user tables in separately designated tablespaces.
Failure to Compress Large Backup Files
If you do large exports and do not compress the files, you will probably run out
of disk space to store the files. Always compress the export files. If you are storing
archived log files on hard disk instead of on tape, these files can be and probably
should be compressed to save space.
Failure to Budget System Resources
You should always budget your system resources before you create your database.
The result of not budgeting system resources could be a poorly performing database.
You should always know whether the database is going to be used for transactions,
warehousing, or queries only. The database's function will affect the number and
size of rollback segments. The number of database users will inevitably affect the
sizing of the USERS and TEMP tablespaces. Do you have enough space
to stripe your larger tables? Tables and indexes should be stored on separate devices
to reduce disk contention. You should keep the redo logs and the data tablespaces
on separate devices to alleviate disk contention. These are just a few of the issues
to address when considering system resources.
Preventing Problems with Your Data
Your data processing center should have a backup system set up. If your database
is small to medium, you can take the extra precaution of using EXPORT to
ensure that your data is backed up. You should make a backup of the export file and
keep it in another location for further safety. Remember that these files can be
large and will require a great deal of space.
Searching for Duplicate Records in Your Database
If your database is perfectly planned, you should not have a problem with duplicate
records. You can avoid duplicate records by using constraints, foreign keys, and
unique indexes.
Summary
Many different types of errors--literally hundreds--can stand in the way of you
and your data. Luckily, most errors/mistakes are not disasters and are easy to remedy.
However, some errors/mistakes that happen are very serious. You need to be careful
whenever you try to correct an error/mistake, as the error can multiply if you do
not dig out the root of the problem. When you do make mistakes, as you definitely
will, use them as learning experiences.
TIP: We prefer to document everything
related to database errors, especially uncommon errors that we happen to stumble
upon. A file of errors is an invaluable Troubleshooting
reference.
NOTE: Day 21 provides you with a sample
of some of the most common Personal Oracle7 errors. For a complete list of errors
and suggested resolutions, remember to refer to your database documentation.
Q&A
- Q You make it sound as if every error has a remedy, so why worry?
A Yes, most errors/mistakes are easy to remedy; but suppose you drop a
table in a production environment. You might need hours or days to do a database
recovery. The database will be done during this time, and your company will be paying
overtime to several people to complete the fix. The boss will not be happy.
Q Any advice on how to avoid errors/mistakes?
A Being human, you will never avoid all errors/mistakes; however, you can
avoid many of them through training, concentration, self-confidence, good attitude,
and a stress-free work environment.
Workshop
The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
Quiz
- 1. A user calls and says, "I can't sign on to the database. But everything
was working fine yesterday. The error says invalid user/password. Can you help me?"
What steps should you take?
2. Why should tables have storage clauses and a tablespace destination?
Exercises
- 1. Suppose you are logged on to the database as SYSTEM, and you
wish to drop a table called HISTORY in your schema. Your regular user id
is JSMITH. What is the correct syntax to drop this table?
2. Correct the following error:
INPUT:
SQL> select sysdate DATE
2 from dual;
OUTPUT:
select sysdate DATE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
![Previous chapter](../buttonart/previous.gif) ![Next chapter](../buttonart/next.gif)
© Copyright, Macmillan Computer Publishing. All
rights reserved.
|