A Post About Me

Hi All,

I just writing a small post to say I recently put live my own personal website so visit me at mayoagardolubo.com. I'm currently doing some freelance web design and you can contact me through my site.

I also designed a website for a great writer Sandra Agard which can be seen at tellingtalesnow.com.

Last thing I'm also selling some stock photography while I test out an app called foap. If you want to check it out visit me here foap.com/community/profiles/mayo.agard.olubo

O.k. plug over next post will be back to programming.

Thanks for reading

Replace varchar in CLOB with CLOB

I was trying to do a replace within a CLOB. The problem I ran into was that either the original CLOB, the replacement CLOB or the final result would exceed the 32k limit for oracle String functions like REPLACE. Here is some code that allows you to replace a part of a CLOB with another CLOB no matter the size.
It uses dbms_lob.copy

FUNCTION replace_clob( p_lob IN CLOB,
                       p_what IN VARCHAR2,
                       p_with IN CLOB )
RETURN CLOB
AS
    n NUMBER;
    l_result CLOB := p_lob;
BEGIN  
    n := dbms_lob.INSTR( p_lob, p_what);  
    IF ( NVL(n,0) > 0 )
    THEN
            dbms_lob.createtemporary(l_result, FALSE, dbms_lob.CALL);
            dbms_lob.copy(l_result, p_lob, n - 1, 1, 1);
            dbms_lob.copy(l_result,
                          p_with,
                          dbms_lob.getlength(p_with) ,
                          dbms_lob.getlength(l_result) + 1,
                          1 );
            dbms_lob.copy(l_result,
                          p_lob,
                          dbms_lob.getlength(p_lob) - (n + LENGTH(p_what)) + 1 ,
                          dbms_lob.getlength(l_result) + 1,
                          n + LENGTH(p_what) );
    END IF;
       
    IF NVL(dbms_lob.INSTR(l_result, p_what), 0) > 0 THEN
        RETURN replace_clob(l_result, p_what, p_with);
    END IF;
   
    RETURN l_result;
   
END;

Hope this helps as I couldn't find this particular solution anywhere else

Running a PL/SQL Process from Column Link

I recently had the problem where I needed to run a pl/sql process after clicking a column link. To run this process I needed a value from the table. I then needed to navigate to a new page and set an item with a value calculated in the process. Below is my solution. If you have a simpler solution please leave me a comment.

First create an intermediate page where you will run your process.
- create a blank page (for this example we will call this process page)
- create a html region on this page
- create a hidden item to receive the value from the table.

- go to your page with the report (we will call this report page)
- create a column link to process page that passes the value from the report to your hidden item on process  page

- go back to process page
- create a Before Header process
- add code to calculate result, send the result value to an item on the target page (we will use 20 as target page and P20_RESULT as target item). Then redirect to target page

example code:

DECLARE
v_result number; --calculated value will go here
BEGIN
--Code goes here to calculate result

--set the value on the target page
APEX_UTIL.SET_SESSION_STATE('P20_RESULT',v_result);

-- redirect to target page
htp.init;
owa_util.redirect_url('f?p=&APP_ID.:20:&SESSION.::NO:::');
apex_application.stop_apex_engine;

END;

Displaying Large CLOBS in Oracle APEX

I had a problem retrieving a clob from the database and displaying it in an apex text area.  The reason for this is it is a very large clob and Apex areas have a 32k character (byte) limit. This is because PL/SQL treats oracle apex page items as varchars not clobs and varchars have a maximum size. Anything over that size will not be displayed properly.

So a PL/SQL process such as

select clob_field
into :P20_CLOB_ITEM
from table
where id = :P20_CLOB_ID

won't work for very large clobs. It won't throw an error it just won't display

To get round this problem you have to use apex.ajax.clob. This uses collections to process large clobs and write them to the text area. Firstly you need to select the CLOB into the collection using the following procedure. Run this on the same page that has the id value for the row that you are getting the clob from

declare
l_clob clob:= empty_clob();

begin

if apex_collection.collection_exists(p_collection_name=>'CLOB_CONTENT') then
apex_collection.delete_collection(p_collection_name=>'CLOB_CONTENT');
end if;

