📄 ch21.htm
字号:
<PRE><FONT COLOR="#0066FF">SQL> <B>@ezinsert.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">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>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>On line 2 a comma is missing between the Social Security number and <TT>SMITH</TT>.
<H3><FONT COLOR="#000077">Column Ambiguously Defined</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@employee_tbl</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">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>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>On line 1 the column name has not been defined. The tables have been given aliases
of <TT>e</TT> and <TT>p</TT>. Decide which table to pull the name from and define
it with the table alias.
<H3><FONT COLOR="#000077">SQL Command Not Properly Ended</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>create view emp_tbl as</B>
2 <B>select * from employee_tbl</B>
3 <B>order by name</B>
4 <B> /</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> order by name
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Why is the command not properly ended? You know you can use a <TT>/</TT> to end
an SQL statement. Another fooler. An <TT>ORDER BY</TT> clause cannot be used in a
<TT>CREATE VIEW </TT>statement. Use a <TT>GROUP BY</TT> instead. Here the query processor
is looking for a terminator (semicolon or forward slash) before the <TT>ORDER BY</TT>
clause because the processor assumes the <TT>ORDER BY</TT> is not part of the <TT>CREATE
VIEW</TT> statement. Because the terminator is not found before the <TT>ORDER BY</TT>,
this error is returned instead of an error pointing to the <TT>ORDER BY</TT>.
<H3><FONT COLOR="#000077">Missing Expression</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@tables.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">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>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice the comma after <TT>table</TT> on the first line; therefore, the query
processor is looking for another column in the <TT>SELECT</TT> clause. At this point,
the processor is not expecting the <TT>FROM</TT> clause.
<H3><FONT COLOR="#000077">Not Enough Arguments for Function</FONT></H3>
<H5><FONT COLOR="#000077">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@tblspc.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">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>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>There are not enough arguments for the <TT>DECODE</TT> function. Check your implementation
for the proper syntax.
<H3><FONT COLOR="#000077">Not Enough Values</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>@ezinsert.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">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>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>A column value is missing. Perform a <TT>DESCRIBE</TT> 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:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>spool ezinsert.lst</B>
SQL> <B>set echo on</B>
SQL> <B>set feedback on</B>
SQL> <B>insert into employee_tbl (ssn, last_name, first_name, mid_name, sex)</B>
2 <B>values ('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE')</B>
3 <B>/</B>
</FONT></PRE>
<H3><FONT COLOR="#000077">Integrity Constraint Violated--Parent Key Not Found</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>insert into payroll_tbl values</B>
2 <B>('111111111', 'SMITH', 'JOHN')</B>
3 <B> /</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> insert into payroll_tbl values
*
ERROR at line 1:
ORA-02291: integrity constraint (employee_cons) violated - parent
key not found
SQL>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>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.
<H3><FONT COLOR="#000077">Oracle Not Available</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">(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: <B>rplew</B>
Enter password:</FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ERROR: ORA-01034: ORACLE not available
ORA-07318: smsget: open error when opening sgadef.dbf file.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>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.
<H3><FONT COLOR="#000077">Inserted Value Too Large for Column</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL><B> @ezinsert.sql</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">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>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>One of the values being inserted is too large for the column. Use the <TT>DESCRIBE</TT>
command on the table for the correct data length. If necessary, you can perform an
<TT>ALTER TABLE</TT> command on the table to expand the column width.
<H3><FONT COLOR="#000077">TNS:listener Could Not Resolve SID Given in Connect Descriptor</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQLDBA> <B>connect rplew/xxxx@database1</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ORA-12505: TNS:listener could not resolve SID given in connect descriptor
SQLDBA> disconnect
Disconnected.
SQLDBA>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>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.
<H3><FONT COLOR="#000077">Insufficient Privileges During Grants</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>grant select on people_tbl to ron;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">grant select on people_tbl to ron
*
ERROR at line 1:
ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
SQL></FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>grant select on demo.employee to ron;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">grant select on demo.employee to ron
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>
</FONT></PRE>
<P>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.
<H3><FONT COLOR="#000077">Escape Character in Your Statement--Invalid Character</FONT></H3>
<P>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.
<H3><FONT COLOR="#000077">Cannot Create Operating System File</FONT></H3>
<P>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.
<H2><FONT COLOR="#000077">Common Logical Mistakes</FONT></H2>
<P>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.
<H3><FONT COLOR="#000077">Using Reserved Words in Your SQL statement</FONT></H3>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>select sysdate DATE</B>
2 <B>from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">select sysdate DATE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In this example the query processor is not expecting the word <TT>DATE</TT> because
it is a reserved word. There is no comma after the pseudocolumn <TT>SYSDATE</TT>;
therefore, the next element expected is the <TT>FROM</TT> clause.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>select sysdate "DATE"</B>
2 <B> from dual;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">DATE
--------
15-MAY-97
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice how the reserved word problem is alleviated by enclosing the word <TT>DATE</TT>
with double quotation marks. Double quotation marks allow you to display the literal
string <TT>DATE</TT> as a column alias.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Be sure to check your specific database
documentation to get a list of reserved words, as these reserved words will vary
between different implementations.
<HR>
</BLOCKQUOTE>
<P>You may or may not have to use double quotation marks when naming a column alias.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -