Snipplr

(PL/SQL) Function to get first record from query and return single value - stews

Wed, 17 Sep 2008 17:44:32 UTC

Nothing amazing here, just an efficient way to get the first row. Be sure to set your ORDER BY clause properly.

(PL/SQL) Refresh an Oracle materialized view - stews

Wed, 17 Sep 2008 17:38:18 UTC

Just provide your materialized view name. Not much code here, but I never remember the command for this.

(PL/SQL) Record Exists function template - stews

Wed, 17 Sep 2008 17:36:38 UTC

I often need to know if a record exists in a table. This method is probably the most efficient. Too many people don't use the exceptions that Oracle provides, which is a shame. I think I got it from Tom Kyte's site? Or Steven Feuerstein?

(PL/SQL) Efficiently compare results from two queries - stews

Wed, 17 Sep 2008 17:33:02 UTC

This compares selected columns between 2 queries, using the WITH clause. It lists the rows and values that are different between the two sources. This is from Tom Kyte (www.asktom.com). I can't say I understand exactly how it works, but it's much faster than other methods I've seen, because it only looks through each query once.

(PL/SQL) Efficiently compare columns from two tables - stews

Wed, 17 Sep 2008 17:26:59 UTC

This compares selected columns between 2 tables that you think might have differences. It lists the rows and values that are different between the two sources You can add WHERE clauses as appropriate. You could also use this to compare the results of two queries. This is from Tom Kyte (www.asktom.com). I can't say I understand exactly how it works, but it's much faster than other methods I've seen. It has the advantage of only looking through each table once.

(PL/SQL) UPDATE using WHERE CURRENT OF clause - stews

Wed, 17 Sep 2008 17:16:50 UTC

I can never remember the syntax on the "FOR UPDATE OF" clause!

(PL/SQL) INSERT rows from BULK COLLECT with LIMIT clause - stews

Wed, 17 Sep 2008 17:11:53 UTC

Reads rows from a source table using BULK COLLECT and LIMIT, then puts them in another collection (presumably with a different layout structure), then loads them to your destination table.