This site was created as an online reference for various ADO and ADO.NET SQL database connection strings. We have tried to list the most popular database connection strings and to give examples of their usage. As our background is with Microsoft technologies, we have great deal of connection string for MS SQL Server and MS Access including SQL Server ODBC connection, SQL Server OLE DB connection, SQLConnection .NET, MS Access ODBC connection, MS Access OLE DB connection, MS Access OLEDBConnection .NET, etc.
We have also created an online database glossary, giving definitions for the most common database terms like ODBC, OLE DB, ADO, database cursor, database transaction, etc.
If you are new to SQL language, make sure you check this comprehensive SQL Tutorial.
This site is by no means a complete reference for every existing database, but is merely attempt to give our fellow software developers a handy database connection strings reference. Have fun :)!
Friday, May 8, 2009
SQL Server connection strings
SQL ODBC connection strings
Standard Security:<> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Uid=Your_Username;Pwd=Your_Password;"Trusted connection:<> "Driver={SQLServer};Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=yes;"
SQL OLE DB connection strings
Standard Security:"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"
Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
SQL OleDbConnection .NET strings
Standard Security:"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;"
Trusted connection:
"Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;" Standard Security:
1. "Data Source=Your_Server_Name;Initial Catalog= Your_Database_Name;UserId=Your_Username;Password=Your_Password;" <>2. "Server=Your_Server_Name;Database=Your_Database_Name;UserID=Your_Username;Password=Your_Password;Trusted_Connection=False"
Trusted connection:
1. "Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated Security=SSPI;"
2."Server=Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;"
MS Access connection strings
MS Access ODBC connection strings
Standard Security:"Driver= {MicrosoftAccessDriver(*.mdb)};DBQ=C:\App1\Your_Database_Name.mdb;Uid=Your_Username;Pwd=Your_Password;"
Workgroup:
"Driver={Microsoft Access Driver (*.mdb)}; Dbq=C:\App1\Your_Database_Name.mdb; SystemDB=C:\App1\Your_Database_Name.mdw;"
Exclusive "Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\App1\Your_Database_Name.mdb; Exclusive=1; Uid=Your_Username; Pwd=Your_Password;"
MS Access OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to Access database:"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password="
Open connection to Access database using Workgroup (System database):
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:System Database=c:\App1\Your_System_Database_Name.mdw"
Open connection to password protected Access database:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:Database Password=Your_Password"
Open connection to Access database located on a network share:
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server_Name\Share_Name\Share_Path\Your_Database_Name.mdb"
Open connection to Access database located on a remote server:
"Provider=MS Remote; Remote Server=http://Your-Remote-Server-IP; Remote Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb"
MySQL connection strings
MySQL ODBC connection strings
Open connection to local MySQL database using MySQL ODBC 3.51 Driver"Provider=MSDASQL; DRIVER={MySQL ODBC 3.51Driver}; SERVER= localhost; DATABASE=Your_MySQL_Database; UID= Your_Username; PASSWORD=Your_Password; OPTION=3"
MySQL OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to MySQL database:"Provider=MySQLProv;Data Source=Your_MySQL_Database;User Id=Your_Username; Password=Your_Password;"
Oracle connection strings
Oracle ODBC connection strings
Open connection to Oracle database using ODBC"Driver= {Microsoft ODBCforOracle};Server=Your_Oracle_Server.world;Uid=Your_Username;Pwd=Your_Password;"
Oracle OLE DB & OleDbConnection (.NET framework) connection strings
Open connection to Oracle database with standard security:1. "Provider=MSDAORA;Data Source= Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"
2. "Provider= OraOLEDB.Oracle;Your_Oracle_Database;UserId=Your_Username;Password=Your_Password;"
Open trusted connection to Oracle database
"Provider= OraOLEDB.Oracle;DataSource=Your_Oracle_Database;OSAuthent=1;"
Monday, January 26, 2009
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};
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.
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 /
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> @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.
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.
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.
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;
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 /
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 /
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.
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;
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;
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.
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>
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>
Subscribe to:
Posts (Atom)