Oracle APEX Blogs - Jaydip Bosamiya - Oracle APEX Consultant

  apexpert.in  has just published a blog about OfficeJS to show preview of your document files in Oracle APEX using Low-code solution. Find our more and  click here  to read complete blog post. Hope that helps! Regards, Jaydip Bosamiya jbosamiya@gmail.com

apexpert.in has just published a blog about how you can build custom nested interactive report in Oracle APEX. Earlier, we have published couple of blogs about classic report as bellow. It is on the same line for Interactive Report. Nested Class Report Enhanced Nested Report Find our more and click here to read complete blog post. Hope that helps! Regards, Jaydip Bosamiya jbosamiya@gmail.com

apexpert.in has just published a blog on how you can integrate with Telegram in your APEX application. This is achieved using oracle pl/sql. Find our more and click here to read complete blog post. Hope that helps! Regards, Jaydip Bosamiya jbosamiya@gmail.com

apexpert.in has just published a blog on how you can use apex_util.get_since in your APEX application and pl/sql code to format dates. Click here to read complete blog post. Hope that helps! Regards, Jaydip Bosamiya jbosamiya@gmail.com

apexpert.in has just published a blog on how you can configure help option in your APEX application using third-party plugins and your custom tables. Click here to read complete blog post. Hope that helps! Regards, Jaydip Bosamiya jbosamiya@gmail.com

