?? ch21.htm
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML><HEAD> <TITLE>Teach Yourself SQL in 21 Days, Second Edition -- Ch 21 -- Common SQL Mistakes/Errors and Resolutions</TITLE></HEAD><BODY TEXT="#000000" BGCOLOR="#FFFFFF"><CENTER><H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR><FONT COLOR="#000077">Teach Yourself SQL in 21 Days, Second Edition</FONT></H1></CENTER><CENTER><P><A HREF="../ch20/ch20.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../wk3rev/wk3rev.htm"><IMGSRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <HR></CENTER><CENTER><H1><FONT COLOR="#000077">- Day 21 -<BR><BR>Common SQL Mistakes/Errors and Resolutions</FONT></H1></CENTER><H2><FONT COLOR="#000077">Objectives</FONT></H2><P>Welcome to Day 21. By the end of today, you will have become familiar with thefollowing:<UL> <LI>Several typical errors and their resolutions <P> <LI>Common logical shortcomings of SQL users <P> <LI>Ways to prevent daily setbacks caused by errors</UL><H2><FONT COLOR="#000077">Introduction</FONT></H2><P>Today you will see various common errors that everyone--from novice to pro--makeswhen using SQL. You will never be able to avoid all errors and/or mistakes, but beingfamiliar with a wide range of errors will help you resolve them in as short a timeas possible.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>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 <TT>ECHO</TT> and <TT>FEEDBACK</TT> to <TT>on</TT> to see the statement. <HR></BLOCKQUOTE><P>Keep in mind that some mistakes will actually yield error messages, whereas othersmay just be inadequacies in logic that will inevitably cause more significant errorsor problems down the road. With a strict sense of attention to detail, you can avoidmost problems, although you will always find yourself stumbling upon errors.<H2><FONT COLOR="#000077">Common Errors</FONT></H2><P>This section describes many common errors that you will receive while executingall types of SQL statements. Most are simple and make you want to kick yourself onthe hind side, whereas other seemingly obvious errors are misleading.<H3><FONT COLOR="#000077">Table or View Does Not Exist</FONT></H3><P>When you receive an error stating that the table you are trying to access doesnot exist, it seems obvious; for example:</P><H5>INPUT:</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_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></FONT></PRE><H5>ANALYSIS:</H5><P>Notice the asterisk below the word <TT>table</TT>. The correct table name is <TT>sys.dba_tables</TT>.An s was omitted from the table name.</P><P>But what if you know the table exists and you still receive this error? Sometimeswhen you receive this error, the table does in fact exist, but there may be a securityproblem--that is, the table exists, but you do not have access to it. This errorcan also be the database server's way of saying nicely, "You don't have permissionto access this table!"<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>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. <HR></BLOCKQUOTE><H3><FONT COLOR="#000077">Invalid Username or Password</FONT></H3><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"> SQL*Plus: Release 3.2.3.0.0 - on Sat May 10 11:15:35 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-01017: invalid username/password; logon deniedEnter user-name:</FONT></PRE><P>This error was caused either by entering the incorrect username or the incorrectpassword. Try again. If unsuccessful, have your password reset. If you are sure thatyou typed in the correct username and password, then make sure that you are attemptingto connect to the correct database if you have access to more than one database.<H3><FONT COLOR="#000077">FROM Keyword Not Specified</FONT></H3><H5><FONT COLOR="#000000">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 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 expectedSQL> spool offSQL></FONT></PRE><H5>ANALYSIS:</H5><P>This error can be misleading. The keyword <TT>FROM</TT> is there, but you aremissing a left parenthesis between <TT>substr</TT> and <TT>file_name</TT> on line2. This error can also be caused by a missing comma between column names in the <TT>SELECT</TT>statement. If a column in the <TT>SELECT</TT> statement is not followed by a comma,the query processor automatically looks for the <TT>FROM</TT> keyword. The previousstatement has been corrected as follows:</P><PRE><FONT COLOR="#0066FF">SQL> <B>select substr(tablespace_name,1,15) a,</B> 2 <B> substr(file_name,1,45) c, bytes</B> 3 <B> from sys.dba_data_files</B> 4 <B>order by tablespace_name;</B></FONT></PRE><H3><FONT COLOR="#000077">Group Function Is Not Allowed Here</FONT></H3><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL> <B>select count(last_name), first_name, phone_number</B> 2 <B>from employee_tbl</B> 3 <B>group by count(last_name), first_name, phone_number</B> 4 <B> /</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"> group by count(last_name), first_name, phone_number *ERROR at line 3:ORA-00934: group function is not allowed hereSQL></FONT></PRE><H5>ANALYSIS:</H5><P>As with any group function, <TT>COUNT</TT> may not be used in the <TT>GROUP BY</TT>clause. You can list only column and nongroup functions, such as <TT>SUBSTR</TT>,in the <TT>GROUP BY</TT> clause.<BLOCKQUOTE> <P><HR><FONT COLOR="#000077"><B>TIP:</B></FONT><TT><B> </B>COUNT</TT> is a function that is being performed on groups in the query. <HR></BLOCKQUOTE><P>The previous statement has been corrected using the proper syntax:</P><PRE><FONT COLOR="#0066FF">SQL> <B>select count(last_name), first_name, phone_number</B> 2 <B>from employee_tbl</B> 3 <B>group by last_name, first_name, phone_number;</B></FONT></PRE><H3><FONT COLOR="#000077">Invalid Column Name</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|| '.' || 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 nameSQL> spool offSQL></FONT></PRE><H5>ANALYSIS:</H5><P>In line 1 the column <TT>tablename</TT> is incorrect. The correct column nameis <TT>table_name</TT>. The underscore was omitted. To see the correct columns, usethe <TT>DESCRIBE</TT> command. This error can also occur when trying to qualify acolumn in the <TT>SELECT</TT> statement by the wrong table name.<H3><FONT COLOR="#000077">Missing Keyword</FONT></H3><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL> create view emp_view 2 select * from employee_tbl 3 /</FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF"> select * from employee_tbl *ERROR at line 2:ORA-00905: missing keywordSQL></FONT></PRE><H5>ANALYSIS:</H5><P>Here the syntax is incorrect. This error occurs when you omit a mandatory wordwith 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<TT>as</TT>. The statement should look like this:</P><PRE><FONT COLOR="#0066FF">SQL> <B>create view emp_view as</B> 2 <B>select * from employee_tbl</B> 3 <B>/</B></FONT></PRE><H3><FONT COLOR="#000077">Missing Left Parenthesis</FONT></H3><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL> <B>@insert.sql</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">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 parenthesisSQL></FONT></PRE><H5>ANALYSIS:</H5><P>On line 2 a parenthesis does not appear before the Social Security number. Thecorrect syntax should look like this:</P><PRE><FONT COLOR="#0066FF">SQL> <B>insert into people_tbl values</B> 2 <B>('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')</B> 3 <B>/</B></FONT></PRE><H3><FONT COLOR="#000077">Missing Right Parenthesis</FONT></H3><H5><FONT COLOR="#000000">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 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 parenthesisSQL> spool offSQL></FONT></PRE><H5>ANALYSIS:</H5><P>On line 1 the right parenthesis is missing from the <TT>substr</TT>. The correctsyntax looks like this:</P><PRE><FONT COLOR="#0066FF"> SQL> <B>select substr(tablespace_name,1,15) a,</B> 2 <B> substr(file_name,1,45) c, bytes</B> 3 <B> from sys.dba_data_files</B> 4 <B>order by tablespace_name;</B></FONT></PRE><H3><FONT COLOR="#000077">Missing Comma</FONT></H3><H5><FONT COLOR="#000000">INPUT:</FONT></H5>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -