Oracle CURRENT_DATE Function with Examples

Complete IT Professional - Tue, 2016-06-14 06:00
The Oracle CURRENT_DATE function is another Oracle function for returning a date from the system. Find out what it does and how it’s different from the other functions in this article. Purpose of the Oracle CURRENT_DATE Function The CURRENT_DATE function is used to return the current date in the session timezone. The session timezone is […]
Categories: Development

8 queens chess problem: solution in Oracle SQL

XTended Oracle SQL - Sun, 2016-06-12 21:16

This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:

 t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(98+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
select n
from x
where l=&d

SQL> @tests/f
Size[8]: 8

a1 c5 d8 e6 f3 g7 h2 i4
a1 c6 d8 e3 f7 g4 h2 i5
a1 c7 d4 e6 f8 g2 h5 i3
a1 c7 d5 e8 f2 g4 h6 i3
a2 c4 d6 e8 f3 g1 h7 i5
a2 c5 d7 e1 f3 g8 h6 i4
a2 c5 d7 e4 f1 g8 h6 i3
a2 c6 d1 e7 f4 g8 h3 i5
a2 c6 d8 e3 f1 g4 h7 i5
a2 c7 d3 e6 f8 g5 h1 i4
a2 c7 d5 e8 f1 g4 h6 i3
a2 c8 d6 e1 f3 g5 h7 i4
a3 c1 d7 e5 f8 g2 h4 i6
a3 c5 d2 e8 f1 g7 h4 i6
a3 c5 d2 e8 f6 g4 h7 i1
a3 c5 d7 e1 f4 g2 h8 i6
a3 c5 d8 e4 f1 g7 h2 i6
a3 c6 d2 e5 f8 g1 h7 i4
a3 c6 d2 e7 f1 g4 h8 i5
a3 c6 d2 e7 f5 g1 h8 i4
a3 c6 d4 e1 f8 g5 h7 i2
a3 c6 d4 e2 f8 g5 h7 i1
a3 c6 d8 e1 f4 g7 h5 i2
a3 c6 d8 e1 f5 g7 h2 i4
a3 c6 d8 e2 f4 g1 h7 i5
a3 c7 d2 e8 f5 g1 h4 i6
a3 c7 d2 e8 f6 g4 h1 i5
a3 c8 d4 e7 f1 g6 h2 i5
a4 c1 d5 e8 f2 g7 h3 i6
a4 c1 d5 e8 f6 g3 h7 i2
a4 c2 d5 e8 f6 g1 h3 i7
a4 c2 d7 e3 f6 g8 h1 i5
a4 c2 d7 e3 f6 g8 h5 i1
a4 c2 d7 e5 f1 g8 h6 i3
a4 c2 d8 e5 f7 g1 h3 i6
a4 c2 d8 e6 f1 g3 h5 i7
a4 c6 d1 e5 f2 g8 h3 i7
a4 c6 d8 e2 f7 g1 h3 i5
a4 c6 d8 e3 f1 g7 h5 i2
a4 c7 d1 e8 f5 g2 h6 i3
a4 c7 d3 e8 f2 g5 h1 i6
a4 c7 d5 e2 f6 g1 h3 i8
a4 c7 d5 e3 f1 g6 h8 i2
a4 c8 d1 e3 f6 g2 h7 i5
a4 c8 d1 e5 f7 g2 h6 i3
a4 c8 d5 e3 f1 g7 h2 i6
a5 c1 d4 e6 f8 g2 h7 i3
a5 c1 d8 e4 f2 g7 h3 i6
a5 c1 d8 e6 f3 g7 h2 i4
a5 c2 d4 e6 f8 g3 h1 i7
a5 c2 d4 e7 f3 g8 h6 i1
a5 c2 d6 e1 f7 g4 h8 i3
a5 c2 d8 e1 f4 g7 h3 i6
a5 c3 d1 e6 f8 g2 h4 i7
a5 c3 d1 e7 f2 g8 h6 i4
a5 c3 d8 e4 f7 g1 h6 i2
a5 c7 d1 e3 f8 g6 h4 i2
a5 c7 d1 e4 f2 g8 h6 i3
a5 c7 d2 e4 f8 g1 h3 i6
a5 c7 d2 e6 f3 g1 h4 i8
a5 c7 d2 e6 f3 g1 h8 i4
a5 c7 d4 e1 f3 g8 h6 i2
a5 c8 d4 e1 f3 g6 h2 i7
a5 c8 d4 e1 f7 g2 h6 i3
a6 c1 d5 e2 f8 g3 h7 i4
a6 c2 d7 e1 f3 g5 h8 i4
a6 c2 d7 e1 f4 g8 h5 i3
a6 c3 d1 e7 f5 g8 h2 i4
a6 c3 d1 e8 f4 g2 h7 i5
a6 c3 d1 e8 f5 g2 h4 i7
a6 c3 d5 e7 f1 g4 h2 i8
a6 c3 d5 e8 f1 g4 h2 i7
a6 c3 d7 e2 f4 g8 h1 i5
a6 c3 d7 e2 f8 g5 h1 i4
a6 c3 d7 e4 f1 g8 h2 i5
a6 c4 d1 e5 f8 g2 h7 i3
a6 c4 d2 e8 f5 g7 h1 i3
a6 c4 d7 e1 f3 g5 h2 i8
a6 c4 d7 e1 f8 g2 h5 i3
a6 c8 d2 e4 f1 g7 h5 i3
a7 c1 d3 e8 f6 g4 h2 i5
a7 c2 d4 e1 f8 g5 h3 i6
a7 c2 d6 e3 f1 g4 h8 i5
a7 c3 d1 e6 f8 g5 h2 i4
a7 c3 d8 e2 f5 g1 h6 i4
a7 c4 d2 e5 f8 g1 h3 i6
a7 c4 d2 e8 f6 g1 h3 i5
a7 c5 d3 e1 f6 g8 h2 i4
a8 c2 d4 e1 f7 g5 h3 i6
a8 c2 d5 e3 f1 g7 h4 i6
a8 c3 d1 e6 f2 g5 h7 i4
a8 c4 d1 e3 f6 g2 h7 i5

92 rows selected.


It works quite fast:
8*8 ~ 0.1s
9*9 ~ 0.6s
10*10 ~4s

script for sqlplus
set arrays 1000;
col n for a80;
accept d prompt "Size[8]: " default 8;
 t as (select/*+inline*/ level i, cast(level as varchar2(2)) c from dual connect by level<=&d)
,x(l,s,n) as (
       select 1 l, c s, chr(97)||c||' ' from t
       union all
       select l+1, x.s||t.c, n||chr(98+l)||i||' '
       from x
            join t
                 on instr(s,c)=0
                    and not exists(select 0 from dual 
                                   where L+1 - t.i = level - substr(s,level,1)
                                      or L+1 + t.i = level + substr(s,level,1)
                                   connect by level<=length(s))
       where L<&d
select n
from x
where l=&d
col n clear;

Categories: Development

Enter your bets on now

Dimitri Gielis - Fri, 2016-06-10 05:55
Looks like I forgot to put on my blog also this year we created a bet site for the European Cup Soccer. Thanks to the people who reminded me to put this post on my blog :)

It all started in 2006 when I first created a site to promote Oracle Application Express (APEX). The site allowed to bet on the games of the World Cup. At that time everybody was using Excel files internally to put the scores together, enter the bets of the people... so I thought why not build it in APEX :) Oh the betting is for fun and honour ... so no money involved!

