Run Time Validation Check Without Page Submit In Oracle Apex

1.Ajax Callback : First we need to create Ajax Callback Process & give a meaningful name of this process. The Process like Bellow........... 

 Begin

    If :P9_QUANTITY is null Then

        htp.prn('Please Input Invoice Quantity.');

    Elsif :P9_QUANTITY =0  Then

        htp.prn('Sorry,Invoice Quantity Should be greater Than 0.');

    Else

        htp.prn('SUCCESS');

    End If;

End;

2. Dynamic Action : Create Dynamic Action in Item Level where we want to execute Validation.

apex.server.process('InvoiceQuantityValidation',
{
   pageItems : '#P9_QUANTITY'
}
,
{
   dataType : 'text', success : function(data)
   {
      if(data != 'SUCCESS') apex.message.alert(data);
   }
}
)

Digital Clock Create In Oracle Apex

Create Function In Function and Global Variable Declaration :

function showTime(){
    var date = new Date();
    var m=date.getMonth("MON");
    var dd=date.getDate();
    var y=date.getFullYear();
    var h = date.getHours(); // 0 - 23
    var m = date.getMinutes(); // 0 - 59
    var s = date.getSeconds(); // 0 - 59
    var session = "AM";
     if(h == 0){
        h = 12;
 }
   if(h > 12){
        h = h - 12;
        session = "PM";
    }
    
    h = (h < 10) ? "0" + h : h;
    m = (m < 10) ? "0" + m : m;
    s = (s < 10) ? "0" + s : s;
    
    //var time =dd +""+ m +"" + y +","+ h + ":" + m + ":" + s + " " + session;
    var time = h + ":" + m + ":" + s + " " + session;
    document.getElementById("MyClockDisplay").innerText = time;
    document.getElementById("MyClockDisplay").textContent = time;
    setTimeout(showTime, 1000);
   }

$(document).ready(function(){
 
    $(".t-Body-contentInner").prepend("<div id='MyClockDisplay' class='clock'></div>");
     showTime();
});

Inline :

body {
    background: black;
}
.clock {
    position: relative;
    left: 95%;
    transform: translateX(-50%) translateY(-50%);
    color: darkslategrey;
    font-size: 14px;
    font-family: Orbitron;
    font-weight: bold;
    letter-spacing: 4px;
}

Custom Authorization In Oracle Apex

 In this article we are going to discuss about Oracle APEX Authorization and Custom Authorization. Oracle Application Express (APEX) has two mechanisms for providing system security to be implemented on its application. The two mechanisms are authentication and authorization. Authentication mechanism  is implemented on login page. Authorization is used to create deeper security control. It might implemented on tab, page or region. The combination usage of authentication and authorization will create an application with confidence security.

On Oracle APEX we can create two kinds of authorization. We can create authorization with Access Control List (ACL) or with custom authorization.In this article we will discuss about custom authorization in oracle apex.

Page Level Authorization Query:

SELECT PAGE_NO FROM MENU_INFO WHERE MENU_NO IN(SELECT MENU_NO FROM MENUPREVS_DETAIL WHERE ROLE_NO=(SELECT USER_LEVEL FROM USER_INFORMATION WHERE LOGIN_NAME=:APP_USER)) AND

PAGE_NO=:APP_PAGE_ID;

New Feature of Oracle Apex 20.2

 

Cards Region

The new Cards Region is a lightweight report region, declaratively supporting customizations of layout, appearance, icon, badge, media and actions. Use cards to embed and share media sourced from BLOB column, URL or video in iFrame. Cards are useful for presenting a variety of information in small blocks. As Cards usually provide entry to more detailed information, you can include a number of actions as button or links declaratively.

Redwood UI

Universal Theme now supports a new Redwood Light theme style, available via Theme Roller. Refresh your existing apps to uptake the latest version of Universal Theme and this new theme style.

New and Improved Items

• New Checkbox item type. This single checkbox offers an alternative to the Switch item type for Boolean columns. Works in Interactive Grid as well, even when not in edit mode. The previous Checkbox type has been renamed to Checkbox Group.
• File Browse item type has been enhanced to support rendering as a drop zone, supporting the drag & drop of a file to be uploaded.
• Rich Text Editor item type has been upgraded to use CKEditor 5 and now supports markdown output.
• Text Field item type has a new Text Case setting to optionally transform the user-entered text to upper or lower case.
• The Text Field Trim Spaces and Text Case settings and Textarea Trim Spaces settings are now applied on the client as well as the server.

Faceted Search Enhancements

