In order to know the schema objects please have a look at http://arjudba.blogspot.com/2008/06/database-objects-in-oracle.html
There are some rules to name a schema objects which are defined below.
•Schema name as well as database objects name can be specified within double quotes and without quotes.
•DB_NAME, GLOBAL_NAME, and database link names are always case insensitive and are stored as uppercase. So if you specify these names as quoted identifiers, then the quotation marks are silently ignored.
•Database name can have maximum 8 bytes.
•Database Link name can have maximum 128 bytes.
•Schema name other than DB_NAME and database link name can be 1 to 30 bytes long.
•If an identifier includes multiple parts then each attribute can be up to 30 bytes long.
•Before going into this section have an idea about oracle database reserved keyqords which are discussed on
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
We can use reserved words to name schema objects within quotes but can't use it without quotes. An example below will make you clear.
SQL> create table is ( a number);
create table is ( a number)
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> create table "IS" ( a number);
Table created.
SQL> create table "is" ( a number);
Table created.
The only exception is ROWID. Uppercase ROWID can't be used to name a column. However uppercase ROWID can be used in another quoted identifier other than column name and you can use the word with one or more lowercase letters (for example, "Rowid" or "rowid" or "ROwid") as any quoted identifier, including a column name.
SQL> create table "ROWID"( "ROWID" number);
create table "ROWID"( "ROWID" number)
*
ERROR at line 1:
ORA-00904: "ROWID": invalid identifier
SQL> create table "ROWID"( "rowid" number);
Table created.
•Though Non ASCII characters can be used oracle recommends to use ASCII characters in database names, global database names, and database link names.
•Non quoted identifiers must begin with an alphabetic character from your database character set.
SQL> create table 1a ( a number);
create table 1a ( a number)
*
ERROR at line 1:
ORA-00903: invalid table name
SQL> create table "1a" (a number);
Table created.
•Non quoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@).
Quoted identifiers can contain any characters and punctuations marks as well as spaces.
So this one containing "(" failed with ORA-00902: invalid datatype.
SQL> create table epr_employee(Mail_(per) varchar2(20), Mail_(off) varchar2(20));
create table epr_employee(Mail_(per) varchar2(20), Mail_(off) varchar2(20))
*
ERROR at line 1:
ORA-00902: invalid datatype
But note that within quotes it is ok.
SQL> create table epr_employee5("Mail_\0 (per)" varchar2(20), "Mail_(off)" varchar2(20));
Table created.
Also see quote within quotes are not valid.
SQL> create table epr_employee5("Mail_\" (per)" varchar2(20), "Mail_(off)" varchar2(20));
create table epr_employee5("Mail_\" (per)" varchar2(20), "Mail_(off)" varchar2(20))
*
ERROR at line 1:
ORA-00902: invalid datatype
It is good to know that neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).
•Within a namespace, no two objects can have the same name. To know more about namespace and objects name please have a look at,
Object Namespace in oracle
•Quoted strings are case sensitive and non quoted string are not case sensitive. Oracle interpret non-quoted strings as uppercase.
•In a table no two columns can have the same name.
•Procedures or functions contained in the same package can have the same name, if their arguments are not of the same number and datatypes. If procedures or functions contained in the same package can have the same name and their arguments are not of the same number and datatypes then it is called overloading functions or overloading procedures.
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
No comments:
Post a Comment