May 23, 2013 The following question recently came through an ERP mailing list (significantly rephrased): I would like to use the Microsoft Query tool in Microsoft Excel to extract records from the ERP database. I would like the list of parent records to be retrieved into Excel when all (more...)
Matrix : What you must learn is that these rules are no different than rules of a computer system. Some of them can be bent, others can be broken. Understand?
Usually an unique index grants the uniqueness of all rows in a specific table which have non-null values; But (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...)
… 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...)
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.
Tom Kyte’s print_table procedure, available on
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...)
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...)
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...)
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...)
I've just noticed in one of LinkedIn PL/SQL forums the following question:
- 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...)
I've taken on an effort to port a custom data integration (PL/SQL, Java, etc) application.
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...)
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...)