APEXBlogs.info

Specjalista Oracle APEX - płatny staż !

Mon, 28 May 2012 09:50:29

Naszą ofertę kierujemy do osób, posiadających minimum podstawową wiedzę z zakresu Oracle Apex, traktujących swe zainteresowanie APEX-em jako ciekawą przygodę a nie ciężki obowiązek.

Niezbędne umiejętności/cechy:

ogólne zainteresowanie nowoczesnymi technologiami IT z naciskiem na technologie Internetowe i mobilne,
umiejętność posługiwania się językiem angielskim na poziomie komunikatywnym,
posługiwanie się SQL i PL/SQL,
samodyscyplina oraz umiejętność pracy w zespole.

Mile widziane:

umiejętność tworzenia projektów graficznych stron WWW,
znajomość standardów HTML, CSS,
umiejętność posługiwania się javascript, jQuery, Jasper Reports,
znajomość administracji Oracle.

Co oferujemy:

pracę przy realizacji projektów krajowych jak i zagranicznych opartych o technologię Oracle APEX,
darmowe szkolenia z zakresu Oracle APEX
rozwój pod opieką doświadczonych programistów
świetną atmosferę pracy możliwość pracy zdalnej (kandydat może być spoza Wrocławia),
wynagrodzenie na start - 500 PLN miesięcznie.
po przebyciu stażu możliwość otrzymania pracy na etacie

Jeżeli jesteś zainteresowany niniejszą ofertą to śmiało prześlij swoje CV na adres praca@dbe.pl.

Rozmowy z potencjalnymi kandydatami będziemy przeprowadzać zdalnie poprzez Skype.

Doctors...

Sun, 27 May 2012 15:38:25

This recent blog post by Seth Godin reminded me a lot of my introduction to Effective Oracle by Design of a few years ago. What was true then is still so true today...

Here is an excerpt from my book that mirrors what he just wrote:

I will use yet another analogy to describe how this book will present information. Pretend for a moment that the developer is instead a medical doctor and the application is the patient. There are many types of MD’s:

The emergency room (ER) doctor. They do “triage” – separating the hopeless from the ones that can be helped. Performing quick fixes to keep patients alive for as long as possible. They strive for short term band-aids to fix up the patient. They will take a patient with a heart attack induced by smoking, bad diet and no exercise and get them stabilized.

The operating room (OR) doctor. They get the patient after the ER doctor has triaged them and patched them up. They strive for long term fixes to keep the patient not only alive but as fully functioning as possible. They perform the by-pass operation on that heart attack attempting to clear the arteries.

The physical therapist (PT). They get the patient after the operating room doctor is finished and begin a long and painful (not to mention expensive) process of rehabilitation.

The preventative medicine doctor. They strive to avoid the above three doctors at all costs. They counsel the patient to quit smoking, eat a healthy diet, and exercise – developing a phased plan to get them in shape. If they do their job right – with the exception of unfortunate accidents (like a car accident), the patient will never see the ER, OR or PT doctors.

Now, the world needs all types of doctors – accidents do happen after all. But one of the most important types of doctors is that last one, the preventative medicine doctor. The one that tries hard to avoid having their patient need the other three.

It is my belief (experience) that most people and books approach tuning using the mindset of the first three doctor types above. They are in support of the hero developer; e.g. the ER or OR doctor. Perhaps that is partially due to my observation that pre-emptive good design and implementation is mostly a thankless exercise. These developers seem to get all of the fame as they snatch the patient from the grasp of death (save the system by doing something miraculous). They get called in at the last moment; work horribly hard for an extended period of time trying to keep the patient alive (and get paid handsomely as well). The physical therapists are the unlucky souls that get the system after the ER/OR doctor has patched it up. They are the ones responsible for keeping this system going.

I feel I am well equipped to speak from that perspective. I am in fact one of those “heroes”. I am called in to “lay hands on” systems and make them better. I could write that book, I’ve been told I should write that book – but I won’t.

What is missing is the comprehensive approach that includes the preventative medicine doctor training. There are some out there – my favorites being Guy Harrison’s developer book, as well as Jonathan Lewis’s DBA book. These books, including my own “Expert One on One Oracle” work to remove the need for the hero. Remember – firefighters are heroes when they do their job, we all just hope wenever need them personally!

Join me at the DOAG Development Conference in Bonn (14.06.2012)

Sat, 26 May 2012 15:53:25

Typically, the DOAG e.V. is hosting its yearly conference in Nuremberg. The DOAG is the German Oracle User Group and the DOAG conference is the premier event in Germany focusing on everything Oracle. There you will find all kinds of presentations for developers, DBAs, management and so forth.

For the first time though, the DOAG will host a smaller conference in Bonn, solely for Oracle developers. The conference covers the following four tracks: "PL/SQL and APEX", "Java and Open Source", "Forms, Reports, ADF, BI-Publisher", "BPM and Softwarearchitecture".
Find the complete conference program as PDF online.