Since then every two years we have updated the site and enabled it again. Today almost 3000 people are playing with us. We changed a few times from url; first it was called DG Tournament, than the World Cup Challenge and this year it's the Euro 2016 Challenge.

So if you didn't put your bets in, there're a few hours left ... happy betting and that the best may win!

This year we (Belgium) have a chance to come far in the tournament, go go go Belgium! :)

Categories: Development

Oracle USERENV Function with Examples

Complete IT Professional - Thu, 2016-06-09 06:00
The Oracle USERENV function allows you to get information about the current session. Learn how to use the USERENV function in this article. Purpose of the Oracle USERENV Function The purpose of the USERENV function is to get information about your current session. It’s a simple function but can take a few different values for […]
Categories: Development

Export your APEX Interactive Report to PDF

Dimitri Gielis - Tue, 2016-06-07 10:36
Interactive Reports (and Grids in 5.1) are one of the nicest features of Oracle Application Express (APEX) as it allows an end-user to look at the data the way they want, without needing a developer to change the underlying code. End-users can show or hide columns, do calculations on columns, filter etc.

Here's an example of an interactive report where highlighting, computation and aggregation is used.

More than once I get the question, how can I export this to PDF or print this Interactive Report?

Here're 3 ways of doing this:

1. Use your browser to Print to PDF

