1 2 Previous Next 15 Replies Latest reply: Apr 22, 2013 7:34 PM by Vite DBA RSS

    what case should be used while nameing objects.

    947771
      hi,

      i wanted know what case i should keep while creating tables and GTT.

      like create table EMPLOYEE ( EMPID NUMBER(4,0))

      OR

      like create table Employee ( EmpId NUMBER(4,0))

      the point to be considered is, in stored proc or queries i want to use Employee for table name and EmpId for column
      becasue it increases readability.

      similarly while creating stored proce want to keep names like GetEmployee
      so should i keep it or change it in GETEMPLOYEE.

      yours sincerely

      sql devloper Version 3.1.07

      Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
      PL/SQL Release 11.2.0.2.0 - Production
      "CORE     11.2.0.2.0     Production"
      TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production
        • 1. Re: what case should be used while nameing objects.
          JustinCave
          So long as you are not enclosing the identifier in double-quotes, it doesn't matter. Oracle is case insensitive (unless you're enclosing the identifier in double quotes). There is no difference between
          CREATE TABLE employee ...
          CREATE TABLE Employee ...
          CREATE TABLE eMpLoYeE ...
          as far as Oracle is concerned. Nor is there any difference between
          SELECT * FROM employee
          SELECT * FROM Employee
          SELECT * FROM empLOYEe
          Of course, the third option in both cases is much harder to read. But it is functionally no different.

          Justin
          • 2. Re: what case should be used while nameing objects.
            Frank Kulash
            Hi,
            944768 wrote:
            hi,

            i wanted know what case i should keep while creating tables and GTT.

            like create table EMPLOYEE ( EMPID NUMBER(4,0))

            OR

            like create table Employee ( EmpId NUMBER(4,0))

            the point to be considered is, in stored proc or queries i want to use Employee for table name and EmpId for column
            becasue it increases readability.

            similarly while creating stored proce want to keep names like GetEmployee
            so should i keep it or change it in GETEMPLOYEE.
            As Justin said, it doesn't matter to Oracle, so you can use whichever way you (and the other people who have to read the code) find most helpful and convenient.

            Why doesn't it matter to Oracle? Because Oracle capitalizes everything that's not inside quotes (either single-quotes or double-quotes) before compiling. So you might say
            create table  Employee
            (   EmpId   NUMBER (4, 0) ...
            or
            Create Table  employee
            (   empid   Number (4, 0) ...
            Either way, the code that will be compiled is
            CREATE TABLE  EMPLOYEE
            (   EMPID   NUMBER (4, 0) ...
            This can be significant when you query the data dictionary:
            SELECT  data_precision          -- case doesn't matter here
            ,     Data_Length          -- case doesn't matter here
            FROM     USER_tab_columns     -- case doesn't matter here
            WHERE     table_NAME          -- case doesn't matter here
                      = 'EMPLOYEE'     -- *** CASE IS IMPORTANT HERE ***
            AND     column_Name          -- case doesn't matter here
                      = 'EMPID'     -- *** CASE IS IMPORTANT HERE ***
            ;
            sql devloper Version 3.1.07

            Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
            PL/SQL Release 11.2.0.2.0 - Production
            "CORE     11.2.0.2.0     Production"
            TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
            NLSRTL Version 11.2.0.2.0 - Production
            Thanks for posting the version information! It may not matter in this particular question, but you don't know that when you ask, and it never hurts to post it. There will be tims when this is very important.
            • 3. Re: what case should be used while nameing objects.
              rp0428
              >
              i wanted know what case i should keep while creating tables and GTT.

              like create table EMPLOYEE ( EMPID NUMBER(4,0))

              OR

              like create table Employee ( EmpId NUMBER(4,0))
              >
              Use the EXACT NAME of the table or column. When you don't use quotes around the names (and you shouldn't) Oracle will use UPPER case for the names. So that is what you should also use for your DDL. That eliminates any possible confusion about what the names are.
              >
              the point to be considered is, in stored proc or queries i want to use Employee for table name and EmpId for column
              becasue it increases readability.
              >
              How does it 'increaase readabilty' to use a name that is different than what Oracle is using? How is 'Employee' any more readable than 'EMPLOYEE'? or 'EmpId' any more readable than 'EMPID'? Use 'EMP_ID' if you want to separate the two parts.
              >
              similarly while creating stored proce want to keep names like GetEmployee
              so should i keep it or change it in GETEMPLOYEE.
              >
              NEITHER! Why don't you use 'GET_EMPLOYEE'? That uses the same name and uppercase that Oracle will use and the underscore makes it 'readable'.

              Oracle has a naming standard for objects: use it. Don't try to enforce some 'naming standard' for Oracle objects that is actually different than what the name actually is as far as Oracle is concerned. You have enough to worry about without trying to document and enforce a naming standard that:

              1. Isn't needed
              2. Is different than what Oracle actually names the objects
              3. Is confusing and just plain wrong in cases where you need to provide the correct name such as when you query the data dictionary.
              • 4. Re: what case should be used while nameing objects.
                BluShadow
                rp0428 wrote:
                How does it 'increaase readabilty' to use a name that is different than what Oracle is using? How is 'Employee' any more readable than 'EMPLOYEE'? or 'EmpId' any more readable than 'EMPID'? Use 'EMP_ID' if you want to separate the two parts.
                There's different views on this.

                On the one hand it is said that reading things that are all UPPERCASE is harder than reading things that are lower or Mixed case.

                http://www.microsoft.com/typography/ctfonts/wordrecognition.aspx

                On the other hand it is also said that this is a myth...

                http://www.graphics.com/modules.php?artid=937&name=Sections&op=viewarticle

                However, mostly these talk about the ability read normal the normal text of language, which is known to be not so fluid as to be just reading left to right or whatever, as described in those two articles.

                What we're dealing with though, isn't just plain language reading, we're talking more about a structured coding language containing various notations and keywords etc. As such, the way we read this code varies from the way we read a paragraph of our own language, especially as a lot of what we use are abbreviations or suchlike.

                As such, using a Mixed case can seem to make reading of the code easier than just using UPPERCASE. e.g. EMPLOYEEID is not so easily read as EmployeeID. Yes, you are correct that we could make it EMPLOYEE_ID, but in the case of longer column names e.g. REASONFORSEARCHID, we end up adding several additional characters e.g. REASON_FOR_SEARCH_ID and it can be just as easy to make it readable without adding so many characters such as ReasonForSearchID.

                Oracle may uppercase things internally (if no double quotes are used), but that's for the purpose of making things case insensitive, and shouldn't really be used as an excuse to dictate that developers should follow suit and also code in uppercase. We should just be thankful to Oracle that they didn't go the way of Java and make things case sensitive with Mixed case, so you really do have to learn exacly how things are written. Coding standards over the years have been tried and tested and general concensus of developers has shown that code written in mixed/camel case is more easy to follow than code written all in uppercase, which just seems blocky and hard to follow.

                There's a whole psychology about it, and we could perform lots of research into it, but I don't think we can say that it should be uppercase just because Oracle stores things internally like that. They could just as easily have chosen to store everything as lowercase for the same reason.
                • 5. Re: what case should be used while nameing objects.
                  Vite DBA
                  I agree there are different views on this.

                  My main gripe on this is when mixed case, without a word separator, is used to name database objects. It may look readable in the DDL script as

                  TransDtlLinesSeq (or it may not)

                  but when it is converted to uppercase in the data dictionary as

                  TRANSDTLLINESSEQ

                  I start making rude suggestions about where java developers can stick their camel code.

                  Use whatever standard works for you in general code, but for database object names, use one case with underscores as word separators. EG

                  TRANS_DTL_LINES_SEQ

                  Regards
                  Andre
                  • 6. Re: what case should be used while nameing objects.
                    BluShadow
                    Vite DBA wrote:
                    I agree there are different views on this.

                    My main gripe on this is when mixed case, without a word separator, is used to name database objects. It may look readable in the DDL script as

                    TransDtlLinesSeq (or it may not)

                    but when it is converted to uppercase in the data dictionary as

                    TRANSDTLLINESSEQ

                    I start making rude suggestions about where java developers can stick their camel code.
                    That's somewhat misguided. The camel case is not just something in Java, it's in the majority of language standards. Java is a pain just for the fact that it's case sensitive, and if you get one character wrong, you can spend ages going through having to find them and fix them.
                    Use whatever standard works for you in general code, but for database object names, use one case with underscores as word separators. EG

                    TRANS_DTL_LINES_SEQ
                    I personally don't like code that is all uppercase. From 30 years of computer programming experience in many different languages, I can honestly say that uppercase code is harder to read than lowercase or camel case.
                    • 7. Re: what case should be used while nameing objects.
                      William Robertson
                      I quite agree that referring to object names in uppercase is unhelpful, and after many years of carefully uppercasing language keywords like SELECT I realised that wasn't really doing any good either, so these days I write everything lowercase.

                      I've got nothing against CamelCase for PL/SQL variable names, except that as Andre mentioned, the dictionary listing for database objects will be in uppercase, so even though your DDL and your code refer to TransDtlLinesSeq, to anyone browsing the schema it will be listed as TRANSDTLLINESSEQ, which is hard to read and would have been better with underscores.

                      Also perhaps from a purist point of view, in Java etc the case has a special meaning whereby initCapName is an instance of class InitCapName, which does not quite translate into PL/SQL, so arguably it is misleading.

                      Edited by: William Robertson on Apr 22, 2013 1:52 PM
                      • 8. Re: what case should be used while nameing objects.
                        BluShadow
                        William Robertson wrote:
                        I quite agree that referring to object names in uppercase is unhelpful, and after many years of carefully uppercasing language keywords like SELECT I realised that wasn't really doing any good either, so these days I write everything lowercase.

                        I've got nothing against CamelCase for PL/SQL variable names, except that as Andre mentioned, the dictionary listing for database objects will be in uppercase, so even though your DDL and your code refer to TransDtlLinesSeq, to anyone browsing the schema it will be listed as TRANSDTLLINESSEQ, which is hard to read and would have been better with underscores.
                        I can agree with that, though lets face it... for most application developers, they shouldn't have any real need to be looking up object names in the data dictionary views. It's just a pain for those who are doing more technical/DBA type things.
                        • 9. Re: what case should be used while nameing objects.
                          Pleiadian
                          I have such a dislike for uppercase, that I often find myself writing code like:
                          select *
                          from   all_tables
                          where  table_name = upper('table_name')
                          just to avoid uppercase
                          • 10. Re: what case should be used while nameing objects.
                            BluShadow
                            Pleiadian wrote:
                            I have such a dislike for uppercase, that I often find myself writing code like:
                            select *
                            from   all_tables
                            where  table_name = upper('table_name')
                            just to avoid uppercase
                            Which fails if some clever person has created a table name using double quotes. ;)
                            • 11. Re: what case should be used while nameing objects.
                              Manik
                              BluShadow wrote:
                              Pleiadian wrote:
                              I have such a dislike for uppercase, that I often find myself writing code like:
                              select *
                              from   all_tables
                              where  table_name = upper('table_name')
                              just to avoid uppercase
                              Which fails if some clever person has created a table name using double quotes. ;)
                              :)

                              Cheers,
                              Manik.
                              • 12. Re: what case should be used while nameing objects.
                                Pleiadian
                                Hmm... I would not call them clever.

                                Single quotes can be confusing also! We had a consultant who indexed a couple of tables like this:
                                create index index_01 on table_name('field_name');
                                They were not very effective ;)
                                • 13. Re: what case should be used while nameing objects.
                                  rp0428
                                  >
                                  We should just be thankful to Oracle that they didn't go the way of Java and make things case sensitive with Mixed case, so you really do have to learn exacly how things are written
                                  >
                                  Good new and bad news.

                                  First the good news: - you will be pleased to know that in 12c the default is to use the current behaviour.

                                  Can't say more for now. ;)
                                  • 14. Re: what case should be used while nameing objects.
                                    Vite DBA
                                    BluShadow wrote:
                                    Vite DBA wrote:
                                    I agree there are different views on this.

                                    My main gripe on this is when mixed case, without a word separator, is used to name database objects. It may look readable in the DDL script as

                                    TransDtlLinesSeq (or it may not)

                                    but when it is converted to uppercase in the data dictionary as

                                    TRANSDTLLINESSEQ

                                    I start making rude suggestions about where java developers can stick their camel code.
                                    That's somewhat misguided. The camel case is not just something in Java, it's in the majority of language standards. Java is a pain just for the fact that it's case sensitive, and if you get one character wrong, you can spend ages going through having to find them and fix them.
                                    Give me a break ;-) I've been around long enough to remember when Java was an Indonesian island. I was just making a point with humour using one of the more common programming languages.
                                    Use whatever standard works for you in general code, but for database object names, use one case with underscores as word separators. EG

                                    TRANS_DTL_LINES_SEQ
                                    I personally don't like code that is all uppercase. From 30 years of computer programming experience in many different languages, I can honestly say that uppercase code is harder to read than lowercase or camel case.
                                    I wasn't advocating any particular case, my main point was that when reading the data dictionary, having an underscore as a word separator is preferable.

                                    Andre
                                    1 2 Previous Next