What’s The Difference Between Oracle ROWNUM vs Oracle ROW_NUMBER?

Complete IT Professional - Mon, 2017-01-16 05:00
Have you seen ROWNUM and ROW_NUMBER in Oracle and wondered what the difference is? Learn what they are and the differences between Oracle ROWNUM vs Oracle ROW_NUMBER in this article. What Is Oracle ROWNUM? You might think that ROWNUM is a function in Oracle. However, it’s not a function. It’s a “pseudocolumn”. It acts like […]
Categories: Development

APEX 5.1 New Features

Denes Kubicek - Mon, 2017-01-16 01:57
APEX 5.1 ist endlich da. Oracle Application Express wird mit jedem Release besser und zieht immer mehr Entwickler weltweit in seinen Bann. Es ist einfach, einfache wie auch komplexe Applikationen auf Basis des Oracle Stacks zu entwickeln. Es macht sogar richtig Spaß !

Mit APEX 5.1 sind als wichtiges neues Feature die Interactive Grids mit dazu gekommen. Wir haben sehr lange auf eine moderne Möglichkeit gewartet, Excel - ähnliche Funktionen auf einer Webseite mit APEX zu implementieren. Jetzt ist es endlich soweit :) . Sogar Master-Detail-Detail-Detail-... Beziehungen sind umsetzbar, unsere Anwender werden sich freuen.

Darüber hinaus gibt es auch in vielen anderen Bereichen wichtige Neuerungen, die uns das Leben erleichtern. Gleichzeitig sind aber auch einige wichtige Dinge zu beachten, damit wir ein reibungsloses Upgrade durchführen können.

In diesem Kurs lernen Sie die neuen Funktionalitäten von Oracle Application Express 5.1 kennen, insbesondere wie Sie diese erfolgreich in der Praxis einsetzen.

Lernen Sie von und diskutieren Sie mit den weltweit bekannten Oracle APEX Experten:

Denes Kubicek, langjährige Projekterfahrung in den Bereichen Oracle und APEX mit multisite Applikationen, Preisträger des "Oracle APEX Developer of the Year 2008" Awards des Oracle Magazines, ein Oracle ACE Director und sehr bekannt im Oracle APEX OTN Forum für seine Beispielapplikation und

Dietmar Aust, erfahrener Oracle Consultant mit Spezialisierung auf Oracle APEX, Oracle ACE und aktiv in den OTN Foren zu APEX und Oracle XE, mit regelmäßigen Präsentationen auf den einschlägigen Oracle Konferenzen (DOAG, ODTUG, Oracle Open World).

Categories: Development

Announcing “Database Star Academy”

Complete IT Professional - Fri, 2017-01-13 20:03
All of the online courses I have created used to be on a separate site of mine called Software Developer Academy. These have now all been moved to a new section under my main site, called Database Star Academy. Read on to find out more. What Is Database Star Academy? It’s the name of a membership section on […]
Categories: Development

Oracle TO_MULTI_BYTE Function with Examples

Complete IT Professional - Fri, 2017-01-13 05:00
In this article, I’ll be covering the Oracle TO_MULTI_BYTE function, and look at some examples. Purpose of the Oracle TO_MULTI_BYTE Function The TO_MULTI_BYTE function is used to convert a character string from single-byte characters to multi-byte characters. Your database must contain both single-byte and multi-byte characters for this function to be useful. If there are […]
Categories: Development

Oracle COVAR_POP Function with Examples

Complete IT Professional - Thu, 2017-01-12 05:00
In this article, I’ll explain what the COVAR_POP function does and show you some examples. Purpose of the Oracle COVAR_POP Function The Oracle COVAR_POP function calculates the population covariance of a set of number pairs. What is a “population covariance”? Good question. Here’s an article that explains what the definition is. Just like the COVAR_SAMP […]
Categories: Development

My first blog post with

Dimitri Gielis - Mon, 2017-01-09 18:18

I’m trying to write this blog post with

Just like Martin I’m also searching for alternative ways to write blog posts. I don’t want to completely migrate my blog to a new platform, so I’m searching for a way to write in Markdown and deploy to Blogger.

Why Markdown?
Since our development of APEX Office Print (AOP) we also use Markdown for our documentation as it makes including code samples easier, it can be version controlled and overall it’s pleasant to write in. Whenever we deploy a new version, we publish the markdown as HTML. For example you see the result of our documentation here. SSjj… we like Markdown so much that we are even looking into supporting Markdown to write your template in AOP, next to Word, Excel and Powerpoint, but more on that in the February timeframe. :)

