view counter

PL/SQL Feed

Oracle PL/SQL (Procedural Language/Structured Query Language) resources, news, and support articles.

Tree with Checkboxes - Pre-Select Data with JSON

After you have modified the standard APEX tree to a tree with checkboxes (see this blogpost) you will probably need to find a way to load data from the database and show the values previously checked by the user.
In this blogpost I will show you how to load the data from the database using an AJAX-call and check the appropriate checkboxes in the tree.

Active Data Guard and Invalidations

To provide data source for our datawarehouse (in a seperate MS SQL database, god help it, but that's beside the point :-), we have a setup where we have several views where the datawarehouse connection user has been granted select rights.When we got Active Data Guard in the spring, we let the datawarehouse user connect to the standby instance to offload the quite heavy selects from the production database. But we did get a surprise concerning object invalidation in this setup...

Grouping Tables Together Physically

create cluster your_data_together (
for_super_efficient_access integer
);

create table when_i_am_frequently_queried (
and_performance_matters varchar2(1)
default 'Y',
for_super_efficient_access integer
)
cluster your_data_together (
for_super_efficient_access
);

create table and_joined_to_me (
clustering_places_us_in inte

Half-day masterclass on Analytic Functions (I hope :-)

I've presented on Analytic Functions twice now - at ODTUG KScope12 and UKOUG 2012. Both times I've felt that an hour is not nearly enough to both teach how to use analytics as well as show use cases of how analytics can really be used for solving a lot of your daily work. Perhaps now I can get a chance to give a half-day masterclass on that topic - at least I am hoping so...

SQL Brainteasers: Guess the Film

Can you guess the name of the film from these code snippets? Put your answers in the comments!

Joining the Bitmap Revolution

create bitmap index can_be_constructed
on the_join_of_tables (
eliminating_a_table
)
from the_join_of_tables,
you_frequently_do
where your_need_for_speed = insatiable;

A rarely used feature in my experience, bitmap join indexes can work well
to eliminate joins from dimensions to fact tables in data warehouses,
provided you don't need to update both tables in the join at the same time!
For a more detailed discussion, you can see aritles from

Q2 2013 PL/SQL Championship Results!

You will find below the rankings for the 2013-2 playoff; the number next to the player's name is the number of times that player has participated in a playoff.

Congratulations first and foremost to our top-ranked players:

1st Place: Frank Schmitt of Germany wins: Amazon.com US$250 Gift Card .

Degradation of Standards

select work_quality
from me
where sleep < 'one picosecond'
and coffee_consumed >=
'the contents of the pacific'
and dealing_with_baby_vomiting
like 'the exorcist';

WRK QUALTY
-------------------------------------
Full of speeling erors and zzzzzz....

....

Relational Algebra syntax in QBQL

When it comes to teaching query languages within introductory database class, a system that supports relational algebra is a must. The first step is ASCII-matizing relational algebra syntax. One approach is to be faithful to RA notation by adopting LATEX syntax. Here is an example from popular Stanford class2go (registration required):

\project_{pizza}
\select_{20 <= age and gender='female'}
(Eats \join Person);

APPENDing to the Table Growth Mystery

delete
from
previously_large_table;

insert /*+ append */
into previously_large_table (
cannot_reuse_existing_space,
so_when_you
)
select more_data, to_put_in
from here_space_consumed_grows;

When you delete, Oracle doesn't deallocate the space that was used by the table.
Inserts using the append hint (aka direct path loads) allocates new space to the
table, rather than re-using what's there.

view counter