Scripts to find a string in a FAST FORMULA

1. Compile the following pkg in development instance.

create or replace package arj_pkg as

function long_to_varchar2 (p_tab IN VARCHAR2

,p_long_col IN VARCHAR2

,p_row_id in rowid)

return CLOB;

end arj_pkg;

/

create or replace package body arj_pkg as

function long_to_varchar2 (p_tab IN VARCHAR2

,p_long_col IN VARCHAR2

,p_row_id in rowid)

return CLOB is

-- v_size = number of characters to retrieve

v_cur integer;

v_rc integer;

v_size integer := 32000;

v_buffer varchar2(32000);

v_buf_len integer;

begin

v_cur := dbms_sql.open_cursor;

dbms_sql.parse(v_cur,'SELECT '||p_long_col||' FROM '||p_tab||' WHERE ROWID = :row_id',dbms_sql.native);

dbms_sql.bind_variable(v_cur, ':row_id', p_row_id);

dbms_sql.define_column_long(v_cur,1);

v_rc := dbms_sql.execute_and_fetch(v_cur);

dbms_sql.column_value_long(v_cur,1,v_size,0,v_buffer,v_buf_len);

dbms_sql.close_cursor(v_cur);

return substr(v_buffer,1,v_buf_len);

end long_to_varchar2;

end arj_pkg;

/
2.Run the following script

declare

--

v_search_string VARCHAR2(2000) := 'SAMPLE_STRING';

v_fmla_name FF_FORMULAS_F.formula_name%TYPE;

v_fmla_text CLOB;

--

cursor get_fmla (c_fmla_name IN varchar2)

is

SELECT

fff.rowid

,fff.formula_name

, TO_CHAR(fff.effective_start_date,'DD/MM/YYYY') effective_date

FROM ff_formulas_f fff

WHERE fff.formula_name LIKE c_fmla_name;

--

begin

FOR rec IN get_fmla ('%') LOOP

BEGIN

v_fmla_text := ccsl_pkg.long_to_varchar2('FF_FORMULAS_F','FORMULA_TEXT',rec.rowid);

IF UPPER(v_fmla_text) LIKE '%'||UPPER(v_search_string)||'%' THEN

DBMS_OUTPUT.PUT_LINE ('Formula '||rec.formula_name||

', eff date '|| rec.effective_date||

' contains the string '||v_search_string);

END IF;

-- EXCEPTION

END;

END LOOP;

--

--exception

end;