Multiple Interactive Reports on One Page

If you have been using Interactive Reports since Apex 3.1 landed, you are probably as impressed with them as I am.

The other day I tried to create more than 1 IR on a page and the Wizard prevented me saying "Only 1 Interactive Report can be declared on (more...)

Columns to String: Comma Separated Values (CSV) (Updated SQL Snippets Tutorial)

Uncategorized
| Mar 27, 2008
The "Columns to String: Comma Separated Values (CSV)" tutorial now includes three new sections, "How to Create a CSV File", "How to Escape Double Quotes, CR, and LF", and "How to Include a Header Line".
...

Objects Remain In Their Original Tablespaces After Run Oatm

Uncategorized
| Mar 26, 2008
Migrated to the new tablespaces using OATM but there are objects left behind in original tablespaces. There were no errors reported during tablespace migration.

SQL> select tablespace_name, count(1) from dba_Segments group by tablespace_name;
TABLESPACE_NAME COUNT(1)
------------------------------ ----------
APPLSYSD 1
APPLSYSX 1
COMD 26
COMX 47
CTXD 77
EDWREP 88
EDWREPX 31
PVD 1
PVX 1

SQL> select segment_name, segment_type from dba_segments
2* where tablespace_name='APPLSYSD'
SEGMENT_NA SEGMENT_TYPE
---------- ------------------
20.42 SPACE HEADER

Cause
*******

One of the circumstances under which a 'SPACE HEADER' segment gets created is if a 'dictionary managed' tablespace is migrated to 'locally managed' (see dbms_space_admin.tablespace_migrate_to_local()).

(more...)

How to Purge the RECYCLEBIN in Oracle 10g

Uncategorized
| Mar 25, 2008
THE RECYCLE BIN
*****************


The Recycle Bin is a virtual container where all dropped objects reside. Underneath the covers, the objects are occupying the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependant objects are not moved, they are simply renamed with a prefix of BIN$$. You can continue to access the data in a
dropped table or even use Flashback Query against it. Each user has the same (more...)

SQL Features Tutorials: Materialized Views (New SQL Snippets Tutorial)

Uncategorized
| Mar 19, 2008
Originally introduced in 1992 as "Snapshots" in Oracle 7, Materialized Views are now used in ways far removed from their original raison d'ĂȘtre, replication. Database programmers use them for data warehousing, denormalization, and even validation. Despite their versatility though, materialized views remain a mystery to some programmers due to their complexity. The new SQL Snippets tutorial "Materialized Views" strips away the mystery with its simple test cases, step-by-step exploration of the basics, common programming pitfall alerts, and a useful utility called MY_MV_CAPABILITIES which analyzes and reports a materialized view's capabilities in a single step.
...

EclipseLink in the News

Pretty exciting day so far at EclipseCon. We have been involved in two press releases at the conference so far.

Eclipse Announces EclipseLink Project to Deliver JPA 2.0 Reference Implementation

Eclipse Announces New Runtime Initiative around Equinox

Ian Skerrett
discusses this announcement (more...)

ruby-plsql gem: simple Ruby API for PL/SQL procedures

In several projects I have used Ruby and Rails to access legacy Oracle databases which have both tables with data as well as PL/SQL packages with lot of existing business logic. Sometimes it is easier just to redo business logic in Ruby but sometimes you need to reuse existing PL/SQL packages and procedures.

Let’s use this simple PL/SQL function as an example:

CREATE OR REPLACE FUNCTION test_uppercase
  ( p_string VARCHAR2 )
  RETURN VARCHAR2
IS
BEGIN
  RETURN UPPER(p_string);
END test_uppercase;

If you are using ruby-oci8 library to connect to Oracle then you can call (more...)

ORA-6502 "Bulk Bind: Truncated Bind" error

ORA-6502 is an error that is apparently not well documented when it occurs in conjunction with the use of PL/SQL tables, and possibly bulk binds.

I ran into this problem recently when some custom code that had worked well for several years suddenly started failing.

As it turns out, and you will see just a little later here, the error is rather easy to fix. What makes it diffucult is if you've never encountered an ORA-6502 under these circumstances. There is precious little about it via MetaLink or Google. Writing about it here may be of help to the next (more...)

Pl/sql optimisation in 10g

Uncategorized
| Mar 5, 2008
Oracle 10g's compiler optimisation for faster PL/SQL, with a new section on optimisation bugs. November 2004 (updated March 2008)

Interesting Corrupted Redo Log Case

It has been a while since I wrote on Renaps’ Blog .. most probably because I didn’t run through any interesting case to talk about for a while !

Yesterday, at one of our main clients, the production (soon to be – on Monday) database hung. Was it because it was a Feb 29th of a bisextile year ? or simply because the week-end arriving in the next 4 hours was the go-live of the main data warehouse ? (more...)

ASM File Handling.

Automatic Storage Management (ASM) is a feature of Oracle Database 10g that provides integrated cluster file system and volume management capabilities at no additional cost. ASM provided a foundation for highly efficient storage management with kernelized asynchronous I/O, direct I/O, redundancy, striping, and an easy way to manage storage.

ASM disk groups are not visible outside the database for regular file system administration tasks such as copying and creating directories. It is always seems to be difficult task to backup or migrating the database. But in reality it is not the case. Oracle has provided various methods to deal with (more...)