OraNA :: Oracle News Aggregator

Sometimes interesting problems lead to shock or dismay at the suppositions of why they occur. Why an ORA-22979 is raised is one of those, and the error is typically:

ERROR at line 1:
ORA-22979: cannot INSERT object VIEW REF OR user-defined REF

This error (more...)

There's nothing more annoying than having to struggle to do something because you don't have the right tools. That's why I love my swiss army knife and why when working for Oracle, I love SQLT. What is SQLT? Read on and find out.

The post What is SQLT? appeared first (more...)

SELECT TO_CHAR(NEXT_DAY(sysdate, 'MON')+ROWNUM-1,'DY')
FROM dual
CONNECT BY LEVEL <= 7;

When working with APEX, you also need a number of tools to improve the efficiency of your work. It isn’t enough to just have the APEX IDE at your disposal. Besides the APEX IDE for the application development, you may use a bunch of other tools. This is what I (more...)

Somebody was trying to create a striped view based on a table’s start_date and end_date temporal columns. They asked for some help, so here are the steps.

Basically, you create a user-defined data type, or structure:

1
2
3
4

CREATE OR REPLACE TYPE item_structure IS OBJECT
( id      NUMBER
 (more...)

When building your queries you might want to search case-insensitive. We normally use the UPPER() function to accomplish this. This is kind of weird IMHO, because you have to uppercase the value you are looking for. I was wondering why we are not using the LOWER() function. I put this question on Twitter the other day and got a lot of replies right away.

Frits Hoogland @fritshoogland

@patch72 AFAIK, old computer systems registered everything in uppercase. I know of old govmt systems. prbly people just kept on doing that.

That would be a very plausible explanation. We do a lot (more...)

Just back from a full week in San Antonio for KScope12. I really had a good time. ODTUG provides us with the best conference for developers in the world. Seen some great sessions, met up with some old friends, made a lot of new ones, met in real life with some people I knew only from the internet.

On Saturday I went to the River Walk with Chet Justice, Jeff Smith, Dan McGhan and Galo Balda. Here we went to see The Alamo, had dinner at Café Ole and met up with Tim Gorman and Kellyn Pot’Vin.

On (more...)

I have been appointed Track Leader for the Developer’s Toolkit for (more...)

It’s been quite some time since my last post. It’s not that I haven’t been busy, just didn’t find the time or a subject to write a blog about. Until now.

I came up with something I didn’t know. I have a pipelined table function where I want to suppress a record from appearing when something is wrong (no data found or something like that). I know, suppressing errors is wrong, but in this case I will log the error. I just didn’t want it to appear in my data because the receiving application will generate errors and reject the (more...)

SQL today is the foundation for any successful application. Whether it is a SOA implementation, an ADF or APEX application, a Forms application or any other usage of the Oracle Database (or any other relational database), SQL is key for the success. AMIS has embraced the evolution of SQL through the subsequent releases of the Oracle Database - trying to stay abreast of the latest features and adopting new functionality whenever it made pragmatic sense. In 2004 we developed the 7Up training - that introduced Oracle developers who started their Oracle careers with Oracle7 to the wonders of Oracle 9i. (more...)

In this post I’ll finish up the CRUD implementation using records, procedures and views. This series of blog posts started with this post which was followed by this.

At this point we have a working report that links to a form. The report is based on a view and the form is based on a procedure. At this point the form is only loading the record in using a procedure that uses a record in its signature. In this post we’ll complete the functionality by using the same form for insert, update, and delete functionality.

Let’s start with adding a (more...)

Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…

One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what (more...)

Take the following simple SQL statement:

SELECT *
FROM dual
WHERE 'x' NOT IN
(SELECT 'a' FROM dual);

Since ‘x’ cannot be found in our subquery, you’d expect this to return a row from Dual right? Indeed it does:

SQL> SELECT *
  2  FROM dual
  3  WHERE 'x' NOT IN
   (more...)