Here is another test script that I use and thought might be useful to someone. I was having a hard time searching for text in long columns, so this does that.
To set it up, copy the code and put it in a test file. I used "search_long_cols.tst".
Just put in the name of a table containing a long column in the table_name variable, put in the text you are looking for in the search_text variable and run it. It will tell if the text was found or not in the found variable. If it is found, click the "..." on the result_set variable and it will show all columns in the table that includes the text.
Here is the code:
To set it up, copy the code and put it in a test file. I used "search_long_cols.tst".
Just put in the name of a table containing a long column in the table_name variable, put in the text you are looking for in the search_text variable and run it. It will tell if the text was found or not in the found variable. If it is found, click the "..." on the result_set variable and it will show all columns in the table that includes the text.
Here is the code:
Code:
PL/SQL Developer Test script 3.0
116
-- Created on 11/19/2004 by Mike Matney
-- $Header$
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- FILE NAME: search_long_cols.tst
--
-- PURPOSE: Searches through long columns in a table and returns all columns
-- in the table that contains the text searched for.
--
-- How to use:
-- 1. Put a table name that has a long column you want to search for in the
-- table_name variable.
-- 2. Put the text you are looking for in the long column in the search_text
-- variable.
-- 3. Execute this test script. The "found" variable will let you know if
-- the search_text is found in the table or not.
-- 4. Click on the "..." in the Value column of the result_set cursor.
-- 5. A SQL Grid will pop up with all of the rows that contain that text
-- in the long column.
--
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DECLARE
-- Local variables
v_table_name VARCHAR2(255) := :table_name;
v_search_text VARCHAR2(255) := :search_text;
v_cursor INTEGER;
v_query_text VARCHAR2(32000);
v_desc_tab dbms_sql.desc_tab;
v_col_count INTEGER;
v_long_col_name VARCHAR2(200);
v_rownumber NUMBER;
v_long_val VARCHAR2(32000);
v_long_len NUMBER;
v_buflen NUMBER := 3200;
v_curpos NUMBER := 0;
v_rowid VARCHAR2(100);
v_first BOOLEAN := TRUE;
v_cursor_text VARCHAR2(32000);
v_long_col_exists BOOLEAN := FALSE;
BEGIN
-- Create a select statement to get the column names. We want the name of the long column.
v_query_text := 'SELECT * FROM ' || v_table_name;
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, v_query_text, dbms_sql.native);
dbms_sql.describe_columns(v_cursor, v_col_count, v_desc_tab);
dbms_sql.close_cursor(v_cursor);
-- get long column name;
FOR i IN 1 .. v_col_count
LOOP
IF v_desc_tab(i).col_type = 8 -- long data type
THEN
v_long_col_name := v_desc_tab(i).col_name;
v_long_col_exists := TRUE;
END IF;
END LOOP;
-- Search
IF v_long_col_exists
THEN
v_query_text := 'SELECT rowid, a.' || v_long_col_name || ' FROM ' || v_table_name || ' a';
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(c => v_cursor, STATEMENT => v_query_text, language_flag => dbms_sql.native);
dbms_sql.define_column_rowid(c => v_cursor, position => 1, column => 'LONG');
dbms_sql.define_column_long(c => v_cursor, position => 2);
v_rownumber := dbms_sql.EXECUTE(c => v_cursor);
v_cursor_text := 'SELECT * FROM ' || v_table_name || ' WHERE ROWID IN (';
LOOP
IF (dbms_sql.fetch_rows(c => v_cursor) > 0)
THEN
v_curpos := 0;
LOOP
dbms_sql.column_value_long(c => v_cursor
,position => 2
,length => v_buflen
,offset => v_curpos
,VALUE => v_long_val
,value_length => v_long_len);
IF v_long_len <= 100
THEN
v_curpos := v_curpos + v_long_len;
ELSE
v_curpos := v_curpos + v_long_len - 100; --100 is the overlap to search the borders of the pieces.
END IF;
IF upper(v_long_val) LIKE upper('%' || v_search_text || '%')
THEN
dbms_sql.column_value(c => v_cursor, position => 1, VALUE => v_rowid);
IF v_first
THEN
v_cursor_text := v_cursor_text || '''' || v_rowid || '''';
v_first := FALSE;
ELSE
v_cursor_text := v_cursor_text || ', ' || '''' || v_rowid || '''';
END IF;
END IF;
EXIT WHEN v_long_len <= 0;
END LOOP;
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.close_cursor(v_cursor);
v_cursor_text := v_cursor_text || ')';
:cursor_text := v_cursor_text;
IF v_cursor_text = 'SELECT * FROM ' || v_table_name || ' WHERE ROWID IN ()'
THEN
:FOUND := v_search_text || ' is NOT found in table ' || v_table_name;
ELSE
:FOUND := upper(v_search_text) || ' is found in column ' || v_long_col_name;
OPEN :result_set FOR v_cursor_text;
END IF;
ELSE
:FOUND := 'There is no long column in table ' || v_table_name;
END IF;
END;
5
table_name
1
fnd_flex_validation_tables
5
search_text
1
per_people_f
5
cursor_text
0
5
found
0
5
result_set
1
<Cursor>
116
0
.
