Reading an xml file as an oracle external table

There is a couple of posts around that imply or claim an oracle external table to be able to read xml files by design. This is not true, really. It may work for you or actually may fit your current xml file structure but you should generally not approach to read xml by external tables. Here is why.

What external tables or sql loader for better can do actually is reading line oriented, file based data in bulk and quite fast. It offers a lot of settings to parametrize the loading process, the reader, according to your input format. You may for example have a look at the excellent series of posts discussing this options by Jiri starting here Oracle External Tables by Examples part 1 – TAB delimited fields.

Well, this functionality, may also be (mis)used to read xml instances from files. Given a file as the following one may be interested in extracting the contents of the token tags.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<FILE_INFO>
	<PERSON_BILD>
		<token_row><token>Fritz &amp; Fischer</token></token_row>
		<token_row><token>Boris Borsberg</token></token_row>
	</PERSON_BILD>
</FILE_INFO>

Some external table definition may then look like this:

Adding page help to a 3.x apex application

I was lately adding some help content to the create/edit form pages of an internal (dev team) change manager application. This is how i did it, discussing some aspects of the design approach as well as the actual execution with apex 3.x (I’m still with apex 3.2, since this version is quite sufficient for an internal app).

Providing on page help for form pages is usually a game like this: some input field may be called “Creation date” and the help text goes as “This is the creation date”. Wow, this is what the world needs to know! I more or less get angry or better ignorant seeing this information garbage but that’s another story. I prefer on page help content that provides page context information, just pointing out dedicated input fields only when necessary.

In fact, apex puts both approaches at your disposal. The key element with apex is the help text region that you may add with the create region dialogue on a page.

Repositioning the wordpress start and posts pages

I was recently asked to provide a general, description-oriented start page on another wordpress blog (ludoix.wordpress.com). An initial direction was given by Gary Barrett with this article on the wordpress support portal. The trick is to set up another page on the Pages view and configure it to have the lowest order index. Afterwards you proceed to the Settings / Reading view and change the selection below Front page displays / Front page to the just created item. That’s great so far, on reloading the blog you’ll now find your new start page on intial display.

However, changing the settings with Settings / Reading / Front page displays I already wondered what to enter with the Front page displays / Posts page select list. I actually expected to find an entry such as posts or default-posts or whatever but to no avail. In fact, leaving out this selection removes the the posts page from the blog completely. Stuck! Just driven by some enlightment of intuition (ha ha) I tried the following: I created another (empty) page on the Pages view, not forgetting to set the order index accordingly. Thereafter, i proceeded to the Settings / Reading view again and configured Front page displays / Posts page to this new (empty) item. And, it worked! That is, wordpress obviously only needs another (empty) container page to put the posts page into.

Another functionality i employed with the new start page was page templates that you’ll find along with the order index on the Pages view. It removes all the sidebar widget stuff from the start page which i find is much more comfortable. Please note however, that the availability and the layout of page templates depend on the theme of the blog.

have fun!

Implementing bottom-up path traversal for hierarchical tables

