Enjoy spexp tool http://valentinnikotin.com/spexp/
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...)
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...)
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...)
I was playing the pl/sql challenge the other day and the question was to identify valid implementations for a “reverse_string” function taking a varchar2 as the input and returning another varchar2 with the characters in inverted order.
One of the possible answers was this:
FUNCTION reverse_string (in_string VARCHAR2) RETURN VARCHAR2 (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...)
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...)
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...)
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)For reference, YAGO is "Year Ago."
01/31/2013 $1,000,000 $900,000
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...)
If you are using Workspace Manager, it could be probably useful to know, that there is an undocumented restriction concerning import/export.
Due to Import and Export Considerations,
…Workspace Manager supports the import and export of version-enabled tables in one of the following two ways: a full database import (more...)
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
Silpa gave a table like this for testing:
create table network_table (
, destination number
And some data as well:
insert into network_table values (11, 12)
insert into network_table values (12, 13)
insert into network_table values (14, 11)