4 Replies Latest reply: Apr 25, 2014 5:26 AM by UnixFan12 RSS

    Oracle 12c: create table

    UnixFan12

      On oracle 12c,with sqlplus

       

      [CODE]

      SQL> CREATE TABLE "durata" (

        2  "iddurata" NUMBER(11) NOT NULL ,

        3  "durata" NVARCHAR2(40) NOT NULL

        4    )

        5  ;

       

      Table created.

      [/CODE]

       

      [CODE]

      INSERT INTO "durata" VALUES ('1', '1:30');

       

      1 row created.

      [/CODE]

       

      [CODE]

      SQL> select * from durata;

      select * from durata

                    *

      ERROR at line 1:

      ORA-00942: table or view does not exist

      [/CODE]

       

      Why?

        • 1. Re: Oracle 12c: create table
          EdStevens

          by default, object names are upper case.  When you created your table you forced the name to be lower case by enclosing the name in quotes.  But when you tried to select from it, you left the name un-quoted, so oracle tried to find a table named 'DURATA'.    'DURATA' != 'durata'.

           

           

          Bottom line is you should never create objects with quotes around their names, thus letting oracle create them with the default of upper-case names.

           

          And before you start -- no, you do NOT want them to be lower-  or mixed-case.  It just causes untold grief, which you are already beginning to experience.

           

          You could get your results with 'SELECT * FROM "durata"',  but you really don't want to go there.

          • 2. Re: Oracle 12c: create table
            rp0428
            Why?

            Answer this question for us: why did you put double quotes around "durata" in the INSERT query?

            INSERT INTO "durata" VALUES ('1', '1:30');

            But didn't put double quotes for the SELECT query:

            select * from durata;

            That should be enough of a hint.

             

            And you should now be able to guess what the result will be if you use this query:

            select iddurata from "durata"

            You will have the same issue with the columns that you defined using double quotes if you try to reference those columns WITHOUT using double quotes.,

             

            See the section 'Database Object Naming Rules' in the SQL Language doc

            http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements008.htm

            Database Object Naming Rules

            Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

            •   A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.
            •   A nonquoted identifier is not surrounded by any punctuation.

            You can use either quoted or nonquoted identifiers to name any database object. However, database names, global database names, and database link names are always case insensitive and are stored as uppercase. If you specify such names as quoted identifiers, then the quotation marks are silently ignored. Refer to CREATE USER for additional rules for naming users and passwords.

            • 3. Re: Oracle 12c: create table
              spiderwoman

              SQL> CREATE TABLE "durata" ( "iddurata" NUMBER(11) NOT NULL ,"durata" NVARCHAR2(40) NOT NULL );

              Table created.

              SQL> INSERT INTO "durata" VALUES ('1', '1:30');

              1 row created.

              SQL> select * from durata;
              select * from durata
                            *
              ERROR at line 1:
              ORA-00942: table or view does not exist


              SQL> select * from "durata";

                iddurata durata
              ---------- ----------------------------------------
                       1 1:30

              SQL>

              • 4. Re: Oracle 12c: create table
                UnixFan12

                Thanks,i usually use postgres when 'durata' is the same as durata.