Setting and retrieving CLOB values in Apex

February 7, 2014 2 comments

Imagine you have a textarea in your app where you want your users to be able to type in large quantities of text, click a button to store this in the database and then later retrieve this large quantity of text and redisplay it in that textarea.

You can’t do this as you would with other page items (i.e. just submit the page and have the values stored in session state). Any value over 32kb in size won’t work. To get round this there is a technique which involves using an Apex Collection called CLOB_CONTENT. Essentially, you just need the two Javascript functions below to get this working. They are both asynchronous and can be passed a Javascript callback function to be executed once the CLOB value has been set or retrieved.

The Javascript function to save large value to the CLOB_CONTENT Apex Collection


function setApexCollectionClob (pBigValue, callback) {

var apexAjaxObj = new apex.ajax.obj (

function() {

var rs = p.readyState;

if (rs == 4) {

callback();

} else {

return false;

};

}

);

apexAjaxObj._set(pBigValue);

}

Example usage:

setApexCollectionClob ('Some large text value...', function(){alert('Data saved to Apex Collection!')})

The Javascript function to retrieve a large value from the CLOB_CONTENT Apex Collection


function getApexCollectionClob(callback) {

var apexAjaxObj = new apex.ajax.clob (

function() {

var rs = p.readyState;

if(rs==4){

callback(p.responseText);

}else{

return false;

}

}

);

apexAjaxObj._get();
}

Example usage:

getApexCollectionClob (function(pReturnedClobValue){ $('#P1_TEXTAREA').val(pReturnedClobValue) })

Retrieving the set value via PL/SQL in, for example, an Application Process

DECLARE
L_CLOB CLOB
BEGIN
SELECT CLOB001
INTO l_clob
FROM apex_collection
WHERE collection_name = 'CLOB_CONTENT'

INSERT INTO my_table (id, myclob) values (123,l_clob);

HTP.p('SUCCESS');

END;

Categories: Ajax, ApEx

Passing more than 10 values with apex.server.process

February 7, 2014 Leave a comment

You may be familiar with the apex.server.process function exposed by Apex’s Javascript API. It allows you to asynchronously interact with Apex Application Processes.

A simple example would be.

Apex Application Process