This blog is about how you can use two different authentication scheme in one single custom authentication. For example, if I wanted my users to be authenticated from my custom database table, but at the same time I want few admin users - who are my application's super users (I might want to enable few extra options to them) and they are already my Application Express Users (can be a developer or administrator) and I wanted to authenticate both of them in a single authentication function. So, here I will need to achieve two things, Authenticate Function First check if the user is authenticated via APEX user's credentials. If not, the user is authenticated via My Custom authentication function. Authorization Function Check if logged in user is a valid Application Express user If not, the user is a regular database user. Following is a sample code block for authentication function: function my_multi_auth(   p_username in varchar2,   p_password in varchar2...

Its been a while since my previous article. So, here is my first blog post of 2020. Oracle APEX has an out-of-box and declarative support to create RESTful Web Services using ORDS. Within no-time, you can build and publish your RESTful APIs. Use of ORDS has gradually increased with common standard across different technology strongly adopted via RESTful Web Services and JSON formatted input/output. With the flexibility of data sharing across the technology or within application, we must also think of how secure my RESTful APIs are. We do believe in strong security of our applications using Authentication and Authorization. But, many times we miss the level of security we should implement in our RESTful APIs. So, here is the very simple and basic steps to secure your ORDS RESTful APIs using OAuth2: We will use a default oracle.example.hr web service module to add OAuth2 for this blog. Navigate to SQL Workshop > RESTful Services. From the left side of tree - click on "Ro...

With lots of feedback and suggestions - which inspired me to dig further into Customized Nested Report and see how we can enhance this further. So, purpose of this blog is to highlight you how you can further enhance  Customized Nested Report . I have tried to add following new things to enhance custom nested reports capabilities. You can try and design your own nested report the way you want. These are just couple of examples that I have created for demo purpose. 3 & 3+ level of nesting with Classic Report Different templates of nested classic reports Classic Report with Badge Template Classic Report with Comments Template Download full application and try it in your own environment. Please note that, it requires default tables EMP & DEPT with some valid data. Working Demo 2 Level Nested Report 3 & 3+ Level Nested Report Nested report as Badge View Nested report as Comment view Please note that, application...

I have been came across an interested requirement ( which is very valid ) to add scrollbars on the top of the regions! I have seen many times when you want to scroll right to see further data of first few records, you will scroll vertically to get to the horizontal scroll-bars. And then you do horizontal scroll and then vertical scroll to go top of the region. Aren't you ??? :-) So, to ease the user interaction with IR - we should have a scrolling option somewhere on the top of the region ? - For Sure So, how can we add scrollbars on top of the report for an Interactive Report ? By default IR Fix the column headers to the page. This setting gives us an opportunity to add scroll-bars just below the column header as well. Just by adding following CSS line you should get a beautiful scroll-bars just below you header and it works really great as well. #MYIR .t-fht-thead{   overflow: auto !important; } I am also trying to find a way to add same function...

In continuation to my previous blog post about adding simple floating button . This blog is about how you can create a list template that will render as a Action Floating Button. Following are the steps: Add below CSS code to your page header or theme roller section - which ever is your preference. .zoom {   z-index:100;   position: fixed;   bottom: 45px;   right: 24px;   height: 70px; } .zoom-fab {   display: inline-block;   width: 40px;   height: 40px;   border-radius: 50%;   background-color: #009688;   vertical-align: middle;   text-decoration: none;   text-align: center;   transition: 0.2s ease-out;   box-shadow: 0 2px 2px 0 rgba(0, 0, 0, 0.14), 0 1px 5px 0 rgba(0, 0, 0, 0.12), 0 3px 1px -2px rgba(0, 0, 0, 0.2);   cursor: pointer;   color: #FFF; } .zoom-btn-sm > span{   margin: 10px; } .zoom-fab:hover {   background-color: ...

Inspired from Material APEX theme to have Floating button in my application. But, I also do not want to migrate from Universal Theme. So, following is a simple tweak - which will convert your existing button into a floating button within Universal Theme. Add following CSS into page header or in theme roller - which ever is your preference to use it. .float{   z-index:100;   position:fixed;   width:60px;   height:60px;   bottom:40px;   right:40px;   background-color: rgba(55, 76, 139, 0.81);   color:#FFF;   border-radius:50px;   text-align:center;   box-shadow: 2px 2px 3px #999; } .my-float{   margin-top:22px; } Open your existing button and make following changes Set CSS Classes as "float my-float" Change Button Template to "Icon" Set Icon as "fa-plus" That's it, start using Floating Buttons in your applications. :-) Working Demo Hope this helps! Regards, Jaydip Bosamiya jbosamiya@gmail.co...

I have been came across requirement for printing nested JSON in one of the application. Using APEX_JSON, there is no straight forward way - I can just print a cursor or ref-cursor to an output as nested JSON. So, I have to write a pl/sql block and manually build JSON output. Purpose of this blog is to share a PL/SQL block which can build a nested JSON. Following is one  sample PL/SQL block to print employee-manager-employee hierarchy. DECLARE   l_return clob;   l_curr_level number := 1;   l_counter number;   l_empno emp.empno%type;   l_ename emp.ename%type;   l_job emp.job%type;   l_sal emp.sal%type;   l_level number;   l_is_open boolean := false;   l_emp_r emp%rowtype;   l_cursor sys_refcursor; BEGIN   IF :P17_ROOT IS NOT NULL THEN     APEX_JSON.initialize_clob_output;          select *       into l_emp_r       from emp     ...

Oracle APEX Interactive Report (IR) has rich APIs to tweak, customize or control the user interactions. One of the API package I have recently used is APEX_IR . Purpose of this blog is to show you how you can re-use your IR run-time query and filters to build all custom reports. So, let's define a requirement - You have two regions in a page - First region with summary of Department wise salary total. Second region with all list of employees. Now, when I apply any filters to detail region (which will be IR) then, the data of summary region should also change. Create region 1 - summary region (classic report) with following query select    d.deptno,   d.dname,   sum(e.sal)   from dept d, emp e  where d.deptno = e.deptno    and e.empno in(select c001 from apex_collections where collection_name = 'P16_EMP') group by d.deptno, d.dname order by d.deptno Create region 2 - employee list region (IR) with f...

Oracle APEX 5 introduced a new feature called "Warn on Unsaved Changes" at page, item and button level. This is a great feature by enabling any unsaved changes to ask for user confirmation before leaving the page. Purpose of this blog is, if we have custom form element such as APEX_ITEM on a classic report or IR, will that by-default use this feature? Answer is No. You have to write code for that. I have seen there are several ways people have implemented this using custom code and logic. Now, here I have tried to use one of the APEX's JavaScript library function to achieve this. Following are the steps: 1. Set "Warn on Unsaved Changes = Yes" from page attribute section. 2. Declare a new variable under "Function and Global Variable Declaration" from page attribute section as below var gSerializeValue; 3. Create new dynamic action named "Initialize Warn on Unsaved Changes" as on page load event. 4. Add true action "Exe...

APEX Office Print (AOP) is a great document printing library for Oracle APEX. While using AOP to print IR or Chart regions into document, you may like to use "aop-region-as" attribute to inform AOP printing engine to print output in different formats. Following are the attribute values, you may set: client_svg:  This will create SVG object in the div. You may use it as {%region}. client_canvas:  This will create CANVAS object in the div. You may use it as {%region}. server_html:  This will put exact html of region inside a div. You may use it as {_region}. client_html:  This will put browser html of region inside a div. You may use it as {_region}. client_screenshot:  This will capture region screenshot rendered in client browser window. You may use it as {%region}. To use this tag, while you are setting regions to be printed into your AOP template, you provide static ID of that region. Along with that enter one of the following attribute into Cu...

Oracle JET Charts  is a beautiful chart library which has out-of-box support in Oracle APEX version 5 on-wards. Purpose of this blog is to introduce with a small tweak in Series Bar Chart. Problem When you use Series Bar Chart and if you have some series with NULL or ZERO value, the bar will not appear at all. This is correct behavior from the Charting way, but visually - you may find it difficult to detect the bar against a series. Below is a sample bar chart with ZERO values where you can see gaps between. Below is same bar chart but fixed the gap with very small bar where the value is ZERO. Solution Inside your series bar chart, you will find Axes, and select "Y" Change following options: Format = "Decimal" Decimal Places = 0 Step = 1 Working Demo Hope this helps! Regards, Jaydip Bosamiya jbosamiya@gmail.com

I came across a very strange issue with my SQL Developer. The connection bar is not opening/expanding/minimizing/maximizing. So, I was scared thinking that I have re-setup my SQL Developer. But, when I did a quick google search, I found that following steps should solved such issues: Go to Windows menu Click Reset Windows To Factory Settings It will take some time and your SQL Developer gets restarted with all your windows resettled. You should be able to get your connection bar back. Hope this helps! Regards, Jaydip Bosamiya jbosamiya@gmail.com

I have came across following URL - which shows how simply you can create jQuery Circular Progress Bar. https://webdevtrick.com/circular-progress-bar-css/ Based on provided information on above link, I have tried to implement same in Oracle APEX - Classic Report. I am sure we can integrate same in Interactive Report, List Regions or Regions with Dynamic PL/SQL. Following are the steps to build Pie chart kind information to show how many percentage of employees belongs to each of the department. 1. Create new classic report region with following query. select deptno, dname, loc, round((emp_cnt/emp_tot),2) circular_val from( select DEPTNO,        DNAME,        LOC,        (select count(1) from emp e where e.deptno = d.deptno) emp_cnt,        (select count(1) from emp) emp_tot   from DEPT d) 2. Now, change circular_val column and add following HTML code in HTM...