How do we Import Oracle Functions as a Table into Frame Work Manager

[vikdev]

Hi

I have the following Function in my Oracle Data Base. How can I Import this Function in FM as Table. How do we pass the values dynamically with out hardcoding. Can you please let me know the process

CREATE OR REPLACE FUNCTION CRYSTAL."F_GET_ATTR"
    (pidm number,term_code varchar2)
return varchar2
as
attr     varchar2(4) := null;
cursor get_attr is
    select sgrsatt_atts_code
      from sgrsatt
     where sgrsatt_pidm = pidm
       and sgrsatt_term_code_eff = term_code;
begin
open get_attr;
fetch get_attr into attr;
close get_attr;
return attr;
end;
/

[blom0344]

Looks to me it returns a dataset. It would be much easier to simply compile the SQL part into a view and then import the view..

[cognostechie]

Have you tried creating a calculated column in a query subject and then bringing in the function?

[Penny]

I found your post interesting because I can tell you must be using Banner.

What we do (in Cognos 7) is make use of some built-in Banner functions that return a character string that contains several other fields delimited with curly brackets '{}'.  The function is invoked and the return value is then used in calculated fields on a report to split the fields out.   We have also written a few of our own that operate similarly.    Look for Banner functions that start with 'f_concat_', and for the function called f_split_fields that accepts a delimiter and position as parameters and returns the extracted field.

Hope this helps.

I also am working with Cognos 10 and Banner and so I am interested in your modelling techniques and whether or not you are using dimensional reporting or relational.  Would you be interested in sharing any of your insights?