HTP.p('You passed "'||APEX_APPLICATION.g_x01 ||'" as the value for x01. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x02 ||'" as the value for x02. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x03 ||'" as the value for x03. ');

Javascript

apex.server.process ( 
  "MY_APP_PROCESS"
,   {   x01: 'my first custom value'
    ,   x02: 'mysecond custom value'
    ,   x03: 'my third custom value'
    }
 , { dataType: 'text'
 ,success: function(pData){alert(pData)}
}
);

If you were to create the Application Process “MY_APP_PROCESS” and run the Javascript above, you’d see an alert popup:

——–

You passed “my first custom value” as the value for x01.
You passed “mysecond custom value” as the value for x02.
You passed “my third custom value” as the value for x03.

———

You can use use x01 through to x10 to pass up to 10 parameters to your application process. What about if you want to pass more than 10 parameters, though? To do this, you first need to create a number of Application Items. You might like to call them :G_11, :G_12, :G_13 etc..

You can then set the values of these items in session state (and hence make them available in your Application Process) by doing the following:

apex.server.process ( 
  "MY_APP_PROCESS"
,   {   x01: 'my first custom value'
    ,   x02: 'mysecond custom value'
    ,   x03: 'my third custom value'
    ,   p_arg_names: ['G_11','G_12','G_13']
    ,   p_arg_values: ['My 11th custom value','My 12th custom value','My 13th custom value']
    }
 , {    dataType: 'text'
    ,   success: function(pData){alert(pData)}
    }
);

Referencing these values inside your Application Process is simply a case of using Bind Variable syntax, e.g.:


HTP.p('You passed "'||APEX_APPLICATION.g_x01 ||'" as the value for x01. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x02 ||'" as the value for x02. ');

HTP.p('You passed "'||APEX_APPLICATION.g_x03 ||'" as the value for x03. ');

HTP.p('You passed "'||:G_11 ||'" as the value for G_11. ');

Categories: Ajax, ApEx Tags:

Adobe Fireworks Colour Picker Problem on Mac OS X Mountain Lion

I’ve had a problem with Adobe Fireworks CS5 ever since upgrading to a Retina Display MacBook. The problem is that the eye-dropper colour picker tool just doesn’t work any more. At all. Very frustrating.

I came across a very simple workaround today which can be found here:

http://simianstudios.com/blog/post/colour-picker-bug-workaround-for-adobe-fireworks-cs4-in-os-x-lion

 

Image

Categories: Uncategorized Tags:

Controlling Hide/Show Apex Regions Using Javascript

April 19, 2013 2 comments

Hide/Show regions are very useful in that they allow users to hide certain on-screen content when it’s not relevant for them (and show it again just as easily) simply by clicking the small arrow icon in the top left of the region.

Image

 

But how can you programmatically do the equivalent of clicking the arrow icons? Perhaps, for example, when a user clicks a particular button on your page, you want all Show/Hide regions to be expanded. How can you do this?

One answer is the snippet of code below. In this code, “MY_REGION” is the static ID of the region for which we wish to programatically click the show/hide arrow icon.

// How to programatically click a Hide/Show region hide/show button
$('#MY_REGION .uRegionControl').click();

We can use the snippet below to work out the current status of a Hide/Show region i.e. whether it is currently expanded or collapsed. The below snippet will return a value or “none” if the region is collapsed.

// How to find out whether a hide/show region is currently shown or not
$('#MY_REGION div.uRegionHeading').next().css('display');

We can build on the above to create a function which expands, collapses or toggles the state of a Hide/Show Region.


// ********************************************************
// ** Function setStateOfHideShowRegion(pRegionStaticId,pDoWhat)(type,id)  
// ** Collapses, Expands of toggles any Hide Show region which has a static id
// ** defined by the string pRegionStaticId.
// ** Returns either "collapsed" or "expanded" to indicate the status of the
// ** region once this function has run.
// ** pDoWhat: either "expand", "collapse" or "toggle"
// ********************************************************
function setStateOfHideShowRegion(pRegionStaticId,pDoWhat) {
    
    var returnState;
    var currentState = 'expanded';
    doWhat = pDoWhat.toLowerCase();
    
    if ($('#'+pRegionStaticId+' div.uRegionHeading').next().css('display') == 'none') {
        
        currentState = 'collapsed';
        
    };
    
    if (doWhat == 'toggle') {
        
        $('#'+pRegionStaticId+' .uRegionControl').click();
        returnState = (currentState=='expanded'?'collapsed':'expanded');
        
    }
    else if (doWhat == 'expand') {
        
        if (currentState !== 'expanded') {
            $('#'+pRegionStaticId+' .uRegionControl').click();
        };
        returnState = 'expanded';
        
    }
    else if (doWhat == 'collapse') {
        
        if (currentState !== 'collapsed') {
            $('#'+pRegionStaticId+' .uRegionControl').click();
        };
        returnState = 'collapsed';
        
    };
    
    return returnState;
    
}
Categories: ApEx, Javascript, jQuery

Advanced Javascript Tutorial

Today I came across a great interactive tutorial which covers a series of advanced Javascript topics. It’s very well put together and lets you try out your own variations of the code being shown which can be very handy in making sure your understanding is correct.

It’s from John Resig, the creator of jQuery. The tutorial is not jQuery-centric though. In fact, it doesn’t talk about or use jQuery at all. It’s just pure Javascript.

You can find it here: http://ejohn.org/apps/learn/

Image

Categories: Uncategorized Tags:

Restoring a dropped table

ImageCatastrophe! You’ve just accidentally dropped a table which contained really rather important data. What to do?

One thing you can do to recover the situation quickly (if you’re running 10g or later, that is) is to run the following command:

FLASHBACK TABLE MY_SCHEMA.MY_SUPER_IMPORTANT_TABLE TO BEFORE DROP;

If the table is still in the Recycle Bin, it’ll be recovered straight away. You can check whether the table is still available in the recycle bin and whether it can be recovered this way, with the following SELECT statement:

SQL> select original_name, can_undrop from recyclebin;

ORIGINAL_NAME CAN
-------------------------------- ---
MY_SUPER_IMPORTANT_TABLE YES

You can read about the Recyle Bin and Flashback Drop here: http://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

Categories: SQL Tags:

An Interesting Feature of NOT IN and Multi-Row Subqueries

March 1, 2013 4 comments

Take the following simple SQL statement:

SELECT *
FROM dual
WHERE 'x' NOT IN
(SELECT 'a' FROM dual);

Since ‘x’ cannot be found in our subquery, you’d expect this to return a row from Dual right? Indeed it does:

SQL> SELECT *
  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual);

D
-
X

What about in the following case?

SELECT *
FROM dual
WHERE 'x' NOT IN
(SELECT 'a' FROM dual
 UNION ALL
 SELECT NULL FROM dual);

You might expect this to return a row as well since ‘x’ is still not in our subquery (which now returns two values: ‘x’ and NULL).

You’d be wrong, however. This query will in fact return no rows:

SQL> SELECT *
  2  FROM dual
  3  WHERE 'x' NOT IN
  4  (SELECT 'a' FROM dual
  5   UNION ALL
  6   SELECT NULL FROM dual);

no rows selected

If the subquery referenced by your NOT IN statement contains any NULL values, the NOT IN condition will evaluate to unknown.

It’s worth noting that this is not the case if you use an IN as opposed to a NOT IN:

SQL>  SELECT *
  2   FROM dual
  3   WHERE 'a' IN
  4   (SELECT 'a' FROM dual
  5    UNION ALL
  6    SELECT NULL FROM dual);

D
-
X
Categories: SQL Tags: ,
Follow

Get every new post delivered to your Inbox.