view counter


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

APEX Tree based on Pipelined Table Function

Representing hierarchical data in an APEX Tree is fairly straight forward, the wizard to create a tree region assists you with that. But when you need to show data from multiple tables, you will need to provide your own query. In a previous blogpost I have written how to achieve that.
It is also possible to create a Pipelined Table Function which can be used a the basis of the APEX Tree. In this blogpost I will show you how to do that.
The first that we will need are some Type definitions, starting with the Object Type:

Loey Goes to Youtube!

I finally got around to posting some videos of Loey on Youtube. Enjoy!

Loey in a Puddle, Loey the Big Lady

Spent the latter half of Sunday with Lovely Loey.

Stormy day (lots of tornados south of Chicago) and both Veva and Loey enjoyed stomping in some puddles in their big yellow boots!


The PIVOT and UNPIVOT operators were introduced in Oracle 11g and the other day I helped out a friend using these operators.
He wanted the data in the columns to be "shifted to the left". To explain a little more, take a look at the following data:

ID V1 V2 V3 V4 V5
---------- ----- ----- ----- ----- -----
1 a b e
2 aaa bbbb cccc
3 a b e
4 a c e
5 b d

How Do You Drop a Materialized View Without Losing the Computed Data?

Something I spotted on the Oracle-L mailing list the other day:
drop materialized view
and_keep_the_results_you preserve table;
For a script, keep reading.

Tree with Checkboxes: Save the Data - JS Array to PL/SQL

In two previous blogposts I described a method to transform a regular APEX Tree component to a Tree component with checkboxes. The second blogpost described a way to retrieve the data from the database using JSON and AJAX and check the appropriate checkboxes. In this last part of the series on Tree with Checkboxes I will describe a method to save the checked values to the database.
In this blogpost I will assume that you created the APEX tree as described in the other two blogposts (the links to these blogpost are at the bottom).

SQL Brainteasers: Guess the Book Title

A bit more highbrow this time, can you guess the book titles from the SQL clues below?

Little example of index creation on extended varchars

-- it's just for fun:
SQL> alter system set "_scalar_type_lob_storage_threshold"=32000;

System altered.
SQL> create table t_varchar32000(v varchar2(32000 byte));

Table created.

SQL> insert into t_varchar32000
2 select rpad(rownum,31999) || `x' str from dual connect by level<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> create index ix_t_varchar32000 on t_varchar32000(v) tablespace users;
create index ix_t_varchar32000 on t_varchar32000(v) tablespace users
ERROR at line 1:
ORA-01450: maxi

Results from the Q3 2013 PL/SQL Championship now available!

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

Congratulations first and foremost to our top-ranked players:

1st Place: Michal Cvan of Slovakia

2nd Place: mentzel.iudith of Israel

More Date Mysteries: When Is Current SYS?

If you work with clients with in different timezones, be wary of the CURRENT_DATE/CURRENT_TIMESTAMP functions:

view counter