PRAGMA AUTONOMOUS_TRANSACTION

PRAGMA AUTONOMOUS_TRANSACTION  is a Seen Text what make sub program successful  even the main program got Rollback in Oracle PL/SQL. Normally we may use this Seen Text in Functions , Procedures , Trigger which we call in the PL/SQL block as a sub Program/Transaction.

1. Mainly PRAGMA AUTONOMOUS_TRANSACTION use to keep log at the middle of a PL/SQL Transaction block.

To Know More and With Example You Can Visit This URL :   PRAGMA AUTONOMOUS_TRANSACTION

CREATE AND DROP ACL VERSION 2

SELECT ACL

SELECT host,
       lower_port,
       upper_port,
       ace_order,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date,
       grant_type,
       inverted_principal,
       principal,
       principal_type,
       privilege
FROM   dba_host_aces

ORDER BY host, ace_order;
CREATE ACL

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'WWW ACL',
                                    principal   => 'PUBLIC',
                                    is_grant    => true,
                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'PUBLIC',
                                       is_grant  => true,
                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => '*');
END;
/
COMMIT;


DROP ACL

BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_acl (
    acl         => 'www.xml');

  COMMIT;

END;

Json Parsing Using Json Table in PLSQL Oracle 12c Release 1

DECLARE
V_JSON VARCHAR2(4000);
ECO VARCHAR2(3000);
BEGIN
/*
{
"ChallanNo":"2020-0000000047",
"RefNo":"1234567",
"BankBranchNameEn":"BANK ASIA LTD., HEAD OFFICE",
"BankBranchNameBn":"BANK ASIA LTD., HEAD OFFICE",
"ClientNameEn":"Md Zafar Iqbal Edit",
"ClientNameBn":"মোঃ জাফর ইকবাল আপডেট",
"Amount":3450.0,
"ChallanEconomic":[
{
"EconomicCode":"1422311",
"EconomicNameEn":"Passport fee",
"EconomicNameBn":"পাসপোরà§à¦Ÿ ফি",
"ChallanSubNo":"01",
"Amount":3000.0
},
{
"EconomicCode":"1141102",
"EconomicNameEn":"Supplementary duty on domestically produced commodities",
"EconomicNameBn":"দেশজ উৎপাদিত পণà§à¦¯à§‡à¦° ওপর সমà§à¦ªà§‚রক শà§à¦²à§à¦•",
"ChallanSubNo":"02",
"Amount":450.0
}
],
"SuccessFlag":"Y"
}
*/
  SELECT a.JSON_RESPONSE.ChallanEconomic.EconomicCode
     INTO eco
     FROM
     IN_OUT_JSON a
    WHERE REQUESTID = 27;

   eco := LTRIM (RTRIM (eco, ']'), '[');

   FOR FOO IN (    SELECT REGEXP_SUBSTR (eco,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             TXT
                     FROM DUAL
               CONNECT BY REGEXP_SUBSTR (eco,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             IS NOT NULL)
   LOOP
      DBMS_OUTPUT.PUT_LINE (FOO.TXT);
   END LOOP;
END;

Multiple Process Execute On Same Button Problem In Oracle Apex

When we try to execute multiple process on same button there is may problem arise from oracle apex. Error would be like Jquery from apex builtin library file. We can get the error from browser inspect and console .

Solve : Goto to Page Properties >>  Advance >> Reload On Submission >> Always

Check Box In Header to Check all In Classic or Interactive Report Oracle Apex

Classic and Interactive Report Header Check Box
-------------------
<input type="checkbox" id="checkAll" > Check All

function and Global Variable Declaration
---------------------
$('#checkAll').click(function () {   
     $('input:checkbox').prop('checked', this.checked);   
 });

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;