9 Replies Latest reply: Nov 23, 2012 10:28 PM by Aman.... RSS

    Bit Confusion

    Girish Sharma
      SQL> select object_name from user_objects
        2  where
        3  object_name like '_'
        4  /
      
      no rows selected
      
      SQL> create table t(a number);
      
      Table created.
      
      SQL> select object_name from user_objects
        2  where
        3  object_name like '_'
        4  /
      
      OBJECT_NAME
      --------------------------------------------------------------------------------
      T
      
      SQL> show user;
      USER is "SYS"
      SQL> select * from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE    11.2.0.1.0      Production
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      
      SQL> desc t;
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       A                                                  NUMBER
      
      SQL> select * from t;
      
      no rows selected
      
      SQL> select object_name from user_objects
        2  where
        3  object_name like 'T'
        4  /
      
      OBJECT_NAME
      --------------------------------------------------------------------------------
      T
      
      SQL>
      Do you see something confusing; as I am, means, I am creating table T and it is showing "_" and "T" in user objects... Or I am confused without any reason ?

      And when I did more :
      SQL> drop table t purge;
      
      Table dropped.
      
      SQL> select object_name from user_objects
        2  where
        3  object_name like '_'
        4  /
      
      no rows selected
      
      SQL> create table t(a number);
      
      Table created.
      
      SQL> select object_name,object_type from user_objects
        2  where
        3  object_name like '_'
        4  /
      
      OBJECT_NAME
      --------------------------------------------------------------------------------
      OBJECT_TYPE
      -------------------
      T
      TABLE
      Regards
      Girish Sharma

      Edited by: Girish Sharma on Nov 23, 2012 4:50 PM
        • 1. Re: Bit Confusion
          Ora
          Underscore '_' in like operator means single character. Hence the result.
          SQL> show user
          USER is "SYS"
          
          SQL> select object_name from user_objects where object_name like '_';
          
          no rows selected
          
          SQL> create table t(a number);
          
          Table created.
          
          SQL> select object_name from user_objects where object_name like '_';
          
          OBJECT_NAME
          --------------------------------------------------------------------------------
          
          T
          
          SQL> select object_name from user_objects where object_name like '/_' escape '/';
          
          no rows selected
          
          SQL> select object_name from user_objects where object_name like '%/_' escape '/';
          
          OBJECT_NAME
          --------------------------------------------------------------------------------
          
          /14e435c2_OracleErrorsText_zh_
          /1a4eada0_SemanticOptionsText_
          /1b1f8d4e_SemanticOptionsText_
          /20b58c58_HandshakeMessageRSA_
          /252ba93a_AQjmsStreamMessages_
          /291c36c5_SemanticOptionsText_
          /2f0d3c49_SemanticOptionsText_
          /30140c7c_XPATHErrorResources_
          /311d6c9_RuntimeRefErrorsText_
          /31b0e9f0_XPATHErrorResources_
          /3289a0c2_SemanticOptionsText_
          Edited by: Ora on 23 Nov, 2012 3:27 AM
          • 2. Re: Bit Confusion
            Fran
            same with other user (not only as sys), and too in version 11.2.0.3
            SQL> select object_name from user_objects where object_name like '_';
            
            no rows selected
            
            SQL> create table t(a number);
            
            Table created.
            
            SQL> select object_name, object_type from user_objects where object_name like '_';
            
            OBJECT_NAME
            --------------------------------------------------------------------------------
            OBJECT_TYPE
            -------------------
            T
            TABLE
            
            
            SQL> select * from v$version;
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            CORE    11.2.0.3.0      Production
            TNS for Linux: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production
            
            SQL> sho user
            USER is "FRAN"
            • 3. Re: Bit Confusion
              Fran
              you are right ORA, i forgot the basics of sql. Thanks for refresh it :)
              • 4. Re: Bit Confusion
                Ora
                wc :)
                • 5. Re: Bit Confusion
                  Girish Sharma
                  Thanks for reply. But my question is :

                  1.What is the relation of T (or may be any other word) with "_" ? Why there is no row when I drop table T ?

                  Actually I was looking all the objects in sys user whose name like "_%"; with double quotes
                  SQL> ed
                  Wrote file afiedt.buf
                  
                    1  select object_name,
                    2  object_type
                    3  from
                    4  user_objects
                    5* where object_name like '"_%"'
                  SQL> /
                  
                  no rows selected
                  Obviously there may not be any object whose name is "_%".
                  But there is one object like :
                  SQL> desc _CURRENT_EDITION_OBJ;
                  ERROR:
                  ORA-00911: invalid character
                  ORA-00911: invalid character
                  
                  
                  SQL> desc "_CURRENT_EDITION_OBJ";
                  
                  ...And result is there.
                  So, I wanted to know the object names who have created with double quotes starting with underscore. May be some regexp requires..

                  Regards
                  Girish Sharma
                  • 6. Re: Bit Confusion
                    Richard Harrison .
                    Hi,
                    '_' is a wilcard character meaning match any single character - you need to disable this facility to show the data you want.

                    So

                    select * from dba_tables where table_name like '\_%';

                    should give you what you want.

                    '\' is the default 'escape' character for the wildcards ( though it can be defined by using the escape keyword) - for example:

                    select * from dba_table where table_name like '~_%' escape '~';

                    Regards,
                    Harry
                    • 7. Re: Bit Confusion
                      Aman....
                      Girish,

                      TBH I am not sure that I got your confusion but if you are looking for to search for the character '_' as a symbol you would need to escape it like below,
                      SQL> select * from V$version;
                      
                      BANNER
                      --------------------------------------------------------------------------------
                      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
                      PL/SQL Release 11.2.0.3.0 - Production
                      CORE    11.2.0.3.0      Production
                      TNS for Linux: Version 11.2.0.3.0 - Production
                      NLSRTL Version 11.2.0.3.0 - Production
                      
                      SQL> conn aman/aman
                      Connected.
                      SQL> create table "_myt" (a number);
                      
                      Table created.
                      
                      SQL> select object_name from user_objects where object_name like '/_%' escape '/';
                      
                      OBJECT_NAME
                      --------------------------------------------------------------------------------
                      _myt
                      
                      SQL> 
                      edit
                      Sorry, I didn't see the reply of Harry.

                      HTH
                      Aman....
                      • 8. Re: Bit Confusion
                        Girish Sharma
                        Thank you very much Aman.

                        Today learnt a new lesson.

                        Regards
                        Girish Sharma
                        • 9. Re: Bit Confusion
                          Aman....
                          :)

                          Aman....