VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
884,725 quizzes played * 1,082 active players * US$69,712 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 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 FridayNo Comments
Last: No Comments
-
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 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Optimizing Functions Executed in SQL: WITH FUNCTION clause

Use the WITH FUNCTION clause introduced in 12.1 to avoid redundant formulas in your SQL statements. 

Author: Steven Feuerstein [10345-931403]
2014-12-26 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

Managing PL/SQL Applications: Invisible Columns

Highly configurable applications can be implemented in many ways. For example database might contain of generic columns such as custom1, custom2, etc and the application determines what is actually stored in each one. Or database tables might have all possible columns defined and the application determines which ones are actually used. This can get complicated quickly, especially if the configuration changes over time.

Oracle 12c provides some useful features to make this easier for database developers.

Author: Darryl Hurley (4638) [10263-923817]
2014-12-19 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Fetching with a Cursor: Fetching past end of result set

When you fetch from an explicit cursor with a FETCH statement, Oracle does not raise an exception if you fetch past the end of the result set. 

Author: Steven Feuerstein [10326-923818]
2014-12-19 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

Advanced Result-Cached Function Topics: Session-Specific Dependencies in Result-Cached Functions

If a result cache function refers to a package variable, then you should add another parameter to the function that passes in that package variable. This way, if the variable's value changes, the function will not return the wrong, cached value.

Author: Steven Feuerstein [6853-923819]
2014-12-19 FridayNo New Comments

Last: 2012-09-30 14:30:12
-
PL/SQL Deja Vu

Applying Constraints with Subtypes: Specify Maximum Length for VARCHAR2 Subtype

Whenever you define a SUBTYPE based on a VARCHAR2 type (directly or indirectly), you can supply a maximum length. This length will override whatever length was associated with the base type of the subtype.

Author: Steven Feuerstein [7462-917217]
2014-12-12 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

Loops in PL/SQL: The EXIT-WHEN Statement

When you fetch row by row in a loop using the FETCH statement, your EXIT WHEN %NOTFOUND should come immediately after the FETCH to avoid unnecessary processing.

Author: Sailaja Pasupuleti (6108) [2482-919547]
2014-12-12 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

The FOR LOOP Statement: Cursor FOR Loops

The cursor FOR loop offers the most convenient and highly optimized way to iterate through all the rows in a result set. Don't use simple or WHILE loops for processing cursors unless you need to conditionally exit. And if your cursor FOR loop contains non-query DML, consider switching to BULK COLLECT and FORALL.

Author: Steven Feuerstein [10304-917215]
2014-12-12 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Nested Tables: SET Function

The SET function removes duplicates from a nested table. It will also remove "gaps" - changing a nested table from sparse to dense.

Author: Steven Feuerstein [10250-895532]
2014-12-05 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

String Indexing: Converting from Integer-Indexed to String-Indexed Collections

Collections can only have a single index and therefore ordering of their elements. You can, however, take advantage of string indexing in associative arrays to, in effect, re-order the contents of a collection.

Author: Steven Feuerstein [3685-900650]
2014-12-05 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

DBMS_SQL: DBMS_SQL.RETURN_RESULT

You can use the RETURN_RESULT procedure to return a result set to a client application. Within SQL*Plus, the result set will be immediately displayed, without any need to call DBMS_OUTPUT.PUT_LINE. You can also execute RETURN_RESULT more than once in a  single procedure, creating multiple result sets.

Author: Steven Feuerstein [10287-913384]
2014-12-05 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

Restrictions on Calling PL/SQL Functions from SQL: Execution of DML statements inside functions called in SQL

PL/SQL functions can be invoked from SQL statements, subject to a number of restrictions.
If the function (or another subprogram called by it) is defined as an autonomous transaction, then some of those restrictions do not hold.
 

Author: mentzel.iudith (71878) [10165-894170]
2014-11-28 FridayNo New Comments
Objections: SUBMITTED
Last: 2014-11-30 19:35:15
-
PL/SQL Deja Vu

The LOG ERRORS statement: REJECT LIMIT clause of the LOG ERRORS statement

Use the REJECT LIMIT clause of LOG ERRORS when you want to stop DML statement execution once a certain number of errors have occurred. The value specified for the rejection limit must be a literal.

Author: Steven Feuerstein [1171-894171]
2014-11-28 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

BULK COLLECT - Multiple Row Selects: Bulk-Fetching from a Cursor Into One Collection of Records

When you bulk collect into a collection of records, you should declare a cursor to "hold" the SELECT statement, then define a collection based on %ROWTYPE of that cursor. That is, unless you can simply use table/view%ROWTYPE.

Author: Steven Feuerstein [10245-894169]
2014-11-28 FridayNo Comments
Last: No Comments
-
1 - 15 Next