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; 

Uncommon Functions and Objects In Oracle SQL and PL/SQL

1. TO_CHAR(Date,'fmDay Month fmDD, YYYY'): 'fm' prefix can be remove Embedded spaces       from value.


2. NVL2( value , value_if_not_null, value_if_null )

3. In NVL and NVL2

NVL2(inv_date,'Pending','Incomplete')               
=> True (value_if_not_null and value_if_null are same type)

NVL2(inv_amt,inv_date,'Not Available')                                       False

NVL2(inv_date,sysdate-inv_date,sysdate)                   
=> True (value_if_not_null and value_if_null are same type)

NVL2(inv_amt,inv_amt*.25,'Not Available')                                False

NVL(ADD_MONTHS(END_DATE,1),SYSDATE)             
=> True (value and value_if_null are same type )

TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))               False

NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')     False

NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')
=> True (value_if_null is convert to value_if_null type)

NVL(TO_CHAR(cust_credit_limit*.15),'Not Available')       
=>  True (value_if_null is convert to value_if_null type)

TO_CHAR(NVL(cust_credit_limit*.15,'Not Available'))             False

NVL(cust_credit_limit*.15,'Not Available')                                  False

NVL(cust_credit_limit,'Not Available')                                         False

4. NULLIF
If both value is same then return null or retrun first value.
SELECT NULLIF(1, 1) FROM emp; Return : null
SELECT NULLIF(1, 2) FROM emp; Return : 1
SELECT NULLIF(null, 2) FROM emp; Return : null

5. COALESCE
COALESCE (value1, value2, value3) is equal to

CASE
WHEN value1 is NOT NULL THEN value1
WHEN value2 is NOT NULL THEN value2
Else value3
END

Strong Authentication

CREATE TABLE  "OTSL_USERS"
   ( "USER_ID" NUMBER,
"USER_NAME" VARCHAR2(100),
"PASSWORD" VARCHAR2(4000),
"CREATED_ON" DATE,
"DEPARTMENT" VARCHAR2(200),
"AUT_SECTION" VARCHAR2(200),
"EXPIRES_ON" DATE,
"ADMIN_USER" CHAR(1),
"FACTORY_NAME" VARCHAR2(200),
"ACCESSCODE" NUMBER(20,0),
"ACTIVE" CHAR(2),
"PHOTO" BLOB,
"SCHOOL_NAME" VARCHAR2(200)
   )
 
 ==================================

CREATE OR REPLACE TRIGGER  "USER_T1" BEFORE INSERT OR UPDATE ON OTSL_USERS FOR EACH ROW
BEGIN

:NEW.PASSWORD:=CUSTOM_HASH(UPPER(:NEW.USER_NAME),:NEW.PASSWORD);

if inserting then
IF :NEW.USER_ID IS NULL THEN
SELECT NVL(MAX(USER_ID)+1,1) INTO :NEW.USER_ID FROM OTSL_USERS;
END if;
end if;
END;

/
ALTER TRIGGER  "USER_T1" ENABLE
/

==========================================
create or replace function custom_hash (p_username in varchar2, p_password in varchar2)
return varchar2
is
  l_password varchar2(4000);
  l_salt varchar2(4000) := 'UYUKQULATJYQ2RJTWPTHHIU7K1O7FR';
begin

-- This function should be wrapped, as the hash algorhythm is exposed here.
-- You can change the value of l_salt or the method of which to call the
-- DBMS_OBFUSCATOIN toolkit, but you much reset all of your passwords
-- if you choose to do this.

l_password := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5
  (input_string => p_password || substr(l_salt,10,13) || p_username ||
    substr(l_salt, 4,10)));
return l_password;
end;
=========================================
create or replace function custom_auth (p_username in VARCHAR2, p_password in VARCHAR2)
return BOOLEAN
is
  l_password varchar2(4000);
  l_stored_password varchar2(4000);
  l_expires_on date;
   t NUMBER;
  l_count number;
begin
t:=0;
select count(*) into l_count from otsl_users where user_name = p_username;
if l_count > 0 and t=0 then
  select password, expires_on into l_stored_password, l_expires_on
   from otsl_users where user_name = p_username;
  if l_expires_on > sysdate or l_expires_on is null then
    l_password := custom_hash(p_username, p_password);
    if l_password = l_stored_password then
      return true;
    else
      return false;
    end if;
  else
    return false;
end if;
else
  return false;
end if;
end;