So I’m giving a try, it’s just another Markdown editor, but it can publish directly to Blogger. It allows me to include code samples like this:

l varchar2(100);
l := 'hello world';

Or if I want to reference somebody I can use a quote:

Oracle Application Express (APEX) changed my life. – Dimitri Gielis

And a list … for example the top 3 reasons I’m looking at a different way to blog:

  1. Faster to write a post
  2. Reuse my writings in different ways
  3. Easier to share code

So this post is really to try the different options of which should show up in Blogger after I hit the publish button.

Here’s an image which I plan to use in my next post:
enter image description here

If you see this and the post looks ok, my test went well :)

Categories: Development

What are the Differences Between a Primary Key vs Foreign Key?

Complete IT Professional - Mon, 2017-01-09 05:00
I explain what a primary key and foreign key is, as well as the differences between primary key vs foreign key in this article. What Is a Primary Key? What is the primary key definition?  A primary key is one or more columns in a table that are used to uniquely identify the row. When […]
Categories: Development

Oracle COVAR_SAMP Function with Examples

Complete IT Professional - Fri, 2017-01-06 05:00
In this article, I’ll explain what the Oracle COVAR_SAMP function does and show you some examples. Purpose of the Oracle COVAR_SAMP Function This function returns the covariance of a set of number pairs. What does that mean? Here’s an explanation of what a covariance is. This function, like the CORR and COVAR_POP function, can be run […]
Categories: Development

Oracle VSIZE Function with Examples

Complete IT Professional - Thu, 2017-01-05 05:00
In this article, I’ll explain what the Oracle VSIZE function does and show you some examples. Purpose of the Oracle VSIZE Function The VSIZE function returns the number of bytes of an expression using the internal representation. That sounds a lot like the LENGTH function, doesn’t it? I’ll explain the differences shortly after we cover […]
Categories: Development

PostgreSQL for Oracle Developers and DBA's

Gerger Consulting - Tue, 2017-01-03 16:16
PostgreSQL is not very well known among Oracle developers and DBA’s. Yet, the skill set of an Oracle developer or a DBA is easily portable to PostgreSQL.
There is no one better than Joshua D. Drake to teach you PostgreSQL and show you how you can migrate from Oracle to PostgreSQL.
Attend Joshua's free webinar and earn how you can benefit from PostgreSQL, the leading free and open source alternative to the Oracle Database.

About The Presenter:

Joshua D. Drake is an Open Source Consultant specializing in Linux and PostgreSQL. He has been consulting on PostgreSQL since 1996. Throughout his PostgreSQL career he has performed a variety of functions within the community including submitting a patch or two, releasing an O'Reilly book on PostgreSQL as well as being part of the sysadmins and advocacy teams. Further he ran a biannual conference series dedicated to PostgreSQL between the years of 2007 - 2011. He is former Director (2008-2016) for SPI (the non-profit for He is also a Founder, and current Director of United States PostgreSQL. Lastly, he is a core member of PgConf US.

About The Webinar:

Even if your organization is an Oracle customer. there are many use cases where PostgreSQL can be an affordable alternative to the Oracle Database. In this webinar, Joshua will discuss the in and outs, ups and downs of migrating from Oracle to PostgreSQL.

The presentation will include PostgreSQL differences, limitations and discussion of commercial needs such as custom features and support.

Attendees should plan on walking away with a comprehensive understanding of the differences of the two products and how to make PostgreSQL work best for their projects.

The webinar is free but space is limited.

Categories: Development

Singular vs Plural and Other Database Table Naming Conventions

Complete IT Professional - Mon, 2017-01-02 05:00
Are you wondering if you should name your Oracle database tables in the singular form or plural form? Learn what the preferred convention is and some reasoning behind it, as well as other database table naming conventions, in this article. Why Do We Need SQL Table Naming Conventions? You might be wondering why we need conventions or […]
Categories: Development

Oracle INSTRB, INSTRC, INSTR2, and INSTR4 Function with Examples

Complete IT Professional - Fri, 2016-12-30 05:00
In this article, I’ll explain what all of the variations of the INSTR function (INSTRB, INSTRC, INSTR2 and INSTR4) do, and show you some examples Purpose of the Oracle INSTR2, INSTR4, INSTRB, and INSTRC Functions These INSTR functions are variations of the basic INSTR function, which searches inside a string for a substring, and returns […]
Categories: Development

Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function with Examples

