VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
883,197 quizzes played * 1,087 active players * US$69,562 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

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 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 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 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 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 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 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 (71701) [10165-894170]
2014-11-28 FridayNo New Comments
Objections: SUBMITTED
Last: 2014-11-30 19:35:15
-
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
-
PL/SQL Explore

DBMS_SCHEDULER: EVALUATE_CALENDAR_STRING

Complex schedules for DBMS_SCHEDULER can be built by creating multiple simpler named schedules and combining them.

Author: Kim Berg Hansen (23942) [10201-886577]
2014-11-21 FridayNo New Comments

Last: 2014-11-20 19:28:14
-
PL/SQL Challenge

Numeric Functions: MOD

Use MOD for a quick and easy way to check to see if an integer is even.

Author: Steven Feuerstein [10221-886576]
2014-11-21 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

Declaring and Calling Subprograms: Calling PL/SQL Functions from SQL

You can call a user-defined function anywhere inside an SQL statement that a bind variable can be referenced, as well as in the FROM clause of a query, in place of a table or view name. Specifically, you can call your function in/on any of the following: the SELECT list of the query as part of an expression; the WHERE clause of a query, update or delete; the right side of the SET clause of an update (that is, column = function() ); the expression list of an insert statement; the FROM clause of a query, when invoked within the TABLE operator (this sort of function is called a table function).

Author: Steven Feuerstein [1298-886578]
2014-11-21 FridayNo New Comments

Last: 2014-11-04 23:50:34
-
PL/SQL Explore

SQL%ROWCOUNT: SQL%ROWCOUNT after EXECUTE IMMEDIATE

The value of SQL%ROWCOUNT is ensured to be accurate only if checked immediately after performing a DML statement.
After executing a dynamic DML statement using EXECUTE IMMEDIATE, the value of SQL%ROWCOUNT will be similar to the one it has after a similar static DML statement. On the other hand, after using EXECUTE IMMEDIATE for executing a dynamic PL/SQL block, the value of SQL%ROWCOUNT will depend on the mode of the bind variables passed to the dynamic PL/SQL block (IN, OUT, IN OUT).

Author: mentzel.iudith (71701) [10164-872569]
2014-11-14 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

Numeric Functions: FLOOR

The FLOOR function returns the largest integer that is equal to or less than the number passed to it as its single argument.

Author: Steven Feuerstein [4122-886519]
2014-11-14 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Expressions and Comparisons: Boolean Expressions

You can perform some operations on Booleans, including equality and inequality checks - but also a greater than or less than check!

Author: Steven Feuerstein [10204-881482]
2014-11-14 FridayNo Comments
Last: No Comments
-
1 - 15 Next