The challenge here's that you would need to add some specific CSS to get rid of the items you don't want to be printed, e.g. the menu, the header and footer and some other components like buttons.
Also if you have many columns, they might not fit on the page and the highlighting is not working when printed, but if you can live with that, it might be an option for you.

Here's the CSS you can use:

@media print {

  .t-Body-nav {

2. Use the download feature of the Interactive Report itself (Actions > Download > PDF)

This feature is build-in APEX and relies on a print server supporting XSL-FO; when using ORDS it will automatically work. If you're using Apache, you will need to configure a print server like BI Publisher or Apache-FOP.

When downloading to PDF, the result looks like this:

The PDF contains the data and we can specify a header, footer and how the columns look like, but we lost many features of the Interactive Report; no highlighting, no computation or aggregation.

3. Use APEX Office Print to print the Interactive Report in your own template defined in MS Word.

One of the unique features of APEX Office Print is that it's tightly integrated with Oracle Application Express and that it understands Interactive Reports as the source of your data.

Here're the steps:

- Create your template in MS Word and add {&interactive} tag where you want the Interactive Report to be

- Give your Interactive Report a static id:

- Add the APEX Office Print Process Plugin to your page and specify the template and the static id: 

And here's the result: 

I'm biased as we created APEX Office Print (AOP), but I just find it awesome :)
In your Word template you just add one tag, that's it!

In all seriousness, we would really want to hear from you if this feature works for your Interactive Report. You can try AOP for free for 100 days. We're trying to be smart and are doing automatic calculations of the column width, but we probably can improve it even more. We introduced this feature with AOP v2.0 (MAR-16) and improved it in v2.1 (MAY-16).

Categories: Development

What Are Oracle SQL Analytic Functions?

Complete IT Professional - Mon, 2016-06-06 06:00
In this article, I explain what Oracle SQL analytic functions are, how they are different from other functions, and show you some examples. What Are Oracle SQL Analytic Functions? Analytic functions are functions that calculate an aggregate value based on a group of rows. A recent article I wrote on SQL aggregate functions mention that […]
Categories: Development

5.1 EA

Denes Kubicek - Sun, 2016-06-05 05:08
It is incredibly quiet around APEX release 5.1. Just a couple of days ago I was talking to Dietmar and we were asking ourselves when is APEX 5.1 going to be released because of our training schedule for the rest of the year. On Tuesday this week Markus visited the EA site and this is what he found there. Many exciting things to appear in the next versions. Read the details and you will get an idea about what is comming.
Categories: Development

New APEX Blog

Denes Kubicek - Sun, 2016-06-05 04:59
Markus Hohloch is a new blogger is the growing APEX community. He is a part of our APEX Experts team and he is blogging mainly in german. He has a couple of interesting postings. Have a look.

Categories: Development

Getting MultiPath Failed Status count using EM12c/EM13c Metric Extension

Arun Bavera - Thu, 2016-06-02 14:06
Customer has requirement to alert if the multipath count goes down from 4.

Command to get the count as privileged User:
multipathd list multipaths status | awk '{print $1"|"$4}'

Once you add this metric Extension as part of your Standard Template, only requirement is to have this executed as Privileged Named Credential  or give access to your normal user say 'oracle' the ability to execute multipathd restricting options in /etc/sudo or put that in a shell script in restricted folder and give access to execute only that shell script.


Categories: Development

Test Drive Oracle APEX 5.1 Early Adopter 1!

Patrick Wolf - Thu, 2016-06-02 10:30
It’s here! Oracle Application Express 5.1 Early Adopter 1 is available at and we are really looking forward to get your feedback (via the Feedback “Bubble” icon in the top right of the Builder)! A list of all new features … Continue reading
Categories: Development

Oracle DBaaS CookBook using EM12c : MyNotes

Arun Bavera - Thu, 2016-06-02 10:15

Cloud/Super Administrator Tasks (EM CLOUD ADMIN Role)

Step 1: Decide with Architects and Cloud decision makers about the Service Catalog offerings

Business Catalog->Technical Catalog -> Service Catalog ( May be Subset of Technical Catalog)

A company can provide the Bronze and Silver tier in the self-service model but choose to deploy the Gold and Platinum tiers using native Oracle Enterprise Manager 12c capabilities.
Platinum Tier, should be implemented in its own reference architecture. In some cases, companies elect to provision these tiers using Oracle Enterprise Manager 12c native capabilities, as opposed to self-service

Step 2: Configure Software Library

Step 3: Deploy the necessary plug-ins. See Section 3.2, "Deploying the Required Plug-ins"

Step 4: Install the Management Agent on unmanaged hosts
so that they can be monitored by Enterprise Manager. See Section 11.2.1, "Adding Hosts".

Step 5: Configure Privilege Delegation for Database Servers Setup > Security > Privilege Delegation Configuring Privilege Delegation Settings

Step 6: Define Named Credentials Setup > Security > Named Credentials. Defining Roles and Assigning Users
In a DBaaS implementation the configuration of the named and preferred credentials is critical. In larger implementations of DBaaS, the preferred credentials are vital especially when using automation using emcli or when using the cloud framework in conjunction to the lifecycle management pack.

Step 7: Creating Cloud Users and Roles Setup > Security > Roles.
Quotas are assigned at the role level and users should be assigned to the roles based on the desired limits.
                 MYPROJECT_SSA_DB_DEV_USER_ROLE [ EM_SSA_USER is assigned to this role, also EM_USER and Public ]


Step 8: Create Self Service Portal Users Setup >Security >Administrators.

Step 9: Defining Databases Zones Setup > Cloud > PaaS Infrastructure Zones
Define and create zones to satisfy the Reference Architecture Design.
              1. DataCenter- A - Primary [ MYPROJECT_PaaS_DBZone_Primary_A ]
              2. DataCenter -B - Standby [ MYPROJECT_PaaS_DBZone_STandBy_B ]
Can be based on Geography (East/West),Lifecycle(Prod/Dev) or Functional ( ERP, DB, Siebel etc)
Placement Policy is maximum constraint for CPU and Memory on these Hosts
Refer: Adding Hosts  and  Creating a PaaS Infrastructure Zone

DBaaS Administrator Tasks (EM_SSA_ADMIN)

Step 10: Define Database Sizes: [ Only emcli option ]
emcli create_database_size -name=Small -description="Small size database" -attributes="cpu:2;storage:2;memory:1;processes:350"
emcli create_database_size -name=Medium -description="Medium size database" -attributes="cpu:3;storage:3;memory:1;processes:500"
emcli create_database_size -name=Large -description="Large size database" -attributes="cpu:4;storage:4;memory:2;processes:700"

Step 11: Defining Database Pools Setup >Cloud > Database and select "Database Pools"
a) Database pool name (select a distinct name that describes purpose of the pool in this example: DataCenter-A Linux64-11202-SI,
b) Provide Global credentials for an Oracle named credential. In this case, the grid and root credentials are optional,
c) Select the Hosts and database homes that will define the pool,
d) Select the PaaS Infrastructure Zone, Platform, database configuration and database version to be installed in the pool.

