
Teach Yourself SQL in 21 Days, Second Edition


- 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


© Copyright, Macmillan Computer
Publishing. All rights reserved.