𝗔 𝘃𝗶𝘀𝘂𝗮𝗹 𝗴𝘂𝗶𝗱𝗲 𝗼𝗻 𝗵𝗼𝘄 𝘁𝗼 𝗰𝗵𝗼𝗼𝘀𝗲 𝘁𝗵𝗲 𝗿𝗶𝗴𝗵𝘁 𝗗𝗮𝘁𝗮𝗯𝗮𝘀𝗲

 *** Which database have you used for which workload?

Collected.

Fetching and Parsing Data from Other Online Source To Oracle Database Using Web service in Oracle Apex

1. Create a table using the below code.

CREATE TABLE  "EMP_INFO" 
   ( "SERIAL" NUMBER, 
"GROUP_NAME" VARCHAR2(100), 
"EMP_NAME" VARCHAR2(100), 
"DESIGNATION" VARCHAR2(100), 
"MOBILE_NUMBER" VARCHAR2(20), 
"EMAIL_ADD" VARCHAR2(10)

)

2. Create a Page then Create a Region With Classic Report type. 

select * from EMP_INFO

3. Create a button like (Pull Data) to execute the process.

4. Create a Process and use the below code  

begin

insert into EMP_INFO
(SERIAL,GROUP_NAME,EMP_NAME,DESIGNATION,MOBILE_NUMBER,EMAIL_AD)select col001, col002, col003 , col004, col005, col006

from table
( apex_data_parser.parse
(  p_content=> apex_web_service.make_rest_request_b('https://raw.githubusercontent.com/Qaiums/Fatch-Data-From-Cloud-In-Oracle-Apex/main/emp_info.csv', 'GET')
, p_file_name=> 'emp_info.csv', p_skip_rows=> 1 )
);

end ;

5. Assign the button to execute the process. 

You can also Fetch and Parse from File Browser Item using APEX_DATA_PARSER. 

That's It...

API Calling From Oracle Apex Using JavaScript

1. Create a blank page then create a region.

2. Create two items (Like P46_URL and P46_RESPONSE) , One for inputting web service URL and another one is for getting response. 

3. Copy-pest the below JavaScript function into page propertice> Function and Global Variable Declaration 

function CALL_TO_WEBSERVICE()
{
 var urlvariable;
 var ItemJSON;
 var v_url = apex.item("P46_URL").getValue();
 URL = v_url ;  
 var xmlhttp = new XMLHttpRequest();
    xmlhttp.onreadystatechange = callbackFunction(xmlhttp);
    xmlhttp.open("GET", URL, false);
    xmlhttp.onreadystatechange = callbackFunction(xmlhttp);
    xmlhttp.send(ItemJSON);
$x("P46_RESPONSE").value = xmlhttp.responseText;

}

function callbackFunction(xmlhttp) 

{

 // alert(xmlhttp.responseXML);

}

4. Create a button and create a Dynamic Action on click event the button with Execute JavaScript Action. just call the function below. 

CALL_TO_WEBSERVICE();

5. That's All. Now just put the Web Service URL into the URL item and click on the button. 

Remove (Leave site? Changes you made may not be saved. Prevent this page from creating additional dialogs.) Warning Dialogs from Oracle Apex

In Oracle Apex whenever we move one page to another page then we may face this dialog warning. Sometimes it's good but sometimes users feel bored. 

So here we see how to remove this warning dialog?

Leave site? 

Changes you made may not be saved.

𐄂 Prevent this page from creating additional dialogs.

Solution:

Goto page properties> Navigation Menu> Warn on Unsaved Changes > Disable it.

That's it. 

Real Time Notification in Oracle Apex Using Ajax Callback Process.

Run time notification in oracle apex. Here it shows only one page. But you can do it globally for getting notifications from any page. 

