Weblog for the AMIS Technology corner
Archive for November, 2006
Exporting data from Oracle to Excel with ApEx – and vice versa.
Nov 29th
If you’re ever asked to export some data from the database into an Excel file (or vice versa), do check if you have ApEx installed on your server. There are some handy utilities available there, especially for end-user tasks.
One of these utilities is export to file. 
Read the rest of this entry »
Changing the prompt dynamically in SQL*Plus
Nov 28th
This is just a simple trick which is not well-known but makes life easier when using the good ol’ SQL*Plus commandprompt (and many people still do!).
If you set the SQLPROMPT environment variabele (either manually or in the login.sql) the standard prompt is replaced by the name of the instance:
SQL> set sqlprompt "_CONNECT_IDENTIFIER>"
mydb>
I use this in combination with the login.sql. I’m not sure if this was a feature before Oracle 10g, but recently I noticed that the login.sql is executed after every login, and not only when you start a new prompt. And that means that everytime you change your connection, the sqlprompt also changes according to the value of _CONNECT_IDENTIFIER. This probably will prevent many mistakes because you’re typing update-statements in the "wrong" environment!
These substitution variables are available from SQL*Plus 9.2 onwards. Other values you can use for sqlprompt are _USER, _DATE, _O_VERSION and _O_RELEASE. They speak for themselves I guess!
mydb> set sqlprompt "_USER _CONNECT_IDENTIFIER> "SCOTT mydb>
Oracle APPS 11i Technology Administrator OCP beta program extended
Nov 27th
Oracle has "recently" launched the Oracle Applications Technology Certified Professional Administrator (OCP) certification.
First, Oracle launched a beta program for this certification, to end on October 31st of this year. However, Oracle now has decided to extend the beta program to the end of January, 2007. This was officially told by Oracle to a colleague of mine. It may not yet be on their web-site, but they promised to announce it soon.
This is good news for all of you Oracle E-Business Suite DBAs, wanting to become certified, and not pay too much money for it, or wanting a kind of "preparation" exam. The exams only cost US$50,- (€39,- in the Netherlands), and they are valid for certification.
In order to become OCP certified for Applications Technology, you must pass three exams: Read the rest of this entry »
Dummy data without dummy tables – just a little SQL trick with the TABLE operator
Nov 27th
While writing useless queries for solving exotic challenges, I realized that the use of dummy tables or unions of selects against DUAL for producing a few data-rows is a awkard solution to a problem that has a much more elegant one. Using a database type and the TABLE operator we have a very compact way at our disposal for generating records on the fly.
This means that using a simple statement like:
select * from table ( num_tbl(1,2,3,4,5,6) )
we can generate rows with numeric values:
COLUMN_VALUE------------ 1 2 3 4 5 6 6 rows selected.
The same goes for strings: 
Read the rest of this entry »
Using a SelectMany… component in a JHeadstart Advanced Search Form – search for all CLERKS & SALESMEN
Nov 27th
JHeadstart is capable of generating so called advanced search forms. These allow the end user of the ADF application created using JHeadstart to enter various search criteria on which records are to filtered. It is ridiculously easy for example to generate a search form with a dropdownlist for a field that has a domain defined for it (or a lookup relation). The JHeadstart runtime code translates the entries in the search form into ViewCritera on the underlying ADF BC ViewObject and executes the query.
However, JHeadstart does not currently support the option of Multi Select search fields: fields that allow selection of a set of values. The example below shows such a multi-select search item: we want to search for all employee that have one of the selected jobs:
In this article, I will demonstrate how you can implement this functionality.
XML-Publisher 5.6.2, datasource and users
Nov 26th
Last week I installed XML-Publisher 5.6.2 using the Windows installer. After this simple and painless installation we can now configure it to connect to our database against which we want to run our reports. You connect to your XML-Publisher using a browser like Internet Explorer and provide the username and password of a user with the XMLP_ADMIN role.
Read the rest of this entry »
WHEN-IMAGE-PRESSED Trigger problem in Oracle Forms 6.0
Nov 22nd
Yesterday we ran into Oracle bug 1004056 while coding a module for a customer. I hear people think "Oracle Forms 60 that’s old". Yes it is but it is a platform still used by a lot of people. The bug is related to the fact that image items are not proper synchronized in a multi record block:
The form has an image with when-image-pressed trigger in order to get the record nr for each image
The form has 15 images and 15 records.
When you click on record 2 and then image 2, you will get different record numbers than the right one, e.g. record 5 instead of record 2 after you have scrolled through the record set.
In other words: the images do not get synchronized with the records.
Oracle sugests as work around: display one image at a time instead of displaying 15 images.
This was not an option for my customer.
However there is a workaround Read the rest of this entry »
XML-Publisher 5.6.2, the automatic install
Nov 22nd
Some time ago I experimented with XML-Publisher 50 Desktop. The tool of which it looks like it is going to replace Oracle Reports in the future. Although the desktop version gives you a possiblility to create reports in several formats like RTF, PDF, Excel and HTMl it does not offer you an interface to do adhoc reports. Since juli XML-Publisher 5.6.2 Enterprise edition is available which does offer you a gui interface. Now I finally had some spare time I decided to have a go at it so I downloaded Oracle XML Publisher Enterprise 5.6.2 for Microsoft Windows (458,014,900 bytes) and started to read the installation guide. Several options are presented, Automatic and Manual. Read the rest of this entry »
The ADF Faces inputText component
Nov 17th
The ADF Faces library contains many JSF components. One of the most common used components is the af:inputText. In the JHeadstart it is the default type for most items. According to the description it will be rendered as an HTML input text element and thus be able to handle user input. However, you must be aware of a huge caveat, that it does not always render as an HTML input text element, but that it can be rendered as plain text too. In this post I will describe under which conditions this occurs and present a solution if you don’t want that behaviour. Read the rest of this entry »
Configuring Oracle Enterprise Service and JDeveloper 10.1.3.1 on Windows – Create a JDeveloper connection to the ESB
Nov 15th
Recenlty I installed both the Oracle SOA Suite 10.1.3 as well as JDeveloper 10.1.3.1 – the Design Time environment for the SOA Suite. Everything was set up locally on my laptop. Although the installation itself through the Oracle Universal Installer went very smoothly, I had a little trouble hooking up JDeveloper with the ESB. When I tried to create the ESB Connection in JDeveloper, it took a while before I had the correct settings. In this article I will show what in the end worked for me – it may be of some help to you.
Note: this article is also published on the Oracle Technology Network as part of a tutorial I developed on Event Driven Architecture with Oracle ESB. Therefore the following statement: "Originally published by Oracle Technology Network. Copyright 2006 Oracle, All Rights Reserved.".
