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!

find value in table (don't know column name yet)

Discussion in 'SQL PL/SQL' started by ecivgamer, Sep 17, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    My need is to find value in table (don't know column name yet).

    Trying to use column list and loop.
    Honestly I'm afraid I can't write proper code to find COLUMN VALUE (2581365), because my code works fine only to find COLUMN NAME (dealid).
    Please help me to find COLUMN VALUE.

    Oracle 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Finally I use this code, it works fine, thanks to all

     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Try with Below Script...

    Code (SQL):
     DECLARE
        lv_sql VARCHAR2(1000);
        lv_col  VARCHAR2(30);
        lv_col_val VARCHAR2(1000);
       
       ld_from_date DATE := to_date( '2013-09-30','YYYY-MM-DD')-60;
        ld_to_date  DATE := to_date( '2013-09-30','YYYY-MM-DD')+60;
        lv_val  VARCHAR2(100) := 'DEALID';
         
    BEGIN

       FOR i IN ( SELECT column_name FROM ALL_TAB_COLUMNS WHERE owner = 'CREATOR' AND TABLE_NAME LIKE UPPER('%dbt_reserve%') )
       LOOP

        lv_col := i.column_name;
       
        lv_sql := ' SELECT '||lv_col||' FROM  CREATOR.dbt_reserve WHERE  arcdate BETWEEN '||ld_from_date||' AND '||ld_from_date||' ISAPPROVED=1  AND UPPER( '||lv_col||') = '||lv_val;
     
            BEGIN
                   EXECUTE IMMEDIATE lv_sql INTO lv_col_val;
            EXCEPTION
                  WHEN OTHERS THEN
                        NULL;
            END;
         
           DBMS_OUTPUT.PUT_LINE(' Column : '||lv_col||' Value '||lv_col_val);
       
       END LOOP;
       
    END;

     

    Regards
    Sambasiva Reddy.K