Take a few minutes to patch Oracle APEX 5.1

Dimitri Gielis - 4 hours 15 min ago
Yesterday a first patch set of Oracle Application Express (APEX) 5.1 has been made available to download.

one-of patches

If you encounter issues, you can ask for support and most likely a bit later a patch is made available through The APEX team is doing a great job with this.

For example some people using APEX Office Print had an issue which was caused by a bug in APEX_JSON (which we heavily use behind the scenes). The next day the APEX Dev Team already made a patch available (PSE 25650850).

patch set

Instead of applying those one-off patches, you can wait for a patch set which includes those one-off patches and more. If you didn't move to Oracle APEX 5.1 yet, you can just download the latest version which includes 5.1.1 immediately. 

There're many fixes for the Interactive Grid features, but next to that, many others as well, like for example login issues.

applying the patch set

If you're on Oracle APEX 5.1, search for patch 25341386. Unzip the file, stop the webserver, run @apxpatch, copy the images folder and start the webserver again.
About 2 minutes later you're on the latest version. 

Happy patching...
Categories: Development

Best Way to Write SQL in Java

Gerger Consulting - Wed, 2017-03-15 01:17

Many of us struggle writing SQL in Java. ORM tools such as Hibernate often generate SQL statements that result in poor application performance. Surely, there must be a better way to write SQL in Java.

Attend the free webinar by Java Champion Lukas Eder to learn the best way to write SQL in Java. Find out how you can use SQL as a type safe domain specific language in Java.

Sign up for the free webinar.

Categories: Development

ORA-01031 insufficient privileges Solution

Complete IT Professional - Fri, 2017-03-10 05:00
Are you getting the “ORA-01031 insufficient privileges” error? Learn what causes this error and how to resolve it in this article. ORA-01031 Cause The cause of the ORA-01031 error is that you’re trying to run a program or function and you don’t have the privileges to run it. This could happen in many situations, such […]
Categories: Development

Passing Values Between Pages in Oracle Application Builder Cloud Service

Shay Shmeltzer - Thu, 2017-03-09 12:50

A common use case for applications that have multiple pages is passing values between pages. For example you might want to pick up a specific record or value in one page and then use that as a parameter for a query in another page.

In the February release or Oracle Application Builder Cloud Service as part of the extension hook points that we provide, we added support for shared resources. These are JavaScript libraries you can add to your application - and that can be used across your app.

In the demo below I show you how you can use the built-in sample template for a shared resource to define a variable, and then how that variable is exposed in various places in the product through the expression builder allowing you to set its value in one page and use that value in another one.

Check it out:

Categories: Development

ORA-00937: not a single-group group function Solution

