As you might know, reports are a crucial part of extracting the data from Oracle Cloud. So you have to create lots and lots of reports. Then there may come a situation where to reduce the complexity of the report, you might want to create a Custom PL- SQL Function and call it in the SQL of the report. in this post we will create a small Custom PL- SQL function just to give the syntax. Of course if mixing SQL and PL-SQL can cause some performance issues. So better watch out for that.
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
So let’s cut to the chase and share the query. Here we have created a very simple custom function named custom_func which takes an input parameter of VARCHAR2 type and just returns the first 4 character of the input parameter. By no mean this is something that can be done only in PL- SQL function. But then, primary motive of this post is to give an idea how to create the PL SQL function in the query and not give the details of PL – SQL nitty gritty.
WITH FUNCTION custom_func (p_in_v_ip in VARCHAR2) RETURN VARCHAR2
as
l_v_loc_var VARCHAR2(1000) ;
BEGIN
SELECT SUBSTR(p_in_v_ip,1,4)
INTO l_v_loc_var
FROM DUAL;
RETURN l_v_loc_var;
EXCEPTION
WHEN OTHERS
THEN RETURN ‘Falure’;
END;
SELECT custom_func(‘TestData’) Func_Output
FROM dual
There is another way to write a PL- SQL procedure in Fusion Report and that is by creating the dataset of PL- SQL type. In this particular type of dataset, output comes in a reference cursor and is slightly more complicated to define and use it in the report. And hence this is not my preferred way of writing the PL SQL procedure of the functions in Fusion Report. And that’s the reason I use the method mentioned above to use PL SQL capabilities in my Business Intelligence report development in Oracle Fusion.
Related Posts
Developing A BI Report In Oracle Fusion
RTF Date Formatting With Example
RTF Number Formatting With Examples
RTF- Looping Finite Number Of Times
Fusion Report Job Parameters
Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.
Reference:
support.oracle.com
Check out the Amazon Deals of the day based on your interests..
