This discussion is archived
9 Replies Latest reply: Nov 23, 2012 8:28 PM by Aman.... RSS

Bit Confusion

Girish Sharma Guru
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    you are right ORA, i forgot the basics of sql. Thanks for refresh it :)
  • 4. Re: Bit Confusion
    Ora Pro
    Currently Being Moderated
    wc :)
  • 5. Re: Bit Confusion
    Girish Sharma Guru
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Thank you very much Aman.

    Today learnt a new lesson.

    Regards
    Girish Sharma
  • 9. Re: Bit Confusion
    Aman.... Oracle ACE
    Currently Being Moderated
    :)

    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points