Wednesday, July 13, 2016

Suppressing Space & Zero into date formating

TO_CHAR(sysdate, 'Month,YYYY')
Result: 'July ,2016'

TO_CHAR(sysdate, 'FMMonth,YYYY')
Result: 'July,2016'

TO_CHAR(sysdate, 'Month DD, YYYY')
Result: 'July 09, 2016'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2016'
See into above examples, when we use FM into format_mask parameter it removing/suppress the space and zero. If you use MON it won't has space, but if using Month is will be returning with right padding space.

Bugs Fixed in the Oracle APEX 5.0.4.00.12

Bugs Fixed in the Oracle APEX 5.0.4.00.12 Patch Set

Table 1 lists bugs fixed in the Oracle Application Express 5.0.4.00.12 patch set.
Table 1 Bugs Fixed in the Oracle Application Express 5.0.4.00.12 Patch Set
Bug NumberDescription
21437474Intermittent bug with Oracle Application Express SQL Workshop uploads - random errors
21517239Reopen cancelled modal dialog in Internet Explorer 11 results in disabled input fields
22110421Editing a workspace end user failed with internal error
22493656Upgrading Oracle Application Express 4.2 to 5.0.3 throws ORA-20001: Compile Error: "Package Body"
22532860APEX_JSON: XML to JSON conversion treats 0 as string and 0123 as number
23249353Classic report escaping: "Display As" for substitution taken from wrong column
23261092SSO: // in redirect path when SSO success URL registered for different host


ORACLE APEX 5.0.4.00.12 is released

Oracle Application Express Release 5.0.4.00.12 was first released on July 12, 2016.
This is a cumulative patch set for Application Express 5.0.0, Application Express 5.0.1, Application Express 5.0.2, and Application Express 5.0.3. The full download is available below.
English only : Download 
All Languages : Download

What is new in Oracle APEX 5.0.4

APEX Views

  • got an improved security check. They now look for read privilege on the apex-admin-role instead of full admin privileges.
  • APEX_DEBUG_MESSAGES finally lists the Call Stack where a message was written (level 9 debug, only)
  • new View APEX_INSTANCE_PARAMETERS

APEX Admin Read Role

  • new Role / Function to give more detailed access to APEX API functions and dictionary views:
    • user is wwv_flow.g_flow_schema_owner, SYS, SYSTEM
    • user has been granted APEX_ADMINISTRATOR_ROLE
    • user has been granted APEX_ADMINISTRATOR_READ_ROLE
    • user has been granted SELECT ANY DICTIONARY

CSS

  • some dramatic changes, mostly colors by a shade, like change a barchart border-color from #7977de to #7978de
  • I guess Shakeeb wanted to make everything look even more “crisp” ;-)

Debug

  • A first glance shows that debug messages have been added. I guess that goes hand in hand with security tightening the code itself.
Ref: http://www.oracle-and-apex.com/whats-new-in-oracle-apex-5-0-4/

Friday, July 1, 2016

APEX 5.1 How to manually process Interactive Grid data using PL/SQL

if you want to execute your own insert/update/delete or PL/SQL api calls for all the modified rows of an Interactive Grid you can do this with the "Save Interactive Grid Data / Interactive Grid Automatic Row Processing (DML)" process which gets automatically created when you make an Interactive Grid editable.

Here is an example if your Interactive Grid is based on EMP.

Settings for your Region:

Source > SQL Query =
  1. select empno, ename, deptno  
  2.   from emp  

Attributes > Edit > Enabled = Yes

Settings for your Save Interactive Grid Data process:

Settings > Set Target Type = PL/SQL Code
Settings > PL/SQL Code to Insert/Update/Delete =
  1. begin  
  2.      case :APEX$ROW_STATUS  
  3.      when 'I' then -- Note: In EA2 this has been changed from I to C for consistency with Tabular Forms  
  4.          insert into emp ( empno, ename, deptno )  
  5.          values ( :EMPNO, :ENAME, :DEPTNO )  
  6.          returning rowid into :ROWID;  
  7.      when 'U' then  
  8.          update emp  
  9.             set ename  = :ENAME,  
  10.                 deptno = :DEPTNO  
  11.           where rowid  = :ROWID;  
  12.      when 'D' then  
  13.          delete emp  
  14.          where rowid = :ROWID;  
  15.      end case;  
  16. end;  
The PL/SQL code is very similar to what you can use for Tabular Forms if you have defined a PL/SQL process which fires for a Tabular Form region. It will be executed for each modified row, the bind variable APEX$ROW_STATUS can be used to find out if the row has been inserted, updated or deleted and bind variables can be used to reference the value of all columns of the Interactive Grid. Please be aware, that the bind variables will still return a string, so be careful in case of numbers and dates.

Note: If your INSERT statement or your PL/SQL API call generates a primary key as it's the case with the ROWID, you have to assign the result to the primary key column again. In the above case I'm using a RETURNING clause to do so. This is important, because that primary key value is used to query the newly inserted data. Only in the case if the end user enters a primary key value then this is not necessary.

This is Post by Patrick on oracle community: https://community.oracle.com/message/13870402#13870402