Home

How to find strings in BLOB/CLOB/LONG columns of Oracle database tables

Sun, 2012.03.04 - 12:45 — 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 the BLOB, CLOB and LONG columns of all tables of the current user and find the piece of data that you're looking for.

DECLARE
  -- Note: when searching in BLOBs, the search string is converted to bytes according to the database characterset.
  sSearchString VARCHAR2(4000) := 'search string';
  bCaseSensitive BOOLEAN := FALSE;
 
  nTmp NUMBER;
  nCID PLS_INTEGER;
  sSQL VARCHAR2(4000);
  sRowid VARCHAR2(255);
  sPiece VARCHAR2(4000);
  sPrevPiece VARCHAR2(4000);
  sJoinedPiece VARCHAR2(8000);
  rwPiece RAW(4000);
  nPieceLength INTEGER;
  nPieceIndex INTEGER;
  bIsLONG BOOLEAN;
  blLOB BLOB;
  clLOB CLOB;
  rwSearchString RAW(32000);
  sSearchStringLC VARCHAR2(4000);
  bFound BOOLEAN;
  bLobOpen BOOLEAN;
  nCnt NUMBER;
BEGIN
  dbms_output.enable(10000000);
  IF sSearchString IS NOT NULL THEN
    IF NOT bCaseSensitive THEN
      sSearchStringLC := LOWER(sSearchString);
    ELSE
      rwSearchString := utl_raw.cast_to_raw(c => sSearchString);
    END IF;
    FOR rec IN (
      SELECT utc.table_name, utc.column_name, utc.data_type
      FROM user_tables ut
      JOIN user_tab_columns utc ON ut.table_name = utc.table_name
      WHERE 1 = 1
      AND utc.DATA_TYPE IN ('BLOB', 'CLOB', 'LONG')
      ORDER BY 1, 2
    ) LOOP
      sSQL := '
        SELECT ROWID, "' || rec.column_name || '" FROM "' || rec.table_name || '" WHERE "' || rec.column_name || '" IS NOT NULL ORDER BY 1
        '
      ;
      nCID := dbms_sql.open_cursor();
      dbms_sql.parse(nCID, sSQL, dbms_sql.native);
      dbms_sql.define_column(nCID, 1, sRowid, 255);
      IF rec.data_type = 'LONG' THEN
        dbms_sql.define_column_long(nCID, 2);
      ELSIF rec.data_type = 'BLOB' THEN
        dbms_sql.define_column(nCID, 2, blLOB);
      ELSE
        dbms_sql.define_column(nCID, 2, clLOB);
      END IF;
      nTmp := dbms_sql.execute(nCID);
      nCnt := 0;
      LOOP
        nTmp := dbms_sql.fetch_rows(nCID);
        EXIT WHEN nTmp = 0;
        nCnt := nCnt + 1;
        dbms_sql.column_value(nCID, 1, sRowid);
        bFound := FALSE;
        bLobOpen := FALSE;
        BEGIN
          IF rec.data_type != 'LONG' THEN
            IF rec.data_type = 'BLOB' THEN
              dbms_sql.column_value(nCID, 2, blLOB);
              dbms_lob.open(lob_loc => blLOB, open_mode => dbms_lob.lob_readonly);
              bLobOpen := TRUE;
              IF bCaseSensitive AND dbms_lob.instr(lob_loc => blLOB, pattern => rwSearchString) > 0 THEN
                bFound := TRUE;
              END IF;
            ELSE
              dbms_sql.column_value(nCID, 2, clLOB);
              dbms_lob.open(lob_loc => clLOB, open_mode => dbms_lob.lob_readonly);
              bLobOpen := TRUE;
              IF bCaseSensitive AND dbms_lob.instr(lob_loc => clLOB, pattern => rwSearchString) > 0 THEN
                bFound := TRUE;
              END IF;
            END IF;
          END IF;
          IF rec.data_type = 'LONG' OR NOT bCaseSensitive THEN
            IF rec.data_type = 'LONG' THEN
              nPieceIndex := 0;
            ELSE
              nPieceIndex := 1;
            END IF;
            sPrevPiece := '';
            LOOP
              nPieceLength := 4000;
              nTmp := nPieceLength;
              IF rec.data_type = 'LONG' THEN
                dbms_sql.column_value_long(nCID, 2, nTmp, nPieceIndex, sPiece, nPieceLength);
              ELSE
                IF rec.data_type = 'BLOB' THEN
                  BEGIN
                    dbms_lob.read(lob_loc => blLOB, amount => nPieceLength, offset => nPieceIndex, buffer => rwPiece);
                  EXCEPTION WHEN NO_DATA_FOUND THEN
                    NULL;
                  END;
                  sPiece := utl_raw.cast_to_varchar2(r => rwPiece);
                ELSE
                  BEGIN
                    dbms_lob.read(lob_loc => clLOB, amount => nPieceLength, offset => nPieceIndex, buffer => sPiece);
                  EXCEPTION WHEN NO_DATA_FOUND THEN
                    NULL;
                  END;
                END IF;
              END IF;
              EXIT WHEN nPieceLength = 0;
              sJoinedPiece := sPrevPiece || sPiece;
              IF bCaseSensitive AND INSTR(sJoinedPiece, sSearchString) > 0 OR NOT bCaseSensitive AND INSTR(LOWER(sJoinedPiece), sSearchStringLC) > 0 THEN
                bFound := TRUE;
              END IF;
              sPrevPiece := sPiece;
              nPieceIndex := nPieceIndex + nPieceLength;
            END LOOP;
          END IF;
          IF bLobOpen THEN
            IF rec.data_type = 'BLOB' THEN
              dbms_lob.close(lob_loc => blLOB);
            ELSE
              dbms_lob.close(lob_loc => clLOB);
            END IF;
          END IF;
        EXCEPTION WHEN OTHERS THEN
          dbms_output.put_line('Exception while processing ' || rec.table_name || '.' || rec.column_name || ', rowid = ' || sRowid || ', SQLERRM = ' || SQLERRM);
          IF bLobOpen THEN
            IF rec.data_type = 'BLOB' THEN
              dbms_lob.close(lob_loc => blLOB);
            ELSE
              dbms_lob.close(lob_loc => clLOB);
            END IF;
          END IF;
          RAISE;
        END;
        IF bFound THEN
          dbms_output.put_line(rec.table_name || '.' || rec.column_name || ': ' || sRowid);
        END IF;
      END LOOP;
      dbms_sql.close_cursor(nCID);
    END LOOP;
  ELSE
    dbms_output.put_line('No search string was specified!');
  END IF;
END;

This procedure is for scanning the contents of the special BLOB, CLOB and LONG datatypes. For all other datatypes take a look at my other script.

P.S.: the above procedure does not search in LONG RAW columns since there's no way to do it in PL/SQL. You can convert the LONG RAW into a BLOB column (using the TO_LOB() function) and search in the BLOB instead.