Find Missing Page numbers in oracle

This code will return the missing page no between 1801 to 2000


select min_a - 1 + level
     from ( 
        SELECT 1801 min_a,
          2000 max_a
        FROM APEX_APPLICATION_PAGES
        WHERE application_id= :app_id
        and page_id between 1801 and 2000
          )
  connect by level <= max_a - min_a + 1
    minus
 select page_id from APEX_APPLICATION_PAGES where application_id =:app_id
and page_id between 1801 and 2000

Oracle Apex Backend View list

select *
from apex_dictionary
where column_id = 0

or

select distinct apex_view_name
from apex_dictionary

  • apex_application_bc_entries
  • apex_application_list_entries
  • apex_application_page_ir_cgrps
  • apex_application_page_ir_comp
  • apex_application_page_rpt_cols
  • apex_application_temp_label
  • apex_application_trees
  • apex_application_web_services
  • apex_appl_developer_comments
  • apex_appl_page_ig_columns
  • apex_appl_page_ig_col_groups
  • apex_appl_plugin_attr_values
  • apex_rest_resource_modules
  • apex_team_bugs
  • apex_workspace_apex_users
  • apex_workspace_log_summary
  • apex_ws_data_grid
  • apex_application_breadcrumbs
  • apex_application_pages
  • apex_application_page_branches
  • apex_application_page_chart_s
  • apex_application_page_groups
  • apex_application_page_ir
  • apex_application_page_map
  • apex_application_supp_objects
  • apex_application_temp_list
  • apex_application_temp_report
  • apex_application_translations
  • apex_appl_load_table_rules
  • apex_appl_page_igs
  • apex_appl_page_ig_rpt_views
  • apex_team_features
  • apex_ui_defaults_tables
  • apex_workspace_group_users
  • apex_ws_data_grid_col
  • apex_applications
  • apex_application_nav_bar
  • apex_application_page_db_items
  • apex_application_page_ir_col
  • apex_application_page_ir_cond
  • apex_application_page_ir_pivot
  • apex_application_processes
  • apex_application_substitutions
  • apex_application_theme_styles
  • apex_appl_page_ig_rpt_aggs
  • apex_appl_page_ig_rpt_highlts
  • apex_appl_plugin_std_attrs
  • apex_appl_template_options
  • apex_ui_defaults_columns
  • apex_workspace_files
  • apex_workspace_groups
  • apex_ws_app_pages
  • apex_application_computations
  • apex_application_lovs
  • apex_application_page_charts
  • apex_application_page_ir_rpt
  • apex_application_page_val
  • apex_application_parent_tabs
  • apex_application_static_files
  • apex_application_supp_obj_chck
  • apex_application_supp_obj_scr
  • apex_application_temp_bc
  • apex_application_temp_page
  • apex_application_temp_popuplov
  • apex_appl_page_ig_rpt_computes
  • apex_appl_plugins
  • apex_appl_plugin_settings
  • apex_team_milestones
  • apex_workspaces
  • apex_workspace_access_log
  • apex_workspace_group_groups
  • apex_workspace_sessions
  • apex_workspace_session_groups
  • apex_ws_applications
  • apex_application_groups
  • apex_application_lists
  • apex_application_locked_pages
  • apex_application_page_chart_a
  • apex_application_page_flash5_s
  • apex_application_page_ir_pvagg
  • apex_application_shortcuts
  • apex_application_templates
  • apex_application_theme_files
  • apex_application_trans_map
  • apex_appl_load_table_lookups
  • apex_appl_page_calendars
  • apex_appl_page_ig_rpt_columns
  • apex_appl_template_opt_groups
  • apex_appl_temp_region_dp
  • apex_team_feedback_followup
  • apex_team_todos
  • apex_themes
  • apex_ui_defaults_groups
  • apex_ui_defaults_lov_data
  • apex_workspace_activity_log
  • apex_workspace_clicks
  • apex_workspace_log_archive
  • apex_application_all_auth
  • apex_application_caching
  • apex_application_items
  • apex_application_lov_entries
  • apex_application_page_ir_cat
  • apex_application_page_ir_grpby
  • apex_application_page_ir_sub
  • apex_application_temp_button
  • apex_application_trans_repos
  • apex_appl_concatenated_files
  • apex_appl_load_tables
  • apex_appl_page_ig_rpts
  • apex_appl_page_ig_rpt_filters
  • apex_appl_temp_page_dp
  • apex_appl_theme_display_points
  • apex_rest_resource_handlers
  • apex_rest_resource_templates
  • apex_team_feedback
  • apex_workspace_log_summary_usr
  • apex_workspace_static_files
  • apex_workspace_ui_types
  • apex_application_build_options
  • apex_application_page_buttons
  • apex_application_page_comp
  • apex_application_page_da
  • apex_application_page_flash5
  • apex_application_page_ir_pvsrt
  • apex_application_page_proc
  • apex_application_page_trees
  • apex_application_supp_obj_bopt
  • apex_application_tabs
  • apex_application_temp_calendar
  • apex_application_temp_region
  • apex_appl_page_ig_rpt_cht_cols
  • apex_appl_plugin_files
  • apex_appl_user_interfaces
  • apex_workspace_developers
  • apex_workspace_sql_scripts
  • apex_application_auth
  • apex_application_authorization
  • apex_application_page_da_acts
  • apex_application_page_items
  • apex_application_page_regions
  • apex_application_page_reg_cols
  • apex_application_page_rpt
  • apex_application_themes
  • apex_application_trans_dynamic
  • apex_appl_plugin_attributes
  • apex_appl_plugin_events
  • apex_rest_resource_parameters
  • apex_ui_defaults_attr_dict
  • apex_workspace_schemas