You can get more information on the conference website, you can also register online.

If you like you can join me there, I will be presenting on "from Oracle Reports to Jasper Reports":

Many customers are moving away from Oracle Forms to other alternatives. Oracle APEX is an especially interesting option because it leverages existing Oracle database skills (SQL and PL/SQL) nicely. But what about your existing Oracle Reports? This presentation will cover the open source solution, JasperReports, as a viable alternative. The session will cover the typical use cases and design patterns used when building reports in Oracle Reports and how to implement them using JasperReports. You will learn how to get started with JasperReports and how to integrate that functionality into your APEX applications.

Cheers,
~Dietmar.

Presenting at ODTUG KScope in June

Sat, 26 May 2012 12:14:34

This year from the 24th to the 28th of June I will be attending the ODTUG KScope conference in San Antonio, Texas.

Here is what ODTUG says about the conference:

ODTUG Kscope12 is the place to be for the Oracle technical community in 2012. If you are a developer, architect, technical lead, or database administrator who works with Application Express, Business Intelligence, Oracle EPM; including Hyperion products, Essbase, Planning; Database Development or Fusion Middleware, Kscope12 is where you should be. It’s hard to find a conference that’s big enough to attract world renowned speakers and small enough to get the chance to share knowledge. Kscope12 is that conference.

I could not agree more. It is really fun to meet up with all my friends from the Oracle APEX community, learn about the new features of the upcoming Oracle APEX release firsthand from the developers and get in touch with sooo many excellent developers from around the world. This is definitely the most important conference for any Oracle developer to attend.

This year I will have two presentations:

Cheers and looking forward to seeing you there,
~Dietmar.

MS Exchange API for PL/SQL

Sat, 26 May 2012 09:33:22

As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!

Features

Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.

Prerequisites

You need access to an Exchange server, obviously! I've done my testing against an Exchange 2010 server, but should also work against Exchange 2007.
If the Exchange server uses HTTPS, then you need to add the server's SSL certificate to an Oracle Wallet. See this page for step-by-step instructions.

Usage

The API should be fairly self-explanatory. The only thing you need to do is to call the INIT procedure at least once per session (but remember that in Apex, each page view is a new session, so place the initialization code in a before-header process or a page 0 process).

begin
ms_ews_util_pkg.init('https://thycompany.com/ews/Exchange.asmx', 'domain\user.name', 'your_password', 'file:c:\path\to\Oracle\wallet\folder\on\db\server', 'wallet_password');
end;

Note also that there are two varieties of most functions: One pipelined function intended for use from plain SQL, and a twin function suffixed with "AS_LIST" that returns a list intended for use with PL/SQL code.

Code Examples

-- resolve names

declare
l_names ms_ews_util_pkg.t_resolution_list;
begin
debug_pkg.debug_on;
l_names := ms_ews_util_pkg.resolve_names_as_list('john');
for i in 1 .. l_names.count loop
debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).mailbox.name, l_names(i).mailbox.email_address);
end loop;
end;

-- resolve names (via SQL)

select *
from table(ms_ews_util_pkg.resolve_names ('john'))

-- expand distribution list

declare
l_names ms_ews_util_pkg.t_dl_expansion_list;
begin
debug_pkg.debug_on;
l_names := ms_ews_util_pkg.expand_public_dl_as_list('some_mailing_list@your.company');
for i in 1 .. l_names.count loop
debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).name, l_names(i).email_address);
end loop;
end;

-- get folder

declare
l_folder ms_ews_util_pkg.t_folder;
begin
debug_pkg.debug_on;
l_folder := ms_ews_util_pkg.get_folder (ms_ews_util_pkg.g_folder_id_inbox);
debug_pkg.printf('folder id = %1, display name = %2', l_folder.folder_id, l_folder.display_name);
debug_pkg.printf('total count = %1', l_folder.total_count);
debug_pkg.printf('child folder count = %1', l_folder.child_folder_count);
debug_pkg.printf('unread count = %1', l_folder.unread_count);
end;

-- find up to 3 items in specified folder

declare
l_items ms_ews_util_pkg.t_item_list;
begin
debug_pkg.debug_on;
l_items := ms_ews_util_pkg.find_items_as_list('inbox', p_max_rows => 3);
for i in 1 .. l_items.count loop
debug_pkg.printf('item %1, subject = %2', i, l_items(i).subject);
end loop;
end;

-- get items in predefined folder

select *
from table(ms_ews_util_pkg.find_folders('inbox'))

-- get items in predefined folder, and search subject

select *
from table(ms_ews_util_pkg.find_items('inbox', 'the search term'))

-- get items in user-defined folder

select *
from table(ms_ews_util_pkg.find_items('the_folder_id'))

-- get items in user-defined folder, by name

