Obsessed with Oracle PL/SQL

I posted the following puzzle on Twitter : A quick little #PLSQL puzzle: I set serveroutput on. After running the code you see in the block below, what will be displayed on the screen? Try it yourself before reading the rest of the post! White space so you do not immediately see my answer.  :-) The output is: 10 1 9 I expect most of you got the first two right and maybe the third one wrong. Note also that the block does not fail with any kind of exception, such as VALUE_ERROR. The reason for all this can be summed up in one phrase: implicit conversion. As noted by several people, this is a collection indexed by strings , not integers. Only associative arrays (INDEX BY) types support this. And that makes all the difference in this puzzle. The value being used in the assignment of 100 to elements in the array is an integer (indx). Since the index type is a string, the PL/SQL engine implicitly converts integers 1 through 10 yto strings "1", "2...

I recently received this question in my In Box: Is FORALL faster with Associative Arrays or Nested Tables? Oracle 12.2 documentation  says : "The most efficient way to pass collections to and from the database server is to use associative arrays with the FORALL statement or BULK COLLECT clause." And a blog post  claims  Associative Arrays with "indices of" option is fastest in 10.2. Just wondering if you have noticed any differences and if so, how much faster Associative Arrays are in 12.2 than Nested Tables? Quick Answer There is no significant difference I can see in the performance based on different collection types (with the limited tests I have run). Don't trust performance tests run on very old versions of Oracle Database (e.g,, 10.2). Use the documentation as a starting, not ending, point of your exploration. Try it yourself! Writing and running the code will teach you more than reading the doc or my blog post. If you would like to read fur...

Collections in PL/SQL make it easy for you to implement lists, arrays, stacks, queues, etc. They come in three flavors: associative arrays, nested tables, and varrays. The three types of collections share many features, and also have their own special characteristics. Here are some tips for making the most of collections. At the bottom of the post, I offer links to a number of resources for diving in more deeply on collections. You Can Query From Collections Collections are, for the most part, variables you will declare and manipulate in PL/SQL. But you can query from them using the TABLE operator (and in 12.2 and higher you even leave off that operator). Use this feature to: Manipulate table data and in-session collection data within a single SELECT. Use the set-oriented power of SQL on your in-session data. Build table functions (functions that return collections and can be called in the FROM clause of a query. Here's a simple demonstration: CREATE OR REPLA...

Collections (Oracle PL/SQL's data structure to implement arrays, lists, stacks, queues, etc.) are not only handy in and of themselves, but are used for many key features of this powerful database programming language, including: High performance querying with BULK COLLECT Super-fast, bulk non-query DML operations with FORALL Table functions (functions that can be treated like a table in a SELECT's FROM clause) PL/SQL offers three types of collections - associative arrays, nested tables, and varrays - each with their own characteristics and ideal use cases. If you are not already using collections on a regular basis in PL/SQL, you are really missing out. Use this article as starting point for accessing a number of useful resources for getting up to speed on collections, and putting them to use in your programs. Documentation The PL/SQL User Guide offers detailed coverage of collection features here . It starts by reviewing the differences between collection...

http://bit.ly/dg-tf A table function is a function that can act like a table inside a SELECT statement. The function returns a collection, and the SQL engine converts that collection into rows and columns that can be manipulated with standard SQL operations (joins, unions, where clauses, etc.). Far and away the most popular post on this blog is an introduction to a series of articles on table functions: Given that level of interest in a very interesting feature of PL/SQL, I thought it would be a good thing to give you even more resources to learn about table functions. So I put together a FREE class at the Oracle Dev Gym  on PL/SQL table functions. It consists of four modules and gives you a solid grounding in table function fundamentals: Each modules consists of a video that covers the basics, followed by a LiveSQL tutorial that dives into more of the details, and gives you an opportunity to run and play with the code. We then finish up the module ...

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...

You are writing PL/SQL code to provide secure, high performance access to your data and implement business rules. [reference: Why Use PL/SQL? ] Right? Good. And you use collections (associative arrays, nested tables, arrays) because they offer all sorts of great functionality. [reference: Collections in PL/SQL YouTube playlist ] Right? Good. So here's a quick reminder about COUNT, one of many methods available for collections (others include DELETE, FIRST, LAST, NEXT, PRIOR, TRIM, EXTEND): It works pretty much like COUNT in SQL. If the collection is empty, COUNT returns 0, not NULL. If you try to "read" an element at an undefined index value, Oracle Database raises NO_DATA_FOUND. Just like a SELECT INTO that identifies no rows. If you check to see if a collection is empty with a call to COUNT, it doesn't raise NO_DATA_FOUND. To verify what I've said, and to have a bit of fun while doing it, you can take a quiz on this topic at the Oracle Dev Gy...