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};

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.

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 /

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.sqlOUTPUT:
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.

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.

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.

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.

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 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 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 /

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.

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;

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;

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.

Common SQL Mistakes/Errors and Resolutions

Objectives

Welcome to Day 21. By the end of today, you will have become familiar with the
following:
l Several typical errors and their resolutions
l Common logical shortcomings of SQL users
l 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.

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

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>