?? 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.lstSQL> set echo onSQL> set feedback onSQL> 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 commaSQL> spool offSQL></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.lstSQL> set echo onSQL> set feedback onSQL> 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 definedSQL> spool offSQL></FONT></PRE><H5>ANALYSIS:</H5><P>On line 1 the column name has not been defined. The tables have been given aliasesof <TT>e</TT> and <TT>p</TT>. Decide which table to pull the name from and defineit 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 endedSQL></FONT></PRE><H5>ANALYSIS:</H5><P>Why is the command not properly ended? You know you can use a <TT>/</TT> to endan 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 processoris 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>CREATEVIEW</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.lstSQL> set echo onSQL> set feedback onSQL> set pagesize 1000SQL> 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 expressionSQL> spool offSQL></FONT></PRE><H5>ANALYSIS:</H5><P>Notice the comma after <TT>table</TT> on the first line; therefore, the queryprocessor 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.lstSQL> set echo onSQL> set feedback onSQL> set pagesize 1000SQL> 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 functionSQL> spool offSQL></FONT></PRE><H5>ANALYSIS:</H5><P>There are not enough arguments for the <TT>DECODE</TT> function. Check your implementationfor 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.lstSQL> set echo onSQL> set feedback onSQL> 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 valuesSQL> spool offSQL></FONT></PRE><H5>ANALYSIS:</H5><P>A column value is missing. Perform a <TT>DESCRIBE</TT> command on the table tofind the missing column. You can insert the specified data only if you list the columnsthat 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 - parentkey not foundSQL></FONT></PRE><H5>ANALYSIS:</H5><P>This error was caused by attempting to insert data into a table without the dataexisting 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 datainto 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> sqlplusSQL*Plus: Release 3.2.3.0.0 - Production on Sat May 10 11:19:50 1997Copyright (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 availableORA-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 statusof the database. Also, make sure that you are trying to connect to the correct databaseif 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.lstSQL> set echo onSQL> set feedback onSQL> 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 columnSQL> spool offSQL></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 descriptorSQLDBA> disconnectDisconnected.SQLDBA></FONT></PRE><H5>ANALYSIS:</H5><P>This error is very common in Oracle databases. The listener referred to in thepreceding error is the process that allows requests from a client to communicatewith the database on a remote server. Here you were attempting to connect to thedatabase. 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 administratorof 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 yourselfSQL></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 privilegesSQL></FONT></PRE><P>This error occurs if you are trying to grant privileges on another user's tableand you do not have the proper privilege to do so. You must own the table to be ableto grant privileges on the table to other users. In Oracle you may be granted a privilegewith the Admin option, which means that you can grant the specified privilege onanother user's table to another user. Check your implementation for the particularprivileges 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 yourSQL statement in the buffer or a file. Sometimes the backspace key puts an invalidcharacter in the statement depending upon how your keys are mapped, even though youmight 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 associateddisk is full or incorrect permissions have been set on the file system. If the diskis full, you must remove unwanted files. If permissions are incorrect, change themto the correct settings. This error is more of an operating system error, so youmay 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 errormessages. Most of these errors are obvious, and their resolutions leave little tothe imagination. The next few mistakes are more (or less) logical, and they may causeproblems 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> becauseit 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 literalstring <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 + -