(OFFTOPIC) Searching a Long Column using PLSQL in Oracle Database 10g

This post is just the continuation of the post to search long column in SQL ,the only difference being this is done for searching in PLSQL

Here i have used the concept of global temporary table with clause on commit delete rows,this concept of global temporary tables is very interesting,for all the information check it in http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2213

create global temporary table LONG_TEST_TEMP
(
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
ROW_ID VARCHAR2(30),
CLOB_DATA CLOB
)
on commit delete rows;

The below is a normal table where you can get the result of the search item

create table CHECK_VALUE_INFO
(
TABLE_NAME VARCHAR2(30),
COLUMN_NAME VARCHAR2(30),
ROW_ID VARCHAR2(30)
)

Compile the below program to search for the LONG column in table with and without the COMMIT to see one good difference between them

This program also supports a wild card search of a LONG column value within the schema with mainly the overhead on time and resources

create or replace procedure pr_check_value_long(p_value IN OUT VARCHAR2,
p_table IN VARCHAR2 DEFAULT NULL,
p_sql_col IN VARCHAR2 DEFAULT NULL) is
Cursor cr_usr is
select *
from user_tab_columns
where data_type = 'LONG'
and table_name = NVL(p_table,
table_name)
and column_name = NVL(p_sql_col,
column_name);
l_sql_text VARCHAR2(32000);
l_count NUMBER;
l_sql_col VARCHAR2(32000);
BEGIN
for rec in cr_usr
Loop
l_sql_col := '"' || rec.column_name || '"';
BEGIN
EXECUTE IMMEDIATE 'insert into long_test_temp select ' || '''' ||
rec.table_name || '''' || ',' || '''' ||
l_sql_col || '''' || ',' || 'rowid' || ',' ||
'to_lob(' || l_sql_col || ')' || ' from ' ||
rec.table_name;
l_sql_text := ' insert into CHECK_VALUE_INFO SELECT TABLE_NAME,COLUMN_NAME,ROW_ID
FROM long_test_temp WHERE clob_data LIKE ' || '''' || '%' ||
p_value || '%' || '''';
EXECUTE IMMEDIATE l_sql_text;
l_sql_text := NULL;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;
end loop;
p_value := NULL;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

Below is a example of doing the search for the column LONG_VALUE of datatype LONG and table name TEST_LONG_VALUE

DECLARE
P_VALUE VARCHAR2(4000):='131';
BEGIN
pr_check_value_lonG(p_value,'TEST_LONG_VALUE','LONG_VALUE');
END;

Also an example of how you can check the result

SELECT a.*  FROM test_long_value a WHERE a.ROWID IN
(SELECT ROW_ID FROM CHECK_VALUE_INFO b where b.table_name='TEST_LONG_VALUE' and b.column_name='LONG_VALUE')

Happy times searching :)