Complete IT Professional - Thu, 2016-12-29 05:00
In this article, we’ll look at the variations of the Oracle LENGTH function – LENGTH2, LENGTH4, LENGTHB, and LENGTHC. Purpose of the Oracle LENGTH2, LENGTH4, LENGTHB, and LENGTHC Function The purpose of these LENGTH function variants is the same as the basic LENGTH function – to find the length of a specified string. However, the […]
Categories: Development

Success Story: Version Control for PL/SQL

Gerger Consulting - Mon, 2016-12-26 15:43
It’s been a little over three months since we released Gitora 2.0 and the first success stories have started to emerge. Here is one of them:

Rhenus Logistics, the leading logistics company from Germany uses Gitora to manage their Oracle Database.
Rhenus IT uses both Java and PL/SQL to serve their users and customers. They have a team of about 10 PL/SQL developers. The team manages more than 20,000 database packages, views, functions, procedures, object types and triggers spread over 30+ database schemas.
Rhenus IT wanted to move to a continuous delivery environment in which they can be more agile and deliver solutions to the business faster. Managing the PL/SQL code was the hardest piece of the puzzle.
After experimenting with other solutions in the market, Rhenus decided to move forward with Gitora.

Gitora enabled Rhenus Developers to:
  • Use Git, the prominent open source version control system used by millions of developers.
  • Move their database code between development and various staging databases automatically.
  • Move code between source and target databases very fast because Gitora only executes differences between source and target databases, without comparing the code bases in both databases first (which can be very time consuming).
  • Enforce check-in, check-out of database objects at the database level.
  • Automate build process for the database code using Gitora API’s.
  • Implement an affordable continuous delivery solution compared to alternatives.
Michiel Arentsen, the System Architect at Rhenus who implemented the solution at Rhenus has started an excellent blog in which he writes about his Gitora implementation. We highly recommend you to check it out. Below are the list of blog posts he wrote which should be very useful to anyone who is currently implementing Gitora at his/her company:
Categories: Development

A Guide to the Oracle TRUNCATE TABLE Statement

Complete IT Professional - Mon, 2016-12-26 05:00
The Oracle TRUNCATE TABLE statement is a useful statement for removing data in Oracle SQL. I’ll explain what the statement does and show you some examples in this article. What Does the Oracle TRUNCATE TABLE Statement Do? The Oracle TRUNCATE statement, or TRUNCATE TABLE statement, removes all data from a table. It’s similar to the […]
Categories: Development

Oracle MEDIAN Function with Examples

Complete IT Professional - Fri, 2016-12-23 05:00
In this article, I’ll explain what the Oracle MEDIAN function is, and show you some examples. Purpose of the Oracle MEDIAN Function The MEDIAN function returns the median of the set of provided values. The MEDIAN is the middle value in a set of values. So, for example, you had these five values: 10, 15, […]
Categories: Development

APEX 5.1 - Client side messaging and apps not using Universal Theme

Anthony Rayner - Thu, 2016-12-22 09:42

In APEX 5.1, we are introducing new client side messaging functionality, which aims to provide more modern, faster messaging across APEX, including the capability to display both error and success messages from the client side, without requiring a full page reload (as is the traditional way in APEX). Messaging in APEX is something we have wanted to modernise for a long time, and in 5.1, precipitated by the introduction of the Interactive Grid and its requirement for such messaging, this comes to fruition. 

This new functionality works well with apps already using the Universal Theme, however apps using older, or custom themes may face some consistency issues in how inline item errors are displayed between pages that display messages the old way and the new way. The purpose of this blog post is to highlight when such inconsistencies may occur, and to provide guidance on how you can go about fixing them.

Note: If you have created your own custom theme, please see Method #2 for instructions on how to update your theme to work with client side messaging.

When can these inconsistencies occur?
Client side messaging for inline item errors will be used in the following scenarios:
  1. To display the error messages if an APEX validation error occurs, and the page attribute Reload on Submit is set to Only for Success (which is the default for new pages, irrespective of theme).

  2. Screenshot showing the new Reload on Submit page level attribute in APEX 5.1The new Reload on Submit page level attribute in APEX 5.1

  3. To display the error messages if a client side validation error occurs such as Value Required = Yes, and the app Compatibility Mode is set to 5.1 (default for new apps, irrespective of theme), a Submit Page button is used with set to Execute Validations = Yes.
Compatibility Mode, available in Application Attributes

So as soon as you start adding new pages to an existing app for example, you will start to hit these inconsistencies, because existing pages will have Reload on Submit set to Always, and new pages will have this set to Only for Success

