OraNA :: Oracle News Aggregator

In your APEX application, you can define several default format mask to be used throughout the application, such as the Application Date Format or the Application Date Time Format. This eliminates the need to specify the format mask for each Date Picker item that you use. It helps in keeping the application at least consistent regarding the format mask used.When you want to do some validation in your forms using the Dates entered by the enduser, you might have a need for the Application Date Format.So instead of hard coding (using the same format mask as you entered):

 (more...)

Yesterday I wrote a blog on how you can highlight a record in a report. That blogpost can be found here.Learco Brizzi posted a comment on a different way of accomplishing the same thing. And there are probably a number of other possibilities to implement the same requirement.In this blogpost I want to show you how to do the same with a Dynamic Action. Why would you want to choose for a Dynamic Action? This is a more efficient way because you don't need to refresh the page or the region. This blogpost will use a report based (more...)

Yesterday I read a question which was posted as a reply to an earlier blogpost. The orginal blogpost was about how to make a Report Row clickable, instead of just the Edit column.The question was: "How do I keep the clicked row highlighted so I can keep it as context information?"To see a working example of the requirement, check out this sample pageIn this blogpost I will provide a step-by-step way of doing just that.For this to work properly, you will need a page with a report. In this blogpost page 9 will contain a (more...)

Yesterday I was showing a colleague how to use a Dynamic Action in an Interactive Report to do an update on the underlying table. To get this to work prior to APEX 4 you would need to write some javascript on the page as well as an Application Process. Mostly I used the method described by Roel Hartman a number of years ago.
Want to take a look at a demo first before the steps to take to make it work? It's right here.
For this example, I will use a simple table named TASKS:

create table TASKS
(
  id               (more...)

Can't believe it's almost two weeks ago. Together with Mark Rittman, Carl Dudley, Sten Vesterli, and Tanel Poder, I was invited to join in for the ACED Nordic Tour.During the Nordic Tour we visited Denmark, Sweden, Norway and Finland. Four countries in four days.I knew it was going to be a busy week, but didn't expect to be exhausted when I got back home on Saturday.On Monday I flew into Copenhagen, Denmark where I met up with Sten Vesterli and Ken Holmstykke Jensen from the Danish Oracle User Group. They were kind enough (more...)

Yesterday evening we had the annual "We went to OOW and this is what's new in the world we call Oracle" at AMIS. Starting around 5pm and ending around 10pm a lot of information was presented by several AMIS colleagues.My contribution to the evening was (amongst others) a little bit of new features in SQL and PL/SQL, only a little bit as there is too much to cover in just 20 minutes. It's the little things that make me happy. What is the little thing that I like?When you define a table you can assign a default value (more...)

One of things I don't like about Oracle SQL Developer is that if you are browsing and clicking on tables, it can automatically open the table on the right side of the IDE.
There is a simple way to switch that off, just heard how to switch this feature off. Navigate to the Preferences settings: Goto Database >> Objectviewer, and uncheck the tickbox labelled "Open Object on Single Click" And that's it.

Yesterday Bryn Llewellyn, Distinguished Product Manager, did a session called "PL/SQL Enhancements brought by the latest generation of database technology". The room was packed. For the upcoming release of the Oracle 12c database the enhancements were broken into three areas:

• Improved PL/SQL and SQL interoperability

  In the current version of the Oracle database you needed a Nested Table or Varray Type defined at schema level in order to be able to use it in the TABLE operator. This is no longer necessary, it can be a PL/SQL declared type.The types that can be used as bind variables has been (more...)

This will be my third time attending Oracle Open World and I'm really looking forward to the event.It is very hard to make a schedule, there are so many good session available. I thought I'd share my schedule, it might inspire you for your own schedule (or makes it harder to make a choice) and I can look at my schedule without having to login to the schedule builder. On a side note: Just before landing in San Francisco there was an announcement: "Place your chair in the upright, most uncomfortable position". I thought it was funny, seems like (more...)

When you use a custom template for your APEX application and you want to use an image on a button, you might encounter a small, trivial bug. Thanks to Anthony Rayner and twitter for a simple workaround.The Setting

For the custom theme that is used in the application that I'm working on, an Application Level Substitution string was created.You can define these substitution strings at the application level, available from the application home page.

In the section "Substitutions" you can create your own substitution string and substitution value pairs. In the screenshot below I created a substitution string (more...)

'COLLABORATE 12: Technology and Applications Forum for the Oracle Community’ is de grootste Oracle gebruikersgroep conferentie ter wereld en wordt jaarlijks georganiseerd in de Verenigde Staten. Het is hét evenement om bij te zijn wanneer je geïnteresseerd bent niet alleen in Oracle Applications (E-Business Suite, JD Edwards, Siebel, etc.) maar ook in Oracle Technologie in het algemeen, van ontwikkeling tot beheer.
Ook dit jaar zal een aantal Nederlandse Oracle experts een presentatie geven op deze conferentie: Roel Hartman (Logica), Alex Nuijten (AMIS), Arian Stijf (Arven) en Arnoud Roth (Inter Access). Aangezien niet iedereen in de gelegenheid is om naar (more...)

It can be quite frustrating when you are working on a database package and your session is terminated time and time again and leaves you clueless as too why this is happening. First of all, this is reproducible on an Oracle 10g Enterprise Edition - haven't tried other versions (yet).

SQL> @../utils/rel

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2. (more...)

With an Interactive Report you can choose whether you want the Actions Menu to be shown or not. This has to be done by the developer. Of course the customer wanted to have this based upon the existing authorization scheme. How can you solve this? Dynamic Actions to the rescue.
Go to the Interactive Report page where you want to base the Actions Menu upon the existing authorization scheme. And right click on the "Dynamic Action" in the tree view, and choose "Create".
In the wizard that follows choose the "Advanced" option (no screenshot). Provide a name for the Dynamic (more...)

This week I attended a webinar by Cary Millsap, organized by Red Gate. It was called "Real Developers DO use Tools" and was focussed on, well, tools. At the end of Cary's talk, James Murtagh showed a demo of their Schema Compare tool. The demo wasn't flawless, but he recovered nicely :) (note to self: don't do live demo's). And this - the demo, not the occuring error - triggered a memory that I wanted to write a blog on the Schema Compare tool.If you missed the webinar you can find it on the website of Red Gate here: (more...)