Step 12: Request Settings Setup >Cloud > Database and select "Request Settings" ( All settings at self service level)
• The amount of time in advance the self service user can schedule a request.
• The maximum amount of time for which a self service user can retain the instance.
• The amount of time after the completed self service create requests will be purged from the repository.

Step 13: Quotas Setup >Cloud > Database and select "Quotas" ( all settings at Role Level)
Using the Quota, the cloud administrator is able to assign to roles the following:
1. The amount of memory all the users assigned to the roles can consume.
2. The amount of storage all the users assigned to the roles can consume.
3. The number of databases all the users assigned to the roles can request.
4. The number of schema requests all the users assigned to the roles can request.
5. The number of Pluggable database requests all the users assigned to the roles can request.

Step 14: Defining Service Templates for Self Service Provisioning Setup >Cloud > Database and select "Profiles and Service Templates"

NOTE: Provide as much as details in description this is what SSA_USERS see when they request
Cloud DBaaS User Tasks [EM_SSA_USER is assigned to this role, also EM_USER and Public]

Step 15: Using Self Service Portal Enterprise > Cloud > Self-Service Portal or If the user has only SSA then when he logins he gets SSA Portal only not the normal view



Categories: Development

Oracle NEXT_DAY Function with Examples

Complete IT Professional - Thu, 2016-06-02 06:00
The NEXT_DAY function is a handy date manipulation function provided by Oracle. In this article, I’ll explain how to use it and show you some examples. Purpose of the Oracle NEXT_DAY Function The Oracle NEXT_DAY function returns the date of the first weekday that comes after the specified date value. It’s good for working with […]
Categories: Development

