Oracle APEX Consultant | Oracle APEX Experts

Suppressing Space & Zero into date formating

2016-07-13T00:49:00.001+05:30

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

2016-07-13T00:33:00.003+05:30

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 Number	Description
21437474	Intermittent bug with Oracle Application Express SQL Workshop uploads - random errors
21517239	Reopen cancelled modal dialog in Internet Explorer 11 results in disabled input fields
22110421	Editing a workspace end user failed with internal error
22493656	Upgrading Oracle Application Express 4.2 to 5.0.3 throws `ORA-20001: Compile Error: "Package Body"`
22532860	`APEX_JSON: XML` to JSON conversion treats 0 as string and 0123 as number
23249353	Classic report escaping: "Display As" for substitution taken from wrong column
23261092	SSO: // in redirect path when SSO success URL registered for different host

ORACLE APEX 5.0.4.00.12 is released

2016-07-13T00:30:00.002+05:30

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

2016-07-13T00:26:00.003+05:30

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/

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

2016-07-01T18:52:00.000+05:30

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 =

select empno, ename, deptno  
  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 =

begin  
     case :APEX$ROW_STATUS  
     when 'I' then -- Note: In EA2 this has been changed from I to C for consistency with Tabular Forms  
         insert into emp ( empno, ename, deptno )  
         values ( :EMPNO, :ENAME, :DEPTNO )  
         returning rowid into :ROWID;  
     when 'U' then  
         update emp  
            set ename  = :ENAME,  
                deptno = :DEPTNO  
          where rowid  = :ROWID;  
     when 'D' then  
         delete emp  
         where rowid = :ROWID;  
     end case;  
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

Dan McGhan's Oracle APEX Blog: Tutorial: Creating a Component Similar to Super LO...

2016-03-19T11:30:00.001+05:30

Dan McGhan's Oracle APEX Blog: Tutorial: Creating a Component Similar to Super LO...: I had a lot of fun contributing to Super LOV. Unfortunately, it's not being maintained much these days and it doesn't work so well i...

Oracle JavaScript Extension Toolkit (JET) is now open source

2016-03-04T18:50:00.000+05:30

Now Oracle JET is declared as open source .

Please check this link: Oracle JavaScript Extension Toolkit (JET)

Link: JavaScript and Oracle

The Oracle Database driver for Node.js has a new JavaScript layer

2016-03-04T18:41:00.000+05:30

Node.js driver for Oracle Database

This is the driver that allows folks using Node.js to tap into Oracle Database – a very powerful combination. Since starting at Oracle, I’ve focused more on this project than any other. In fact, most of the posts in this blog involve this driver in one way or another.
Now the driver is sporting a new JavaScript layer! This new layer will allow JavaScript to be used over C where it’s more advantageous to do so. Additionally, the new layer will help make it easier for folks in the JavaScript community to contribute to the project without having to know C.

This first release of the JavaScript layer adds connection request queueing (no more ORA-24418: Cannot open further sessions!) and we have some pretty cool features planned for future releases.

Links:

Wishing Happy Diwali

2015-11-11T20:12:00.001+05:30

Application Express 4.2.6 is now available

2014-09-27T18:19:00.001+05:30

Oracle Application Express Release 4.2.6.00.03 was released on September 24, 2014. This is a cumulative patch set for Application Express 4.2.0, Application Express 4.2.1, Application Express 4.2.2, Application Express 4.2.3, Application Express 4.2.4, and Application Express 4.2.5. The full download is available below.

Click the here to download the latest version of Oracle Application Express. Application Express 4.2.6 includes numerous bug fixes.