1. Create a Table For Notification

 CREATE TABLE "NOTIFICATION_TABLE"  

 ( "PID" NUMBER, 

 "MESSAGE" VARCHAR2(30),

 "SEEN_TIME" TIMESTAMP (6), 

 "SEEN_TYPE" VARCHAR2(1)

 )

2. Put the function into the Notification page>> Function and Global Variable Declaration

(function loop(i) {

   setTimeout(function() { 

       apex.server.process ( 

          'AJAX_NOTIFICATION_CALL',

          {}, // params

          {

              async     : true,

              dataType  : 'json',

              success   : function(data) {

                 if (data.MESSAGE) {

                     if (data.SEEN_TYPE == 'N') { 

                      apex.message.showPageSuccess(data.MESSAGE);

                     }

                 }

             }

          }

       ); 

       loop(i);

   }, 4000); // 4sec forever

})();

3. Create an Ajax Call back process and put the below code into this process. Finally, Remane the process name as AJAX_NOTIFICATION_CALL.

BEGIN

       APEX_JSON.OPEN_OBJECT();

       FOR c IN (

           SELECT * FROM NOTIFICATION_TABLE WHERE SEEN_TIME IS NULL ORDER BY PID 

           FETCH FIRST 1 ROWS ONLY

       ) LOOP

           APEX_JSON.WRITE('MESSAGE', c.MESSAGE);

           APEX_JSON.WRITE('SEEN_TYPE', c.SEEN_TYPE);

           UPDATE NOTIFICATION_TABLE

           SET SEEN_TIME = LOCALTIMESTAMP,

                SEEN_TYPE = 'Y'

           WHERE PID = c.PID; 

       END LOOP;

       APEX_JSON.CLOSE_OBJECT();

    END;

4. Finally input the value into the NOTIFICATION_TABLE  table from anywhere and check the Notification page without loading. 

Check any one Checkbox In Oracle Apex

Sometimes we have some requirements like the user can not check more than one option. So we can do the solution for this purpose using javascript.

1. Create a report as usual then example below code.

select EMPLOYEEID,

       EMPLOYEENAME,

       EMAIL,

       MOBILE,

       apex_item.checkbox(1,'#ROWNUM#') checkbox  -- code for creating check box in classic report 

  from EMPLOYEES ;

2. Use the blow javascript in  Function and Global Variable Declaration.

$('input[name="f01"]').on('change', function(){

    $('input[name="f01"]').not(this).prop('checked',false);

});

//Here f01 is the name of the check box column.  

XML Visualization And Download the XML into a txt File In Oracle Apex

 

1. Create a page and a region with  PL/SQL Dynamic Content type. Put the below code into PL/SQL Code 

DECLARE

v_text     CLOB;

BEGIN

HTP.p ('<span id="TEST_ID">');      

      FOR i

         IN (

     select to_char(XML)   "Information" 

FROM EMPLOYEES where EMPLOYEEID=1243 )

 LOOP

         v_text := i."Information";

         HTP.p ('<pre>' || replace(v_text,'&amp;','&') || '</pre>');

      END LOOP;

      HTP.p ('</span>');

END;

2. Put the like HTML link code above the code. 

HTP.p ('<a download="Information.txt" id="downloadlink"  ><b>Download</b><br><br><br<br></a> ');  

3.  Finally copy-pest the below JavaScript into page properties>  Function and Global Variable Declaration  Option. Make sure TEST_ID and downloadlink these two id is in the proper place.

(function () {

var textFile = null,

  makeTextFile = function (text) {

    var data = new Blob([text], {type: 'text/plain'});

    // If we are replacing a previously generated file we need to  

    // manually revoke the object URL to avoid memory leaks.

    if (textFile !== null) {

      window.URL.revokeObjectURL(textFile);

    }

    textFile = window.URL.createObjectURL(data);

    return textFile;

  };

  var create = document.getElementById('create'),

    textbox = document.getElementById('TEST_ID'); 

    var link = document.getElementById('downloadlink');

    link.href = makeTextFile(textbox.innerHTML.replace("&amp;", "&").trim());

    link.style.display = 'block';

})();