select *
from table(ms_ews_util_pkg.find_items(
ms_ews_util_pkg.get_folder_id_by_name('Some Folder Name', 'inbox')
)
)

-- get item (email message)

declare
l_item ms_ews_util_pkg.t_item;
begin
debug_pkg.debug_on;
l_item := ms_ews_util_pkg.get_item ('the_item_id', p_include_mime_content => true);
debug_pkg.printf('item %1, subject = %2', l_item.item_id, l_item.subject);
debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));
end;

-- get item (calendar item)

declare
l_item ms_ews_util_pkg.t_item;
begin
debug_pkg.debug_on;
l_item := ms_ews_util_pkg.get_item ('the_item_id', p_body_type => 'Text', p_include_mime_content => true);
debug_pkg.printf('item %1, class = %2, subject = %3', l_item.item_id, l_item.item_class, l_item.subject);
debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));
debug_pkg.printf('start date = %1, location = %2, organizer = %3', l_item.start_date, l_item.location, l_item.organizer_mailbox_name);
end;

-- create calendar item

declare
l_item ms_ews_util_pkg.t_item;
begin
debug_pkg.debug_on;
l_item.subject := 'Appointment added via PL/SQL';
l_item.body := 'Some text here...';
l_item.start_date := sysdate + 1;
l_item.end_date := sysdate + 2;
l_item.item_id := ms_ews_util_pkg.create_calendar_item (l_item);
debug_pkg.printf('created item with id = %1', l_item.item_id);
end;

-- create task item

declare
l_item ms_ews_util_pkg.t_item;
begin
debug_pkg.debug_on;
l_item.subject := 'Task added via PL/SQL';
l_item.body := 'Some text here...';
l_item.due_date := sysdate + 1;
l_item.status := ms_ews_util_pkg.g_task_status_in_progress;
l_item.item_id := ms_ews_util_pkg.create_task_item (l_item);
debug_pkg.printf('created item with id = %1', l_item.item_id);
end;

-- create message item

declare
l_item ms_ews_util_pkg.t_item;
begin
debug_pkg.debug_on;
l_item.subject := 'Message added via PL/SQL';
l_item.body := 'Some text here...';
l_item.item_id := ms_ews_util_pkg.create_message_item (l_item, p_to_recipients => t_str_array('recipient1@some.company', 'recipient2@another.company'));
debug_pkg.printf('created item with id = %1', l_item.item_id);
end;

-- update item
-- item id and change key can be retrieved with following query:
-- select item_id, change_key, subject, is_read from table(ms_ews_util_pkg.find_items('inbox'))

declare
l_item_id varchar2(2000) := 'the_item_id';
l_change_key varchar2(2000) := 'the_change_key';
begin
ms_ews_util_pkg.update_item_is_read (l_item_id, l_change_key, p_is_read => true);
end;

-- get list of attachments

select *
from table(ms_ews_util_pkg.get_file_attachments('the_item_id'))

-- download and save 1 attachment

declare
l_attachment ms_ews_util_pkg.t_file_attachment;
begin
debug_pkg.debug_on;
l_attachment := ms_ews_util_pkg.get_file_attachment ('the_attachment_id');
file_util_pkg.save_blob_to_file('DEVTEST_TEMP_DIR', l_attachment.name, l_attachment.content);
end;

-- create attachment (attach file to existing item/email)

declare
l_attachment ms_ews_util_pkg.t_file_attachment;
begin
debug_pkg.debug_on;
l_attachment.item_id := 'the_item_id';
l_attachment.name := 'Attachment added via PL/SQL';
l_attachment.content := file_util_pkg.get_blob_from_file('DEVTEST_TEMP_DIR', 'some_file_such_as_a_nice_picture.jpg');
l_attachment.attachment_id := ms_ews_util_pkg.create_file_attachment (l_attachment);
debug_pkg.printf('created attachment with id = %1', l_attachment.attachment_id);
end;

Download

The MS_EWS_UTIL_PKG package is included in the Alexandria Utility Library for PL/SQL.

Known Issues

The CREATE_ITEM functions don't seem to return the ID of the created item (which do get created in Exchange). I think the issue is with the XML parsing of the returned results; I will look into this later.

Please report any bugs found via the issue list.

Flashback Friday: Kscope 11 Interview with Jeff Erickson

Fri, 25 May 2012 17:08:09

Jeff Erickson is a Senior Editor with Oracle Publishing and he was kind enough to let us interview him at Kscope11. Earlier in the week Jeff attended Community Service Day where he not only interviewed Edward Roske he chipped in and helped up organize and clean up the Boys and Girls Club of Long Beach.

Check out his interview with us

Here’s his article: Developing a Better Community

A friend in need: Flashback Query

Fri, 25 May 2012 14:45:50

"It is sayd, that at the nede the frende is knowen."