APEX Notes: RDS, The PluginRegion Display Selector - How I (Wo...

2012-02-03T16:40:00.000+05:30

APEX Notes: RDS, The PluginRegion Display Selector - How I (Wo...: Update For the update on ORA-06550 and ORA-06502 please see the end of the post. The day has come and I have decided to write the RDS...

APEX Notes: Region Display Selector - How I (Would) Like It

2012-02-03T16:34:00.000+05:30

APEX Notes: Region Display Selector - How I (Would) Like It: In APEX4 I welcomed very much the new region type - the Region Display Selector. The only thing I don't like is how it displays the regions....

Apex Tutorial - Form & Report sharing the same page

2011-09-26T16:19:00.000+05:30

Apex Tutorial - Form & Report sharing the same page

Oracle Application Express - New Features in Release 4.1

2011-09-17T11:42:00.001+05:30

Oracle Application Express
New Features in Release 4.1
	Error Handling Release 4.1 includes improved error handling and user-defined exception processing. You can now easily capture error messages generated by the Oracle database and replace the message text with user friendly text. For example, instead of an error message such as "ORA-00001 unique constraint (EMP_UK) violated" the developer can define a message such as "Employee Name must be unique".

Use of ROWID

Application Express now supports the use of ROWID for updates, inserts and deletes as an alternative to specifying primary keys. Previously you could only specific two primary key columns. Therefore, if your
table had more than two key columns then you could not use the default DML processes and had to manually write your own processes. Use of ROWID is now the default method for any new DML processes generated.

Data Upload
Developers can now easily add the capability for end-users to load spreadsheet data into existing tables within an application. The developer can utilize a wizard to create a collection of pages that allow the end user to upload a file or cut and paste data into a table. Additionally the developer can define lookup columns and data transformations. End users are then led through a runtime wizard which allows them to load the data, map the columns, see whether the data will be inserted or updated, and review the results.

Calendar

The calendar wizards have been enhanced to include the ability to create an Edit page as part of creating the calendar. Further calendars can now include drag and drop functionality which allows the end user to change the date and or time of a record by simply dragging it on
the calendar itself.

Websheets
Building on their initial introduction in Release 4.0, the look and feel of Websheets in Release 4.1 has been substantially improved and the controls redesigned to make it more intuitive for users. There are also new page section types, and enhanced data grid integration. Learn more >

Tabular Forms

Tabular Forms now support all validation types. Depending on the validation type you can use bind variable syntax, substitution syntax, or just specify the tabular form column name. These enhancements allow
developers to declaratively define complex validations instead of having to perform extensive manual PL/SQL coding.

Plug-Ins
Plug-ins enable developers to enhance the existing built-in functionality by writing PL/SQL components for various components. This release expands the plug-in functionality and introduces the ability to create plug-ins for authentication and authorization schemes. View and Download Plug-Ins >

Dynamic Actions

Dynamic Actions allow developers to declaritively define client-side functionality, without needing to master JavaScript and AJAX. With this release, a number of enhancements were added including the ability to define dynamic actions for buttons and also use dynamic actions to set multiple item values.

	Accessibility Release 4.1 includes dramatic improvement in the HTML generated by the Application Express engine, together with accessibility improvements in existing themes and HTML templates.
Mobile Applications Release 4.1 includes a number of key improvements to improve support for mobile frameworks, including form rendering without HTML tables.

New Browser Security Attributes in APEX 4.1

2011-09-17T11:26:00.002+05:30

Oracle Application Express (APEX) 4.1 added two new Browser Security attributes: Cache and Embed in Frames. The attributes can be found by navigating to Shared Components > Security Attributes > Browser Security (region). Clicking on the items’ labels reveals some great documentation. I couldn’t find any other documentation online to link to so I’ve copied the contents here for everyone to see:

Cache

Enabling the cache allows the browser to save the contents of your application's pages in its cache, both in memory and on disk. If a user presses the browser back button, the page will typically be loaded from the cache, not from the server. If the cache is disabled, the browser is instructed to not save page content and will request it anew from the server.
From a security perspective the cache should be disabled, so the browser does not store sensitive data and will always request pages if the URL changes. Otherwise, it may even be possible to go back in the browser history after a logout and see cached content of the former session.
Disabling the browser cache will also prevent subtle back button issues with pages that use partial page refreshes for example pages with Interactive Reports.
If this item is set to "Disabled", Application Express will send the HTTP header cache-control: no-store which instructs the Browser to not cache the page contents on disk or in memory.
Note: This feature requires modern browsers that support the HTTP header response variable "cache-control".

Embed in Frames

Use this attribute to control if the browser is allowed to display your application's pages within a frame:

Deny: The page cannot be displayed in a frame, regardless of the site attempting to do so.

Allow from same origin: The page can only be displayed in a frame on the same origin as the page itself.

Allow: The page can be displayed in any frame.

Displaying pages within frames can be misused with "clickjacking" attacks, when an attacker uses multiple layers to trick a user into clicking on a button or link on another page when they were intending to click on the the top level page. Thus, the attacker is hijacking clicks (and/or keystrokes) meant for their page and routing them to another page.
Note: This feature requires modern browsers that support the HTTP header response variable "X-Frame-Options".

The Embed in Frames attribute was preventing the plug-in from working correctly - it was set to “Deny”. As the plug-in uses iframes, this attribute’s value must be set to either “Allow from same origin” or “Allow”. After upgrading to APEX 4.1, existing applications will be set to “Allow” but new applications will be set to “Deny”. Perhaps “Allow from same origin” would have been a better choice for the new default?
At the end of the day, the new Cache and Embed in Frames security attributes are very powerful in that they can help make your applications more secure with very little investment. Also, the additional benefits of the Cache feature sound great. However, developers should be mindful of the impact these attributes can have on their applications. Always test!

Generating random numbers and strings in Oracle

2011-08-02T16:47:00.000+05:30

Do you know how to auto generate random numbers or strings in Oracle? Generating random numbers is required when there is a need to create a lot of data for testing purposes, or when we simply need to use a number to temporarily tag a process. It may also be necessary to generate random password strings of a fixed size--a very common requirement for websites that create and maintain logins for users.

Whatever the need, the fact is that Oracle provides us with a random number generator. This option is faster than writing your own random generation logic in PL/SQL as Oracle's internal processing logic is used. In addition, it can also be used to generate both character and alphanumeric strings.

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the script dbmsrand.sql available in the <ORACLE_HOME>/rdbms/admin directory.

The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.

VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.

STRING - generate strings in upper case, lower case or alphanumeric format.

The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
U - Upper case
L - Lower case
A - Alphanumeric
X - Alphanumeric with upper case alphabets.
P - Printable characters only. Providing any other character will return the output in upper case only.
The size of the string should also be provided as the second parameter.

Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.

INITIALIZE - Initialize the package to proceed with the number generation.

Provide a number (seed) as input to the routine.

SEED - Used to change the seed value. It is used in the internal algorithm to generate values. Setting this will

generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.

TERMINATE - Close the process of random number generation.

Examples:

Below are some examples of using the package.

E.g.: Generating a random number (positive or negative)

SQL> select dbms_random.random from dual;

       RANDOM
_____________
   1393936551

E.g.: Generating a random number between 0 and 1.

SQL> select dbms_random.value from dual;

        VALUE
_____________
            1

E.g.: Generating a random number from a range, between 1 to 1000.

SQL> select dbms_random.value(1,1000) num from dual;

          NUM
_____________
          611

E.g.: Generating a 12 digit random number.

SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

          NUM
_____________
 175055628780

E.g.: Generating an upper case string of 20 characters

SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD

E.g.: Generating a lower case string of 20 characters

SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
xpoovuspmehvcptdtzcz

E.g.: Generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.

SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB

E.g.: Generating an upper case alphanumeric string of 20 characters

SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

E.g.: Generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.

SQL> select dbms_random.string('P', 20) str from dual;

STR
___________________
*Yw>IKzsj\uI8K[IQPag

E.g.: Example for calling the dbms_random package and setting the seed for generating the same set of random numbers in different sessions. Please note that the same random numbers are generated in different sessions. Though I have found this to work on most accounts, in some cases, the first number generated was different in different sessions and the remaining were same. I recommend not using this option in any of production code until it is properly document by Oracle.

jaJA>declare
  2     l_num    number;
  3  begin
  4    l_num := dbms_random.random;
  5    dbms_output.put_line(l_num);
  6    dbms_random.seed('amar testing 67890');
  7    l_num := dbms_random.random;
  8    dbms_output.put_line(l_num);
  9  end;
 10  /
483791552
478774329

PL/SQL procedure successfully completed.

Conclusion

DBMS_RANDOM is a good utility and will find its way into lot of development projects, especially web based ones. However, this Package is not exhaustively documented. One should not use it just for the sake of it being there. Make sure that there is a true requirement or a necessity of random values before making use of this package. If you already have a custom code meant for the same purpose, check out the benefits that are available when using this package compared to your application.

The Principles of Good Programming

2011-08-01T15:47:00.000+05:30

The principles of good programming are closely related to principles of good design and engineering. The following programming principles have helped me over the years become a better programmer, and I believe can help any developer become more efficient and to produce code which is easier to maintain and that has fewer defects.
DRY - Don’t repeat yourself. - Probably the single most fundamental tenet in programming is to avoid repetition. Many programming constructs exist solely for that purpose (e.g. loops, functions, classes, and more). As soon as you start repeating yourself (e.g. a long expression, a series of statements, same concept) create a new abstraction.
http://en.wikipedia.org/wiki/Don%27t_repeat_yourself
Abstraction Principle - Related to DRY is the abstraction principle “Each significant piece of functionality in a program should be implemented in just one place in the source code.”
http://en.wikipedia.org/wiki/Abstraction_principle_(programming)
KISS (Keep it simple, stupid!) - Simplicity (and avoiding complexity) should always be a key goal.
http://en.wikipedia.org/wiki/KISS_principle
Avoid Creating a YAGNI (You aren’t going to need it) - You should try not to add functionality until you need it.
http://en.wikipedia.org/wiki/YAGNI
Do the simplest thing that could possibly work - A good question to ask one’s self when programming is “What is the simplest thing that could possibly work?” This helps keep us on the path towards simplicity in the design.
http://c2.com/xp/DoTheSimplestThingThatCouldPossiblyWork.html
Don’t make me think - This is actually the title of a book by Steve Krug on web usability that is also relevant in programming. The point is that code should be easily read and understood with a minimum of effort required. If code requires too much thinking from an observer to understand, then it can probably stand to be simplified
http://www.sensible.com/dmmt.html
Open/Closed Principle - Software entities (classes, modules, functions, etc.) should be open for extension, but closed for modification. In other words, don't write classes that people can modify, write classes that people can extend.
http://en.wikipedia.org/wiki/Open_Closed_Principle
Write Code for the Maintainer - Almost any code that is worth writing is worth maintaining in the future, either by you or by someone else. The future you who has to maintain code often remembers as much of the code, as a complete stranger, so you might as well always write for someone else.
A memorable way to remember this is “Always code as if the person who ends up maintaining your code is a violent psychopath who knows where you live.”
http://c2.com/cgi/wiki?CodeForTheMaintainer
Principle of least astonishment - The principle of least astonishment is usually referenced in regards to the user interface, but the same principle applies to written code. Code should surprise the reader as little as possible. The means following standard conventions, code should do what the comments and name suggest, and potentially surprising side effects should be avoided as much as possible.
http://en.wikipedia.org/wiki/Principle_of_least_astonishment
Single Responsibility Principle - A component of code (e.g. class or function) should perform a single well defined task.
http://en.wikipedia.org/wiki/Single_responsibility_principle
Minimize Coupling - Any section of code (code block, function, class, etc) should minimize the dependencies on other areas of code. This is achieved by using shared variables as little as possible. “Low coupling is often a sign of a well-structured computer system and a good design, and when combined with high cohesion, supports the general goals of high readability and maintainability”
http://en.wikipedia.org/wiki/Coupling_(computer_programming)
Maximize Cohesion - Code that has similar functionality should be found within the same component.
http://en.wikipedia.org/wiki/Cohesion_(computer_science)
Hide Implementation Details - Hiding implementation details allows change to the implementation of a code component while minimally affecting any other modules that use that component.
http://en.wikipedia.org/wiki/Information_Hiding
Law of Demeter - Code components should only communicate with their direct relations (e.g. classes that they inherit from, objects that they contain, objects passed by argument, etc.)
http://en.wikipedia.org/wiki/Law_of_Demeter
Avoid Premature Optimization - Don’t even think about optimization unless your code is working, but slower than you want. Only then should you start thinking about optimizing, and then only with the aid of empirical data.
"We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil" Knuth.
http://en.wikipedia.org/wiki/Program_optimization
Code Reuse is Good - Not very pithy, but as good a principle as any other. Reusing code improves code reliability and decrease development time.
http://en.wikipedia.org/wiki/Code_reuse
Separation of Concerns - Different areas of functionality should be managed by distinct and minimally overlapping modules of code.
http://en.wikipedia.org/wiki/Separation_of_concerns

jQuery Mobile - Anatomy of a Page

2011-06-20T15:45:00.000+05:30

The jQuery Mobile "page" structure is optimized to support either single pages, or local internal linked "pages" within a page.
The goal of this model is to allow developers to create websites using best practices — where ordinary links will "just work" without any special configuration — while creating a rich, native-like experience that can't be achieved with standard HTTP requests.

http://jquerymobile.com/demos/1.0a4.1/#docs/pages/docs-pages.html

Oracle Application Express 4.1 Early Adopter is now available

2011-05-29T10:25:00.000+05:30

On Friday 06.05.2011 the APEX Development Team had a Surprise for us: they published the first Early Adopter Version of Oracle APEX 4.1

Oracle Application Express 4.1 Early Adopter is now available
Click here to access the hosted site.
Our statement of direction is available here.

Oracle Application Express 4.1

Oracle Application Express 4.1 will focus on enhancement to existing functionality and additional capabilities to support applications running on mobile devices. Application Express 4.1 is planned to incorporate the following:

Development for Mobile Applications – Include themes and HTML templates suitable for smart phones and mobile devices.
Error Handling - Improve error handling and user-defined exception processing.
Tabular Forms – Continue to expand tabular forms validations.
Dynamic Actions – Allow dynamic actions to be defined for buttons and enhanced conditional processing.
Plug-Ins - Add plug-in support for additional components and enhance plug-in definitions.
Use of ROWID – Allow usage of ROWID for Automatic DML processing (as an alternative to identifying the PK columns).
Websheets – Improved user interface, new page section types, and enhanced spreadsheet / datagrid integration.
Data Upload - Enable end-users to upload data into an existing table (within an application).
Accessibility – Improve accessibility in existing themes and HTML templates.
Numerous functional and performance improvements.

Oracle APEX Consultant | Oracle APEX Experts

Suppressing Space & Zero into date formating

Bugs Fixed in the Oracle APEX 5.0.4.00.12

Bugs Fixed in the Oracle APEX 5.0.4.00.12 Patch Set

ORACLE APEX 5.0.4.00.12 is released

What is new in Oracle APEX 5.0.4

APEX Views

APEX Admin Read Role

CSS

Debug

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

Dan McGhan's Oracle APEX Blog: Tutorial: Creating a Component Similar to Super LO...

Oracle JavaScript Extension Toolkit (JET) is now open source

The Oracle Database driver for Node.js has a new JavaScript layer

Node.js driver for Oracle Database

Wishing Happy Diwali

Application Express 4.2.6 is now available

APEX Notes: RDS, The PluginRegion Display Selector - How I (Wo...

APEX Notes: Region Display Selector - How I (Would) Like It

Apex Tutorial - Form & Report sharing the same page

Oracle Application Express - New Features in Release 4.1

New Browser Security Attributes in APEX 4.1

Generating random numbers and strings in Oracle

DBMS_RANDOM package

Examples:

Conclusion

The Principles of Good Programming

jQuery Mobile - Anatomy of a Page

Oracle Application Express 4.1 Early Adopter is now available

Oracle Application Express 4.1 Early Adopter is now available Click here to access the hosted site. Our statement of direction is available here.

Oracle Application Express 4.1

Oracle Application Express 4.1 Early Adopter is now available
Click here to access the hosted site.
Our statement of direction is available here.