Oracle Apex 5.1 Error handling procedure with example

Apex error handling procedure.. we are commonly using raise application error only.. when system will through the oracle error code like ORA-20001. it will come before message.. so if we use the below code we will avoid the error code.


apex_error.add_error (
    p_message          => 'This custom account is not active!',
    p_display_location => apex_error.c_inline_in_notification );
	
	
 apex_error.add_error (
    p_error_code       => 'INVALID_CUSTOMER_ID',
    p0                 => l_customer_id,
    p_display_location => apex_error.c_inline_with_field_and_notif,
    p_page_item_name   => 'P5_CUSTOMER_ID' );
	
APEX_ERROR.ADD_ERROR (
    p_message          in varchar2,
    p_additional_info  in varchar2 default null,
    p_display_location in varchar2,
    p_region_id        in number,
    p_column_alias     in varchar2 default null,
    p_row_num          in number );
	
apex_error.add_error (
    p_message          => 'Invalid Customer ID!',
    p_display_location => apex_error.c_inline_with_field_and_notif,
    p_region_id        => l_region_id,
    p_column_alias     => 'CUSTOMER_ID',
    p_row_num          => l_row_num );
	

Uninstalling oracle apex

Please follow by the below steps and uninstall oracle apex in your oracle db.

  1. Open the cmd prompt and assign your apex  folder like below
  2. c:\> cd C:\dev_apex\apex (Place your apex setup folder here)
  3.  type the below command

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 6 14:07:26 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected.
SQL> @apxremov.sql

When the script executed without errors, you should see the following message at the end:

PL/SQL procedure successfully completed.

…Application Express Removed
SQL>exit

This is the process to uninstall oracle apex from your system.

 

uninstalling_oracle_apex

 

Display Date and time format in oracle apex using javascript

This code is show the current date and time

Date.prototype.getFromFormat = function(format) {
    var yyyy = this.getFullYear().toString();
    format = format.replace(/yyyy/g, yyyy)
    var mm = (this.getMonth()+1).toString(); 
    format = format.replace(/mm/g, (mm[1]?mm:"0"+mm[0]));
    var dd  = this.getDate().toString();
    format = format.replace(/dd/g, (dd[1]?dd:"0"+dd[0]));
    var hh = this.getHours().toString();
    format = format.replace(/hh/g, (hh[1]?hh:"0"+hh[0]));
    var ii = this.getMinutes().toString();
    format = format.replace(/ii/g, (ii[1]?ii:"0"+ii[0]));
    var ss  = this.getSeconds().toString();
    format = format.replace(/ss/g, (ss[1]?ss:"0"+ss[0]));
    var ampm = (hh >= 12) ? "PM" : "AM";
    format = format.replace(/ampm/g, (ampm[1]?ampm:"0"+ampm[0]));
    return format;
};
var time_var = $('#899_TIME');
var myVar = setInterval(myTimer, 1000);
function myTimer() {
    var d = new Date(); 
    var date = d.getFromFormat('dd-mm-yyyy hh:ii:ss:ampm');
    time_var.text(date);
    
} 

Interactive Grid and Display item background color highlight in oracle apex 5.1

If you want show columnwise highlighted color option, please use the below code.

1. first you need to update the static id in the interactive grid (ie:)

2. copy the below css code and paste in you apex inline css or you need to all place means update the code in common css file

Note : if you want to show the color position in 3 or 2 column you need to change the position like 3 instead of 5 or 6

