VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
858,484 quizzes played * 1,202 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
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 (32114) [8513-793925]
2014-08-15 Friday-No New Comments

Last: 2013-12-03 12:11:45
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 (67803) [4267-793931]
2014-08-15 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 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 Explore

Datetime Functions: TRUNC (date)

Watch out for the IW format because that might bring you back to the previous month.

Author: Jeroen Rutte (25057) [9661-782651]
2014-08-01 Friday-No New Comments

Last: 2014-08-05 13:56:07
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
PL/SQL Explore

Storing, Retrieving and Using Persistent Objects: Executing DML statements on object tables

A PL/SQL variable defined using the %ROWTYPE attribute against an object table expects that variable to be populated with an instance of an object of the type used by the table definition. A variable of a different but identically defined record type cannot be used instead of the object type instance, as this is possible for usual (non-object) relational tables.

Using object tables allows storing instances of object types as rows in a database table. Each object stored in a row does have an object identifier which allows creating a REF reference (or pointer) to that specific object.

Object instances can also be stored in columns of relational tables, along with other relational data.

Author: mentzel.iudith (67803) [9118-774035]
2014-07-25 Friday-No New Comments

Last: 2014-07-30 17:27:00
PL/SQL Challenge

Datetime and Interval Data Types : The DATE Datatype

When converting a string to a date, only one format mask can be specified. So if you need to support several different formats, store them in a relational table or in a collection.

Author: Steven Feuerstein [9621-774046]
2014-07-25 Friday-No Comments
Last: No Comments
PL/SQL Deja Vu

CURRVAL and NEXTVAL: Call NEXTVAL and CURRVAL natively in PL/SQL

As of 11.1, you no longer need to execute a SELECT-FROM-DUAL to get the next and current values from a sequence.

Author: Steven Feuerstein [9444-774036]
2014-07-25 Friday-No New Comments

Last: 2014-06-18 17:58:08
PL/SQL Explore

Data Manipulation Language (DML) Statements: RETURNING Column Values from DML Statements

The RETURNING clause is used to retrieve the columns values into individual variables or collections that are returned by the statements - DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE. Returned values may not always be the same as the value of the column(s) in a table after the statement execution. This can happen when triggers are defined on the table and changes column after the statement execution (statement level triggers). These kind of after statement triggers are typically used to avoid ORA-4091 mutating table errors.

Author: Jeroen Rutte (25057) [9527-770508]
2014-07-18 Friday-No New Comments

Last: 2014-07-23 21:28:28
PL/SQL Deja Vu

Querying Data with PL/SQL: Fetching a Single Row

When you need to select just a single row, use an implicit cursor (SELECT INTO), and then implement the rules specified for the expected errors (no rows found, too many rows found, null arguments).

Author: Steven Feuerstein [6855-770509]
2014-07-18 Friday-No Comments
Last: No Comments
PL/SQL Challenge

Working with collection variables: Nested Collections

Oracle PL/SQL supports nested collections (a collection of collections or a collection of records, with at least one field of a collection type). The syntax can, however, be quite tricky to get your head around.

Author: Steven Feuerstein [9603-770528]
2014-07-18 Friday-No Comments
Last: No Comments
1 - 15 Next