What Are Oracle SQL Aggregate Functions?

Complete IT Professional - Wed, 2016-06-01 06:00
Oracle SQL aggregate functions are very useful, and are some of the most commonly used functions. Learn what Oracle SQL aggregate functions are and what they do in this article. What Are Oracle SQL Aggregate Functions? Aggregate functions are functions that allow you to view a single piece of data from multiple pieces of data. […]
Categories: Development

NoSQL for SQL Developers

Gerger Consulting - Tue, 2016-05-31 06:00
Oracle Developers! Want to learn more about NoSQL, but don't know where to start? Look no further.

Attend our free webinar by the venerable Pramod Sadalage. He'll answer all the questions you have about NoSQL but were too afraid to ask. :-) 

After all, he wrote the book.

 Sign up at this link. 
Categories: Development

Crowdsourced software development experiment

Dimitri Gielis - Thu, 2016-05-26 16:30
A few days ago I got an email about an experiment how to program with the crowd.

I didn't really heard about it before, but found it an interesting thought. In this experiment people will perform microtasks (10 minutes task), as a member of the crowd. People don't know each other, but will collaborate together. The system is distributing the work and supplies instructions. The challenge is in creating quality code that meets the specifications.

Job is still searching for some people to be part of the experiment, so I thought to put it on my blog, in case you're interested you find more details below and how to contact him.

Categories: Development

Oracle CURRENT_TIMESTAMP Function with Examples

Complete IT Professional - Thu, 2016-05-26 06:00
The CURRENT_TIMESTAMP function is one of the many time and date-related functions in Oracle. Learn how to use it and see some examples in this article. Purpose of the Oracle CURRENT_TIMESTAMP Function The Oracle CURRENT_TIMESTAMP function will display the current date and time. It’s displayed in the session time zone, which is the timezone of […]
Categories: Development

The river floes break in spring...

Greg Pavlik - Wed, 2016-05-25 19:37

Alexander Blok
 The river floes break in spring...
March 1902
translation by Greg Pavlik 