Finally I had some time to upgrade my Oracle XE database from version 10 to version 11, something I wanted to try out but could never find the time to do it. I wanted to try it out, to see if it all worked as advertised, usually it does. This time however I ran into some problems with the export and import of the APEX applications, good thing it was just on my sandbox database. The first step in the documentation (link at the bottom of this blogpost) is to log in as SYS and run the gen_inst.sql script (more...)

While it is very easy to create a "Form and a Report" on a single table (or view), just follow the wizard, for end users it is not always intuitive that they should navigate to the form page to remove the record. A nicer solution is the one described by Anthony Rayner in this demo page. This solution uses a number of -very simple- dynamic actions to include a little trashcan on the report page. In this blogpost I will not describe how to create the trashcan functionality in the report, this is already done by Anthony, but about how (more...)

For the first post of the year, one of my New Year's resolutions is to write more posts than last year which shouldn't be too hard, I wanted to collect some different ways of generating multiple records. Sometimes I need, for whatever reason, generate multiple records. These are a few different ways of doing so. This is not really "generating multiple rows", but just a simple way of having multiple lines of output. Just take a big table and limit the number of rows by using "where rownum <= ":

SQL> select 'Happy New Year' msg
  2    from all_objects
  3   where rownum <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

One of my favorite ways is to use the "Connect by level" trick:

SQL> select 'Happy New Year' msg
  2    from dual
  3  connect by level <= 10
  4  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

Another way is to use the MODEL clause (Oracle 10g). I still find the MODEL clause quite hard to read and understand.

SQL> select msg
  2    from dual
  3    model
  4   dimension by (0 d)
  5   measures (cast ('m' as varchar2(30)) msg)
  6   rules iterate (10)
  7     (msg [iteration_number] = 'Happy New Year')
  8  /

MSG
------------------------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

SQL>

And lastly the Recursive Subquery Factoring way (Oracle 11g)

SQL> with lots(r)
  2  as
  3  (select 1 r from dual
  4    union all
  5   select r+1 from lots
  6   where r < 10
  7  )
  8  select 'Happy New Year' msg
  9    from lots
 10  /

MSG
--------------
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year
Happy New Year

10 rows selected.

I know there are more ways of generating multiple rows as output, this is not meant as a complete list of all possibilities. This post was meant to sent a subliminal message to wish you a Happy New Year!

Now that the UKOUG annual conference in Birmingham is over, it's time to write my thoughts down. As this was the second time that I attended the UKOUG conference, I already knew that it is a big conference. Lots of great speakers and a very good agenda. On the agenda were very interesting session, sometimes making it very hard to choose which session to go to. Guess you can't complain about that. On the Sunday before the actual conference starts, the OakTable organized a special day. Originally I didn't plan to attending this day, as I was flying in in (more...)

When you create a "Report with Form", there will be an icon in the report which allows you to navigate to  the form page. Only when the user clicks the icon this navigation will take place. For the current project, this was not what they wanted. They wanted to click on the row instead of just the icon. This can be simply implemented using jQuery.
For this example we are going to use a "Report with Form" and modify it as described below.
All of our pages in the application have a page alias, the Report page alias is "EMP001" (more...)

Always a good idea to upgrade on a Friday.. not... In the past I did numerous upgrades to the latest APEX release, and never really encountered big problems. It was always very smooth, kudos to the APEX-team. If something went wrong it was mostly my own fault, which I will admit now. Because of the history of going very smoothly, I dare to do an upgrade on Friday morning. And it did go smoothly, the upgrade to APEX 4.1 ... until...
Until I started up the application, then I got this "Invalid Login Credentials:
This had me scratching my head (more...)