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.
Recent comments
2 years 23 weeks ago
3 years 45 weeks ago
3 years 45 weeks ago
3 years 47 weeks ago
3 years 48 weeks ago
4 years 3 weeks ago
4 years 3 weeks ago
4 years 3 weeks ago
4 years 3 weeks ago
4 years 3 weeks ago