The river floes break in spring,
And for the dead I feel no sorrow -
Toward new summits I am rising,
Forgetting crevasses of past striving,
I see the blue horizon of tomorrow.

What regret, in fire and smoke,
What agony of Aaron’s rod,
With each hour, with each stroke -
Or instead - the heavens’ gift stoked,
From the Bush of Moses, the Mother of God!


Весна в реке ломает льдины,
И милых мертвых мне не жаль:
Преодолев мои вершины,
Забыл я зимние теснины
И вижу голубую даль.

Что сожалеть в дыму пожара,
Что сокрушаться у креста,
Когда всечасно жду удара
Или божественного дара
Из Моисеева куста!
 Март 1902

Please, use HTTPS for your APEX apps

Dimitri Gielis - Wed, 2016-05-25 17:07
Why use HTTPS?

When you Google this question you get many different answers, but this answer of Google Developers answers it for me in short (click the link for more details):
  • HTTPS protects the integrity of your website/APEX app
  • HTTPS protects the privacy and security of your users
  • HTTPS is the future of the web; many new technologies only work with https (for example Service Workers; you can read more about Service Workers and APEX in my presentation)
Industry going to HTTPS

Before websites had an HTTP portion and an HTTPS portion, which became active when you would login to the site, but nowadays everything is under HTTPS. Google will actually rank your site higher when it's using HTTPS. Look at the sites you visit; many of them will now use HTTPS as a default.

HTTPS on localhost

If you're developing locally, you don't really need HTTPS on localhost, but I still like to have that.
Here're the steps I did in Chrome on my Mac (OSX) to get the nice green lock when developing locally (works also with APEX Front-End Boost)
  • In the address bar, click the little lock with the X. This will bring up a small information screen. Click the button that says "Certificate Information."
  • Click and drag the certificate image to your desktop. 
  • Double-click it. This will bring up the Keychain Access utility. Enter your password to unlock it.
  • Be sure you add the certificate to the System keychain, NOT the login keychain. 
  • After it has been added, double-click it. 
  • Expand the "Trust" section. "When using this certificate," set to "Always Trust"
  • Close Keychain Access and restart Chrome, and your self-signed certificate should be recognized now by the browser.
HTTPS on your own server

For years I've been using SSL certificates ordered from Godaddy, but depending the certificate you get, it might not be that cheap. The APEX R&D website is a multi-site certificate - the same certificate is used for the APEX Office Print website.

But there's some good news... you can get SSL for free too (and it's very easy to do!), thanks to Letsencrypt. I used Letsencrypt to protect the APEX app/website for example.
Here's the Getting Started Guide from Let's Encrypt. This is the command I used (after installing the package):

./letsencrypt-auto certonly --webroot -w /var/www/euro2016 -d -d

If you're not yet on https with your APEX app/site, I would definitely recommend looking into it :)

Categories: Development

Formspider is coming to US

Gerger Consulting - Wed, 2016-05-25 02:56
Dear Oracle Developers,
We are opening a branch in US! As part of this effort, I will be in US between Jun 13 – July 4 to meet with organizations and developers who are interested in Formspider.
Formspider offers  interesting opportunities for freelancers, consulting firms, IT Departments and ISV’s. 
Please get in touch to find out how Formspider can help you grow your business, meet your goals and make it easier to develop cross platform applications.
I’ll be happy to arrange a meeting to talk to you.
Kind Regards,
Yalim K. Gerger
Categories: Development

Mobile Apps with PL/SQL

Gerger Consulting - Tue, 2016-05-24 09:25
We are happy to announce that the first mobil applications built with Formspider are now in production. You can download them from the Apple App Store using this link, or from the Google Play Store using this link.


Formspider enables PL/SQL developers to build cross platform mobile apps using only PL/SQL as the programming language.

Read the story of our Dutch client to learn more about Formspider Mobile and the possibilities it opens for Oracle PL/SQL Developers.
Categories: Development


Subscribe to Oracle FAQ aggregator - Development