• Bar or pie charts of facet value counts. Quickly display a chart of facet value counts in a dialog or 'dashboard' area.
• Groups of checkbox facets for Boolean columns. Checking the facet will find records that match the 'yes' or 'true' value of the column.
• Input Field facet type supports comparing a user-entered value with the facet column. This enables faceted searches such as finding stores within a user entered number of miles or records where a column contains the user entered text.
• Performance optimization for distinct value facets.

Report Printing

• Built-in PDF printing and Excel download for Interactive Reports, Interactive Grids and Classic Reports. Make sure to enable PDF and Excel as additional download format in your region attributes. Users can change the page orientation and size in the download dialog.
• Interactive Report - Send E-Mail: All download formats can now be attached.
• Enhanced integration with BI Publisher.
• New APEX_REGION.EXPORT_DATA and APEX_DATA_EXPORT APIs to programmatically generate PDF, CSV, Excel, HTML, JSON and XML files.

Developer Experience

• Page Designer has been enhanced to support multiple tabs in the Property Editor pane, making it more efficient to access the attributes of a region.
• A new code editor has been implemented throughout the development environment, resulting in a greatly improved code editing experience. The improved editor includes enhanced code completion, syntax highlighting and vastly improved accessibility.
• The Embedded Code utility allows developers to inspect the SQL, PL/SQL and JavaScript contained within an APEX application. Having the ability to view the embedded code makes conducting tasks such as code reviews, security evaluations or application tuning, far more convenient. Code can be saved to the file system from the App Builder, or using the APEXExport utility.
• Quick SQL has been enhanced to support the saving of a model, and the automatic population of a Primary Key using column default

Automations

Automations are a sequential set of PL/SQL actions, triggered by query results. They are used to monitor data and then perform the appropriate action (examples are auto-approving specific requests and sending email alerts). An automation can be triggered on Schedule or on Demand, by invoking the APEX_AUTOMATION package. Query results can be derived from:

• Table or View, SQL Query or a PL/SQL function returning a SQL Query.
• Local Database or REST Enabled SQL
• REST Data Source (aka Web Source Modules)

REST Data Source Synchronization

APEX supports data synchronization from a REST Data Source (formerly known as Web Source Modules) to a local table. Synchronization can run either on Schedule or on Demand, by calling the APEX_REST_SOURCE_SYNC package. Developers don't need to build custom PL/SQL code in order to copy data from REST services to local tables; APEX provides this as a declarative option.

• APEX can generate the local target table automatically, based on the attributes of the REST Data Source.
• REST Source Data can be appended or merged to the local table. Replacing all local data is also supported.
• APEX components using the REST Data Source can be configured to use the local table instead.
• Technical details like HTTP request limits, commit intervals or delete methods for the Replace mode are configurable.

REST Data Source Connector Plug-Ins

The APEX Plug-In infrastructure has been extended to support Connector Plug-Ins for external REST APIs. This enables APEX to fully leverage REST API features like result pagination or server-side filtering, also for 3rd Party REST Services which are not ORDS or Oracle Fusion SaaS Services.

• The Developer creates a Plug-In of the REST Data Source type.
• The Plug-In code handles REST service-specific implementation details like the pagination style or how filters are passed to the REST API.
• When APEX invokes a REST Data Source (e.g. to render a report), the engine will invoke the Plug-In code and pass all relevant context information.
• The Plug-In code executes one or multiple HTTP requests and passes results back to the APEX engine.
• APEX processes the REST response received from the Plug-In.

New Web Credential Types

APEX 20.2 introduces new URL Query String and HTTP Header types for Web Credentials. This allows developers to use the secure and encrypted credential storage also for REST Services which expect e.g. an API key as part of the URL. APEX makes sure that such sensitive parts are not written to debug or execution logs.

A web credential can now be protected by providing a URL pattern. APEX will only use the Web Credential for URLs starting with the given pattern; otherwise an error message will be raised. To change the URL pattern, the secret part of the Web Credential needs to be entered again.

JavaScript Library Upgrades

• Oracle JET 9.1.0
• jQuery 3.5.1
• CKEditor 5
• Monaco Editor 0.20.0

Database Backup & Restore Using Data Pump

 1. First we need to create Database Directory.

   CREATE DIRECTORY DB_BACKUP  AS 'D:\DATABASEB_BACKUP';
   GRANT READ, WRITE ON DIRECTORY DB_BACKUP TO HPP(Schema Name);