Language Change Using Google Translator

1. Create a global region on 0 page with a static content type.

2. Copy-pest the below code then hide or customize UI yourself.

<div id="google_translate_element"></div>

<script type="text/javascript">

function googleTranslateElementInit() { new google.translate.TranslateElement({pageLanguage: 'en'}, 'google_translate_element'); }

</script>

<script type="text/javascript" src="//translate.google.com/translate_a/element.js?cb=googleTranslateElementInit">

</script>

</body>

</html>

DBMS_REDACT Advanced Security Feature

How to leverage the data redaction from the Advanced Security feature in order to protect your sensitive data in your application even before it leaves your ATP

You have to execute the below code using the Admin user.

BEGIN

  DBMS_REDACT.ADD_POLICY(

  object_schema          => 'redact', -- (Schema Name)

  object_name            => 'credit_card', -- (Table Name)

  column_name            => 'card_val', --(Column Name)

  policy_name            => 'redact_REG_EXP',

   function_type       => DBMS_REDACT.PARTIAL,

   function_parameters => 'VVVVFVVVVFVVVVFVVVV,VVVV-VVVV-VVVV-VVVV,*,1,12',  -- (Column Value Expression)

   expression          => '1=1',

   policy_description  => 'Partially redacts our credit card numbers', -- Description/Comments

   column_description  => 'card_val contains credit card numbers in VARCHAR2 format' -- Description/Comments

);

END; -- More Details

 

Interactive Report Upper Scroll Bar

1. Make an ID in the region Static ID 

2. Page CSS Inline 

#ID .t-fht-thead{

  overflow: auto !important;

}

API Calling By Oracle PL-SQL/APEX Using UTL_HTTP/ APEX_WEB_SERVICE and ACL Configuration

API Calling Using URL_HTTP

declare

    v_req       utl_http.req;
    v_res       utl_http.resp;
    v_buffer    varchar2(4000); 
    v_body      varchar2(4000) := '{"field":"value"}'; -- Your JSON
begin
    -- Set connection.
 v_req := utl_http.begin_request('http://yourapi_base_url/operation','POST');
    utl_http.set_authentication(v_req, 'username','password');
    utl_http.set_header(v_req, 'content-type', 'application/json'); 
    utl_http.set_header(v_req, 'Content-Length', length(v_body));
    
    -- Invoke REST API.
    utl_http.write_text(v_req, v_body);
  
    -- Get response.
    v_res := utl_http.get_response(v_req);
    begin
        loop
            utl_http.read_line(v_res, v_buffer);
            -- Do something with buffer.
            dbms_output.put_line(v_buffer);
        end loop;
        utl_http.end_response(v_res);
    exception
        when utl_http.end_of_body then
            utl_http.end_response(v_res);
    end;
end;

But if you have Oracle APEX installed, then you can try APEX_WEB_SERVICE package (much simpler).

declare
    v_response      clob;
    v_buffer        varchar2(32767);
    v_buffer_size   number := 32000;
    v_offset        number := 1;
begin
    -- Set connection and invoke REST API.
    v_response := apex_web_service.make_rest_request(
        p_url           => 'http://yourapi_base_url/operation',
        p_http_method   => 'POST',
        p_username      => 'username',
        p_password      => 'password',
        p_body          => '{"field":"value"}' -- Your JSON.
    );
    
    -- Get response.
    begin
        loop
            dbms_lob.read(v_response, v_buffer_size, v_offset, v_buffer);
            -- Do something with buffer.
            DBMS_OUTPUT.PUT_LINE(v_buffer);
            v_offset := v_offset + v_buffer_size;
        end loop;
    exception
        when no_data_found then
            null;
    end;
end;

If you get an ACL exception, then you have to create ACL to open TCP port to connect with REST API.

