Day, Minute, Second Difference Between Two Date

SELECT :end_date,
       :start_date,
       TRUNC (MONTHS_BETWEEN ( :end_date, :start_date) / 12) AS yrs,
       TRUNC (MOD (MONTHS_BETWEEN ( :end_date, :start_date), 12)) AS mnts,
       TRUNC (:end_date- ADD_MONTHS(:start_date,MONTHS_BETWEEN(:end_date,:start_date)))AS dys,
       TRUNC (24 * MOD ( :end_date - :start_date, 1)) AS hrs,
       TRUNC (MOD (MOD ( :end_date - :start_date, 1) * 24, 1) * 60) AS mins,
       MOD (MOD (MOD ( :end_date - :start_date, 1) * 24, 1) * 60, 1) * 60 AS secs
  FROM (SELECT :end_date, :start_date FROM DUAL);

Remove Special Character From A String Using REGEXP_REPLACE

SELECT REGEXP_REPLACE('test'dssdf/sdfs','[^a-z_A-Z ]') FROM DUAL

RESULT : testdssdfsdfs

How to add Captcha In Your Login Page in Oracle Apex

Step 1

Create a item in your login page.

Step 2

Copy below code into item "Post Text"

<div style="background-color:#FFF; border:1px solid #ececec; padding:3px;">
<img width="25" height="25",=""
src="APEX_050100.wwv_flow_image_generator.get_image?p_position=1&p_sessionid=&APP_SESSION."><img
width="25" height="25",=""
src="APEX_050100.wwv_flow_image_generator.get_image?p_position=2&p_sessionid=&APP_SESSION."><img
width="25" height="25",=""
src="APEX_050100.wwv_flow_image_generator.get_image?p_position=3&p_sessionid=&APP_SESSION."><img
width="25" height="25",=""
src="APEX_050100.wwv_flow_image_generator.get_image?p_position=4&p_sessionid=&APP_SESSION."><img
width="25" height="25",=""
src="APEX_050100.wwv_flow_image_generator.get_image?p_position=5&p_sessionid=&APP_SESSION.">
</div>

Step 3 

Create a process and copy the code into your process and select when process execute button.

DECLARE
   vCount   NUMBER := 0;
BEGIN
      IF TRIM (:P101_CAPTCHA) IS NULL
      THEN
         RAISE_APPLICATION_ERROR (-20001,
                                  'Please Confirm Verification Code.');
      END IF;

      BEGIN
         SELECT 1
           INTO vCount
           FROM APEX_050100.wwv_flow_request_verifications
          WHERE SESSION_ID = :APP_SESSION
                AND SUBSTR (VERIFICATION_STRING, 1, 5) = :P101_CAPTCHA;
      EXCEPTION
         WHEN OTHERS
         THEN
            RAISE_APPLICATION_ERROR (-20001,
                                     'Please Confirm Verification Code.');
      END;

      IF vCount = 1
      THEN
         NULL;
      ELSE
         RAISE_APPLICATION_ERROR (-20001,
                                  'Please Confirm Verification Code.');
      END IF;
END;

Step 4

Error Message

#SQLERRM_TEXT#

Json to text Parsing with List ( [ ] ) in Oracle PL/SQL

DECLARE 
    j apex_json.t_values; 
BEGIN 
    apex_json.parse(j, '{ "items": [ 1, 2, { "foo": "qaium" } ] }'); 
  
        dbms_output.put_line('found items[3].foo--'||apex_json.get_varchar2(p_path => 'items[%d].foo', p0 => 3, p_values => j)); 
   
END;

Get Workspace ID

To import a single page at APEX application, we need workspace id. The id can be detected by the following query.

SELECT WORKSPACE, TO_CHAR (WORKSPACE_ID) 

FROM APEX_WORKSPACES;

OR

SELECT APPLICATION_ID, WORKSPACE, TO_CHAR (WORKSPACE_ID) 

FROM APEX_APPLICATIONS;

Oracle Apex Tree Collapse OR Expand Automatically

Follow the image steps for collapse on page load.

Step 1. Create a Dynamic action on Page Load.

Step 2. Select Expand or Collapse Tree.

If you want to expand on page load then just in step 2 select Expand Tree

(Click on the Image for clear view)

Remove Extra Space From A String By Oracle SQL / PLSQL

SELECT REGEXP_REPLACE ('Remove     Extra Space', '[[:blank:]]+', ' ') FROM DUAL

Json Parsing by Select Statement in Oracle

DECLARE
  l_cursor SYS_REFCURSOR;
BEGIN
  
  OPEN l_cursor FOR
    SELECT e.empno AS "employee_number",
           e.ename AS "employee_name",
           e.deptno AS "department_number"
    FROM   emp e ;
   -- WHERE  rownum <= 2;

  APEX_JSON.initialize_clob_output;

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;

  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;

Output:

{
"employees":[
{
"employee_number":7839
,"employee_name":"KING"
,"department_number":10
}
,{
"employee_number":7698
,"employee_name":"BLAKE"
,"department_number":30
}
]

}

Check Digit eAN13

<html>
<body>

<h2>JavaScript Class</h2>

<p>Check Digit eAN13</p>
<input type="text" id="eAN13" onchange="eanCheckDigit(this.value);">
<p id="demo"></p>

<script>
function eanCheckDigit(s){
    var result = 0;
    for (counter = s.length-1; counter >=0; counter--){
        result = result + parseInt(s.charAt(counter)) * (1+(2*(counter % 2)));
    }
    document.getElementById("demo").innerHTML = (10 - (result % 10)) % 10;

}
</script>

</body>
</html>

// Credit goes to FAHIM JAMAL VAI

Multiple File Select And Upload In A Time By Oracle Apex

1. Create File Browse Item
2. Goto Item >Advanced> Custom Attributes> Type Multiple  (For selecting multiple data)
3. Create another text field to get File Name
4. Create Dynamic Action on File Browse Item
   "Onchange" event JavaScript.
 
   var x = document.getElementById("P30_DOCUMENTS");
vlength=x.files.length;
var txt = "";
$x("P30_FILE_NAME").value="";
if ('files' in x) {
for (var i = 0; i <vlength; i++) {
if (x.files.length == 0) {
            txt = "Select one or more files.";
            $x("P30_FILE_NAME").value="";
        } 
else{
                txt += "<br><strong>" + (i+1) + ". file</strong><br>";
                 console.log("txt ="+txt );
                var file = x.files[i];
                if ('name' in file) {
                    txt += "name: " + file.name + "<br>";
                   $x("P30_FILE_NAME").value+=file.name;
                }
                if ('size' in file) {
                    txt += "size: " + file.size + " bytes <br>";
                }
if(i!=vlength-1)
{
          $x("P30_FILE_NAME").value+=",";
}
}
            }
}

5. Create the Page Process to get the file from APEX_APPLICATION_FILES and Store it in a Temporary Table.
Code:

DECLARE
   l_selected    apex_application_global.vc_arr2;
   lv_filename   VARCHAR2 (100);
BEGIN
   l_selected := apex_util.string_to_table (:p37_filename, ':');
   FOR i IN 1 .. l_selected.COUNT
   LOOP
      lv_filename := l_selected (i);
      BEGIN
         INSERT INTO mul_file_attach
                     (file_id, filename, mime_type, file_content,file_size)
            SELECT ID, lv_filename, mime_type, blob_content,DOC_SIZE
              FROM apex_application_files
             WHERE filename = lv_filename AND created_by = :app_user;
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            raise_application_error (-20585, 'Error in Insertion' || SQLERRM);
      END;
      DELETE FROM apex_application_files
            WHERE filename = lv_filename AND created_by = :app_user;
      COMMIT;
   END LOOP;
END;