VF=>user  ID=>     Login Register
Feedback   FAQ   Blog
871,112 quizzes played * 1,145 active players * US$68,662 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 Challenge

Parameters: Default Values

Add a DEFAULT clause to a parameter to allow the subprogram to be called without specifying a value for that parameter. But NULL can still be passed for that parameter, so you may also need to use NVL inside the subprogram to convert the NULL value to something else. 

Author: Steven Feuerstein [10086-856654]
2014-10-17 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

Re-Raising an Exception: The RAISE; Statement

If you need to re-raise the same exception, don't include a WHEN clause for each possible exception. Just use RAISE;.

Author: Steven Feuerstein [10085-856656]
2014-10-17 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

Calling PL/SQL Functions from SQL: Behavior When Function Raises NO_DATA_FOUND

When a function that is called in an SQL statement propagates out an unhandled NO_DATA_FOUND exception, the calling query acts as though the function returned a NULL value.

Author: Jan Leers (iAdvise.be) (5328) [3085-856657]
2014-10-17 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

Built-in (Supplied) Packages and Types: DBMS_DESCRIBE

Use DBMS_DESCRIBE.DESCRIBE_PROCEDURE to obtain information about the parameters of a procedure or function, defined within a package or at the schema level. You cannot, however, use this supplied procedure to get information about parameters of object type methods

Author: Steven Feuerstein [6947-850771]
2014-10-10 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

Datetime and Interval Data Types : INTERVAL DAY TO SECOND

Formal parameters of PL/SQL subprograms only contain a data type declaration, but not a precision for that data type.
When such a parameter is of an INTERVAL data type, it uses the default precision of that data type, and not the maximum precision, as is the case with other data types. As a result, subprogram calls might fail if an argument passed to the subprogram exceeds the precision allowed by the corresponding formal argument. For using subprogram parameters of INTERVAL data types, sometimes a "properly crafted" data type should be used instead of the simple INTERVAL DAY TO SECOND, for avoiding errors due to precision exceeded.

Author: mentzel.iudith (70015) [9943-847208]
2014-10-10 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Data Type Conversion: Implicit Conversion

Beware implicit conversions - they can have all sorts of unanticipated consequences in your code.

Author: Steven Feuerstein [10041-847207]
2014-10-10 FridayNo New Comments
Objections: SUBMITTED
Last: 2014-10-18 06:48:02
-
PL/SQL Explore

Equality Comparisons on Nested Tables: Impact of NULL Elements on Nested Table Equality

If your nested table has one or more NULL elements, then an equality comparison to another nested table will never return TRUE, even if that second nested table has the same element values.

Author: Steven Feuerstein [10023-845629]
2014-10-03 FridayNo New Comments

Last: 2014-10-02 23:51:05
-
PL/SQL Deja Vu

TO_CHAR (datetime): Use CC to Get Century

Use the CC format model to determine (and return, with a call to TO_CHAR) the century in which the date lies.

Author: Steven Feuerstein [7084-845630]
2014-10-03 FridayNo New Comments

Last: 2014-10-01 05:09:47
-
PL/SQL Challenge

Managing PL/SQL Applications: Techniques to Avoid Hard-Coding

Use constants to hide literal values, but don't go overboard and make sure you rely on them as consistently and thoroughly as possible.

Author: Steven Feuerstein [10021-845625]
2014-10-03 FridayNo New Comments

Last: 2014-10-05 07:07:46
-
PL/SQL Explore

How PL/SQL Resolves Identifier Names: Overriding Standard Function names

Static SQL statements or PL/SQL blocks resolve object names differently than similar statements executed dynamically inside the same package, so, a same name referenced by a static or dynamic statement can be resolved to a different object. Also, functions having the same name as a standard built-in functions are resolved differently by SQL and PL/SQL statements.

Author: mentzel.iudith (70015) [9843-844134]
2014-09-26 FridayNo New Comments

Last: 2014-09-29 19:05:07
-
PL/SQL Deja Vu

Guidelines for Optimizing Performance: Avoid SELECT from DUAL Queries

There are very few reasons to SELECT from DUAL in PL/SQL programs in modern versions of the Oracle Database. If you ever see such a query in a PL/SQL program unit, make sure it is justified. If not, replace with a native PL/SQL computation and assignment.

Author: Steven Feuerstein [8944-843086]
2014-09-26 FridayNo New Comments
Objections: REJECTED
Last: 2014-09-26 20:36:45
-
PL/SQL Challenge

Scalar Data Types : Numeric Data Types

There is almost always more than one way to implement a requirement. Aim for minimal complexity and maximum readability. And make sure it performs well enough to meet user needs.

Author: Steven Feuerstein [10001-843085]
2014-09-26 FridayNo Comments
Last: No Comments
-
PL/SQL Deja Vu

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. Also, Oracle will let you "read" an OUT parameter's value, but it will be NULL until you assign it a value in the subprogram.

Author: Steven Feuerstein [6694-834669]
2014-09-19 FridayNo Comments
Last: No Comments
-
PL/SQL Explore

Built-in (Supplied) Packages and Types: DBMS_SYS_SQL

DBMS_SYS_SQL works just like DBMS_SQL, except that in place of PARSE there is a PARSE_AS_USER procedure that allows you to parse a SQL statement as if you were another user in the database. IN other words, the statement executes with that user's privileges. The privilege to execute this package should be very strictly controlled.

Author: Steven Feuerstein [8287-834670]
2014-09-19 FridayNo Comments
Last: No Comments
-
PL/SQL Challenge

Raising Exceptions: Avoid raising exceptions

If you can anticipate that a certain exception could or is likely to be raised in your code, then either trap that exception by name or, even better, write code to avoid the exception ever being raised. Exception raising and handling slows your program down significantly.

Author: Steven Feuerstein [9947-834668]
2014-09-19 FridayNo Comments
Last: No Comments
-
1 - 15 Next