- William Caxton: "Sonnes of Aymon" (1489)

"I say, that when you screw up, Flashback Query is a friend indeed!"

- Morten Braten: "SQLs of Oracle" (2012)

The Flashback Query feature of Oracle (10g) can be really helpful when you run that UPDATE or DELETE statement and then, after the COMMIT, you discover that you made a mistake!

Wouldn't it be nice to be able to look back and see what the data looked like before you messed things up? With a Flashback Query you can do just that:

-- see the data as it was 10 minutes ago
select *
from emp
as of timestamp systimestamp - interval '10' minute
where empno = 7369

Or you can see a history of changes:

-- history of all changes last 20 minutes
select empno, ename, sal, job,
versions_operation,
versions_starttime,
versions_endtime,
versions_xid
from emp
versions between timestamp systimestamp - interval '20' minute and systimestamp
where empno = 7369
order by versions_endtime nulls last

I've found this useful several times lately, so I'm adding it to the blog as a quick reminder, as I always struggle to remember that funky interval syntax thingy.

Generic RTF Documents - Part 5 - The End

Fri, 25 May 2012 14:33:08

As promised, I’ve added some validation (more could be added, but there always could, and this blog is more geared to enable the process, than production-ise it). I’ve also revisited an area of the code I wasn’t happy with, and removed the kludge (Actually, I think it was my problem, in the first place with bad code, but at least I admit it).

I have uploaded the example onto apex.com – link – please don’t amend the existing samples, but create your own as you see fit.

Code can be obtained here – the zip file contains the apex application, schema objects creation and the package spec/body.

So, what extra did I add and why ?.

First, I went back to the screen that I used to record the fields associated with the standard letter. I added some validation to ensure that the only “fields” that began with “FIELD”,”SINGLE” or “TABLE”, and for the first two, had 3 digits – the easiest way of doing this, is via a reg-exp validation to the field_name column as follows.

CASE SUBSTR(:FIELD_NAME, 1, 1)

WHEN 'F' THEN

IF NOT REGEXP_LIKE(:FIELD_NAME, '(^FIELD\d{3}$)') THEN

RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

WHEN 'S' THEN

IF NOT REGEXP_LIKE(:FIELD_NAME, '(^SINGLE\d{3}$)') THEN

RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

WHEN 'T' THEN

IF NOT REGEXP_LIKE(:FIELD_NAME, '(^TABLE*)') THEN

RETURN FALSE;

ELSE

RETURN TRUE;

END IF;

ELSE

RETURN FALSE;

END CASE;

The reason for insisting on 3 digits for the field, is so that when we come to replace, we replace the correct entry, otherwise, FIELD1 would replace FIELD10 – FIELD19 and all the FIELD1nn entries – obvious now, but not when I started.

Next, I ensured that when I read in the template, any field had to exist in the list defined for the standard letter. This meant adding a section of code in the EXTRACT_VARIABLES procedure, along with raising an error and aborting the template load. The following code was inserted just after extracting the field_name and before inserting the record into the TEMPLATE_SUB_COLUMNS table.

SELECT NVL(COUNT(S.FIELD_NUMBER), 0)

INTOL_COUNT

FROM STANDARD_LETTER_FIELDS S, TEMPLATE T

WHERE T.TEMPLATE_ID =P_TEMPLATE_ID

AND T.LETTER_ID = S.LETTER_ID

AND S.FIELD_NAME =L_FIELD_NAME;

IFL_COUNT = 0 THEN

-- The retrieved Field is not listed as being a field available for that report / letter

RAISE_APPLICATION_ERROR(-20000, 'Field name ' ||L_FIELD_NAME || ' has not been defined in the template. ');

END IF;

And then, again just before the insert, I ensured that the “SUBSTITUTION_TYPE” field is correctly initialised by adding the following code

CASE SUBSTR(L_FIELD_NAME, 1, 1)

WHEN 'F' THEN

L_SUBSTITUTION_TYPE := 'FIELD';

WHEN 'S' THEN

L_SUBSTITUTION_TYPE := 'SINGLE';

ELSE

L_SUBSTITUTION_TYPE := 'TABLE';

END CASE;

Due to the fact that the field has already been validated against the list allowed for the standard letter (which in turn validated that only fields of type “FIELD”,”SINGLE” and “TABLE” are entered), I can get away with the “else”.

To get the above code to work however, I had to revisit the UPLOAD_RTF procedure. In the blog (part 1) I mentioned that I had to work around a deficiency and did so by the use of a “kludge”, well, I’m here to eat my own words and say that it was due to my coding. Once the code was corrected, it worked correctly, and the dynamic actions on the button could be removed and all the processing put where it should have been in the first place – that is, in the submit processing. In doing so, raising the error was correctly trapped and any transactional work rolled back.

