Recently in Development Category

If you are looking for a comfortable way of efficiently logging information within your Oracle database or you want a seamless logging from within your Java application which already uses log4j and uses an Oracle database - look no further. It is part of the Apache project and can be freely used within production solutions. So take a look here for details on log4plsql.

Okay, so we all love to build tokenizer's but take a look at this one. It is really versatile. I used it to easily convert some very dynamic path information into individual catagories - works great.

I thought this blog entry from Optimizermagic was very well put together and explains the differences between Oracle inner and outer join syntax and the ansi syntax. It is both clearly laid out and easy to follow. So if your are unsure of the pitfalls take a closer look.

Have you had sporadic problems with sql statements firing at the wrong schema and can’t work out why? Well you may be a victim of an ongoing Oracle bug. This bug has been around since 9i and is documented under Note: 392673.1. Under certain circumstances an insert or select will use an old cursor which fires at the wrong schema and object. This will, however, only happen under certain conditions and only if the object is not full qualified. It is fixed in version 11 and patched for 10.2.0.4. Check out this thread for more details.

Okay, so this problem only comes up once a century - but it's just so easy to handle. If you have some date fields you want to insert into an Oracle table - say from Excel - then your two-digit years will get expanded from 96 to 2096. What you probable want is to see 1996 rather than 2096.

The solution is really very simple, insert the year using the date operator 'RR' as apposed to 'YY'. For example let’s say Excel gives you a date string like "13. Jan 78". Using the date format to_date('13. Jan 78', 'DD. Mon RR') any year values between 0-49 will arrive in the table as 2000-2049 and values between 50-99 will arrive as 1950-1999 in your database.
If you are looking for more information check-out this link.

If you're thinking of calling commands on the operating system from Oracle PL/Sql you might want to take a look at this code or this explanation. There are also some security aspects which you should not overlook when using this kind of functionality.

By default, a stored procedure executes with the privileges of its definer, not its invoker. Such procedures are bound to the schema in which they reside. For example, assume that the following standalone procedure, which can drop any kind of database object, resides in schema scott:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

Also assume that user jones has been granted the EXECUTE privilege on this procedure. When user jones calls drop_it, as follows, the dynamic DROP statement executes with the privileges of user scott:
SQL> CALL drop_it('TABLE', 'dept');

Also, the unqualified reference to table dept is resolved in schema scott. So, the procedure drops the table from schema scott, not from schema jones.
However, the AUTHID clause enables a stored procedure to execute with the privileges of its invoker (current user). Such procedures are not bound to a particular schema. For example, the following version of drop_it executes with the privileges of its invoker:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

This is a link to OTN which has a whole load of howto's for JDeveloper - check it out.

I saw this iText come up on a list feed this morning. Someone was looking for a way to store TIFF-Data in the database and present it via a pdf. It's available from SourceForge under an MPL 1.1 license.

iText is a library that allows you to generate PDF files on the fly. The iText classes are very useful for people who need to generate read-only, platform independent documents containing text, lists, tables and images. The library is especially useful in combination with Java(TM) technology-based Servlets: The look and feel of HTML is browser dependent; with iText and PDF you can control exactly how your servlet's output will look. iText requires JDK 1.2.

If you are already using or thinking of using JDeveloper10g in your development process and what to enforce Code Auditing then you should take a look at the above press release on OTN.