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.