The next issue I encountered wasn’t with APEX, it was our beloved Microsoft Word. For some unfathomable reason, when creating layouts with aligned text, the first couple of fields are ok, then it starts to insert all sort of garbage rtf codes at random, often within the “field” name, thus blowing the guts of this whole blog. Needless to say, OpenOffice doesn’t have this problem, and I could continue to blog.

I hope the journey has been of interest and inspired you. There is no doubt a lot more that could be done to extend the RTF functionality, and if anyone does, I would be interested to see what you have done.

APEX IR - Date Filter usage like String columns

Fri, 25 May 2012 13:50:57

If you want to filter date values like this:

Then extend the code to your date columns (inside the region source) like this:
My date column name is: IMPORT_DATE
select
...
decode(IMPORT_DATE,null,null,'' || IMPORT_DATE) as IMPORT_DATE
from
...

For more information take a look into this forum thread:
https://forums.oracle.com/forums/thread.jspa?threadID=2346579&start=15&tstart=0

Relacja ze szkoleń

Fri, 25 May 2012 09:53:11

Ostatnia tura szkoleń majowych zbliża się ku końcowi jeszcze tylko kilka godzin i się pożegnamy z ostatnią grupą. Szkolenia realizowaliśmy nieco w innej formie niż to miało miejsce do tej pory. Skupiliśmy się bardziej na konkretnych przypadkach niż nad ciągłością scenariusza. Podręczniki w 60% były zmodyfikowane i dostosowane do najnowszej wersji Oracle APEX 4.1. Mam nadzieję że szkolenie spełniło oczekiwania uczestników i będzie zachętą do szkoleń z wyższego poziomu : )

I jak zawsze załączam zdjęcia.

Wszyscy, którzy nie zdołali wziąć udziału w naszych szkoleniach a chcieli by w przyszłości w nich uczestniczyć, zachęcam do śledzenia bloga. Jak tylko ustalimy następny termin poinformujemy o tym na stronach bloga (nie wcześniej niż za 2 miesiące).

Mike’s Blogging About Cameron Lackpour’s Blog

Thu, 24 May 2012 16:39:25

My good friend, fellow ODTUG Board Member, and Oracle ACE Cameron Lackpour is quite prolific when it comes to writing (anyone who writes more than I do must be prolific, right?). Well, this week he is going slightly overboard, but for good reasons. Kscope12 is exactly ONE MONTH away today. Cameron has put a blog post up each and every day this week highlighting a session or a bunch of presentations that he thinks that you might be interested in. While Cameron talks mainly about the EPM space, he does have some highlights that go outside of the EPM space. I always try to catch up with Cameron’s posts, even though I’m not in the EPM space, because he is a great writer with a very unique sense of humor. He is also very thorough and informative!

So, here are the links to each day’s posts on Cameron’s blog:

Kscope12 session highlight No. 1

KScope12 session highlight No. 2

KScope12 session highlight No. 3

KScope12 session highlight No. 4

Extra credit and brownie points to the first reader of these posts that spots the other ODTUG Board Member’s presentation in these four postings of Cameron’s. Leave me a comment in my blog when you find it.

If you haven’t registered yet for Kscope12, click the link and click on the registration from the Kscope12 home page. You have until June 9 to save $300.00 off of our standard registration rate. Tell them that you heard about it on the ODTUG Blog!

Turning off dynamic listener registration on non-RAC systems to protect against “Oracle TNS Listener Poison Attack”

Thu, 24 May 2012 12:09:28

Environment: Oracle database 11.2.0.3 64-bit, Oracle Linux 6.2 64-bit

Oracle recently released a security alert (CVE-2012-1675) where they warn against a possible “Oracle TNS Listener Poison Attack”. There is no real fix, but there are a number of workarounds that you can use to protect your listeners against unauthorised hijacking.

One of the workarounds involves turning off dynamic registration of database instances by the listener service. However, this workaround can only be used for stand-alone, non-RAC installations. For RAC, you can implement secure transports, which are explained in My Oracle Support document 1453883.1 (if you have access to Oracle support).

Dynamic registration is by default turned on in Oracle 11g. To turn it off, there are two things you need to modify in your $ORACLE_HOME/network/listener.ora file:

First, you need to add a description for all the database instances that the listener will handle. If you fail to do this, any clients trying to connect will receive “ORA-12154: TNS:could not resolve the connect identifier specified” errors after you turned off the dynamic registration.

This is an example for my instance “oratst.mydomain.com”:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oratst.mydomain.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = oratst)
    )
  )

Note: the GLOBAL_DBNAME and SID_NAME can be found by checking the Oracle initialization parameters db_name, db_domain and instance_name.

Next, to turn off dynamic registration, you need to add the following line to the listener.ora file:

DYNAMIC_REGISTRATION_LISTENER = OFF

This is how my full listener.ora file now looks like:

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-tst.mydomain.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oratst.mydomain.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = oratst)
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

