Conditional Unique Indexes


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...)

Partition Info in V$SESSION_LONGOPS

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...)

UTC timestamps for Salesforce from Oracle

I came across the requirement the other day to update Salesforce every 5 minutes with data from Oracle. 

 The data in Oracle was a simple table with few columns A,B,C  and a timestamp column T indicating the last modified date/time of the record. 

To my surprise whenever I (more...)

Grouping Data Sets by Week Number of the Month

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...)

(UTL_RAW.)CAST_TO_DATE

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...)

Performance tuning. Spending time is NOT OK (if you do not know exactly why)

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...)

Inserting into Record with Long Character Fields

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.

SPEXP

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


A simple pipelined version of print_table

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...)

Restoring a dropped table

ImageCatastrophe! 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...)

XTRSBY – Tuning Oracle Data Guard

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...)

What is SQLT?

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...)

DUMMY issues

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...)

Analysis Tools…

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...)

The power of using records in APEX III

Uncategorized
| Mar 14, 2013

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...)

TO_DATE or not TO_DATE

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...)

An Interesting Feature of NOT IN and Multi-Row Subqueries

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...)

SQL Access to Salesforce data

In this post I will talk about an ODBC/JDBC driver solution I discovered lately which enables you to Access your Salesforce data using the standard SQL query language.

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...)

Fun with Date Math

Uncategorized
| Feb 10, 2013
(First off, sorry Mike, I'm hoping this will break my writer's block...)

On Friday I was asked to look at a report that wasn't returning all of the data. Sample:
Year/Month  Total Sales Total Sales (YAGO)
------------------------------------------
01/31/2013 $1,000,000 $900,000
03/31/2013 $950,000
For reference, YAGO is "Year Ago."

Notice anything funny there?

Yeah, February is missing. The (OBIEE) report has a filter on Jan, Feb and Mar of 2013. But it wasn't showing up. I confirmed via manual SQL (hah!) that there was (YAGO) data in there for February. Any ideas?

I immediately suspected one of two (more...)

Tables of the Heapish Variety – Tables 101

Uncategorized
| Jan 30, 2013

A table is the basic storage unit in an oracle database. A table stores the data that you think of when you think of a database.

 

A heap table is the default table type in Oracle. When we refer to a table, we are usually refering to a heap table. A heap table is just a table that stores the data in no partiuclar order. It just heaps the data on.

 

A heap table can be a relational table or an object table. A relational table is a t