VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
902,414 quizzes played * 1,109 active players * US$73,147 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

Instantiation and Initialization: Behavior When Package Initialization Fails

The package specification raises a VALUE_ERROR exception, so the initialization section never fires to set the value to 100. 

Author: Steven Feuerstein [10689-1031662]
2015-03-20 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

Nested Tables: Checking for Empty Nested Table

To check to see if a nested table is empty, you can call the COUNT method, use the IS EMPTY operator or call the CARDINALITY function.

Author: Steven Feuerstein [6059-1022693]
2015-03-20 FridayNo New Comments

Last: 2012-09-14 20:01:15
-
PL/SQL Challenge

The FOR LOOP Statement: Cursor FOR Loops

Use a cursor FOR loop to fetch all rows from a cursor (unless the loop body contains DML, in which case consider using BULK COLLECT and FORALL). The cursor can be defined locally or in a package. Write your own explicit loop and you are more likely to introduce bugs.

Author: Steven Feuerstein [10681-1029166]
2015-03-20 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Embedding Single Quotes in String Literals: Q notation for string literals

When you need to double up or more on single quotes in your string literal or expression, use the Q notation to specify a string terminator that is not a single quote. Then within the string, you can use just one single quote.

Author: Steven Feuerstein [10661-1021202]
2015-03-13 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

Write a line of output to the DBMS_OUTPUT buffer: Datatype Limitations for DBMS_OUTPUT.PUT_LINE

DBMS_OUTPUT.PUT_LINE (and PUT) can only display values that are strings or can be implicitly converted to a string. So it cannot, for example, display the value of a Boolean.

Author: Steven Feuerstein [5804-1021203]
2015-03-13 FridayNo New Comments

Last: 2012-10-08 13:12:08
-
PL/SQL Explore

Data Manipulation Language (DML) Statements: Impact of Exception on Completed DML Statements

If a SQL statement raises an error, then any changes made by that statement are rolled back (assuming there is no LOG ERRORS clause). But any DML statements that already completed in your session are not rolled back simply because that statement failed. Already-completed statements are only rolled back if you execute a ROLLBACK statement explicitly - or if the outermost PL/SQL block terminates with an unhandled exception.

Author: Steven Feuerstein [10664-1022422]
2015-03-13 FridayNo New Comments
Objections: REJECTED
Last: 2015-03-21 19:59:39
-
PL/SQL Challenge Championship

FORALL: Statement-level triggers and FORALL INSERT

When you apply FORALL to insert statements remember that statement level triggers fire just once per FORALL, not once per insert statement executed by the FORALL. So check the logic in your triggers and make sure they still work when executed at the FORALL level, and not for each insert.

Author: Steven Feuerstein [8463-993866]
2015-03-11 WednesdayNo Comments
Last: No Comments
-
PL/SQL Challenge Championship

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 or invokers rights.

Author: Elic (37096) [8942-1011813]
2015-03-11 WednesdayNo New Comments
Objections: ACCEPTED
Last: 2015-03-15 07:32:36
-
PL/SQL Challenge Championship

Nested Tables: MULTISET Operators

A big advantage of nested tables is that you can use MULTISET set operators on them, similar to - but also with notable differences from - SQL set operators.

Author: Steven Feuerstein [10599-998712]
2015-03-11 WednesdayNo Comments
Last: No Comments
-
PL/SQL Challenge Championship

Error Management: Handling Exceptions

When executing multiple non-query DML statements that might update multiple rows, choose carefully among LOG ERRORS, FORALL with SAVE EXCEPTIONS and even nested blocks inside loops to control how many statements succeed, how many rows are updated, how far you continue past exceptions.

Author: Steven Feuerstein [10612-968819]
2015-03-11 WednesdayNo Comments
Last: No Comments
-
PL/SQL Challenge Championship

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

Even if a user-defined type in one schema has the same name and same structure as a type in another schema, they are different types.

Author: Elic (37096) [10593-993867]
2015-03-11 WednesdayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

PLS_INTEGER, BINARY_INTEGER and Related Subtypes: Preventing PLS_INTEGER Overflow

The maximum value allowed for PLS_INTEGER is 2147483647. To avoid overflow errors when working with integers approaching this limit, switch to INTEGER.

Author: Steven Feuerstein [8410-1016819]
2015-03-06 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

Oracle PL/SQL: Dynamic SQL

When fetching a fixed number of columns (that is, known at compile time) from a dynamically-constructed query, you can use EXECUTE IMMEDIATE, OPEN FOR or DBMS_SQL. The first approach, native dynamic SQL, is usually the simplest. 

Author: Steven Feuerstein [10646-1016817]
2015-03-06 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Handling Exceptions: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

DBMS_UTILITY.format_error_backtrace reveals the number on which an error was raised. DBMS_UTILITY.format_call_stack reveals the execution call stack, with line numbers in each program unit in stack that were executed.

Author: Steven Feuerstein [10638-1010392]
2015-03-06 FridayNo New Comments

Last: 2015-03-02 19:43:02
-
Oracle Magazine QuizFeature and summary not shown till quiz has closed.

Author: Steven Feuerstein [10503-968803]

2015-02-28 SaturdayNo Comments
Last: No Comments
-
1 - 15 Next