Complete IT Professional - Wed, 2017-03-08 05:00
Have you received an ORA-00937: not a single-group group function error? Learn what it is and how to fix it in this article. ORA-00937 Cause The cause of the ORA-00937 error is that a SELECT statement is trying to be executed, and the SELECT statement has an aggregate function (e.g. COUNT, MIN, MAX, SUM, or […]
Categories: Development

Business Logic in Oracle Application Builder Cloud Service

Shay Shmeltzer - Tue, 2017-03-07 16:35

As you start building more complex applications in Oracle Application Builder Cloud Service, you'll might need to define more complex interactions between objects or validations on rows of data or specific fields.

In the new version of ABCS that we rolled out in February we added these type of capabilities.

There are several things you could do with the new Business Rules section of ABCS

  • Triggers - allow you create logic that will be executed in specific CRUD events such as when you insert, remove or update a record.
  • Object Validators - allowing you to define checks across multiple fields in your object
  • Field Validators - allowing you to define a check on specific field values.
  • Object Functions - reusable functions that can be called from the other points

Note that these logic points will be running on the server side of ABCS. 

In the video below I demonstrate a couple of these capabilities. You'll learn how to update one object when a specific value is set in another object's field. You'll also see how to check whether a combination of values in fields in a record is valid.

Check it out:

<p> </p>

Here is a screenshot of the nice logic flow editor:

Categories: Development

60 SQL Interview Questions and Answers

Complete IT Professional - Mon, 2017-03-06 05:00
Are you going for a job where you need to know SQL, such as a Database Developer or Database Administrator? Brush up on your interview questions with this extensive list of SQL interview questions. This collection of interview questions on SQL has been collated from my experience with SQL and from various websites. It contains […]
Categories: Development

ORA-00907: missing right parenthesis Solution

Complete IT Professional - Fri, 2017-03-03 05:00
Did you get an ORA-00907: missing right parenthesis error? Learn what caused it and how to resolve it in this article. ORA-00907 Cause When working with Oracle SQL, all left parenthesis (the “(” character) must be paired with a right parenthesis character (the “)” character). If there are more left parentheses than right parentheses, then […]
Categories: Development

ORA-06502 PLSQL numeric or value error Solution

Complete IT Professional - Wed, 2017-03-01 05:00
Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article. ORA-06502 Cause The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things: A value is being assigned to a numeric variable, but the value is […]
Categories: Development

Updating the Oracle EM12c Agent Configuration Properties in Bulk

Arun Bavera - Tue, 2017-02-28 14:41
1. Goto Enterprise->Job ->Activity->Choose from ‘create Job’ list -> Agent Configuration Operation ->Click Go
Give job name and Add all the Agent Targets you want to modify
Goto Parameter tab and Add Custom properties or Change the existing listed properties values.
For example:
Name:     Log.log.level
Value:     ERROR
imageThis job does not require any credentials.
Click Submit:

Categories: Development

Oracle GROUP BY – The Complete Guide

Complete IT Professional - Mon, 2017-02-27 05:00
The Oracle GROUP BY clause is a powerful clause, especially when analysing large amounts of data. Learn what the GROUP BY clause is and everything you can do with it in this article. In this article, we will cover: What Is The GROUP BY Clause? Sample Data Examples of Aggregate Functions with GROUP BY Filtering […]
Categories: Development

ORA-06550 line N column N Solution

Complete IT Professional - Fri, 2017-02-24 05:00
Are you getting the ORA-06550 error when running an SQL statement? Learn the cause of this error and the solution in this article. Demonstration of the Error To demonstrate this error, I can run this code: CREATE OR REPLACE PROCEDURE testProcedure AS   textValue VARCHAR2(3); BEGIN   textValue := someOtherValue; END; If I compile this […]
Categories: Development

ORA-00933 SQL command not properly ended Solution

Complete IT Professional - Wed, 2017-02-22 05:00
Are you getting the ORA-00933: SQL command not properly ended error? Learn what causes it and how to resolve it in this article. ORA-00933 Cause You have run an SQL statement and have gotten this error: ORA-00933: SQL command not properly ended What causes this error? Most likely, the SQL statement you’re running has a […]
Categories: Development

APEX 5.1 New Features - neuer Termin

Denes Kubicek - Tue, 2017-02-21 00:33
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

Database Star Academy Membership is Now Open

Complete IT Professional - Mon, 2017-02-20 21:15
The Database Star Academy membership is now open! Here’s what you need to know. What Is the Database Star Academy Membership? It’s a membership site with a monthly fee, that gives you access to many different online video courses and PDF guides related to Oracle database development. What’s Included? As part of your membership, you […]
Categories: Development

Why Focused Learning Is Better For Your Career

Complete IT Professional - Mon, 2017-02-20 05:00
In this article, I’ll explain what I mean by “focused learning” and why it’s a good idea for your career. What is Focused Learning? When we want to learn something, we tend to research on it. We look for websites, articles, videos on the topic. Or we enrol in a university course, if it’s career […]
Categories: Development

How to speed up slow unicode migration of a table with xmltype columns

XTended Oracle SQL - Sun, 2017-02-19 18:46

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 ( for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute


to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);

5. Disable event 22838:


6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

Categories: Development

Script to get OMS ,Agent and DB Plugin Patch details

Arun Bavera - Sat, 2017-02-18 18:17



export OMS_HOME=/u01/middleware/oms
export PLUGIN_HOME=/u01/middleware/plugins/oracle.sysman.db.oms.plugin_12.

DT=$(date '+%m%d%Y')


echo "1.Getting the OMS system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the OMS DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}



export AGENT_BASE=/u01/app/oracle/product/agent12c
export AGENT_HOME=${AGENT_BASE}/core/
export PLUGIN_HOME=${AGENT_BASE}/plugins/oracle.sysman.db.agent.plugin_12.

DT=$(date '+%m%d%Y')


echo "1.Getting the AGENT system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the AGENT DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}

Categories: Development

Coming Soon: Database Star Academy Membership

Complete IT Professional - Fri, 2017-02-17 05:00
The Database Star Academy membership will be launching very soon. Read on to find out more. What Is The Database Star Academy Membership? Over the last couple of years, I’ve developed several video courses and PDF guides to help database developers improve their SQL skills and their career. Up until now, I’ve made these available […]
Categories: Development

Weekly Link Roundup – Feb 17, 2017

Complete IT Professional - Fri, 2017-02-17 00:39
Here’s a collection of interesting articles I’ve read this week. Articles I’ve Read Truncate 12c Jonathan Lewis writes an article about some improvements to the TRUNCATE statement in Oracle 12c, and how it relates to “on delete cascade”. THere’s a small example and a good explanation on how it works.   Step by Step […]
Categories: Development


Subscribe to Oracle FAQ aggregator - Development