Oracle APEX Theme Switcher Application

Oracle APEX Theme Switcher Application

Step 1 – Download the theme series folder Delta, Alpha etc.

Step 2 – Unzip the theme downloaded and copy under apex image folder

Step 3 – Login to Apex instance -> Application -> Shared Components

Step 4 – Import the theme sql file available in unzipped folder

Step 5 – Switch theme to the new theme Delta Series

Step 6 – Navigate to Shared Component and click Edit Definition

Step 7 – Create 2 substitution Variable  THEME_PATH and assign the path of the theme folder

                For ex – if you copied delta themes under apex images folder the assign /i/delta/

Step 8 – Create Variable UI_THEME and set the default theme name Example – black-tie

Step 9 – Create page zero for the application if it not available in the application

Step 10 – Create a region with no template and add a select list of theme items for switching the themes

Step 11 – Make select list as submit type on change

Step 12 – Run the Application and test the theme switcher by selecting different themes  from the select list

Contact me on raghu.j.prasad@gmail.com if you stuck with theme switcher

Reading XML Document from Oracle SQL

Query for Reading XML Document from Oracle SQL

select extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/ID') SID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/SWDHDRID') SWDHDRID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/HOSTNAME') HOSTNAME
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/HOSTIP') HOSTIP
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/HOSTMAC') HOSTMAC
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/RESOLVEDHOSTID') RESOLVEDHOSTID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/MANUFACTURER') MANUFACTURER
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PRODUCT') PRODUCT
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/EDITION') EDITION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/SOFTWAREVERSION') SOFTWAREVERSION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PRODUCTOPTION') PRODUCTOPTION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/QUANTITY') QUANTITY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLPATH') TOOLPATH
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLFILENAME') TOOLFILENAME
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLPRODUCT') TOOLPRODUCT
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLPRIMARY') TOOLPRIMARY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLSECONDARY') TOOLSECONDARY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/TOOLTERTIARY') TOOLTERTIARY
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PRODUCTID') PRODUCTID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/MANUFACTURERID') MANUFACTURERID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/EDITIONID') EDITIONID
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/APPLICATION') APPLICATION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/SOURCE') SOURCE
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PARTOFPRODUCT') PARTOFPRODUCT
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PARTOFEDITION') PARTOFEDITION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/PARTOFOPTION') PARTOFOPTION
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/CUSTOMISED') CUSTOMISED
, extractvalue (value (t2), '/MODEL_SOFTWARE_ROW/BUSINESSAPPLICATION') BUSINESSAPPLICATION
from table (xmlsequence (extract (b_xmldoc, '//MODEL_SOFTWARE_ROW'))) t2;

Oracle 10g Regular Expression Replace Function

Replace Repeating Characters with Single Character

Ex: RAAGHHUUUUUUUUUUUUUUU  with RAGHU

SELECT regexp_replace ('RAAGHHUUUUUUUUUUUUUUU', '([A-Z])\1{1,}', '\1') from dual

Output::- RAGHU

The \1 refers to the previous character and check {1,} 1 or more times and replace with the same character. This is called Backward reference.

Remove any Special Charaters other than Alphabets and Number

Ex:- RAJ33*#$%+_HU

select regexp_replace (p_str, '([^A-Z0-9])', '') from dual

Ouput - RAJ33HU

Removes all other characters expect number and Alphabets

Tri State Checkbox in HTML & Javascript

Below HTML Script implemets tristate checkbox for handling 3 values using the checkbox. Each click will change the state of the value and assign the new value

In the example below tick will have value 1 , cross value 0 and unchecked will have value -1

<HTML>
<HEAD>
<TITLE> Check Box Test</TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
<script>
function showf(){
 if(document.getElementById("i1").style.display=='' && document.getElementById("i2").style.display=='none'){
  document.getElementById("i3").style.display = '';
  document.getElementById("i1").style.display = 'none';
  document.t1.value = -1;
 }else if(document.getElementById("i3").style.display=='' && document.getElementById("i1").style.display=='none'){
  document.getElementById("i2").style.display = '';
  document.getElementById("i3").style.display = 'none';
  document.t1.value = 0;
 }else if(document.getElementById("i2").style.display=='' && document.getElementById("i3").style.display=='none'){
  document.getElementById("i1").style.display = '';
  document.getElementById("i2").style.display = 'none';
  document.t1.value = 1;
 }
 
}
</script>
</HEAD>
<BODY>
<form id="t" name="tform" action="testsubmit.php" method="post">
<input type="hidden" name="t1" value="1"><img src="tick.gif" id="i1" border="1" onclick="showf();">
<input type="hidden" name="t1"><img src="none.gif" id= "i2" border="1" onclick="showf();" style="display:none;">
<input type="hidden" name="t1"><img src="cross.gif" id= "i3" border="1" onclick="showf();" style="display:none;">
</form>
</BODY>
</HTML>

Reading CSV content from BLOB and Querying with SQL

We can load the csv file content to blob and then query the blob content as a table data using below functions. These functions even handles field data which contrains commas.

Step 1 - Create the function to handle commas within quotes