apex_collection.create_or_truncate_collection(p_collection_name=>'CLOB_CONTENT');
dbms_lob.createtemporary( l_clob, false, dbms_lob.SESSION );

select clob_field
into l_clob
from table
where id = :P19_CLOB_ID;

apex_collection.add_member(p_collection_name => 'CLOB_CONTENT',p_clob001 => l_clob);
end;

Then on the page with the text area put the following javascript code in the HTML Header of the page

<script type="text/javascript">
function clob_get(){
        var clob_ob = new apex.ajax.clob(
            function(){
                var rs = p.readyState
                if(rs == 1||rs == 2||rs == 3){
                    $x_Show('AjaxLoading');
                }else if(rs == 4){
                    $s('P20_CLOB_ITEM',p.responseText);
                    $x_Hide('AjaxLoading');
                }else{return false;}
            }
        );
        clob_ob._get();
    }
</script>

p.responseText retrieves the clob value from the collection CLOB_CONTENT

then call the javascript. in my case I place this in the html body of the page

onload = "javascript:clob_get();"

Hope this helps as it caused me quite a few problems.

for more on how to use this check out a blog post that was very helpful to me in solving this problem
http://www.advnettech.net/blog/?p=7

Check All check boxes in oracle apex report

If you have a report that contains check boxes (to do this see the APEX_ITEM API) you may want add a function to check or uncheck all of the boxes at once.

To do this in a standard report change the name of the column in the report attributes to

"<input type="Checkbox" onclick="$f_CheckFirstColumn(this)"><span>Column name here</span>"

This creates a check box in the title that when checked will check all of the boxes. However if you have an interactive report this solution is not suitable as clicking on this top check box will bring up the column sorting and filtering options.

In this case the solution can be found in an Apex Njnja's white paper on page 17

The solution from this paper is quoted here


Select/unselect all

Another functionality that is a must, especially when working with many rows, is a select/unselect all.
This can be done by creating an item situated in the IR region, located above the region. Create the item
as display as text,without any label. Then, in the Pre Element Text region, paste this code:

"<input type="button" name="CheckAll" value="[+]"
onClick="checkAll(f01)">
<input type="button" name="CheckAll" value="[-]"
onClick="uncheckAll(f01)">"

This will add the two buttons on your screen:

Now, add the two javascript functions inside you page HTML  Header :

"<SCRIPT LANGUAGE="JavaScript">
<!-- Begin
function checkAll(field)
{
for (i = 0; i < field.length; i++)
{field[i].checked = true;}
}
function uncheckAll(field)
{
for (i = 0; i < field.length; i++)
{field[i].checked = false ;}
}
</script>"

These functions will be called when clicking the two buttons created before and will check/uncheck all
of the checkboxes from the IR.

APEX_ITEM not displaying properly in oracle APEX 4.1

Hi all,

I just experienced this problem and thought I'd share it in case anybody else runs into it. The problem is that after upgrading to apex 4.1 my reports that use the APEX_ITEM API were no longer displaying properly. They were displaying like as the actual html code

e.g. "<input type="text" name ="f02" size="10", maxlength ="10", value = "Test" />"

I don't know why the upgrade changed these columns but if this has happened to you here's how to fix it.
1. go to the region settings
2. go to the report attributes tab
3. click on the little pencil icon which indicates column attributes (for the column using the apex_item api)
4. change "Display As" under column attributes to "Standard Report Column"

Not a very difficult problem but if like me you were unaware that this attribute would be changed by the upgrade this will hopefully be helpful to you.

Some useful links for creating cron expressions

If you ever have to create cron expressions (I do while working with the Quartz Scheduler) these links may be helpful to you.

Firstly a basic cron expression tutorial for quartz (what they are, the basics and some examples)
http://www.quartz-scheduler.org/documentation/quartz-1.x/tutorials/crontrigger

some more examples of working with the cron format
http://www.nncron.ru/help/EN/working/cron-format.htm

And finally a useful website which can generate basic cron expressions for you and can be used to check that cron expression you have written do what you think they do.
http://www.cronmaker.com/

Hope this helps