# needed to solve a conflict with the ONS service installed in the OHS home
# see metalink note 284602.1
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF

DYNAMIC_REGISTRATION_LISTENER = OFF

After this, use the listener control utility (lsnrctl) to reload your listener’s configuration, and check the status of the services and the dynamic registration:

>lsnrctl

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-MAY-2012 13:45:49

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> reload
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
The command completed successfully
LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
Services Summary...
Service "oratst.mydomain.com" has 1 instance(s).
  Instance "oratst", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
The command completed successfully
LSNRCTL> show dynamic_registration
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-tst.mydomain.com)(PORT=1521)))
LISTENER parameter "dynamic_registration" set to OFF
The command completed successfully

Note: the status “UNKNOWN” is normal when you are not using dynamic registration.

Finally, check if you can still connect to the database using a remote client!

HTH,
Matthias

When is a foreign key not a foreign key...

Wed, 23 May 2012 21:55:23

I learn or relearn something new every day about Oracle. Just about every day really!

Last week I was in Belgrade Serbia delivering a seminar and an attendee reminded me of something I knew once but had totally forgotten about. It had to do with foreign keys and the dreaded NULL value.

Many of you might think the following to be not possible, we'll start with the tables:

ops$tkyte%ORA11GR2> create table p
2 ( x int,
3 y int,
4 z int,
5 constraint p_pk primary key(x,y)
6 )
7 /
Table created.

ops$tkyte%ORA11GR2> create table c
2 ( x int,
3 y int,
4 z int,
5 constraint c_fk_p foreign key (x,y) references p(x,y)
6 )
7 /
Table created.

Looks like a normal parent child relationship - a row may exist in C if and only if a parent row exists in P. If that is true - then how can this happen:

ops$tkyte%ORA11GR2> select count( x||y ) from p;

COUNT(X||Y)

-----------

ops$tkyte%ORA11GR2> select count( x||y ) from c;

COUNT(X||Y)

-----------

There are zero records in P - none. There is at least one record in C and that record has a non-null foreign key. What is happening?

It has to do with NULLs and foreign keys and the default "MATCH NONE" rule in place. If your foreign key allows NULLs and your foreign key is a composite key - then you must be careful of the condition where by only SOME of the foreign key attributes are not null. For example - to achieve the above magic, I inserted:

ops$tkyte%ORA11GR2> insert into c values ( 1, null, 0 );

1 row created.

The database cannot validate a foreign key when it is partially null. In order to enforce the "MATCH FULL" option of a foreign key - you would want to add a constraint to your table:

ops$tkyte%ORA11GR2> alter table c add constraint check_nullness

2 check ( ( x is not null and y is not null ) or

3 ( x is null and y is null ) )

4 /

Table altered.

That will ensure either:

All of the columns are NULL in the foreign key
None of the columns are NULL in the foreign key

As long as that constraint is in place - your foreign key will work as you probably think it should work.

Evènement : Live Webcast - Oracle Cloud and Oracle Platinum Services

Wed, 23 May 2012 07:22:27

Mercredi 6 Juin 2012

The IN operator trap

Wed, 23 May 2012 07:11:22

If you look for workarounds when you reach the edge of the IN operator functionality then take a look here:
http://www.williamrobertson.net/documents/comma-separated.html

A very nice collection of different workarounds regarding the IN operator.

Apex collections - small case study

Wed, 23 May 2012 01:00:05

Where do you want to hard-code your information?

Somewhere pieces of data need to be written down for reference. They could be in a table; in some variables; within the query itself... there would be quite a few possibilities, regardless of the scenario.

Consider this statement:

select * from my_table where my_col IN ('CODE_1', 'CODE_2', 'CODE_3')

If it where the SQL source from your Apex application, it's minimal coding on your part - but what if the list of available values changes? You'd need to send your application, or at least certain components through the change control process. At the moment that can be annoying.

We could move this code to a PL/SQL package, perhaps using nested tables. The code is simple enough, I'm sure we've all done something similar before.

First we define a table of records

TYPE sql_rec IS RECORD
  (col1  VARCHAR2(200));

TYPE nt_sql_vc200 IS TABLE OF sql_rec;

Then in this case a simple function to pipe the information back will suffice.

FUNCTION param_list
RETURN nt_sql_vc200 PIPELINED IS
  r1 sql_rec;
  r2 sql_rec;
  r3 sql_rec;
BEGIN
  r1.col1 := 'CODE_1';
  r2.col1 := 'CODE_2';
  r3.col1 := 'CODE_3';

  PIPE ROW(r1);
  PIPE ROW(r2);
  PIPE ROW(r3);

END param_list;

Now we have no hard-coded literals in our SQL source, and it's just like having a sub-query. If we need to change the code list, we can modify the PL/SQL package - a little smoother for deployment.

WHERE my_col IN 
  (SELECT col1
   FROM TABLE(my_pkg.param_list));

