14 Replies Latest reply: Apr 24, 2013 3:10 AM by BluShadow RSS

    should i put "" around columns like name.

    947771
      hi,

      i have noticed

      select name from abc ;

      name column becomes blue in color in sql devloper.
      but it does not give any problem.

      can i leave it as it is because it is working, or i should put "".

      but unnecesserlly i do not want to put "".

      yes if any problem can come then i will use "" around name columne.

      ----------------------------------------------------------------------------------------------------
      while declaring varchar2(32767) in stored proce , can i keep it 32767 or keeping it as much as required is better?


      Does any memory problem can come if i keep 32767 and use only 9000 of it.



      yours sincerely

      Edited by: 944768 on Apr 23, 2013 9:07 PM
        • 1. Re: should i put "" around columns like name.
          Purvesh K
          944768 wrote:
          hi,

          i have noticed

          select name from abc ;

          name column becomes blue in color in sql devloper.
          but it does not give any problem.

          can i leave it as it is because it is working, or i should put "".

          but unnecesserlly i do not want to put "".

          yes if any problem can come then i will use "" around name columne.
          You need to understand the Significance of Double Quotes in Oracle. Usage of Double Quotes for object names signify Case Sensitive usage of the objects.

          Meaning, if you declare a column name as "Name" (double quotes included) in your Table, then
          select name from your_table
          Will give you an error as there does not exist a column which matches name, but is defined as Name.

          See, below demonstration:-
          drop table test_table;
          
          create table test_table (col1 number, "Col2" number);
          
          select table_name, column_name
            from user_tab_columns
           where table_name = 'TEST_TABLE';
          
          TABLE_NAME                     COLUMN_NAME                    
          ------------------------------ ------------------------------ 
          TEST_TABLE                     COL1                           
          TEST_TABLE                     Col2
          
          Notice how the COL2 column is stored in Database dictionary. Hence, It has to be used exactly as stored. The below SQL shall fail:
          
          select col2 from test_table;
          
          Error starting at line 9 in command:
          select col2 from test_table
          Error at Command Line:9 Column:7
          Error report:
          SQL Error: ORA-00904: "COL2": invalid identifier
          
          select "Col2" from test_table;
          
          Col2                   
          ----------------------
          And the reason why SQL Developer displays name in Blue, is because NAME is a reserved keyword in Oracle and use of Reserved keyword is a bad practice. Ideally, it should be changed to something meaningful viz. user_name (Not USERNAME), first_name etc.

          >
          ----------------------------------------------------------------------------------------------------
          while declaring varchar2(32767) in stored proce , can i keep it 32767 or keeping it as much as required is better?


          Does any memory problem can come if i keep 32767 and use only 9000 of it.
          I assume you are referring to PL/SQL here and not SQL, because SQL has limit of 4000 bytes and cannot incude 32767 bytes.

          To answer your question, since you declared variable as VARCHAR2, it will only consume the space required to store the particular value. Meaning, if you are storing only 9K bytes, it will consume space relevant to the value, unlike CHAR field which will consume entire 32767 bytes of space.

          Edited by: Purvesh K on Apr 24, 2013 11:13 AM
          Added Demonstration.
          • 2. Re: should i put "" around columns like name.
            ranit B
            Hi,

            Part 1
            A small demonstration :
            /* 
               I've a table called 'action' with a column named - 'action_id'
            */
            create table action(
              action_id number(10)
            );
            
            /*
               "these are permissible" 
            */
            select action_id from action;
            select ACTION_ID from action;
            select AcTioN_Id from action;
            
            /*
               "these are not permissible" - throws ORA-00904 : Invalid Identifier
            */
            select "action_id" from action;
            select "ACtIoN_Id" from action;
            
            /*
               "But surprisingly this is acceptable"
            */
            select "ACTION_ID" from action;
            The last scenario "ACTION_ID" worked because Oracle by-default stores everything in UPPER-CASE. So, the pattern matches with what is stored in the DB.
            So, be cautious while using "double-quotes" with object names.

            Part 2
            name column becomes blue in color in sql devloper.
            For this, please check this :
            SELECT *
              FROM v$reserved_words
             WHERE keyword = 'NAME'
            Part 3
            while declaring varchar2(32767) in stored proce , can i keep it 32767 or keeping it as much as required is better?
            DECLARE
                demo_char CHAR(10);
                demo_varchar2 VARCHAR2(10); 
            BEGIN
                demo_char := 'AAA';
                demo_varchar2 := 'BBB';
            
                dbms_output.put_line('length of CHAR = '||LENGTH(demo_char));
                dbms_output.put_line('length of VARCHAR2 = '||LENGTH(demo_varchar2));
            END;
            
            Output :
            length of CHAR = 10
            length of VARCHAR2 = 3
            HTH.
            • 3. Re: should i put "" around columns like name.
              947771
              i know oracle's working , what i wanted to know is what should be used.( 1,2,4) or any other thing .



              while createing table if one uses.


              create table abc(name varchar2(250));
              create table abc(NAME varchar2(250));
              create table abc("NAME" varchar2(250));


              1)select name from abc;

              2)select NAME from abc;

              select "name" from abc; -- will not work others will work.

              4)select "NAME" from abc;

              -----

              and i am not declaring variable as VARCHAR2, i am declaring as varchar2(32767).

              "while declaring varchar2(32767) in stored proce , can i keep 32767 or keeping it as much as required is better"?

              yorus sincerely


              Edited by: 944768 on Apr 23, 2013 11:23 PM

              Edited by: 944768 on Apr 23, 2013 11:23 PM

              Edited by: 944768 on Apr 23, 2013 11:23 PM

              Edited by: 944768 on Apr 23, 2013 11:29 PM

              Edited by: 944768 on Apr 23, 2013 11:29 PM

              Edited by: 944768 on Apr 23, 2013 11:29 PM
              • 4. Re: should i put "" around columns like name.
                Purvesh K
                You need to have a good reason to maintain Case Sensitive object names. Do you have any?

                If not, then I will always suggest Case Insensitive. i.e. in your case Option 1.

                And both
                create table abc(name varchar2(250));
                create table abc(NAME varchar2(250));
                are equivalent. As Oracle converts all object names to Upper Case.

                To answer another question,
                and i am not declaring variable as VARCHAR2, i am declaring as varchar2(32767).

                "while declaring varchar2(32767) in stored proce , can i keep 32767 or keeping it as much as required is better"?
                It depends. My Opinion is, provide width to variables according to need. Say you have a variable that stores Age, so defining it as NUMBER will automatically allow to store value upto 38 digits, which is quite unnecessary, though there is no harm in doing so, it just isn't right. You can declare the variable to be NUMBER(3).
                Similar applies to Varchar2 fields as well.
                • 5. Re: should i put "" around columns like name.
                  947771
                  thanku

                  what i have understood is the blue word need not be put in side ""?

                  and

                  please tel me what should i use while declaring the table?

                  create table abc(name varchar2(250));
                  create table abc(NAME varchar2(250));
                  create table abc("NAME" varchar2(250));



                  yours sincerely

                  Edited by: 944768 on Apr 23, 2013 11:35 PM
                  • 6. Re: should i put "" around columns like name.
                    andrewmy
                    Case sensitive object names can cause lots of confusion, so my usual rule of thumb is: unless you are really sure you need case-sensitive object names, don't use double quotes to name any object.
                    • 7. Re: should i put "" around columns like name.
                      947771
                      what i have understood is the blue word need not be put in side "", and it left as it is will do any problem?
                      • 8. Re: should i put "" around columns like name.
                        William Robertson
                        No, don't use double quotes when defining objects unless you absolutely have to. Either they are redundant (so you needn't have bothered) or you will need to double-quote the name every time you refer to it. It's also a bad habit to use them by default because it can mask errors (e.g. you name a column "CUSTOMER ID" by mistake when you actually meant "CUSTOMER_ID").

                        PL/SQL optimises small VARCHAR2s for performance, and large ones for storage. See the section on Memory Allocation under VARCHAR2 in the PL/SQL Reference.
                        • 9. Re: should i put "" around columns like name.
                          William Robertson
                          944768 wrote:
                          what i have understood is the blue word need not be put in side "", and it left as it is will do any problem?
                          It might annoy you whenever you look at it, otherwise should not cause errors. Some keywords are only significant when they are part of a specific command, for example NAME is part of SET TRANSACTION (though might appear elsewhere - I only had a quick look, plus I haven't had my morning coffee yet so could be missing something obvious) and so although they are in the list and therefore highlighted in some editors (and upper-cased by some, if you have that setting), they won't actually cause problems. I think.
                          • 10. Re: should i put "" around columns like name.
                            ranit B
                            PL/SQL optimises small VARCHAR2s for performance, and large ones for storage. See the section on Memory Allocation under VARCHAR2 in the PL/SQL Reference.
                            Exactly speaking this link - http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#CIHEDDFG
                            Right William? ;)

                            Just 2 weeks back only got to know this concept. Great job Oracle!
                            • 11. Re: should i put "" around columns like name.
                              947771
                              thank u,

                              what i noticed is,

                              when object is declared like following

                              create table abc("NAME" varchar2(250));

                              it can be used with out "".


                              select name from abc;
                              select NAME from abc;

                              that means one does not have to put "" while using them in select.

                              i am using sql devloper Version 3.1.07

                              yours sincerely

                              Edited by: 944768 on Apr 24, 2013 12:31 AM
                              • 12. Re: should i put "" around columns like name.
                                William Robertson
                                Yes, when the name already complies with Oracle identifier naming rules the double quotes are redundant, but also a dangerous habit to get into because they can hide mistakes.
                                • 13. Re: should i put "" around columns like name.
                                  _Karthick_
                                  i am using sql devloper Version 3.1.07
                                  SQL Developer is a client tool like SQL Plus or Toad or PL/SQL Developer or JAVA or Dot Net etc.

                                  The client tool that you use has no relevance with the question that you have asked. I believe you have some basic misconception in this regard.
                                  • 14. Re: should i put "" around columns like name.
                                    BluShadow
                                    944768 wrote:
                                    thank u,

                                    what i noticed is,

                                    when object is declared like following

                                    create table abc("NAME" varchar2(250));

                                    it can be used with out "".


                                    select name from abc;
                                    select NAME from abc;

                                    that means one does not have to put "" while using them in select.

                                    i am using sql devloper Version 3.1.07
                                    Yes, it may work... this time, but it's bad practice to use keywords or reserved words.

                                    Better would be to give your column a meaningful name. The column "name" is very generic. What sort of "name" is it? You would be better using something more meaningful like SURNAME, FORENAME, COMPANY_NAME, PET_NAME, BOOK_NAME etc. and then, not only is it clearer in the table, but it can also make your code more easy to understand, especially if you have different tables, all storing different types of names, it would cause such confusion if they were all called NAME.