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!

Finding a particular value in column that exists in numerous tables

Discussion in 'SQL PL/SQL' started by Donnie28, May 21, 2014.

  1. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Hi All

    I am trying to find a value in a particular column in a database but the column exist in numerous tables so I don't know which table the value comes from. The column name is called CMPL_STAT_ID and the value I want to search for is "Unsuccessful".

    Any help on this would be very much apprecaited

    Regards
    Donal
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    what tables are output by this request ?

    Code (SQL):

    SELECT * FROM user_tab_cols  WHERE COLUMN_NAME = 'CMPL_STAT_ID'
     
     
  3. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    Thanks for the reply Sergey this gives me back a number of columns like Table_Name, Column_Name, Data_Type etc. and they are all blank which I wasn't expecting as I know the colun exists in a number of tables
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What you need to find?

    for example :
    Code (SQL):


    DECLARE
      i INT;
    BEGIN
        FOR z IN (
                SELECT
                       c.TABLE_NAME,
                       'select count(*)  from '||c.TABLE_NAME||' where '||c.COLUMN_NAME||' between :1 and :2' sql_txt
                       FROM user_tab_cols c  column_name = 'CMPL_STAT_ID'              )
        loop
             EXECUTE immediate z.sql_txt
             INTO  i
             USING  val1,val2;
             dbms_output.put_line(z.TABLE_NAME||' row count '||i);
        END loop;                    
    END;


     
     
  5. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    I ned to find where the word 'Unsuccessful' exist in the column name 'CMPL_STAT_ID'......tried tio run the above bu just gives an java.sql.SQLException: ORA-06550: line 18, column 0:
    PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

    ; <an identifier> <a double-quoted delimited-identifier>
    The symbol ";" was substituted for "end-of-file" to continue.

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForR
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    It is correct that there is an error since the example was for numerical values....
    this variant of request can be upgraded simply under search of string value...

    for example ...

    Code (SQL):



    DECLARE
      i INT;
      v varchar2(20) := 'Unsuccessful';
    BEGIN
        FOR z IN (
                SELECT
                       c.TABLE_NAME,
                       'select count(*)  from '||c.TABLE_NAME||' where '||c.COLUMN_NAME||'= :1' sql_txt
                       FROM user_tab_cols c WHERE c.column_name = 'CMPL_STAT_ID'              )
        loop
             EXECUTE immediate z.sql_txt
             INTO  i
             USING v;
             dbms_output.put_line(z.TABLE_NAME||' row count '||i);
        END loop;                    
    END;

     
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Must be a copy / paste error: there's a "WHERE" missing in the FOR IN Select clause.

     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    yes... it is a copying error...
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Donnie,

    Can you provide data for this here....

    SELECT * FROM all_tab_cols WHERE COLUMN_NAME = 'CMPL_STAT_ID'
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Donnie,

    Can you provide data for this here....

    SELECT * FROM all_tab_cols WHERE COLUMN_NAME = 'CMPL_STAT_ID'
     
  11. Donnie28

    Donnie28 Active Member

    Messages:
    43
    Likes Received:
    1
    Trophy Points:
    80
    when I run

    SELECT * FROM all_tab_cols WHERE COLUMN_NAME = 'CMPL_STAT_ID'

    I just get the attached error message

    Regards
    Donal
     

    Attached Files:

  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I do not get any error running that query from SQL*Plus; where are you running that query from?
     
  14. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Donnie,

    Are you connected to the database?

    It's may be connection issue. Is any other queries are running well?
     
  15. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Donnie,

    From the error message details, it looks like you're incorporating this SQL statement in a Java program (in Eclipse IDE) ?

    If yes, can you please include the code surrounding the SQL statement and the variables to which the output of the results are being assigned.

    Thanks.

    P.S: I suppose connection to the database has been established - as remarked by Jagadekara.