function handle_comma_within_quotes(str IN VARCHAR2) RETURN VARCHAR2
   AS
    copy varchar2(32000);
    inQuotes boolean := false;
   begin
     FOR i IN 1..LENGTH(str) LOOP
        IF substr(str,i,1)='"' then
              inQuotes := NOT inQuotes;
 END IF;
        if substr(str,i,1) = ',' AND inQuotes THEN
            copy := copy||'|';
        else
            copy := copy||substr(str,i,1);
        END IF;
    END LOOP;
    return replace(copy,'"','');
END handle_comma_within_quotes;

Create the pipelined function to query the csv file contents as table data

function f_csv_load_data(p_filename IN VARCHAR2) return loadcsvarray pipelined
is
    l_clob clob;
    l_blob blob;
    l_n    pls_integer;
    l_line varchar2(32000);
    rowname varchar2(4000);
    i number := 1;
begin
    select blob_content into l_blob from TEMP_TABLE WHERE loadfilename = p_filename;
    l_clob := blob_to_clob(l_blob);
    if substr(l_clob,-1,1) != chr(10) then l_clob := l_clob || chr(10); end if;
    l_n := instr(l_clob,chr(10));
    l_line := substr(l_clob,1,l_n)||',';
   
    while l_n > 0 loop
     
      l_line := handle_comma_within_quotes(l_line);
     
      if i=1 then
        rowname := 'HEADROW';
      else
        rowname := 'DATAROW';
      end if;
     
      i:= i+1;
      pipe row (loadcsvobject(rowname,
        replace(substr(l_line, 1, instr(l_line,',')-1),'|',','),
        replace(substr(l_line, instr(l_line,',')+1, instr(l_line,',',1,2)-instr(l_line,',')-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,2)+1, instr(l_line,',',1,3)-instr(l_line,',',1,2)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,3)+1, instr(l_line,',',1,4)-instr(l_line,',',1,3)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,4)+1, instr(l_line,',',1,5)-instr(l_line,',',1,4)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,5)+1, instr(l_line,',',1,6)-instr(l_line,',',1,5)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,6)+1, instr(l_line,',',1,7)-instr(l_line,',',1,6)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,7)+1, instr(l_line,',',1,8)-instr(l_line,',',1,7)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,8)+1, instr(l_line,',',1,9)-instr(l_line,',',1,8)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,9)+1, instr(l_line,',',1,10)-instr(l_line,',',1,9)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,10)+1, instr(l_line,',',1,11)-instr(l_line,',',1,10)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,11)+1, instr(l_line,',',1,12)-instr(l_line,',',1,11)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,12)+1, instr(l_line,',',1,13)-instr(l_line,',',1,12)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,13)+1, instr(l_line,',',1,14)-instr(l_line,',',1,13)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,14)+1, instr(l_line,',',1,15)-instr(l_line,',',1,14)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,15)+1, instr(l_line,',',1,16)-instr(l_line,',',1,15)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,16)+1, instr(l_line,',',1,17)-instr(l_line,',',1,16)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,17)+1, instr(l_line,',',1,18)-instr(l_line,',',1,17)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,18)+1, instr(l_line,',',1,19)-instr(l_line,',',1,18)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,19)+1, instr(l_line,',',1,20)-instr(l_line,',',1,19)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,20)+1, instr(l_line,',',1,21)-instr(l_line,',',1,20)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,21)+1, instr(l_line,',',1,22)-instr(l_line,',',1,21)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,22)+1, instr(l_line,',',1,23)-instr(l_line,',',1,22)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,23)+1, instr(l_line,',',1,24)-instr(l_line,',',1,23)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,24)+1, instr(l_line,',',1,25)-instr(l_line,',',1,24)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,25)+1, instr(l_line,',',1,26)-instr(l_line,',',1,25)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,26)+1, instr(l_line,',',1,27)-instr(l_line,',',1,26)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,27)+1, instr(l_line,',',1,28)-instr(l_line,',',1,27)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,28)+1, instr(l_line,',',1,29)-instr(l_line,',',1,28)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,29)+1, instr(l_line,',',1,30)-instr(l_line,',',1,29)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,30)+1, instr(l_line,',',1,31)-instr(l_line,',',1,30)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,31)+1, instr(l_line,',',1,32)-instr(l_line,',',1,31)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,32)+1, instr(l_line,',',1,33)-instr(l_line,',',1,32)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,33)+1, instr(l_line,',',1,34)-instr(l_line,',',1,33)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,34)+1, instr(l_line,',',1,35)-instr(l_line,',',1,34)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,35)+1, instr(l_line,',',1,36)-instr(l_line,',',1,35)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,36)+1, instr(l_line,',',1,37)-instr(l_line,',',1,36)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,37)+1, instr(l_line,',',1,38)-instr(l_line,',',1,37)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,38)+1, instr(l_line,',',1,39)-instr(l_line,',',1,38)-1),'|',','),
        replace(substr(l_line, instr(l_line,',',1,39)+1, instr(l_line,',',1,40)-instr(l_line,',',1,39)-1),'|',',')
        ));
      l_clob := substr(l_clob,l_n+1);
      l_n := instr(l_clob,chr(10));
      l_line := substr(l_clob,1,l_n)||',';
    end loop;
END f_csv_load_data;


Query

select * from table(f_csv_load_data('filename'))

Will display the result in the tabular form