Category: Oracle

Where to put the comma?

I spend most of my work programming Oracle SQL and PL/SQL. Ofcourse much time is consumed by maintaining existing code. Here it is essential the code is readable and many times I start with reformatting the code. Put it in a format that I like. This refactoring also helps me in understanding the code, it’s structure. So the reformatting is not wasted time. On the contrary!

But many times I note that my preferences differ from most(?) tools or programmers. For instance the immense important question¬†ūüėČ :¬†¬†Where to put the comma?

I find, in a continued statement the comma should always be at the beginning of a line and never at the end of the prior line.

Why? Simple! Just ask yourself the question: to what part of the code does the comma belong to? It is a character that indicates the next part is separated from the prior part. If there would be no next part, then there would also be no  comma!

Other advantages of the comma in the beginning:

  • If you would have to add a parameter value to a list, you only have to insert a new line, start with the comma¬†and code the value. Would you have your comma’s at the end of¬†lines, you would have to go to the end of the line, code the comma, insert a new line and only then code the value.
  • The “comma-at-the-end” style also has the disadvantage that when you compare two versions of a piece of code, you also get the¬† lines where only the comma was added; with the comma at the begin, you get only the inserted lines!
  • Often I generate code from some SQL statement. It’s much easier to add a comma at the begin of each new line, then figuring out if the last line has been reached …
select decode(rownum,1,' ',',') || column_name as stmt_part
from user_tab_columns
where table_name = upper(:table_name)
  • If I have to comment a line, I do not need to bother about whether I have to remove the “continuation comma” at the end of the prior line. And when I un-comment the line again… but that comma back.
  • You immediately see that the line is part of the statements on prior lines(s).
  • It eases the method of “vertical code reading”;¬† you need less time to read it because you don’t have to read the whole line, scroll horizontally or move your eyes to the end of the line (of these ever increasing monitors)

Code is not natural language. Or didn’t you notice the different punctuation: Statements ending with a semicolon and not a period? The indendation? Color coding? Camel case?Aligning statement parts? Brackets, braces and curly braces? Abbreviations? …

Let’s make it more easy and efficient to understand and maintain!

APEX: Who is doing what?

Ever wanted to know what a user in doing in Oracle APEX? Which pages he visited in his current session? What the values of his session variables are at this very moment? It’s possible! (I’m writing this while using APEX 5.1, but the info is also available in APEX 04.02 so the path to the option in APEX Development¬†might differ somewhat from your APEX version.)

Where are Dashboards?

In the first place you can find a lot of information in the Oracle APEX Development environment. Go to “Monitor Activity” and then select “Active Sessions”.

Or open de “Dashboards”, Click on “Activities” and click on the Arrow right of “Recent Logins”.

The data shown in these APEX pages is accessible thru views that exist in the APEX_nnnnnn schema’s. Be aware that some of these views have an implied selection of a so-called security_group_id which is retrieved at runtime, so trying to see the data from SqlDeveloper will only work

  1. if you have the proper authorisations
  2. if you are logged-in as the user of the parsing schema.

If the parsing schema user has sufficient privileges you can ofcource access the data from APEX SQL Workshop when logging in to the proper workspace.

And the views you should look for are:

  • apex_050100.apex_workspace_access_log
    Shows all the login info of sessions that are still available in the running APEX-engine. These might not be all active sessions! Oracle APEX has it’s own rules for cleaning up sessions, and ofcourse you can use time-out and possible your own cleaning-up.
    This view does not contain an APEX session id.
  • apex_050100.apex_workspace_sessions
    Shows the current sessions and contains an APEX¬†session id, which is important to be able to see the session variables. It also contains the session’s NLS settings like the territory.
  • apex_050100.wwv_flow_data_view
    This is the view where you can see in detail all the session variables of each session still available in the APEX runtime. For sake of security you should add a WHERE condition on the security_group_id, for instance¬† by limiting the rows to those where security_group_id =¬†v(‘FLOW_SECURITY_GROUP_ID’);

These views should give you sufficient data to link it to your own user tables so you can add application info like full names, email and phonenumber in case you have to contact a certain user.

Regarding the implementation: Since the views you are referencing are APEX-owned views, you should consider building your own application-owned views on top of these APEX-views and only using those application-owned views. Then in case you have to upgrade to a new APEX version you only have to change these three application-owned views.

Oracle Monday is not always 1

As some of you might have discovered, especially in the non-GB/US countries, in Oracle the result of


is not always 1. It depends on the settings of your client, not even of your database or server. If the client is set to territory FRANCE, it will be 1 indeed. But if the client is set to territory AMERICA, the same statement will result in 2!

To know what the client has been set to, you can execute this simple query:

select *
from nls_session_parameters;

To switch to another territory, just use these examples:

alter session set nls_territory = AMERICA;


alter session set nls_territory = FRANCE;

Compare the nls_session_parameters after each of these.

The nls_session_parameters do not show the first day of the week for that specific territory.

Likewise for the date of the first day of a week that a certain date is in. You have two options:

trunc(testdate, 'day')

results in Monday or Sunday depending on the client’s territory

trunc(testdate, 'iw')

always results in the Monday based on ISO definitions, independent of the client’s territory.

So if the client’s territory is FRANCE or THE NETHERLANDS, then both will result in the same date. But for territory AMERICA these two will differ.

Combining all this knowlegde, it is possible to create a statement that will always result in the same Monday no matter what territory, without using the NLS Session Parameters (as far as we know ūüėČ :

to_char(testdate, 'd') + (trunc(testdate, 'day') - trunc(testdate, 'iw'))

This should always return the weekday number based on the ISO definitions, i.e. Monday=1, Tuesday=2…Sunday=7.

In the next attachment you can find my SQL script with more interesting ways to apply this “territory offset” and even a supposed-to-be-test that runs forever, but I don’t understand why…


Oracle previous_day function

Oracle has a SQL function NEXT_DAY (date, char) which returns the date of the first weekday named by char that is later than the date. But Oracle does not have a PREVIOUS_DAY, for instance to get the Monday of the current week.

Now to get the Monday of the week a certain date is in, you can easily use TRUNC(date,’iw’). And you think perhaps that TRUNC(date,’day’) does the same? Well, that depends on your NLS_TERRITORY setting, because e.g. in America the first day of the week is Sunday, but in The Netherlands it is Monday!

So to get last Monday’s date is not such a great deal. How about Tuesday? Or any other day of the week?

I created next function PREVIOUS_DAY, which is the opposite of Oracle’s NEXT_DAY. It has the same parameters and is independent of the NLS_TERRITORY.

-- The counterpart of the standard Oracle NEXT_DAY function.
create or replace function previous_day
    (pi_date in date := sysdate
    ,pi_day  in varchar2 := to_char(sysdate,'dy','nls_date_language = english')
return date
    l_date    date;
    select (pi_date - level) as previous_day
    into l_date
    from dual
    where to_char((trunc(pi_date) - level),'dy','nls_date_language = english') = lower(substr(pi_day,1,3))
    connect by level <= 7;
    return l_date;
end previous_day;


Oracle export schema DDL

You can use the SQL/Developer wizzard for downloading DDL to a file. But you need to remember your preferences, select a file, assign proper name, etc. etc. etc.¬† And it’s not complete since you cannot select jobs to export (version 4.1), as we found out the hard way…

SQL/Plus to the rescue! Attached is a file I compiled based on several information on the internet. You only have to start a SQL/Plus session and run the file (rename it from qa_export_db_ddl.txt to qa_export_db_ddl.sql) with one parameter: The schema you want to be exported. Tadaaaa!

Download qa_export_db_ddl.txt here.

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy