Help Celebrate One Million Answers at the PL/SQL Challenge!

Sometime in the next couple of months, someone will submit the 1,000,000th answer on the PL/SQL Challenge.

And Chris Saxon, member of the AskTom Answer Team, SQL Wizard and Database Design Quizmaster, assures me that he will write a query to identify exactly who that person is (will be).

That's a very nice milestone, so we figured it will be a good time to celebrate.

We are still sorting out precisely what we will do, but for sure, we will want to feature stories from our players:

How the PL/SQL Challenge has helped you in your career
High points (or low points) from your activity on the site (I have low points, which I will share, hopefully none of you do!)
Interesting stories from the last six years of answering and commenting on quizzes
Whatever else comes to mind!

You are welcome to post your stories here or the PL/SQL Challenge blog or email them directly to steven.feuerstein@oracle.com. I will be collating them for publication on the site.

I would also be very pleased to do some video interviews of players, reviewers, etc. After all, we do live in the Age of YouTube.

So if you are open to or interested in that, let me know.

Thanks!

Steven Feuerstein

Comments

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables). All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes They asked for my help. The error occurred on this SELECT: SELECT * FROM header_tab trx WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab) AND ((trx.column_ntab MULTISET EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of work, revamping algorithms, ensuring correctness, you know the score. Then my eyes snagge

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory. Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes from those scalars, which can be very helpful from the p

Obsessed with Oracle PL/SQL

Search This Blog