#FGCharge_Bgcolor .a-GV-table tr td:nth-child(5).a-GV-cell,
#FGCharge_Bgcolor .a-GV-table th:nth-child(5),
#FGCharge_Bgcolor .a-GV-table tr td:nth-child(6).a-GV-cell,
#FGCharge_Bgcolor .a-GV-table th:nth-child(6){
background: #f38e00!important;
color: #fff!important;
}

#FGCharge_Bgcolor .a-GV-table tr td:nth-child(7).a-GV-cell,
#FGCharge_Bgcolor .a-GV-table th:nth-child(7),
#FGCharge_Bgcolor .a-GV-table tr td:nth-child(8).a-GV-cell,
#FGCharge_Bgcolor .a-GV-table th:nth-child(8) {
background: #007aff !important;
color: #fff !important;
}

3.If you want total row highlighted color option please use the below code

#FGCharge_Bgcolor .a-GV-table tr .a-GV-cell {
background: #f38e00!important;
color: #fff!important;
}

Negative Value Highlight Table Cell in Oracle Apex

		$(document).ready(function () {

			$('td').each(function () {

				var cellvalue = $.trim($(this).text());
				if (cellvalue.substring(0, 1) == '-') {
					$(this).wrapInner('');
				}
			});

		});
  • Copy the code and paste the code for Execute When Page Load or maintain custom js file
  • Write the inline css or custom css file, class name is red_color ie :
    		.red_color{
    			color:red;
    		}
    
  • Save the Page and run, you will get the negative value highlight

Oracle Apex 5.1 Installation Method

1- The installation requires

Before installing Oracle Application Express (Oracle APEX) you need to:

  1. Install Oracle Database (10g, 11g or 12c)
You can reference how to install Oracle Database 11g at:

2- Download Oracle APEX

To download, you need to log in with an account of Oracle, if not, you can register a free account.

3- Installing Oracle APEX

Decompress zip file you have downloaded in some position of hard disk drive. Here I decompress it at C:/DevPrograms.

Note: Your folder must be named apex. This is compulsory.
Open CMD and CD to apex directory:
Next, enter sqlplus statement in order to log in sqlplus (Note: Don’t open sqlplus directly. It should be through CMD after cd to apex directory).
Sign in to user sys, as sysdba.
Login successful:
Run script apexins.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--
-- Run script apexins.sql with parameters:
--
-- @apexins.sql tablespace_apex tablespace_files tablespace_temp images
--
-- Where:
-- tablespace_apex is the name of the tablespace for the Oracle Application Express application user.
-- tablespace_files is the name of the tablespace for the Oracle Application Express files user.
-- tablespace_temp is the name of the temporary tablespace or tablespace group.
-- images is the virtual directory for Oracle Application Express images.
-- (To support future Oracle Application Express upgrades, define the virtual image directory as /i/.)
--
@apexins.sql sysaux sysaux temp /i/
Wait for about 30 minutes until the above statement is completed.
The above script create some SCHEMA on Database, you can test by querying View ALL_USERS on SQLPlus:

4- Configuring APEX

You need log in SQLPlus again from CMD and sign in sys user as sysdba.

apex_egp_config.sql

This script will load the Application Express images into XDB and then configure a DAD for use by Application Express in the Embedded PL/SQL Gateway.
1
2
3
4
5
6
--
-- Running script apex_epg_config.sql with parameter:

-- @apex_epg_config.sql
--
@apex_epg_config.sql C:\DevPrograms

Unlock user anonymous:

1
2
3
-- Unlock user anonymous:
alter user anonymous account unlock;

apexconf.sql

Run script apexconf.sql
Used to perform the final configuration steps for Oracle Application Express, including setting the XDB HTTP listener port and Application Express ADMIN password.
1
2
3
4
5
6
7
--
-- Running script: @apxconf.sql
-- Setup password for ADMIN
-- Configuring XDB Http Listener port
--
@apxconf.sql
To user, you can use ADMIN by default, and email can be skipped.
To password, the system requires you to enter a strong password:
Password does not conform to this site’s password complexity rules.
  1. Password must contain at least 6 characters.
  2. Password must contain at least one alphabetic character (abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ).
  3. Password must contain at least one punctuation character (!”#$%&()“*+,-/:;?_).
  4. Password must contain at least one upper-case alphabetic character.
  5. Password must contain at least one lower-case alphabetic character.
Port default is 8080:
The configuration has been completed.

5- Running Oracle APEX admin

You have successfully logged into the system of management of Oracle APEX:

6- Getting Started with Oracle APEX programming

Next you can be ready with Oracle APEX programming, you can read document: