OraNA :: Oracle News Aggregator

Oracle has build a number of formatting options into SQL to allow you to output your data in some standard formats. This removes the need to use other tools or to write extra code or performs various follow up steps.

All you need to do is to add a comment!

(more...)

Oracle’s advanced partitioning has some deficiencies. For example, partition info is missing in V$SESSION_LONGOPS for scan-operations ( full table scans, full index scans ). V$SESSION_LONGOPS.TARGET only shows OWNER.TABLE_NAME in these cases, even when the underlying table/index is partitioned, though the longop doesn’t refer to the whole segment but (more...)

Tom is not not a fan of public synonyms, here is why:

• public synonyms pollute the namespace.
• public synonyms can lead to security issues.
• public synonyms can lead to a maintenance headache.
• public synonyms are public – no one owns them.

So, instead of public synonyms…

create PRIVATE synonyms or (more...)

May 1, 2013 I saw a decent SQL brain teaser this morning in the comp.databases.oracle.server Usenet group.  The OP in the message thread is attempting to summarize data in one of his tables, with the summarizations broken down by month and then the week within that month. (more...)

Tim wrote
… the UTL_RAW package has a bunch of casting functions for RAW values (CAST_TO_BINARY_DOUBLE, CAST_TO_BINARY_FLOAT, CAST_TO_BINARY_INTEGER, CAST_TO_NUMBER, CAST_TO_NVARCHAR2, CAST_TO_VARCHAR2). Note the absence of a CAST_TO_DATE function.

Bertrand Drouvot also misses it, see Bind variable peeking: Retrieve peeked and passed values per execution in oracle 11.2

Here is (more...)

April 25, 2013 Roughly 12 years ago I was attempting to analyze customer order changes that were received through electronic document interchange (EDI), specifically X12 830 documents that show order forecasted demand for specific part numbers.  At the time, the EDI data was partially transformed and inserted into an (more...)

Dedicated to NoSQL and Big Data expert Gwen Shapira for forcing me to think. Bring the past for judgment into the thousand-eyed present, and live ever in a new day—American philosopher Ralph Waldo Emerson in his essay on Self-Reliance. First, a short but fun quiz. 1. Before relational databases, there (more...)

Yet another performance tuning story, similar to one that happened about a month ago. Just to keep it short:

• once upon a time there was a very time-consuming module.
• eventually we were forced to take much closer look - WHY is it so time-consuming
• we found in our own (more...)

Most of us here follow the good practice of executing our SQL statements directly against the database before using them within an SQLExec in PeopleCode, a view or an SQL actions in App Engines. Even after following this, we might get hit in some situations. Read on to find more.

Enjoy spexp tool http://valentinnikotin.com/spexp/

Tom Kyte’s print_table procedure, available on
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958#14442395195806
seems to be very popular and there exist tricky variations on the theme, for example the following nice xml-trick by Sayan Malakshinov.

Please note that it is very easy to use the existing print_table-code to generate a pipelined version which (more...)

Here is a simple example for when SQL Analytical Functions are simple yet useful.

I wanted a basic 1,2,3,4 count so I could alternate colours in a report.

select ename, sal, rownum rn
  ,mod(rownum,4) mod_rn
  ,mod(row_number() over (order by sal),4) mod_rna
from emp
order by sal

ENAME      SAL     RN     MOD_RN   (more...)

My take is that application developers have belatedly but correctly concluded that an RDBMS is not the best tool for every application. For example, relational algebra, relational calculus, and SQL are not the best tools for graph problems. As another example, weblogs are non-transactional and don’t benefit from the ACID (more...)

Catastrophe! You’ve just accidentally dropped a table which contained really rather important data. What to do?

One thing you can do to recover the situation quickly (if you’re running 10g or later, that is) is to run the following command:

FLASHBACK TABLE MY_SCHEMA.MY_SUPER_IMPORTANT_TABLE TO BEFORE DROP;

If the table (more...)

In a post entitled “NoSQL and Oracle, Sex and Marriage,” Cary Millsap asks why NoSQL technologies are suddenly so popular. My take is that application developers have belatedly but correctly concluded that an RDBMS is not the best tool for every application. For example, relational algebra, relational calculus, and SQL (more...)

If you're reading this, you probably love SQLT and want to learn more about it, but sometimes, a SQLT Xtract isn't the best tool for the job. Here's an example, tuning Data guard. Using the right tool always makes the job a little easier.

The post XTRSBY – Tuning Oracle (more...)

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind (more...)

I’ve previously written about manually rewriting an OR condition into a UNION ALL using LNNVL.

This is a description of a performance issue observed in the real world from the optimizer coming up with a CONCATENATION operation against many child operations including an INLIST operator and other children which then (more...)