VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
890,125 quizzes played * 1,143 active players * US$70,072 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


View
Quiz
Played In
Feature / Summary / Author
Ended
OnDescending
Commentary
Pct Correct
PL/SQL Challenge

Impact of commits when there is no RELIES_ON clause: RELIES_ON clause deprecated in 11.2

As of 11.2, the RELIES_ON clause is deprecated. Oracle automatically determines all tables on which a function relies. You can leave the clause in your code - you can even list the â€‹wrong table in the clause - and Oracle will just ignore it entirely.

Author: Steven Feuerstein [10461-960323]
2015-01-23 FridayTemporarily Disabled-
PL/SQL Deja Vu

Exception Propagation: Exceptions Raised in Declaration Section

Exceptions raised in the declaration section, when attempting to assign a default value to a variable, are never trapped in that blocks exception section - even if you have a WHEN OTHERS clause. They always will propagate out unhandled. This applies also to package-level variables and the initialization section of the package.

Author: Steven Feuerstein [5472-960326]
2015-01-23 FridayTemporarily Disabled-
PL/SQL Explore

Guidelines for Optimizing Performance: Avoid SELECT from DUAL Queries

There are very few situations that require the use of a SELECT FROM dual (or another "dummy" table) in PL/SQL. Almost all SQL functions are now available in PL/SQL natively. 

Author: Steven Feuerstein [10462-960325]
2015-01-23 FridayTemporarily Disabled-
PL/SQL Explore

Methods that change collection contents: Deleting Collection Elements (DELETE Method)

Use the DELETE method to remove all, one or a range of elements from a collection. If you have complex removal rules, you may need to write your own custom algorithm to select them out.

Author: Steven Feuerstein [10421-952646]
2015-01-16 FridayTemporarily Disabled-
PL/SQL Deja Vu

Loops in PL/SQL: The CONTINUE-WHEN Statement

The CONTINUE [WHEN] statement gives developers very fine control over how many times loop bodies (even multiply-nested loop bodies) execute, based on Boolean expressions.

Author: Steven Feuerstein [9204-952643]
2015-01-16 FridayTemporarily Disabled-
PL/SQL Challenge

Packages: Package-Level Data

Variables and constants declared in the package specification cannot be directly referenced inside a SQL statement that is executed outside of a PL/SQL block.

Author: Steven Feuerstein [10402-952642]
2015-01-16 FridayTemporarily Disabled-
PL/SQL Explore

Numeric Functions: CEIL

CEIL returns the smallest integer that is greater than or equal to n.

Author: Steven Feuerstein [10392-946416]
2015-01-09 FridayTemporarily Disabled-
PL/SQL Deja Vu

Numeric Functions: TRUNC (number)

Oracle performs implicit conversions when necessary and appropriate. It also provides a TRUNC function that "truncates" both numbers and datetimes. If you pass a string to TRUNC, it always treat it as a number for purposes of implicit conversion.

Author: Steven Feuerstein [6858-946414]
2015-01-09 FridayTemporarily Disabled-
PL/SQL Challenge

Associative Arrays (Index-By Tables): Negative Index Values

One of the distinguishing characteristics of an associative array is that you can use as index values any negative integer between -2**31 and -1. Neither nested tables nor varrays allow negative index values.

Author: Steven Feuerstein [10391-946412]
2015-01-09 FridayTemporarily Disabled-
PL/SQL Challenge

Oracle Exceptions: ORA-00942: Table or view does not exist

When you compile a PL/SQL program unit, the schema to which you are connected must have directly granted privileges to all referenced database objects, such as tables and views. Otherwise, you are likely to see an ORA-00942 error at compile-time. You (or worse, your users) can, however, also encounter this error at run-time if you are working with dynamic SQL, invokers rights or the LOG ERRORS feature.

Author: Steven Feuerstein [10384-941301]
2015-01-02 FridayTemporarily Disabled-
PL/SQL Explore

Conditional Compilation: Selection Directives

You can use all the usual comparison operators of PL/SQL in a conditional compilation expression.

Author: Steven Feuerstein [10385-941303]
2015-01-02 FridayTemporarily Disabled-
PL/SQL Deja Vu

FORALL: Handling FORALL Exceptions (%BULK_EXCEPTIONS Attribute)

If you want your FORALL statement to make it through all the DML statements specified by the bind array, use SAVE EXCEPTIONS. And when you use SAVE EXCEPTIONS you should trap for the ORA-24381 error by name to make your code more readable.

Author: Steven Feuerstein [8932-941302]
2015-01-02 FridayTemporarily Disabled-
Oracle Magazine QuizFeature and summary not shown till quiz has closed.

Author: Steven Feuerstein [9924-826459]

2014-12-31 WednesdayTemporarily Disabled-
PL/SQL Deja Vu

Error Management: Handling Exceptions

There is a difference between calling a PL/SQL block directly from PL/SQL and executing that same PL/SQL dynamically (that is, through the SQL layer).

Author: Gary Myers (11729) [2601-931405]
2014-12-26 FridayTemporarily Disabled-
PL/SQL Explore

Data Types: Collections

There are three types of collections in PL/SQL: associative arrays, nested tables and varrays. Many performance-related features rely on collections. 

Author: Steven Feuerstein [10347-933742]
2014-12-26 FridayTemporarily Disabled-
1 - 15 Next