VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
860,708 quizzes played * 1,217 active players * US$67,977 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
%
Correct
Commentary
Oracle Magazine QuizFeature and summary not shown till quiz has closed.

Author: Steven Feuerstein [9523-753122]

2014-08-31 Sunday-No Comments
Last: No Comments
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-811743]
2014-08-29 Friday-No New Comments
Objections: REJECTED
Last: 2012-09-27 23:18:23
PL/SQL Explore

Built-in Functions: XOR

XOR is the undocumented "Exclusive OR" boolean function. It can simplify expressions which require "Exclusive OR" logic since equivalent expression using only documented operators is complex and hard to understand. There is no equivalent operator.

Author: Elic (32400) [9846-811744]
2014-08-29 Friday-No Comments
Last: No Comments
PL/SQL Challenge

Continue past exceptions: SAVE EXCEPTIONS in FORALL

Use SAVE EXCEPTIONS to make sure all statements generated by the driving array(s) are executed. But remember: if at least one statement fails, then when FORALL is done, Oracle will raise the ORA-24381 error.

Author: Steven Feuerstein [9848-811742]
2014-08-29 Friday-No New Comments

Last: 2014-08-28 12:59:04
PL/SQL Explore

Conversion Functions: TO_CHAR (datetime)

If you want to add "template" text inside the format mask supplied to TO_CHAR, you must surround it in double quotes.
Or you can concatenate that text around the converted date.

Author: Steven Feuerstein [9825-805435]
2014-08-22 Friday-No Comments
Last: No Comments
PL/SQL Challenge

Program Units: Functions

You can improve readability of code greatly by hiding complex expressions behind constants and/or functions. But be sure to carefully analyze the scope of these "hiders" and make sure that they will operate correctly under all circumstances.

Author: Steven Feuerstein [9824-805433]
2014-08-22 Friday-No Comments
Last: No Comments
PL/SQL Deja Vu

How PL/SQL Resolves Identifier Names: Qualified Names and Dot Notation

Use the name of the scope in which a variable is declared (the name of the procedure or function, or even the name given to an anonymous/nested block with a label) to ensure that it is not confused with a SQL identifier, such as the name of the column.

Author: Jeff Kemp (17450) [2342-805434]
2014-08-22 Friday-No Comments
Last: No Comments
PL/SQL Challenge

Serially Reusable Packages: Restrictions on Use of Serially Reusable Packages

If you define a package to be serially reusable, you will not be able to reference any element in that package in a SQL context (within a SQL statement, inside a trigger, and so on).

Author: Steven Feuerstein [9803-793926]
2014-08-15 Friday-No Comments
Last: No Comments
PL/SQL Explore

Packages: Package invalidation and session state, the ORA-04068 error

Beware of the implications of invalidating stateful packages that are currently in use by active sessions.

Author: mentzel.iudith (68031) [4267-793931]
2014-08-15 Friday-No Comments
Last: No Comments
PL/SQL Deja Vu

Interval Literals: INTERVAL DAY TO SECOND Literals

DAY TO SECOND interval literals have limited support in PL/SQL. They should use one of the following syntax:

  • INTERVAL 'integer' [DAY|HOUR|MINUTE]
  • INTERVAL 'seconds [. frac_secs]' SECOND
  • INTERVAL '[+|-] days hours : minutes : seconds [. frac_secs]' DAY TO SECOND

Also PL/SQL does not allow specify precision of day and fractional seconds which are always maximum (=9).

Author: Elic (32400) [8513-793925]
2014-08-15 Friday-No New Comments

Last: 2013-12-03 12:11:45
PL/SQL Deja Vu

SQL%ROWCOUNT: SQL%ROWCOUNT After Inserts

Use SQL%ROWCOUNT to obtain the number of rows changed or added by the most recent DML statement in your session. For an INSERT, it will always return 1 (unless you are executing an INSERT-SELECT). With FORALL, it will return the total number of rows modified by all the statements executed by that FORALL.

Author: Steven Feuerstein [7456-787884]
2014-08-08 Friday-No Comments
Last: No Comments
PL/SQL Explore

Declaring and Calling Subprograms: Overloaded Subprograms

If you need to pass different combinations of arguments to the "same" program (the underlying treatment of those arguments is the same), you can either provide default values for some/all of the arguments or overload (define multiple subprograms with the same name and different numbers/types of parameters).

Author: Steven Feuerstein [9763-787883]
2014-08-08 Friday-No Comments
Last: No Comments
PL/SQL Challenge

RAISE_APPLICATION_ERROR: The keeperrorstack argument of RAISE_APPLICATION_ERROR

If you pass TRUE for the third argument of RAISE_APPLICATION_ERROR (keeperrorstack), then the errors previously raised (and re-raised up through the nested blocks) are all retained for display by a call to SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK. The default value for this argument is FALSE. SQLERRM does not offer this option. It simply truncates the error message at 512 characters.

Author: Steven Feuerstein [9721-785054]
2014-08-08 Friday-No Comments
Last: No Comments
PL/SQL Challenge

Raising Exceptions: Re-Raising an Exception

Don't set up different WHEN clauses in your exception section if the code within those clauses executes the same code, and then explicitly raises the exception again. Instead, use RAISE; to re-raise the exception.

Author: Steven Feuerstein [9662-779863]
2014-08-01 Friday-No Comments
Last: No Comments
PL/SQL Deja Vu

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

When you need to index by a negative integer value, use an associative array. Neither nested tables nor varrays support negative index values.

Author: Steven Feuerstein [6956-779865]
2014-08-01 Friday-No New Comments
Objections: REJECTED
Last: 2012-11-28 00:56:52
1 - 15 Next