VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
829,518 quizzes played * 1,706 active players * US$66,852 in prizes awarded

The PL/SQL Challenge offers an ever-growing library of information to help you become more expert in the Oracle technology stack. The Quizzes tab gives you access to all quizzes taken in the past. The Resources tab offers searchable access to many topics in Oracle documentation, to popular Ask Tom threads, and to fascinating blog posts from around the world. Utilities gives you quick access to SQL and PL/SQL utilities, i.e., reusable code. Visit Commentary to search across all quiz discussions.

All of the quizzes already taken by players are available on this page. You can search for a specific string in the topic for that question; filter for a particular type of quiz (you might, for example, want to check out the quizzes given in the last playoff); check out all the advanced quizzes, etc.

Filter Quizzes

Played In
Feature / Summary / Author
PL/SQL Challenge


When you need to communicate an application-specific error to your users, call RAISE_APPLICATION_ERROR.

Author: Steven Feuerstein [9165-664057]
2014-04-11 Friday-No Comments
Last: No Comments
PL/SQL Deja Vu

Criteria for successful overloading: Overloading based on different numbers of parameters

Overloading, also known static polymorphism, requires differences in headers of programs with same name so that the PL/SQL compiler can distinguish between them. The most common way to overload is to use different numbers of arguments.

Author: Steven Feuerstein [1816-664053]
2014-04-11 Friday-No New Comments

Last: 2014-04-15 18:10:52
PL/SQL Explore


Using the SKIP LOCKED clause of the SELECT FOR UPDATE statement allows several sessions to perform concurrently the SAME select statement and retrieve and lock different rows. The locking behavior of FOR UPDATE SKIP LOCKED is different from that of a simple FOR UPDATE: While a cursor using a SELECT FOR UPDATE will lock all the selected rows when the cursor is OPEN-ed, a cursor using a SELECT FOR UPDATE SKIP LOCKED will lock the selected rows only when fetching them, but will skip rows that are currently locked by other sessions (as its name shows :) ) and also rows that were effectively changed after the cursor was opened.

Author: mentzel.iudith (62819) [6743-667226]
2014-04-11 Friday-No Comments
Last: No Comments
Daily PL/SQL Quiz

nested tables and varrays with BULK COLLECT: Varray Filled When Limit Exceeded

When you use BULK COLLECT to populate a varray and its limit on elements is exceeded, Oracle raises an error, but still leaves the varray filled.

Author: Steven Feuerstein [9087-613985]
2014-03-31 Monday-No New Comments

Last: 2014-04-02 18:36:48
Daily PL/SQL Quiz

Character Functions Returning Character Values: Change characters in string

You can use both TRANSLATE and REPLACE to remove a single character from a string. If you use TRANSLATE, make sure that the third argument is NOT NULL. In that case, the function will always return NULL.

Author: Steven Feuerstein [9098-613984]
2014-03-28 Friday-No Comments
Last: No Comments
Daily PL/SQL Quiz

Performance: Bulk Processing with FORALL and BULK COLLECT

Whenever you see DML statements inside a loop, convert to BULK COLLECT and FORALL for a significant boost in performance.

Author: Steven Feuerstein [9099-613983]
2014-03-27 Thursday-No New Comments

Last: 2014-03-28 23:20:22
Daily PL/SQL Quiz

Oracle Exceptions: PLS-00306: wrong number or types of arguments in call

It can be plenty challenging to figure out why Oracle raises the "PLS-00306: wrong number or types of arguments in call to....". Beyond looking for, well, the wrong number of arguments or wrong data types, watch out for the wrong name in named notation.

Author: Steven Feuerstein [9109-613982]
2014-03-26 Wednesday-No New Comments
Objections: SUBMITTED
Last: 2014-03-26 20:00:10
Daily PL/SQL Quiz

Oracle PL/SQL

Oracle PL/SQL and SQL together offer a wide range of tools you can use to build algorithms meeting user requirements.

Author: Steven Feuerstein [9097-613981]
2014-03-25 Tuesday-No Comments
Last: No Comments
Daily PL/SQL Quiz

Numeric Functions: POWER

Sure, you could write your own POWER algorithm, but instead use the POWER function or the ** operator.

Author: Steven Feuerstein [9092-613980]
2014-03-24 Monday-No New Comments

Last: 2014-03-25 10:57:25
Daily PL/SQL Quiz

Methods that change collection contents: EXTEND Collection Method

Before assigning values to elements in a nested table (and varray), you must define index values for those elements. You can do this with the constructor function, but usually the EXTEND method is used. Finally, a BULK COLLECT query will automatically extend as needed, but don't use this feature unless you are actually populating the collection from a table.

Author: Steven Feuerstein [9089-613979]
2014-03-21 Friday-No Comments
Last: No Comments
Daily PL/SQL Quiz

Oracle PL/SQL: Transaction Processing in PL/SQL

The effects of DML statements executed in your session are immediately visible in that session, but not in other sessions until you commit.

Author: Steven Feuerstein [9091-613978]
2014-03-20 Thursday-No Comments
Last: No Comments
Daily PL/SQL Quiz

Transaction Processing in PL/SQL: Set Transaction Properties with SET TRANSACTION

Isolation Levels allow Oracle to manage potential conflicts that could arise between multiple transactions on a database.

A correct tuning allows the right balance between the need of transactions to access 'fresh data' from a database while preventing them from phenomena like dirty/fuzzy/phantom reads.

Author: Dan Voiculet (23271) [9074-613977]
2014-03-19 Wednesday-No New Comments

Last: 2014-03-22 03:12:15
Daily PL/SQL Quiz

Character Functions Returning Number Values: ASCII

The ASCII function, combined with the CHR function can be very useful when manipulating strings or looping through the alphabet.

Author: Jeroen Rutte (22656) [8592-613976]
2014-03-18 Tuesday-No Comments
Last: No Comments
Daily PL/SQL Quiz


If you need to create a table inside a PL/SQL block, you must use dynamic SQL (EXECUTE IMMEDIATE, or another dynamic mechanism) - and then any other references to that table in the block must also be dynamic.

Author: Steven Feuerstein [9086-613975]
2014-03-17 Monday-No Comments
Last: No Comments
Daily PL/SQL Quiz

RELIES_ON clause deprecated in 11.2: Function result cache dependencies in Oracle 11gR2

In 11gR2 a DML (INSERT,UPDATE,DELETE) performed inside a result cache function does NOT cause the target table to be registered as a dependency for the cached result. As a consequence, neither the DML itself nor another DML performed outside of the function
will invalidate the cached result. In 11gR1 this behavior was different. When using the RELIES_ON clause, the presence of a DML statement inside the function caused the function to be reexecuted each time, because the cached result was marked as Invalid after each execution.

Author: mentzel.iudith (62819) [5489-613974]
2014-03-14 Friday-No New Comments

Last: 2014-03-17 12:38:03
1 - 15 Next