oracle supports hierarchical queries on tables by the start with and connect by clauses. this usually preassumes that the rows of some table are chained by a value in column dad_id of some row pointing to a value in column son_id in a parent row, where son_id also denotes the primary key in the table. the root rows of the tree being set up that way, furthermore have a dedicated value in column dad_id. for positive, evolving numbers (id’s , one may just choose 0 here. so far, a typical hierarchical query for a (male) family tree may look like this:

Managing the apex (3.2.x) multiselect control

introduction

hi, you are facing the task to edit one tables data as well as an associated relationship table by id on a single apex page? you think that a multiselect list control will do for the relationship table entries but do not know how to integrate it? iff so, you are at the right stop. the following article will give you a screenshot tour of a step-by-step procedure with appropriate annotations.

placing and feeding the multiselect list control

#1 at first, on the dedicated edit page, create a new item within the form region.

Passing sqlplus return codes to the cscript ws-host

introduction

there lately was an article, discussing a certain aspect of managing oracle on windows machines with shell scripting, called Redirecting dbv output to a file. this article is going to dive deeper into the scripting matter, especially contemplating an approach to control the execution of sqlplus-scripts, supervised by logic being embedded in the csript/jscript-interpreter of the windows scripting host (wsh).

sqlplus and scripting on windows – wsh and alternatives

the main problem of using sqlplus to feed the database with code and/or data is its inability to incorporate logic control. this is most notably true iff you have to manage more than one script, probably even branching or staging. sqlplus has variables, assignments, a lot of print formatting and so on but misses any concept of if-then-else and friends. you are obviously impelled to find some other, enclosing runtime environment to express your execution flow plans (and exception escapes .

scripting on windows using the msdos-batch command set was always a nightmare, wsh did a lot better on 2003- and xp-systems and now we are told to have powershell (msh formely) with vista and 2008, also see Next sysdba credentials syntax for impdp with Windows PowerShell. i do not want to examine any pro/con of this evolution nor do i want to introduce alternatives in its facets on windows. to me, having a free choice, i would always and anywhere employ python. it does the daily tasks in an amazingly smart fashion, offering an easily adaptable solution for nearly every duty. however, customer admins may have another perspective or another affectation or may just dislike python.

to that end, iff you are looking for a reliable friend that will help you on with programming your sqlplus script execution on a multitude of windows environments, wsh will not be the worst choice. not to be unmentioned though, wsh development has been discontinued by microsoft. it is to be replaced by powershell somewhere in the future. the most widespread wsh version counts 5.6, with a bugfix update to 5.7 for the vista- and 2008-systems.

download.oracle.com not available – use edelivery.oracle.com

already noticed? download.oracle.com has been down for around 2 days now, as of june 10, 13:30 hours. never expected something like this may happen anyway such that i was urgendly waiting for good news, but …

i contacted hello oracle in germany and these guys linked me to another source for oracle downloads that i merely suspected to be a post office for cd or better dvd shipping. but i was wrong. edelivery.oracle.com does offer another possibility for electronic delivery of oracle install packages.

me, i was in need of downloading 11gR2 win x64. here is how it goes:

Getting the system time in milliseconds from oracle

it is quite popular these days, especially in the java and web worlds to pass around timestamps in form being defined as:

The difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

indeed there is a dedicated method for this in the java editions since long being declared as public static long currentTimeMillis() of System. also, environments based on javascript and the like offer native logic to transform a given utc-timestamp to the (web-)clients timezone and timestamp, respectively.

delivering that msecs-utc-timestamp from oracle may seem straightforward at first, but may become more and more a jungle of options and possibilities of timestamp creation, conversion, extraction and so on. this is especially true iff one actually has no timestamp instance at hand and needs to start the transformation from the good old date datatype (forget about a call to new_time(); here, because it supports a very limited count of source timezones only).

ok, here’s how:

create or replace function get_date_millis (i_date in date) return number is
begin
return to_number(
to_date(to_char((cast(i_date as timestamp with time zone)) at time zone 'gmt', 'yyyy.mm.dd hh24:mi:ss'),
'yyyy.mm.dd hh24:mi:ss')
- to_date('01.01.1970','dd.mm.yyyy')
) * (24 * 60 * 60 * 1000);
end;
/

unpacking all that nested calls, firstly get a timestamp instance from the given date input parameter and than normalize that instance to utc-time.  secondly reconvert the timestamp instance to a date instance again and substract any time being passed before january 1st 1970. finally calculate the msecs-value, preassuming that one oracle day  is, in numbers, exactly = 1.

have fun.

Next sysdba credentials syntax for impdp with Windows PowerShell

there are a lot of posts around concerning the different credentials syntax for sysdba logins between sqlplus and impdp/expdp. in most cases people get stuck upon the fact, that the usual double quote syntax, they know from sqlplus:

sqlplus "/ as sysdba"

ceases to work for impdp/expdp like so:

expdp "/ as sysdba" directory=data_pump_dir ...
LRM-00108: Unzulõssiger Wert des Positionsparameters 'sysdba'

ok, we all learned that impdp/expdp prefer a single quote syntax for tea time:

expdp '/ as sysdba' directory=data_pump_dir ...

on unix system shells even escaped by \.

expdp \'/ as sysdba\'  directory=data_pump_dir ...

however, the introdution of windows powershell:

PS C:\Users> cmd
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. Alle Rechte vorbehalten.

found me learning another variant of the syntax game. because windows powershell uses a diffenrent approach of interpreting empty spaces on command lines, you have to enclose all the unnamed (credentials) and named (all the rest) switches of impdp/expdp into additional double quotes.

impdp "'/ as sysdba' directory=data_pump_dir ..."

that’s it.

last but not least, do not forget that employing sys for impdp/expdp may deliver inconsistent data over a multiple of transactions, since sys cannot and shall not do a transaction level consisent read (read-only transaction). this is especially important for partitioned or nested tables.

regards

Oracle sudden, inordinate irrensponsibility and ORA-07445

if you ever experience sudden, inordinate irrensponsibility of your oracle instance (one or more threads are running at 100% cpu) and find an error message like this in your alert.log:

ORA-07445: Exception aufgetreten: CORE Dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C921766] [ADDR:0x4C43414F] [UNABLE_TO_READ] []

this windows xp sp2 hotfix may be for you:

KB951312 – When you run an application that uses a timer queue on a Windows XP-based computer, the application may stop responding

regards