VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
854,148 quizzes played * 1,188 active players * US$67,602 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 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 (66938) [9118-774035]
2014-07-25 Friday-No New Comments

Last: 2014-07-30 07:07:08
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 (24604) [9527-770508]
2014-07-18 Friday-No New Comments

Last: 2014-07-23 21:28:28
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
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

Collections : Associative Arrays (Index-By Tables)

When working with associative arrays, they can be indexed by integer or by string. They can also be dense (every index value between FIRST and LAST defined) or sparse (gaps of undefined index values between FIRST and LAST).

Author: Steven Feuerstein [9562-761264]
2014-07-11 Friday-No New Comments

Last: 2014-07-13 12:53:16
PL/SQL Explore

Trigger-Related Errors: Mutating Table Errors

Starting with Oracle 11g R1, we can write code for different timing points, for a specific database object, into a single, compound, trigger. The individual timing points can share a global declaration. In previous releases, such functionality was only possible by defining multiple triggers whose shared variables were defined in a separate package.

Author: Livio Curzola (13605) [9485-761244]
2014-07-11 Friday-No Comments
Last: No Comments
PL/SQL Deja Vu

Explicit Cursors: Attributes of Explicit Cursors

Oracle offers a number of attributes for explicit cursors, which will help you understand the state of the cursor (is it open or closed, how many rows have been fetched through the cursor, etc.).

Author: Steven Feuerstein [6133-761265]
2014-07-11 Friday-No Comments
Last: No Comments
PL/SQL Explore

Responding to Database Events Through Triggers: How Events are Published Through Triggers

In an after event trigger the source code of the created object is not visible in the *_source views

Author: Jeroen Rutte (24604) [9503-754238]
2014-07-04 Friday-No New Comments
Objections: REJECTED
Last: 2014-07-02 12:13:09
PL/SQL Deja Vu

Data Types: LONG Data type

Oracle continues to support the LONG datatype for backward compatibility. You should, however, never use LONG in new code and instead take advantage of a LOB datatype. Whenever possible, migrate LONG data to LOB - and watch out for LONGs popping up in the Oracle data dictionary.

Author: Viacheslav Stepanov (72251) [6527-755315]
2014-07-04 Friday-No New Comments
Objections: REJECTED
Last: 2012-08-31 20:18:54
PL/SQL Challenge

Scalar Data Types : Boolean Data Types

If you are working entirely within PL/SQL, there is no need to use VARCHAR2 (Y/N) or INTEGER (1/0) to represent Boolean values. Instead, use the BOOLEAN datatype and TRUE/FALSE values.

Author: Steven Feuerstein [9525-754237]
2014-07-04 Friday-No Comments
Last: No Comments
Oracle Magazine QuizFeature and summary not shown till quiz has closed.

Author: Steven Feuerstein [9181-671422]

2014-06-30 Monday-No New Comments

Last: 2014-04-24 10:42:18
PL/SQL Challenge

OUT Mode: OUT argument NULL if not set

If you do not assign a value to an OUT parameter in your subprogram, Oracle will set it to NULL. And inside that subprogram, the value of the OUT parameter is NULL until it is assigned a value.

Author: Steven Feuerstein [9484-744256]
2014-06-27 Friday-No Comments
Last: No Comments
PL/SQL Deja Vu

DBMS_SQL: Parse long strings

Oracle Database 11g allows you to easily parse very long strings (> 32767 characters). EXECUTE IMMEDIATE and DBMS_SQL.PARSE accept a CLOB as well as a VARCHAR2 string.

Author: Steven Feuerstein [1820-744257]
2014-06-27 Friday-No Comments
Last: No Comments
1 - 15 Next