2. Provide Export/Import Privilege to Schema User:

   GRANT DATAPUMP_EXP_FULL_DATABASE TO HPP;
   GRANT DATAPUMP_IMP_FULL_DATABASE TO HPP;

3.Database Export Command For Specific Schema:

 EXPDP   HPP/HPP@ORCL schemas=HPP  DIRECTORY=DATABASE_BACKUP                    DUMPFILE=MRHS.dmp LOGFILE=full_exp.log   FULL=NO;

4.Database Import Command For Specific Schema :

    If user Exist Than First Drop User: DROP USER MRHS CASCADE;

   impdp  system/sys@ORCL  DIRECTORY = DATABASE_BACKUP  DUMPFILE = MRHS1.dmp             remap_schema=HPP:MRHS LOGFILE=full_IMPDp.log

5.Database Export Command For Multiple Schema:

 EXPDP   HPP/HPP@ORCL schemas=HPP,HPP_TRAN  DIRECTORY=DATABASE_BACKUP                    DUMPFILE=MRHS.dmp LOGFILE=full_exp.log  ;

6.Database Import Command For Multiple Schema :

    If user Exist Than First Drop User: DROP USER MRHS CASCADE;

    impdp  system/sys@ORCL  DIRECTORY = DB_BACKUP  DUMPFILE = hrms_07032022_v1.dmp             remap_schema=hrms:hrms,hrms_tran:hrms_tran LOGFILE=full_IMPDp.log

Remove Success Message After Specific Time

 From Page properties go to==>Function and Global Variable Declaration

setTimeout(function() {

  $("#t_Alert_Success").remove();

}, 5000);

Auto Refresh Report After a Specified Interval In Oracle Apex

 

1.First we need to define Region Static ID. Example: Region Static ID is ATTENDANCE.

2.Go to page properties===>Execute When Page Loads 

//This will Auto-Refresh after 1 minutes
setInterval("jQuery('#ATTENDANCE').trigger('apexrefresh');", 100000);

Custom Authentication In Oracle Apex

Custom Authentication Function :

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; l_count NUMBER; BEGIN SELECT COUNT (*) INTO l_count FROM USER_INFORMATION WHERE UPPER (LOGIN_NAME) = UPPER (p_username); IF l_count != 0 THEN SELECT pwd INTO l_stored_password FROM USER_INFORMATION WHERE UPPER (LOGIN_NAME) = UPPER (p_username); l_password := f_Password (UPPER (p_username), p_password); IF l_password = l_stored_password THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END IF; END;

Creating Automated Database Backup

Automated Backups Creating in Oracle Apex

Oracle Apex 20.1 Introduce new feature Automated Backup and these Backups are performed as part of daily maintenance. The only application that have been created & modified will be backed up. To create an Automated Backup in Oracle Apex, Follow these step :

1.Go to  Oracle Apex Application, then click---Utilities =>Manage Backup.

2. Then click on the Create button to create an automatic backup for  current application.

3.After that click Backup button.

It will create initial backup of our Oracle Apex Application. If we make any changes in our application than it will create automated backup end of the day. This backup process will run every day and create automated backup of our oracle apex application.

Custom Template Create In Oracle Apex

Add This Code in Template Body:

<div style="text-align:center;color:green"><marquee onMouseOver="this.stop()" onMouseOut="this.start()">Developed By Oracle Apex & Database</marquee></div>

Add This Code in Global Page For Background Image :

<style>

#t_PageBody{background:url(#APP_IMAGES#kindajean.png); background-repeat: repeat;}

</style>

Dynamic Side Navigation Menu in Oracle Apex

Image File store outside of Oracle Database and show image into Apex Application

Directory Create :

grant execute on utl_file to HRMS;

CREATE OR REPLACE DIRECTORY STOCK_DIRECTORY AS 'C:\Program Files (x86)\Apache Software Foundation\Tomcat 9.0\webapps\i\IMAGE_STORE';

 

GRANT read, write ON DIRECTORY STOCK_DIRECTORY TO HRMS;

Create Procedure In Database :

CREATE OR REPLACE PROCEDURE HRMS.blob_to_file (p_blob      IN OUT NOCOPY BLOB,

                                          p_dir       IN  VARCHAR2,

                                          p_filename  IN  VARCHAR2)

AS

  l_file      UTL_FILE.FILE_TYPE;

  l_buffer    RAW(32767);

  l_amount    BINARY_INTEGER := 32767;

  l_pos       INTEGER := 1;

  l_blob_len  INTEGER;