BEGIN
    DBMS_NETWORK_ACL_ADMIN.create_acl (
        acl          => 'acl.xml',
        description  => 'Connecting with REST API',
        principal    => 'YOUR_DATABASE_SCHEMA',
        is_grant     => TRUE, 
        privilege    => 'connect',
        start_date   => SYSTIMESTAMP,
        end_date     => NULL
    );
    
    DBMS_NETWORK_ACL_ADMIN.assign_acl (
        acl         => 'acl.xml',
        host        => 'localhost', -- Or hostname of REST API server (e.g. "example.com").
        lower_port  => 80, -- For HTTPS put 443.
        upper_port  => NULL
    );
    
    COMMIT;
end; 

I assume your REST API is protected by basic authentication scheme (user and password). To keep this example simple i used HTTP. If you have to connect via HTTPS, then you have to change TCP port in ACL and configure Oracle Wallet for your Oracle database instance.

Create Oracle Database Table and Load Data from Excel in Couple of Seconds

You can create a table and load data from excel with the appropriate column data type within 5 seconds.

Oracle Apex will read the whole excel and then create a table with the appropriate data type by respective data. Finally, it loads all data into the table from excel. 

RMAN Backup and Archivelog / Noarchivelog

RMAN Backup:

Step-1 : Go to

C:\oracle\app\user\product\12.1.0\dbhome_1\BIN>

open CMD on this path.

Step-2:

Assign database name to ORCLE_SID.

C:\app\user\product\12.1.0\dbhome_1\BIN>set ORACLE_SID=orcl

Check :

C:\app\user\product\12.1.0\dbhome_1\BIN>echo %ORACLE_SID%

Step-3:

C:\app\user\product\12.1.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jul 22 13:56:48 2022

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1613180020)

RMAN>

Step-4:

RMAN> backup database ;

Starting backup at (Date)

using target database control file instead of recovery catalog

...................................................

Finished backup at (Date)

----------------------------------------------

Common Error:

RMAN> backup database ;

Starting backup at 22-JUL-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=364 device type=DISK

RMAN-00571: ========================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS 

RMAN-00571: =============================================

RMAN-03002: failure of backup command at 07/22/2022 13:40:26

RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

Solution:

 Archivelog On

1. Shutdown the Database

SQL>shutdown immediate;

database closed

database dismounted

Oracle instance shut down

2. Open the Database Up-to mount stage.

SQL> Startup Mount

connected to target database (not started)

Oracle instance started

database mounted

Total System Global Area 1610609384 bytes

Fixed Size 9028328 bytes

Variable Size 402653184 bytes

Database Buffers 1191182336 bytes

Redo Buffers 7745536 bytes

Advertisements

REPORT THIS AD

3. Enable the Archive log

alter database archivelog;

4. Check destination parameter and change value for archive generation.

-- check

show parameter log_archive_dest

–change value

alter system set log_archive_dest=’D:\Oracle’ scope=both;

5. Open the database.

alter database open;

6. Verify the archive generation by checking the location.

alter system switch logfile;

Disable the Archive Mode:

Rollback Process: noarchivelog

Shutdown immediate;

startup mount;

alter database noarchivelog;

alter database open;

Oracle Apex Password Expired: ORA-28001: the password has expired

java.sql.SQLException: ORA-28001: the password has expired

Local Apex password expired solution:

------------------------------------

APEX_200200

ALTER USER APEX_200200 IDENTIFIED BY "Password";  

ALTER USER system ACCOUNT UNLOCK;       

APEX_LISTENER

ALTER USER APEX_200200 IDENTIFIED BY "Password";

ALTER USER APEX_200200 ACCOUNT UNLOCK;

YOUR_SCHEMA

ALTER USER YOUR_SCHEMA IDENTIFIED BY "Password";

ALTER USER YOUR_SCHEMA ACCOUNT UNLOCK;

APEX_PUBLIC_USER

