Oracle Apex & Database Solutions

Monday, April 11, 2022

Database Normalization with Example

Database Normal Forms :

Database Normalization: Normalization is the process of splitting or organizing data to reduce redundancy and avoid data anomalies.

Here is a list of Normal Forms in SQL:

1NF (First Normal Form)
2NF (Second Normal Form)
3NF (Third Normal Form)
BCNF (Boyce-Codd Normal Form)
4NF (Fourth Normal Form)
5NF (Fifth Normal Form)
6NF (Sixth Normal Form)

However, in most practical applications, normalization achieves its best in the 3rd Normal Form. The evolution of Normalization in SQL theories is illustrated below-

Database Normalization With Examples

Database Normalization Example can be easily understood with the help of a case study. Assume a video library maintains a database of movies rented out. Without any normalization in the database, all information is stored in one table, as shown below. Let’s understand the Normalization database with a normalization example with a solution:

Here you see the Movies Rented column has multiple values. Now let’s move into 1st Normal Forms:

1NF (First Normal Form) Rules: No multivalue attribute.

Each table cell should contain a single value.
Each record needs to be unique.

The above table in 1NF-

1NF Example :

TRIGGERS IN ORACLE FORMS

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object. There are different level trigger in oracle forms.

Block Processing Triggers: Block processing triggers fire in response to events related to record management in a block.

When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.
When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.
When-Database-Record Perform an action whenever Oracle Forms changes a record’s status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.

Master/Detail Triggers: Oracle Forms generates master/detail triggers automatically when a master/detail relation is defined between blocks.

On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master/detail relation.
On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master/detail relation because those records no longer correspond to the current record in the master block.
On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master/detail relation so that detail records are synchronized with the current record in the master block.

Message-Handling Triggers:

Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.

On-Error Replace a default error message with a custom error message, or to trap and recover from an error.
On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.

Query-Time Triggers:

Query-time triggers fire just before and just after the operator or the application executes a query in a block.

Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.

Interface Event Triggers:

Interface events trigger fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.

When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.
When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.
When-Image-Activated Initiate an action whenever the operator double-clicks an image item.
When-Image-Pressed Initiate an action whenever an operator clicks on an image item.
When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.
When-Window-Activated Initiate an action whenever an operator or the application activates a window.
When-Window-Closed Initiate an action whenever an operator closes a window with the window manager’s Close command.
When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.

Transactional Triggers:

Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.

On-Delete
On-Insert
On-Update
On-Logon
On-Logout
Post-Database-Commit
Post-Delete
Post-Insert
Post-Update
Pre-Commit
Pre-Delete
Pre-Insert
Pre-Update

Navigational Triggers:

Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers and When-New-Instance triggers. Pre- and Post- Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.

Pre-Form Perform an action just before Oracle Forms navigates to the form from “outside” the form, such as at form startup.
Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.
Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.
Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.
Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.
Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.
Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.
Post-Form Perform an action before Oracle Forms navigates to “outside” the form, such as when exiting the form.

When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.
When-New-Item-Instance Perform an action immediately after the input focus moves to a different item.

Validation Triggers:

Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occur in response to operator input, programmatic control, or default processing, such as a Commit operation.

When-Validate-Item
When-Validate-Record

What is the difference between CALL_FORM, NEW_FORM, and OPEN_FORM?

CALL_FORM: It starts a new form and passes control to it. It will open the requested form by keeping the parent form active but hidden.

New_Form: It' terminates the current form and replaces it with the indicated new form.

Open_form: It opens the indicated new form without replacing or suspending the parent form.

What are the sequences of firing triggers in forms?

The sequence of firing triggers in forms When forms open is

Pre-form
Pre-block
Pre-recorded
Pre-text-item
When-new-form-instance
When-new-block-instance
When-new-record-instance
When-new-item-instance

What is the canvas in oracle forms?

A canvas is a layer within a window where visual objects like interface items or graphics can be placed. Oracle forms support four types of canvas, as mentioned below:

1. Content Canvas (Default)
2. Tab Canvas
3. Toolbar Canvas
4. Stacked Canvas