However, in the Apex environment it's still not the most elegant. The Apex team have provided us with a wheel called Apex Collections.

In using an Apex Collection, we still need some PL/SQL, but it's all made a little simpler.
First define a process that might be called when rendering the page

DECLARE
  PROCEDURE add_col(pc VARCHAR2) IS
  BEGIN
    apex_collection.add_member
      (p_collection_name => 'PARAM_LIST'
      ,p_c001            => pc);
  END add_col;
BEGIN
  apex_collection.create_or_truncate_collection
    (p_collection_name => 'PARAM_LIST');

  add_col('CODE_1');
  add_col('CODE_2');
  add_col('CODE_3');
END  build_params_collection;

Then modify the where clause a touch

WHERE my_col IN 
 (SELECT c001
  FROM apex_collections
  WHERE collection_name = 'PARAM_LIST');

There are plenty of reasons out there why you'd want to use Apex Collections - either check out the documentation linked above, or install the sample application and check out how it's done.

Here endeth the lesson.

If anyone has other ideas for this problem, either more elegant or more esoteric, I'm all ears!

Some Interesting Oracle Analytic Functions

Tue, 22 May 2012 14:04:26

I had to write some reports for a personal application of mine last night and needed to expand beyond my usual set of analytic functions that I normally use. I thought it would be a good idea to do a quick blog post on these analytic functions.

If you've never heard of or used Oracle analytic functions please read this article first. It's by far the best article I've read at explaining what analytic functions are and how to use them. The Oracle analytic function documentation can be found here.

RATIO_TO_REPORT

RATIO_TO_REPORT compares the current value against the sum of the other set of values. The following example shows the percentage of each employees salary when compared to the sum of their department's salary. You'll notice that you can calculate the same value using a different method (sal_dept_ratio2) which may be why I haven't seen RATIO_TO_REPORT used that often.

SELECT d.dname, e.ename, e.sal, 
  round(ratio_to_report(sal) OVER (PARTITION BY e.deptno),2) sal_dept_ratio, 
  round(sal / sum(e.sal) OVER (PARTITION BY e.deptno),2) sal_dept_ratio2
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL SAL_DEPT_RATIO SAL_DEPT_RATIO2
-------------- ---------- ---------- -------------- ---------------
ACCOUNTING     CLARK            2450            .28             .28
ACCOUNTING     MILLER           1300            .15             .15
ACCOUNTING     KING             5000            .57             .57
RESEARCH       FORD             3000            .28             .28
RESEARCH       SCOTT            3000            .28             .28
RESEARCH       JONES            2975            .27             .27
RESEARCH       SMITH             800            .07             .07
RESEARCH       ADAMS            1100             .1              .1
SALES          WARD             1250            .13             .13
SALES          MARTIN           1250            .13             .13
SALES          TURNER           1500            .16             .16
SALES          JAMES             950             .1              .1
SALES          ALLEN            1600            .17             .17
SALES          BLAKE            2850             .3              .3

NTH_VALUE

NTH_VALUE returns the nth row in the window clause. It's extremely important that you explicitly define the window or your values may not make sense. I'll write another post explaining this later.

The following example shows the 2nd highest salary in each department.


