1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

regarding all_tab_columns

Discussion in 'SQL PL/SQL' started by karthikeyanc2003, Aug 10, 2009.

  1. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    hi folks,
    i have a great doubt that i ever seen in oracle,
    consider a senirio , i have a table called employee having six column, while i run the following query;

    DECLARE
    t1 NUMBER;
    table_name VARCHAR2(100);
    BEGIN
    table_name := 'employee';
    SELECT Count(*) INTO t1 FROM all_tab_columns WHERE TABLE_name = upper( table_name );
    Dbms_Output.Put_Line(t1);
    END ;


    i get a output as 14206

    rather than 6

    let me know what wents wrong in this.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is because you are comparing table_name with field table_name instead of variable name. Oracle will give preference to column ahead of variable in case both of them having the same name. This can be resolved by using different variable name.

    Code (SQL):
    SQL> DECLARE
      2  t1 NUMBER;
      3  TABLE_NAME VARCHAR2(100);
      4  BEGIN
      5  TABLE_NAME := 'emp';
      6  SELECT COUNT(*) INTO t1 FROM user_tab_columns
      7  WHERE TABLE_NAME = UPPER( TABLE_NAME );
      8  Dbms_Output.Put_Line('Number of columns in table '||UPPER( TABLE_NAME )||'= '|| t1);
      9  END ;
     10  /
    NUMBER OF COLUMNS IN TABLE EMP = 29

    PL/SQL PROCEDURE successfully completed.

    SQL> DECLARE
      2  t1 NUMBER;
      3  tname VARCHAR2(100);
      4  BEGIN
      5  tname := 'emp';
      6  SELECT COUNT(*) INTO t1 FROM user_tab_columns
      7  WHERE TABLE_NAME = UPPER( tname );
      8  Dbms_Output.Put_Line('Number of columns in table '||UPPER( tname )||' = '|| t1);
      9  END ;
     10  /
    NUMBER OF COLUMNS IN TABLE EMP = 8

    PL/SQL PROCEDURE successfully completed.

    SQL>
     
  3. karthikeyanc2003

    karthikeyanc2003 Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    100
    thanks Rajavu