Forum Stats

  • 3,757,058 Users
  • 2,251,192 Discussions


how to find a character string through the whole schema?

Dear DBA Frank
Dear DBA Frank Member Posts: 166 Bronze Badge
edited Feb 28, 2017 7:57AM in General Database Discussions

Hello all,

That Oracle-based application is giving me an error.  And I see that the error is caused by a typo somewhere in the value of some column of some table -- but I don't know the data model of that application, so I don't know where to find the typo.  It occured to me that I don't know how to find where a specific character string is stored in the schema of that application.  Is there a way to search for a character string in all the tables of a schema?


Best Answer


  • JohnWatson2
    JohnWatson2 Member Posts: 4,315 Silver Crown
    edited Feb 28, 2017 4:35AM Accepted Answer

    Here is some code that will find all occurrences of a string in your current schema,

    OraFAQ Forum: SQL & PL/SQL » How do I find a value anywhere in a Oracle schema ?

    there are many other examples in the topic. Note that this particular example will not search LOBs. Do you need to do that?

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited Feb 28, 2017 6:15AM

    Hi Frank, I think posting the error might help with identifying which table(s) is involved in the error!- instead of searching your whole schema for a specific string.

  • Dear DBA Frank
    Dear DBA Frank Member Posts: 166 Bronze Badge
    edited Feb 28, 2017 7:57AM

    Thank you John.  The piece of PL/SQL code in your link does the trick.  I modified it slightly so that it will search for any column that contains my character string, instead of any column the value of which equals my character string:

    variable COLUMN_VALUE VARCHAR2(4000)

    exec :COLUMN_VALUE := 'mycasesensitivecharacterstring'

    WITH t1 AS (  SELECT  /*+ materialize */  table_name, column_name  FROM  user_tab_columns  WHERE data_type IN ('CHAR','VARCHAR2')  ),

        t2 AS (    SELECT  table_name,  column_name, xmlcast( xmlquery( '/ROWSET/ROW'

        passing dbms_xmlgen.getxmltype(    'select count(*) from "' || table_name || '" where "' || column_name || '" like ''' ||'%'|| replace(:column_value,q'[']',q'['']')||'%'||  ''' and rownum = 1'  )

                                        RETURNING CONTENT  )  AS NUMBER  ) flag    FROM  t1          )

    select  table_name,  column_name,  :column_value column_value

      from  t2

      WHERE flag = 1

      order by table_name, COLUMN_NAME ;

    And no, I don't need to search throught LOBs.  If, however, you have the code to do that, I think it would be nice to share it for the benefit of other community users.

This discussion has been closed.