In both of the above scenarios, an inline item error will be shown but it may not look exactly the same as on other existing pages. This is because as it is, the client side messaging logic isn't able to use the corresponding template markup from the theme because of some missing information (more on that in a moment), and instead relies on some generic fallback logic to display the error. We can see the inconsistency below. 

Here is an inline item error, rendered using server-side logic due to the page level attribute Reload on Submit being set to Always (default for existing apps).

Screenshot showing Inline item error display for Theme 21, when the page level attribute Reload on Submit is set to Always

And here is the same error, rendered on the client side due to the page level attribute Reload on Submit being set to Only for Success (default for new pages).

Screenshot showing Inline item display for Theme 21 in APEX 5.1 when the page level attribute Reload on Submit is set to Only for Success

The Known Issues page for 5.1 mention this issue, including a few suggestions for working around it.

Screenshot showing excerpt from known issues page detailing this issue. To read the full issue please follow the known issues page for 5.1 link, and search for 'client-side messaging issues in apps using old, or custom themes'

Now let's explore each of the suggestions in a bit more detail.

Method #1 - Disallowing client side validation

The first solution is described in the Known Issues as follows:
These differences can be avoided by setting Reload on Submit to Always, (please note the Interactive Grid feature requires this to be set to Only for Success and not using client side validation by either setting app Compatibility Mode to less than 5.1, or setting Execute Validations = No.Setting Reload on Submit to Always, or disallowing client side validation as described will of course resolve the inconsistencies, but are somewhat brute-force, with the negative side-effects that you cannot benefit from the new way, you are precluded from upgrading your compatibility mode and also by setting Reload on Submit to Always, this means you cannot use the Interactive Grid on that page.

This is good for a quick-fix, but does place limitations upon your app, which are far from ideal.

Method #2 - Updating your theme
A better way to resolve this, and one that doesn't limit you in the same way as detailed in Method #1, is to update your themes so that they work better with the new client side messaging. Specifically this involves an update to your theme's label templates. 

The reason that the client side messaging functionality is not able to use the markup from the label template, is because it relies on the following:
  1. The error markup being provided in the Error Template attribute of the label template.
  2. The #ERROR_TEMPLATE# substitution string being included in either the Before Item or After Item attribute of the label template.
Now we know what's required, let's look at an example using the standard Theme 21, and the Optional Label with Help label template. In this template, you will see the Error Display information is provided as follows:

Screenshot showing Theme 21 > Optional Label with Help label template before any changes. Shows an empty Error Template and error markup defined in the On Error Before Label and On Error After Label attributesTheme 21 > Optional Label with Help label template before any changes. Shows an empty Error Template and error markup defined in the On Error Before Label and On Error After Label attributes

Given the aforementioned rules, this clearly won't work, there is no Error Template defined, so the client side will need to resort to its fallback template, causing the possible inconsistencies.

So firstly, we need to move the error related markup into the Error Template attribute. However, this presents a slight problem, because the way it is currently, the On Error Before Label markup will be rendered exactly as described, before the label markup when an error occurs, and the On Error After Label markup similarly will be rendered after the label markup. This means that it will effectively wrap the label markup in the template, which is very difficult to replicate by switching to the Error Template approach (where the markup is defined in its entirety in a single place, and inserted into the template in a single place). Unfortunately this means we will need to compromise, and go with an approach that will be slightly different to how it looked prior to 5.1, however importantly this will be consistent between client side and server-side rendered messages now, and without the downsides of having to resort to Method #1.

So we move the combined error markup into the Error Template, as shown:

Screenshot showing Theme 21 > Optional Label with Help label template after moving the error markup into the Error Template attributeTheme 21 > Optional Label with Help label template after moving the error markup into the Error Template attribute

Note: We removed the line break because we no longer need to force a new line after the label as was the case before, and because the DIV will automatically start on a new line because it is a block-level element, so the error will appear on a new line after its preceding element.

The next part is to add the #ERROR_TEMPLATE# substitution string to the best place in either of the two attributes where it is supported in a label template, either the Before Item or After Item attributes. Let's go with the more common approach of displaying the error after the item, so simply add #ERROR_TEMPLATE# in the After Item attribute, as shown:

Screenshot showing #ERROR_TEMPLATE# substitution string added to the After Item attribute of the label template

You will need to replicate this in the different label templates in your app, and of course different themes will have slightly different implementations for label templates, but if you follow the rules laid out above, then it should be a fairly straight forward switch over. 

Screenshot showing Updated appearance following template change with the error displayed below the input, now consistent irrespective messaging typeUpdated appearance following template change, now consistent irrespective messaging type

Method #3 - Migrate to Universal Theme
This will almost certainly result in the most work, but will provide great benefit in the long run, and all these problems will automatically go away.

Screenshot showing Universal Theme Sample Application, available in your workspace via Packaged Apps > SampleUniversal Theme Sample Application, available in your workspace via Packaged Apps > Sample

This will fix these inconsistency issues, and would be the best thing you can do to also benefit from the huge improvements offered by Universal Theme over our legacy themes, but will most likely take the most time and should be considered as part of a migration project (rather than a simple change), so may of course not be feasible. To see our Migration Guide, specifically the section entitled Migrating from Other Themes for further information.

The new client side messaging of APEX 5.1 offers some great benefits over the server-side approach, but does represent some issues with inline errors and apps not using Universal Theme as detailed here. If you have time, converting to Universal Theme would definitely be the way to go, there are just such massive improvements to benefit from in terms of capability, accessibility, responsiveness, modern look and feel, and more, but this will of course take the most time and investment. If this is not feasible, we would recommend updating your existing themes as detailed here, to be able to provide a consistent look and feel, and still benefit from other new features in 5.1 as described.
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none} p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Helvetica; -webkit-text-stroke: #000000} span.s1 {font-kerning: none}
Categories: Development