ALTER USER APEX_PUBLIC_USER IDENTIFIED BY "Password";

ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

APEX_REST_PUBLIC_USER

ALTER USER APEX_REST_PUBLIC_USER IDENTIFIED BY "Password";

ALTER USER APEX_REST_PUBLIC_USER ACCOUNT UNLOCK;

APEX_INSTANCE_ADMIN_USER

ALTER USER APEX_INSTANCE_ADMIN_USER IDENTIFIED BY "Password";

ALTER USER APEX_INSTANCE_ADMIN_USER ACCOUNT UNLOCK;

ORDS_PUBLIC_USER

ALTER USER ORDS_PUBLIC_USER IDENTIFIED BY "Password";

ALTER USER ORDS_PUBLIC_USER ACCOUNT UNLOCK;

Find Number of Inserted Row.

sql%Rowcount is working to print number of inserted row. like below.

dbms_output.put_line('No Of rows'||sql%Rowcount);

Set Alarm In Oracle Apex Using HTML, CSS, JavaScript

Few days ago I had a plan to make a alarm clock in Oracle Apex. So I tried to find out something to do it. And finally did it using HTML,CSS, JavaScript. I feel it works very well. So I am going to share my tricks and code to you. You can try or use it if needed. Even you may customize it your self according to your requirement.  Follow below steps. 

Step 1. Create a new page and a region using static content type. 

Step 2. Copy-Paste this CSS and Java Codes into Page properties >> HTML Header 

<style>

/* (A) FONT */

#ctime, #tpick {

  font-family: Impact, sans-serif;

}

.header {

  text-align: center;

  font-weight: normal;

  margin: 5px 0 10px 0;

}

/* (B) CURRENT TIME */

#ctime {

  margin: 0 auto;

  max-width: 350px;

  padding: 10px;

  background: #000;

  text-align: center;

}

#ctime .header {

  color: #c61d1d;

}

#ctime .square {

  display: inline-block;

  padding: 10px;

  margin: 5px;

}

#ctime .digits {

  font-size: 24px;

  background: #fff;

  color: #000;

  padding: 20px 10px;

  border-radius: 5px;

}

#ctime .text {

  margin-top: 10px;

  color: #ddd;

}

/* (C) TIME PICKER */

#tpick {

  margin: 0 auto;

  max-width: 350px;

  padding: 10px;

  background: #f2f2f2;

  white-space: nowrap;

}

#tpick-h, #tpick-m, #tpick-s {

  display: inline-block;

  width: 32%;

}

#tpick select {

  box-sizing: padding-box;

  width: 100%;

  font-size: 1.2em;

  font-weight: bold;

  margin: 20px 0;

}

#tset, #treset {

  box-sizing: padding-box;

  width: 50%;

  background: #3368b2;

  color: #fff;

  padding: 10px;

  border: 0;

  cursor: pointer;

}

#tset:disabled, #treset:disabled {

  background: #aaa;

  color: #888;

}

</style>

<script>