SELECT d.dname, e.ename, e.sal, 
   nth_value(e.sal, 2) OVER (
    PARTITION BY e.deptno ORDER BY e.sal DESC 
    -- Don't forget to define the window
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sec_high_sal_dept
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL SEC_HIGH_SAL_DEPT
-------------- ---------- ---------- -----------------
ACCOUNTING     KING             5000              2450
ACCOUNTING     CLARK            2450              2450
ACCOUNTING     MILLER           1300              2450
RESEARCH       FORD             3000              3000
RESEARCH       SCOTT            3000              3000
RESEARCH       JONES            2975              3000
RESEARCH       ADAMS            1100              3000
RESEARCH       SMITH             800              3000
SALES          BLAKE            2850              1600
SALES          ALLEN            1600              1600
SALES          TURNER           1500              1600
SALES          WARD             1250              1600
SALES          MARTIN           1250              1600
SALES          JAMES             950              1600

LISTAGG

LISTAGG was a highly requested feature that was implemented in 11gR2. Overall LISTAGG allows you to group values in multiple rows and put them in a comma delimited column on one row.

The following example (which is not an analytic function) shows all the employees for each department:


SELECT d.dname,
  listagg(e.ename, ',') WITHIN GROUP (ORDER BY e.ename) dept_emp_list
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

-- Result

DNAME          DEPT_EMP_LIST
-------------- ----------------------------------------

ACCOUNTING     CLARK,KING,MILLER
RESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Using the LISTAGG as an analytic function you can see each employee's colleagues in their department, including themselves.


SELECT d.dname, e.ename,
  listagg(e.ename, ',') WITHIN GROUP (ORDER BY e.ename) 
    OVER (PARTITION BY e.deptno) dept_colleagues
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME      DEPT_COLLEAGUES
-------------- ---------- --------------------------------------

ACCOUNTING     CLARK      CLARK,KING,MILLER
ACCOUNTING     KING       CLARK,KING,MILLER
ACCOUNTING     MILLER     CLARK,KING,MILLER
RESEARCH       ADAMS      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       FORD       ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       JONES      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       SCOTT      ADAMS,FORD,JONES,SCOTT,SMITH
RESEARCH       SMITH      ADAMS,FORD,JONES,SCOTT,SMITH
SALES          ALLEN      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          BLAKE      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          JAMES      ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          MARTIN     ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          TURNER     ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
SALES          WARD       ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

NTILE

NTILE allows you to divide your values into buckets and see which bucket the current row belongs to. For educational institutions this is a very good function to let students know that they're in the top x% of the class.

The following example shows the 3 tiers (or buckets) of salaries across the entire company. It allows you to easily see who's in the top 33% of salaries.


SELECT d.dname, e.ename, e.sal,
  ntile (3) over (order by sal desc) three_tier_sal
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- Result

DNAME          ENAME             SAL THREE_TIER_SAL
-------------- ---------- ---------- --------------
ACCOUNTING     KING             5000              1
RESEARCH       SCOTT            3000              1
RESEARCH       FORD             3000              1
RESEARCH       JONES            2975              1
SALES          BLAKE            2850              1
ACCOUNTING     CLARK            2450              2
SALES          ALLEN            1600              2
SALES          TURNER           1500              2
ACCOUNTING     MILLER           1300              2
SALES          WARD             1250              2
SALES          MARTIN           1250              3
RESEARCH       ADAMS            1100              3
SALES          JAMES             950              3
RESEARCH       SMITH             800              3

Annonce : Consolidated Database Replay

Tue, 22 May 2012 09:58:07

Oracle a annoncé ce jour une nouvelle fonctionnalité attendues depuis longtemps : Consolidated Database Replay

Consolidated Database Replay permet de faire un replay de multiples workloads de productions capturés.

jQGrid Integration Kit for PL/SQL and Apex version 1.2 available

Mon, 21 May 2012 18:50:39

It's been over two years since I released the initial version of the jQGrid Integration Kit for PL/SQL and Apex.

Since then, I've added some additional features and fixed a few bugs:

Version 1.1

Procedure include_static_files_once will make sure required JS and CSS files are correctly included
Support for "popup_lov" column edit type
Procedure render_grid supports p_read_only parameter that can be used to set read-only dynamically
Support for some jQGrid built-in formatters based on Oracle column data type
Lists of values (select list variety, not Popup LOV) now support bind variables and Static LOVs
Date parser handles dates and times
Bug Fix: JSON generator now handles NULL parameter values (Thanks to Matt Nolan for spotting this one)

Version 1.2

Popup LOV now handles Apex bind variables and arbitrary LOV column names
Support for jQGrid grouping (experimental)

The latest version can be downloaded here.

New installation: Run the install.sql script. Then run all scripts in the upg folder. Copy the files in the js folder to your web server.

Upgrade from previous version: Run all scripts in the upg folder. Copy the files in the js folder to your web server (overwriting any previous version of the same files; or copy to a different folder and change references accordingly).

Note on jQuery and jQGrid versions: The Integration Kit (via the include_static_files_once procedure) references a specific jQuery version (1.3.2). If your web page includes a more recent version of jQuery, you should modify/remove the reference in the Integration Kit to avoid conflicts.

The version of jQGrid bundled with the Integration Kit is version 3.6. At the time of writing, the latest jQGrid version available is 4.3.2; this has not been tested with the Integration Kit.

Have fun, and please put any bug reports into the project issue list.

New Series of Tutorials now Available

Mon, 21 May 2012 18:51:32

You can now try out the latest Workshop series tutorials available on Oracle Learning Library. This series of tutorials includes labs on Oracle Application Express, Oracle SQL Developer, and Oracle SQL Developer Data Modeler. In these tutorials, you use Oracle Application Express 4.1.1 and Oracle SQL Developer 3.1.

Re-engineering your Database using Oracle SQL Developer Data Modeler 3.1: In this tutorial, you use SQL Developer Data Modeler to import your database schema, make some changes, and then generate the modified DDL.
Testing and Debugging Procedures using SQL Developer 3.1: This tutorial covers how to
execute a DDL script, review changes to the database objects, create, execute, test and debug a PL/SQL procedure.
Building an Application Using Oracle Application Express: Part 1: In this tutorial, you learn how to build and run a database application in Oracle Application Express.
Enhancing your Oracle Application Express Application: Part 2: This tutorial covers how enhance your database application by switching to a different theme, creating a chart, dynamic actions, plug-ins and tabular form with a validation