Home

How to find arbitrary data in an Oracle database

Sun, 2012.03.04 - 10:38 — müzso
Sometimes you've to reverse engineer a database (or an app using a database) and all you've is some piece of data that is used in whatever function(ality) you're looking into. Eg. you see some message/text on a screen of the app and you've to figure out where and how it is used. For this you've to first find the table and the row where that data is stored. Here comes this little PL/SQL procudure handy.

It'll go through all tables and columns of the current user and find the piece of data that you're looking for. I've added detailed comments describing all the parameters you can use to customize the search.

DECLARE
  -- bHtpOutput: generate output via htp.p() or dbms_output.put_line()
  -- (htp.p() is somewhat faster, but dbms_output.put_line() is more compatible
  --  with various IDEs)
  bHtpOutput BOOLEAN := FALSE;
  -- bDebug: print various debug output like the SQL statements being executed
  bDebug BOOLEAN := FALSE;
  -- All filter expressions should start with an " AND" (if they're not NULL).
  -- sTableFilter: "WHERE" condition for filtering the USER_OBJECTS view
  --               (alias: "uo") and/or the USER_TABLES view (alias: "ut").
  sTableFilter VARCHAR2(4000) := NULL;
  -- sColumnFilter: "WHERE" condition for filtering the USER_TAB_COLUMNS view.
  sColumnFilter VARCHAR2(4000) := ' AND Data_Type LIKE ''%CHAR%''';
  -- sDataFilter: "WHERE" condition for filtering data in a column of a table.
  sDataFilter VARCHAR2(4000) := ' AND {COL} = ''search string''';

  aTabNames dbms_sql.Varchar2_Table;
  aColNames dbms_sql.Varchar2_Table;
  bTableHeaderWritten BOOLEAN;
  sSQL VARCHAR2(32767);
  sSQLFirst VARCHAR2(1000);
  sSelect VARCHAR2(4000);
  bOverflow BOOLEAN;
  sPrevRowid VARCHAR2(4000);
  aFetchedRowids dbms_sql.Varchar2_Table;
  aFetchedColNames dbms_sql.Varchar2_Table;
  aFetchedColValues dbms_sql.Varchar2_Table;
  nIdxTab NUMBER;
  nIdxCol NUMBER;
  nIdxRow NUMBER;
  nIdxRow2 NUMBER;
  nIdxRow3 NUMBER;
  nCur INTEGER;
  nCurCol INTEGER;
  nTmp NUMBER;
  PROCEDURE mprint(p_sInput IN VARCHAR2) IS
  BEGIN
    IF bHtpOutput THEN
      htp.p(p_sInput);
    ELSE
      dbms_output.put_line(SUBSTR(p_sInput, 1, 255));
    END IF;
  END mprint;
BEGIN
  IF bHtpOutput THEN
    mprint('<PRE>');
  ELSE
    dbms_output.enable(10000000);
  END IF;
 
  nCur := dbms_sql.open_cursor;
  nCurCol := dbms_sql.open_cursor;

  -- We select from user_objects, because "user_tables"
  -- does not contain all tables.
  sSQL := '
    SELECT
      uo.Object_Name
    FROM
      User_Objects uo,
      User_Tables ut
    WHERE 1 = 1
      AND uo.Object_Type = ''TABLE''
      AND uo.Object_Name = ut.Table_Name(+)
  ';
  IF sTableFilter IS NOT NULL THEN
    sSQL := sSQL || ' ' || sTableFilter;
  END IF;
  sSQL := sSQL || ' ORDER BY uo.Object_Name';
  dbms_sql.parse(
    c => nCur,
    statement => sSQL,
    language_flag => dbms_sql.native
  );
  nIdxRow := 1;
  dbms_sql.define_array(
    c => nCur,
    position => 1,
    c_tab => aTabNames,
    cnt => 100,
    lower_bound => nIdxRow
  );
  nTmp := dbms_sql.execute(c => nCur);
  LOOP
    nTmp := dbms_sql.fetch_rows(c => nCur);
    dbms_sql.column_value(
      c => nCur,
      position => 1,
      c_tab => aTabNames
    );
    EXIT WHEN nTmp < 100;
  END LOOP;
 
  sSQL := 'SELECT Column_Name FROM User_Tab_Columns WHERE Table_Name = :tablename';
  IF sColumnFilter IS NOT NULL THEN
    sSQL := sSQL || ' ' || sColumnFilter;
  END IF;
  sSQL := sSQL || ' ORDER BY Column_Name';
 
  dbms_sql.parse(
    c => nCurCol,
    statement => sSQL,
    language_flag => dbms_sql.native
  );

  sSQLFirst := 'SELECT '''', '''', '''' FROM DUAL WHERE 1 = 0';
 
  nIdxTab := aTabNames.FIRST;
  WHILE nIdxTab IS NOT NULL LOOP
    bTableHeaderWritten := FALSE;
   
    aColNames.DELETE();
    dbms_sql.bind_variable(
      c => nCurCol,
      name => ':tablename',
      value => aTabNames(nIdxTab)
    );
    nIdxRow := 1;
    dbms_sql.define_array(
      c => nCurCol,
      position => 1,
      c_tab => aColNames,
      cnt => 100,
      lower_bound => nIdxRow
    );
    nTmp := dbms_sql.execute(c => nCurCol);
    LOOP
      nTmp := dbms_sql.fetch_rows(c => nCurCol);
      dbms_sql.column_value(
        c => nCurCol,
        position => 1,
        c_tab => aColNames
      );
      EXIT WHEN nTmp < 100;
    END LOOP;
   
    IF aColNames.COUNT > 0 AND sDataFilter IS NOT NULL THEN
      sSelect := ' UNION ALL SELECT ROWIDTOCHAR(ROWID), ''{COL}'', SUBSTR("{COL}", 1, 200) FROM "' || aTabNames(nIdxTab) || '"'
        || ' WHERE 1 = 1 ' || sDataFilter
      ;
      sSQL := sSQLFirst;
      nIdxCol := aColNames.FIRST;
      WHILE nIdxCol IS NOT NULL LOOP
        bOverflow := FALSE;
        BEGIN
          sSQL := sSQL || REPLACE(sSelect, '{COL}', aColNames(nIdxCol));
          nIdxCol := aColNames.NEXT(nIdxCol);
        EXCEPTION WHEN VALUE_ERROR THEN
          bOverflow := TRUE;
        END;
       
        IF bOverflow OR nIdxCol IS NULL THEN
          BEGIN
            sSQL := sSQL || ' ORDER BY 1, 2';
          EXCEPTION WHEN VALUE_ERROR THEN
            NULL;
          END;
           
          aFetchedRowids.DELETE();
          aFetchedColNames.DELETE();
          aFetchedColValues.DELETE();
           
          dbms_sql.parse(
            c => nCur,
            statement => sSQL,
            language_flag => dbms_sql.native
          );
           
          nIdxRow := 1;
          dbms_sql.define_array(
            c => nCur,
            position => 1,
            c_tab => aFetchedRowids,
            cnt => 100,
            lower_bound => nIdxRow
          );
          nIdxRow2 := 1;
          dbms_sql.define_array(
            c => nCur,
            position => 2,
            c_tab => aFetchedColNames,
            cnt => 100,
            lower_bound => nIdxRow2
          );
          nIdxRow3 := 1;
          dbms_sql.define_array(
            c => nCur,
            position => 3,
            c_tab => aFetchedColValues,
            cnt => 100,
            lower_bound => nIdxRow3
          );
          nTmp := dbms_sql.execute(c => nCur);
          LOOP
            nTmp := dbms_sql.fetch_rows(c => nCur);
            dbms_sql.column_value(
              c => nCur,
              position => 1,
              c_tab => aFetchedRowids
            );
            dbms_sql.column_value(
              c => nCur,
              position => 2,
              c_tab => aFetchedColNames
            );
            dbms_sql.column_value(
              c => nCur,
              position => 3,
              c_tab => aFetchedColValues
            );
            EXIT WHEN nTmp < 100;
          END LOOP;

          IF bDebug THEN
            mprint(
              'searching in "' || aTabNames(nIdxTab)
              || '; found ' || aFetchedRowids.COUNT || ' record(s).'
            );
            mprint(sSQL);
          END IF;

          nIdxRow := aFetchedRowids.FIRST;
          WHILE nIdxRow IS NOT NULL LOOP
            IF NOT bTableHeaderWritten THEN
              mprint('');
              mprint('TABLE: ' || aTabNames(nIdxTab));
              bTableHeaderWritten := TRUE;
            END IF;
             
            IF
              XOR(aFetchedRowids(nIdxRow) IS NULL, sPrevRowid IS NULL)
              OR aFetchedRowids(nIdxRow) != sPrevRowid
            THEN
              mprint('');
              mprint('  ROWID: ' || aFetchedRowids(nIdxRow));
              sPrevRowid := aFetchedRowids(nIdxRow);
            END IF;

            mprint('  ' || aFetchedColNames(nIdxRow) || ': ' || aFetchedColValues(nIdxRow));
             
            nIdxRow := aFetchedRowids.NEXT(nIdxRow);
          END LOOP;
          IF bOverflow THEN
            sSQL := sSQLFirst;
          END IF;
        END IF;
      END LOOP;
    END IF;

    nIdxTab := aTabNames.NEXT(nIdxTab);
  END LOOP;
 
  dbms_sql.close_cursor(c => nCurCol);
  dbms_sql.close_cursor(c => nCur);
  IF bHtpOutput THEN
    mprint('</PRE>');
  END IF;
END;

The only data you won't be able to find with this procedure are the ones stored in LONG and LOB (CLOB/BLOB) columns since these require special care, but if you're interested, I've written a separate procedure for them.