var ac = {

  // (A) INITIALIZE ALARM CLOCK

  init : function () {

    // (A1) GET THE CURRENT TIME - HOUR, MIN, SECONDS

    ac.chr = document.getElementById("chr");

    ac.cmin = document.getElementById("cmin");

    ac.csec = document.getElementById("csec");

    // (A2) CREATE TIME PICKER - HR, MIN, SEC

    ac.thr = ac.createSel(23);

    document.getElementById("tpick-h").appendChild(ac.thr);

    ac.thm = ac.createSel(59);

    document.getElementById("tpick-m").appendChild(ac.thm);

    ac.ths = ac.createSel(59);

    document.getElementById("tpick-s").appendChild(ac.ths);

    // (A3) CREATE TIME PICKER - SET, RESET

    ac.tset = document.getElementById("tset");

    ac.tset.addEventListener("click", ac.set);

    ac.treset = document.getElementById("treset");

    ac.treset.addEventListener("click", ac.reset);

    // (A4) GET ALARM SOUND

    ac.sound = document.getElementById("alarm-sound");

    // (A5) START THE CLOCK

    ac.alarm = null;

    setInterval(ac.tick, 1000);

  },

  // (B) SUPPORT FUNCTION - CREATE SELECTOR FOR HR, MIN, SEC

  createSel : function (max) {

    var selector = document.createElement("select");

    for (var i=0; i<=max; i++) {

      var opt = document.createElement("option");

      i = ac.padzero(i);

      opt.value = i;

      opt.innerHTML = i;

      selector.appendChild(opt);

    }

    return selector

  },

  // (C) SUPPORT FUNCTION - PREPEND HR, MIN, SEC WITH 0 (IF < 10)

  padzero : function (num) {

    if (num < 10) { num = "0" + num; }

    else { num = num.toString(); }

    return num;

  },

  // (D) UPDATE CURRENT TIME

  tick : function () {

    // (D1) CURRENT TIME

    var now = new Date();

    var hr = ac.padzero(now.getHours());

    var min = ac.padzero(now.getMinutes());

    var sec = ac.padzero(now.getSeconds());

    // (D2) UPDATE HTML CLOCK

    ac.chr.innerHTML = hr;

    ac.cmin.innerHTML = min;

    ac.csec.innerHTML = sec;

    // (D3) CHECK AND SOUND ALARM

    if (ac.alarm != null) {

      now = hr + min + sec;

      if (now == ac.alarm) {

        if (ac.sound.paused) { ac.sound.play(); }

      }

    }

  },

  // (E) SET ALARM

  set : function () {

    ac.alarm = ac.thr.value + ac.thm.value + ac.ths.value;

    ac.thr.disabled = true;

    ac.thm.disabled = true;

    ac.ths.disabled = true;

    ac.tset.disabled = true;

    ac.treset.disabled = false;

  },

  // (F) RESET ALARM

  reset : function () {

    if (!ac.sound.paused) { ac.sound.pause(); }

    ac.alarm = null;

    ac.thr.disabled = false;

    ac.thm.disabled = false;

    ac.ths.disabled = false;

    ac.tset.disabled = false;

    ac.treset.disabled = true;

  }

};

// (G) START CLOCK ON PAGE LOAD

window.addEventListener("load", ac.init);

</script>

Step 3.  Copy-Paste this code into Page properties >> Region>> Source

<!-- (A) CURRENT TIME -->

<div id="ctime">

  <h1 class="header">THE CURRENT TIME</h1>

  <div class="square">

    <div class="digits" id="chr">00</div>

    <div class="text">HR</div>

  </div>

  <div class="square">

    <div class="digits" id="cmin">00</div>

    <div class="text">MIN</div>

  </div>

  <div class="square">

    <div class="digits" id="csec">00</div>

    <div class="text">SEC</div>

  </div>

</div>

<!-- (B) SET ALARM -->

<div id="tpick">

  <h1 class="header">

    SET ALARM

  </h1>

  <div id="tpick-h"></div>

  <div id="tpick-m"></div>

  <div id="tpick-s"></div>

  <div>

    <input type="button" value="Set" id="tset"/>

    <input type="button" value="Reset" id="treset" disabled/>

  </div>

</div>

<!-- (C) ALARM SOUND -->

<audio id="alarm-sound" loop>

  <source src="#APP_IMAGES#Arekbar.mp3" type="audio/mp3">

</audio>

Step 4.  Upload a ringtone in  

Shared Components

Static Application Files

  then copy the reference for use as your ringtone source. 

Step 5. Change the music directory source form Step 3 code. Use here new directory of your ringtone. 

<audio id="alarm-sound" loop>

  <source src="#APP_IMAGES#Arekbar.mp3" type="audio/mp3">

</audio>

That's All. Now Just Set Alarm And Enjoy It. Thank You.  :)