BEGIN

  l_blob_len := DBMS_LOB.getlength(p_blob);

  

  -- Open the destination file.

  l_file := UTL_FILE.fopen(p_dir, p_filename,'WB', 32767);

  -- Read chunks of the BLOB and write them to the file until complete.

  WHILE l_pos <= l_blob_len LOOP

    DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);

    UTL_FILE.put_raw(l_file, l_buffer, TRUE);

    l_pos := l_pos + l_amount;

  END LOOP;

  

  -- Close the file.

  UTL_FILE.fclose(l_file);

  

EXCEPTION

  WHEN OTHERS THEN

   -- Close the file if something goes wrong.

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

    RAISE;

/* WHEN UTL_FILE.invalid_operation THEN dbms_output.PUT_LINE('cannot open file invalid name');

WHEN UTL_FILE.read_error THEN dbms_output.PUT_LINE('cannot be read');

WHEN no_data_found THEN dbms_output.PUT_LINE('end of file');

UTL_FILE.fclose(l_file);

 RAISE;*/

END blob_to_file;

/

Create Process To Insert Image  

DECLARE

l_blob BLOB;

VID NUMBER;

BEGIN

SELECT NVL(MAX(NVL(EMPLOYEE_ID,0)),0)+ 1 INTO VID FROM                      EMPLOYEE_INFORMATION_IMAGE ;

 

 select BLOB_CONTENT INTO l_blob FROM apex_application_temp_files

where NAME= :P7_IMAGE ;

blob_to_file(p_blob => l_blob,

p_dir => 'STOCK_DIRECTORY',

p_filename => VID||'.png');

INSERT INTO EMPLOYEE_INFORMATION_IMAGE(EMPLOYEE_ID,EMPLOYEE_NAME,DESIGNATION,JOINING_DATE,FATHERS_NAME)

VALUES(VID,:P7_EMPLOYEE_NAME,:P7_DESIGNATION,:P7_JOINING_DATE,:P7_FATHER_NAME);

END;

 

Delete Specific Row From Interactive Grid In Oracle Apex

1. Create a New Report Column =>>Type=>>Link

     Target  ======>>  javascript:void(null);

     Link Text ====>>  <span class="t-Icon fa fa-trash-o" aria-hidden="true"></span>

    Link Attribute =>>  id='&DEDUCTION_ID.' class="delete t-Button t-Button--danger t-Button--simple                                     t-Button--small" title="Delete Allowance : &DEDUCTION."

2. Create New Item In Report Region :

    Create Dynamic Action : Event ==>> Click ; Selection Type ==>> jQuery Selector;

                                            jQuery Selector ==>> a.delete

   Create True Action: Confirm;

   Create True Action: Set Value ==>> Set Type ==>> JavaScript Expression

                                                              JavaScript Expression ==>> this.triggeringElement.id

   Create True Action: Execute PL/SQL Code 

            PL/SQL Code: UPDATE DEDUCTION_SETUP SET STATUS=0  WHERE                                                         DEDUCTION_ID=:P43_DEDUCTION_ID AND BRANCH_CODE=:BRANCH_CODE;

   Create True Action : Refresh Report Region.

How can use Apex Collection In Oracle Apex

Insert Data Into Apex Collection :

begin

if not apex_collection.collection_exists('EMPLOYEE_COLLECTION') then

apex_collection.create_collection('EMPLOYEE_COLLECTION');

end if;

apex_collection.add_member (

p_collection_name=>'EMPLOYEE_COLLECTION',

p_c001=>:P2_EMPLOYEE_ID,

p_c002=>:P2_EMPLOYEE_NAME,

p_c003=>:P2_FATHERS_NAME,

p_c004=>:P2_MOTHERS_NAME,

p_c005=> :P2_DESIGNATION

);

end;

Data Show Into Report From Apex Collection :

SELECT SEQ_ID,c001 EMPLOYEE_ID,c002 EMPLOYEE_NAME,c003 FATHERS_NAME,c004 MOTHERS_NAME,c005 DESIGNATION 

from APEX_COLLECTIONS where COLLECTION_NAME='EMPLOYEE_COLLECTION' ; 

 

Insert Data Into Database From Apex Collection : 

IF apex_collection.collection_exists('EMPLOYEE_COLLECTION') THEN

DECLARE

VID NUMBER;

 BEGIN

  

    INSERT INTO EMPLOYEE_DETAILS(EMPLOYEE_ID,EMPLOYEE_NAME,FATHERS_NAME,MOTHERS_NAME,DESIGNATION)

    SELECT c001,c002,c003,c004, c005 FROM  APEX_COLLECTIONS where COLLECTION_NAME='EMPLOYEE_COLLECTION';