Name the different triggers associated with Oracle Forms having a master-detail relationship.
The enlisted trigger gets created during the creation of the master-detail block:

ON-CHECK-DELETE-MASTER
ON-CLEAR-DETAILS
ON-POPULATE-DETAILS

Various types of reports include:

Tabular
Group Left
Group Above
Matrix-Minimum 4 Groups are required
Matrix with Group
Form Reports
Form Letter Reports
Mailing Labels Reports

There are three types of columns in Oracle reports. They are:

Formula Columns: Columns that can do user-defined calculations on values within other columns and return some value. Formula columns are used to calculate information dynamically.
Summary Columns: Columns can do summary computations like sum, average, etc. on values placed in the other columns.-Used for calculating summary information.
Placeholder Columns: Column for which data type or value can be set using PL/SQL.Used to hold some values at runtime.

Report trigger & execution sequence:

1. Before parameter form

2. After parameter form

3. Before report

4. Between Page

5. After report

Monday, November 15, 2021

Login User Image Display on Navigation Menu In Oracle Apex

1.Create application item name > 'IMAGE_ID',> Session State Protection > Unrestricted.

2.Create Application Processes > 'GETIMAGE',> Process Point > Ajax Callback, > PL/SQL Code >

begin for c1 in (select * from employee_information where unique_id = :IMAGE_ID) loop sys.htp.init; sys.owa_util.mime_header( c1.MIME_TYPE, FALSE ); sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( c1.PICTURE)); sys.htp.p('Content-Disposition: attachment; filename="' || c1.FILENAME || '"' ); sys.htp.p('Cache-Control: max-age=3600'); -- tell the browser to cache for one hour, adjust as necessary sys.owa_util.http_header_close; sys.wpg_docload.download_file( c1.PICTURE); apex_application.stop_apex_engine; end loop; end;

3.Create/Select navigation bar where you want to show image > User Defined Attributes 1.

<img style="width:25px;height:25px;border-radius:50%;cornflowerblue" src="f?p=&APP_ID.:0:&APP_SESSION.:APPLICATION_PROCESS=GETIMAGE:::IMAGE_ID:&APP_USER." alt=" ">

Tuesday, November 9, 2021

Oracle Apex 21.2 Installation & ORDS Configuration

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

2.@apxchpwd.sql

3.@apex_rest_config.sql

4. 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%'; )

For Oracle Database version 12c or later run the below script:

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'APEX_210100',
principal_type => xs_acl.ptype_db));
END;
/ BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'localhost',
ace => xs$ace_type(privilege_list => xs$name_list('connect'),
principal_name => 'APEX_210100',
principal_type => xs_acl.ptype_db));
END;
/ EXEC DBMS_XDB.sethttpport(0);

#unzip ords file :

copy the images folder from the apex directory and paste it to ords folder.

Download Link :

jdk-https://www.oracle.com/java/technologies/javase-downloads.html

Ords-https://www.oracle.com/database/technologies/appdev/rest-data-services-v192-downloads.html

Ords Run Command:

Set path=C:\Program Files\Java\jdk-18.0.2.1\bin
java -jar ords.war serve

java -jar ords.war --config D:\Oracle\ords\config serve

Internal User Unlock & Password change:

First need to log in Oracle sys user.

SELECT TO_CHAR(user_id),
first_name,USER_NAME,
last_name,
default_schema
FROM wwv_flow_fnd_user
WHERE user_name = 'ADMIN'
ORDER BY last_update_date DESC;

ALTER SESSION SET CURRENT_SCHEMA = APEX_190200;

begin
wwv_flow_security.g_security_group_id := 10;
wwv_flow_fnd_user_api.UNLOCK_ACCOUNT('ADMIN');
commit;
end;

UPDATE wwv_flow_fnd_user
SET web_password = 'Enter your password'
WHERE user_name = 'ADMIN'
AND user_id =1200348866127291;

What's New in Oracle APEX 21.2

1. Smart Filters : Smart Filters is a new search component that allows users to quickly narrow data down with filters suggestions or search items. In Smart Filters include -Powerful Search Bar, Search Suggestions, Suggestion Chips.

