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;
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;
BEGIN
FOR rec IN get_fmla ('%')
LOOP
BEGIN
v_fmla_text :=
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;
Fast Formula - Database Items - Logic/Query:
select * from ff_database_items where user_name like '%SAMPLE%'; --XXX
select * from ff_user_entities where user_entity_id = XXX;--YYY
select * from ff_routes where route_id = YYY; -- ZZZ
select * from ff_route_parameters where route_id = ZZZ--NNN
select * from ff_route_parameter_values where ROUTE_PARAMETER_ID = '' and user_entity_id = '';
