Hi,

I am looking for qry to find the list of fast formulas having a certain text in the formula code.

Ex:- List of formulas text contains an string "GB_RATES_HISTORY"

Can anyone help me to get the qry for this.

thanks in advance

KP/..

The problem you have of course is that the formula_text is a LONG column, which does not support any functions applied to it such as LIKE...

One suggestion might be to extract the text from all the formulas into, say, a spreadsheet, then do a string search within the spreadsheet.

Alternatively, you could possibly create your own table like FF_FORMULAS_F except the formula_text column should be CLOB. CLOB columns can have things such as LIKE applied to them in WHERE CLAUSES - then just copy the data from FF_FORMULAS_F into your version, and do the query there.

Alternatively, I seem to remember having in my possession a function that converts LONG to VARCHAR, I need to try and resurrect it from wherever I have hidden it.

Providing your ARRAYSIZE and MAXDATA settings are correct you should be able to simply select the contents of a LONG straight into a CLOB without any issues.

You can then manipulate the CLOB with any of the string functions as if it were a VARCHAR2

Hi Clive,

thanks for advice, I have got it now.
Here is the steps -

Step1:

create table xx_test_formulas(formula_name varchar2(1000),formula_text CLOB)

Step2:

insert into xx_test_formulas
select formula_name,to_lob(formula_text)
from ff_formulas_f
where sysdate between effective_start_date and effective_end_date;

Step3:

select *
from xx_test_formulas
where formula_text like '%rate_history%'


cheers
KP/..

Thanks a lot all of you..
I was looking for a solution but was caught up with something else..

Cool!

OK I took the bait on my third alternative, which was to rustle up the function - I've done some rudimentary testing (the only kind I know, as anyone who knows me will tell you!) on my R12 instance, and I picked up 2 results:

Formula UK_PRORATION_GRADE_RATE, eff date 01/01/1901 contains the string GB_RATES_HISTORY
Formula UK_PRORATION_SPINAL_POINT, eff date 01/01/1901 contains the string GB_RATES_HISTORY

The scripts that I used are attached - one is the package header/body, the other is the script that makes use of the package function.

Usual disclaimers apply, unless you want to pay me to support it!

thanks for Script