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...)
- Which one is faster and why SELECT 1 FROM DUAL or SELECT ROWID FROM DUAL?
Yes, it's pretty basic question, but it's interesting enough to be covered. The answer is pretty simple - do not use ROWID (more...)
In that regard, I'm doing a fair amount of analysis right now. So I need help finding two tools:
1. A tool that will allow me to map (visually or otherwise) a single data point (more...)
I had one scenario where having some SQL was handy, so I started with this
SELECT TO_CHAR(NEXT_DAY(sysdate, 'MON')+ROWNUM-1,'DY')It's possible to then place this as (more...)
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
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.
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...)
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...)
The company which provides these drivers is called Progress|DataDirect
Their JDBC/ODBC Salesforce Connect XE drivers, acts as translators between SQL and SOQL (The Salesforce proprietary query language). So you can write your joins, use expressions like SUBSTRING on your Salesforce data as if the data was in a relational database.
I found the concept quite interesting. If you already know SQL -and there are many people which do- you can just (more...)