2.Progressive Web Apps : APEX apps can easily be defined as Progressive Web Apps (PWAs) to take advantage of advanced caching and improved performance. These apps can also be installed on smartphone of choice with device-specific installation instructions. Progressive Web Apps Include-
App Performance, Apex Application Instable, Custom Offline Page.

3. Universal Theme & UI Updates : The updated Reference App now provides live Template Option previews, documented CSS variables, and a new Theme Version menu in the header to quickly jump to Reference Apps for previous versions of the Universal Theme. New updates are- Greater Flexibility in Page Layouts, Model Dialog Drawers, Accessibility Improvements, Faster Page Rendering.

4. New & Updated Components :
1.Faceted Search Enhancements : Multi-Value Facets, Additional UI Controls,
Accessibility Improvements.

2. Update Alert and Confirm Dialogs : Customizable Dialogs, Support for Template Directives, Button Confirmation.

5.Geocoding and Map Items : Geocoding your addresses has never been easier, and you can even display a map as a page item . The best part is that you don't need an API key.

6.REST Catalogs and Improvements :
1. Search Catalogs Across Apps.
2. Easy Export and Import.
3.URL-based Updating.
4. Pagination Support for REST Services.

7. Improved Report Downloads, Subscriptions, and Printing :
1. Images in Report Downloads.
2. Download Dialog Improvements.
3.Interactive Report Subscriptions.

8. Developer Experience :
1.Improved Editors :Static File Editor, Improved Breadcrumb Editor, Run Larger SQL in SQL Commands (SQL Commands now supports more than 32k characters).

9. Email and Email Template Enhancements :
1. Email Support in Automations.
2.Copy Email Templates.
3. Inline Email Attachments.

10. Additional Features :
1.Data Packager.
2. Popup LOV Set Display Value.
3. New Date Picker 'Show-On' attribute.
4. New Modal Dialog Event.
5.Copyright Banner in App Exports.
6.New PL/SQL APIs.
7. New JavaScript APIs.
8.Build Apex Apps in 21 additional Language.
9. JavaScript Library Upgrades.

Oracle Apex Uninstall Process

Oracle Apex Uninstall :

First we need to go to Apex Folder & Connect Database Using SQL Plus Or CMD.
Then Execute These Command: @apxremov_con.sql OR @apxremov.sql

Sunday, September 19, 2021

Parent Page Refresh After Model Page Close

1. First we need to create Dynamic Action In Parent Page.

          Event : Dialog Close
          Selection Type : Region
          Region : Select Specific Region

Create True Action :
Action : Close Dialog

Create another True Action :
Action : Refresh
Selection Type : Region
Region : Select Specific Region

2. After That we need to create Process in Model Page .
      Name : PageClose(Give a meaningful Name )
      Type : Close Dialog
Server Side Condition:
      Type : Request is contained in Value
      Value :Shift_Update(Update Button Name)

Friday, July 30, 2021

Custom Chart & Custom Chart Color In Oracle Apex

From Chart Attribute In Advance Option

JavaScript Initialization Code Section add Following Code :
========================================

function (options){

options.dataFilter = function (data){

data.series[0].color = "green";

data.series[1].color = "red";

data.series[2].color = "blue";

data.series[3].color = "Yellow";

data.series[3].color = "gray";

return data;

};

// Add new 3D effect to existing chart style defaults

options.styleDefaults.threeDEffect = "on";

return options;

//options.styleDefaults = $.extend( options.styleDefaults, {threeDEffect: "on"});

// return options;

}

Classic Report Background Color Change (Based On Condition)

1. Create Dynamic Action

=> Execute JavaScript Code

Add Following Code in to Execute JavaScript Code Section:
=============================================

$('td[headers="ENTRY_STATUS"]').each(function() {

if ( $(this).text() === 'Weekend' ) {

$(this).closest('tr').find('td').css({"background-color":"rgb(37 90 185 / 68%)"});

$(this).closest('tr').find('td').css({"color":"black"});