END;

    begin

    apex_collection.delete_collection(p_collection_name =>'EMPLOYEE_COLLECTION');

    end;

   

END IF;

Delete Apex Member From Apex Collection : 

if  apex_collection.collection_exists('EMPLOYEE_COLLECTION') then

begin

    APEX_COLLECTION. DELETE_MEMBER( p_collection_name => 'EMPLOYEE_COLLECTION' , p_seq =>:P2_SELECTED_ID);

end;

end if;

Oracle Apex & ORDS Installation & Apache Tomcat Server Configuration

Apex Configuration :

1. Run--------@apexins.sql sysaux sysaux temp /i/

2. Go To Apex Location Folder => Run--------@apex_epg_config.sql  D:/Oracle/apex/apex <parent of apex directory> (@apex_epg_config.sql)

3. @apxconf.sql
4. alter user anonymous account unlock;

5. @apxconf.sql

6. @apex_rest_config.sql
7. Unlock All Locked User (

                select username,account_status from dba_users  where username like 'APEX%';

                select username,account_status from dba_users  where username like  'ORDS%';

       OR

        select username,account_status from dba_users where lock_date is not null

                 and username like 'APEX%'; )

Run Procedure For ACL Create

 DECLARE

  ACL_PATH  VARCHAR2(4000);

BEGIN

  -- Look for the ACL currently assigned to '*' and give APEX_050100----------( Or APEX_050000)

  -- the "connect" privilege if APEX_050100 does not have the privilege yet.

 

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

 

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050100',

     'connect') IS NULL THEN

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

     'APEX_050100', TRUE, 'connect');

  END IF;

 

EXCEPTION

  -- When no ACL has been assigned to '*'.

  WHEN NO_DATA_FOUND THEN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',

    'ACL that lets power users to connect to everywhere',

    'APEX_050100', TRUE, 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');

END;

/

COMMIT;

DECLARE

  ACL_PATH  VARCHAR2(4000);

BEGIN

  -- Look for the ACL currently assigned to 'localhost' and give APEX_050100

  -- the "connect" privilege if APEX_050100 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS

   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

   

  IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_050100',

     'connect') IS NULL THEN

      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,

     'APEX_050100', TRUE, 'connect');

  END IF;

  

EXCEPTION

  -- When no ACL has been assigned to 'localhost'.

  WHEN NO_DATA_FOUND THEN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',

    'ACL that lets users to connect to localhost',

    'APEX_050100', TRUE, 'connect');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');

END;

/

COMMIT;

ORDS Configuration :

1. Go To ORDS Location=======>>D:\Oracle\ords

         Connect SQL Plus & Execute===>>EXEC DBMS_XDB.SETHTTPPORT(0);

2. Copy Image Folder From Apex Folder To Ords Folder.

3. java -jar ords.war instal

 Enter the location to store configuration data:C:\Oracle\OracleApp\apex\ords\cofig
Enter the name of the database server [localhost]:localhost
Enter the database listen port [1521]:1521
Enter 1 to specify the database service name, or 2 to specify the database SID1]:1
Enter the database service name:orcl
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Please login with SYSDBA privileges to verify Oracle REST Data Services schema.
Enter the username with SYSDBA privileges to verify the installation [SYS]: sys
Enter the database password for sys:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step:2
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:1
Enter the database password for APEX_PUBLIC_USER:
Confirm password:
   Enter 1 to specify passwords for Application Express RESTful Services database
   sers (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:1
   Enter the database password for APEX_LISTENER:
   Confirm password:
   Enter the database password for APEX_REST_PUBLIC_USER:
  Confirm password:
Enter 1 if you wish to start in standalone mode or 2 to exit [1]:1
Enter the APEX static resources location:C:\Oracle\OracleApp\apex\ords\images
Enter 1 if using HTTP or 2 if using HTTPS [1]:1

Internal Server Error (500):

alter system flush buffer_cache;

alter system flush shared_pool;

Apex ORDS Database Verification : (When Image Showing Problem then validate database) :

java -jar ords.war validate --database apex;

Apex ORDS 404 Error:(The request could not be mapped to any database. Check the request URL is correct, and that URL to database mappings have been correctly configured) :

1.SELECT username, account_status, created, lock_date, expiry_date FROM dba_users WHERE account_status != 'OPEN';

2.Then perform a bunch of “alter user xxx identified as xxxx account unlock”.

3.After you have open every necessary user account, perform the following in your console as administrator:

After Run Command(Java -jar ords.war setup)