APEX 4.1 Shuttle Null on Right

Question

APEX version: 4.1.1.00.23

I have a shuttle that populates the right site based on what is chosen from a select list. Whenever an item is picked from the select list, the correct item or items are populated on the right panel, but there is always a null that is added. How do I get ride of the null?

Shuttle name: P51_Shuttle Select list name: P51_Analyst

Here is my code: HTML Header:

<script type="text/javascript">

function LoadRightPane()
{ 
  var objDQAnalyst = document.getElementById("P51_DQ_ANALYST");
  var ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=get_DQAttributes',0);
  ajaxRequest.add('P51_DQ_ANALYST',objDQAnalyst.value);
  ajaxResult = ajaxRequest.get();

//alert(ajaxResult);

  if(ajaxResult)
    {  

    var shuttleRight = document.getElementById("P51_SHUTTLE_RIGHT");
      shuttleRight.options.length = 0;
      var attrArray = ajaxResult.split("~colsep~");
      for(var i=0; i < attrArray.length; i++) {
        shuttleRight.options[i] = new Option(attrArray[i], attrArray[i]);
      }
    }
    else
    {
      shuttleRight.options.length = 0;
    }
  ajaxRequest = null; 
} 

function moveitem(item) {
return;
   s_left = document.getElementById("P51_SHUTTLE_TO_LEFT");
   db.transaction(function(tx){
      tx.executeSql('select distinct DQ_ATTRIBUTE from DQ_MANUAL_EDIT where DQ_ANALYST = ?',[item],function(tx,results)
  {
      for (var i=0; i < s_left.options.length;i++)
   {
     if (results.value == s_left.options[i].value) 
     {
         s_left.options[i].selected = true;
         g_Shuttlep_v15.move();
     }
   }
  });
 });
}

</script>

Page HTML Body Attribute:

onLoad="Javascript:LoadRightPane();"

Applicaiton/Page process called (get_DQAttributes)

DECLARE

L_RETURN VARCHAR2(2000):= NULL ;

BEGIN

BEGIN

FOR rec IN (    
select distinct dq_attribute 
from DQ_MANUAL_EDIT
where dq_analyst = :P51_DQ_ANALYST
)    
LOOP      
L_RETURN := L_RETURN || rec.dq_attribute || '~colsep~' ;

END LOOP;

end;


htp.prn(L_RETURN);

END;

Answer 1

Your ajax process always returns something suffixed with ~colsep~

When you run a string like that in firebug:

"test1~colsep~".split("~colsep~")

The result will be an array with 2 elements:

["test1", ""]

So your NULL element originates from that. To fix this just adjust the ajax process to trim the last separator with RTRIM.

DECLARE
   L_RETURN VARCHAR2(2000):= NULL ;
BEGIN
   FOR rec IN ( select distinct dq_attribute 
                  from DQ_MANUAL_EDIT
                 where dq_analyst = :P51_DQ_ANALYST)    
   LOOP      
      L_RETURN := L_RETURN || rec.dq_attribute || '~colsep~' ;
   END LOOP;
   l_return := rtrim(l_return, '~colsep~');

   htp.prn(L_RETURN);
END;

Answer 2

Everything about Tom's answer is correct. I had to make one modification to the line

l_return := rtrim(l_return, '~colsep~');

When running that code, the last two letters would be removed. And if I had multiple items in the shuttle, the first item would be fine, but the second one would be cut off. For example: Using the code: l_return := rtrim(l_return, '~colsep~'); the right panel of the shuttle would look like this

Score
Da

But the actual text should be

Score
Data

So I used the 'substr' function and now it is working

l_return := substr(l_return,0,(length(l_return)-8));

Here is the whole code

DECLARE
   L_RETURN VARCHAR2(2000):= NULL ;
BEGIN
   FOR rec IN ( select distinct dq_attribute
                  from DQ_MANUAL_EDIT
                 where dq_analyst = :P51_DQ_ANALYST)    
   LOOP      
      L_RETURN := L_RETURN || rec.dq_attribute || '~colsep~' ;
   END LOOP;
      L_RETURN := substr(l_return,0,(length(l_return)-8));

   htp.prn(L_RETURN);
END;

Thank you Tom for getting my mental ball rolling!!!