Oracle SINH, COSH, and TANH Functions with Examples

Complete IT Professional - Thu, 2016-12-22 05:00
In this article, I’ll cover what the Oracle SINH, COSH, and TANH functions are, and show you some examples. Purpose of the Oracle SINH, COSH, and TANH Functions These three functions are used to calculate the hyperbolic sine, cosine, or tangent of a number: SINH: Calculate the hyperbolic sine of a number COSH: Calculate the […]
Categories: Development

What Is Professional Networking and Why Should I Do It

Complete IT Professional - Wed, 2016-12-21 05:00
Networking is a common term in the IT industry. It can have two meanings – the process of linking computers and other devices together so they can communicate, and the process of meeting and talking to other people to help your IT career. This article is about speaking to people – professional networking. What Is […]
Categories: Development

Oracle ABCS - Traversing Relationships, Conditional Navigation, Query and Update with JavaScript

Shay Shmeltzer - Mon, 2016-12-19 11:02

Oracle Application Builder Cloud Service (ABCS) lets you do a lot of things in a declarative way, however for more complex validation and conditional logic you might need to resort to some basic coding in JavaScript.

I ran into such a case with an application I was developing, and figured out that the code sample from that system will be a good way to illustrate some coding techniques in ABCS.

The application I was working on allows people to register to various events. Each event has a certain capacity, so if there are too many people registered to an event, we want the rest to be added to a wait list. For each record of a person registering, we keep a reference to the event they want to attend.  So the logic flow is:

  1. Check how many open spaces are available for the event we are trying to register for.
  2. If there is space in the event, save the new person data, and show a success message.
  3. If there isn't space, update the person "Waitlisted" field to be true, save the data, and show a message saying that the person is on the wait list. 

In the demo video below I'm showing how to:

  • Define declarative conditional flow of steps based on results from custom code
  • Traverse relationships between custom object through code
  • Execute a conditional query and run through the results from a custom object with code
  • Set the value for a property of a custom object through code

For reference here is the complete code from the sample:

require([    'operation/js/api/Conditions',    'operation/js/api/Operator'], function (Conditions, Operator) {    var lab = Abcs.Entities().findById('Lab');    var id = lab.getProperty('id');    //condition is to find a lab with the id that is in the page's drop down box    var condition = Conditions.SIMPLE(id, Operator.EQUALS, $Person.getValue('ref2Combo_Box'));    var operation = Abcs.Operations().read({        entity: lab,        condition: condition    });    //if query returned value we loop over the rows and check the capacity and registration columns    operation.perform().then(function (operationResult) {        if (operationResult.isSuccess()) {            operationResult.getData().forEach(function (oneRecord) {                if ((oneRecord.Capacity - oneRecord.Registered) < 1) {                    $Person.setValue('Waitlisted', true);                    reject("error");                } else                {                    resolve("ok");                }            });        }    }).catch(function (operationResult) {        if (operationResult.isFailure()) {            // Insert code you want to perform if fetching of records failed            alert('didnt worked');            reject("error");        }    });});

More information on the JavaScript APIs used here are in the Oracle ABCS Documentation.

Categories: Development


Subscribe to Oracle FAQ aggregator - Development