Skip to main content

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts

Sten Vesterli published a very thought-provoking post on his blog:

Please stop reading this post, and read that one. When you are done, come on back here for my thoughts on Sten's thoughts.

OK. You read it. Here we go.

First, thanks, Sten, for being such an interesting, wise, sometimes provocative voice in our community.

Next, Sten writes:

Now, on the one hand, I certainly agree that the vast majority of young developers are currently caught up in the modern version of a Gold Rush, which is: "Build an app using JavaScript, pay no attention to that database behind the curtain."

But I can assure you that I still do meet young PL/SQL programmers, regularly, when I am at conferences and doing onsite presentations at companies.

So, young person who writes PL/SQL: do not be afraid! You are not alone! And you are super-smart to have made the choice you did. :-)

Next, Sten offers this advice to managers:

I agree that PL/SQL is a "specialized technology" - it always has been, almost by definition: it is a special-purpose database programming language, and used only within Oracle Database.

But I do think there is another really big "place" in which PL/SQL should be leveraged: 

Any application built on top of Oracle Database

The bottom line is that you cannot build a fully optimized, secure and easy-to-maintain application on Oracle Database without PL/SQL. Doesn't matter if the application is for batch processing, transaction processing, or otherwise.

Sure, you can build an app without PL/SQL. But if you use the database only as a bit bucket, minimizing use of SQL and avoiding PL/SQL all-but-entirely, you will end up with an insecure mess on your hands.

I am not going to go into full "YesPLSQL" mode in this post, though. Instead, I encourage you to check out Bryn Llewellyn's Why Use PL/SQL whitepaper, and his promotion of the "Thick Database" paradigm.

Back to the word "specialized" - I do think that over time we will see a certain narrowing of roles within dev teams. I don't see it as highly likely that JavaScript developers will ever take the time to learn SQL, much less PL/SQL, well enough to avoid botching it up. 

So if I were a fully enlightened manager (feel free to check with my team if you'd like to find out my current status :-) ), I would have one or more specialized people on my team who know Oracle Database inside-out from a developer perspective, and can serve up clean, secure, fast APIs to underlying data and business rules.

Next, Sten offers some advice to developers:

I agree with the first point: it is, unfortunately, impractical to try to convince a developer with no immediate prospects for applying their SQL and PL/SQL knowledge to get up to speed on it.

I don't quite agree with the second point. I suggest that all PL/SQL developers have at least a working familiarity with Java and JavaScript. And get really good at Application Express.

Java because you can implement Java classes in the database to complement/extend the reach of PL/SQL. 

JavaScript because you at least need to be aware of what and how many other developers are writing code these days.

Oracle Application Express because it is a fantastic way to leverage your SQL and PL/SQL skillset to build websites (and you will use some of that JavaScript knowledge, too!).

Thanks, again, Sten, for this very interesting contribution to an important conversation in our community. 


  1. Hello All,
    This is a very interesting topic, and I am not sure that a general and 100% cutting answer can be given at world level ...

    Here, in Israel, as I followed for several months after the job
    market, I can say that I hardly saw any ORACLE job ... let alone PL/SQL ...

    The IT Managers do invest here an enormous energy in moving away from Oracle at all ... and they are very proud when they are able to do this, regardless of the effective results ...

    For example, my very nice project that I developed for many years based on Oracle Forms, SQL and PL/SQL was thrown away
    in favor of a SalesForce application ... which is far and will still be far from the previous one's features and performance ...
    The next step will be to replace SAP on Oracle with SAP Hana...
    and that will be considered a very high achievement ...

    Each place where Oracle can be thrown away in favor of no matter which other platform, will be considered a success ...
    Just as they could live with Oracle while very few developers
    did indeed master the Oracle internal workings, that is,
    all what is beyond simple syntax that compiles without errors,
    will be a proof that not only the tool knowledge (like PL/SQL)
    is useless ... but the database itself need not be known ...

    I liked Oracle because it always gave me (or, better say, I took to myself) a high degree of freedom in mastering an entire application, without anyone being involved in it in any way,
    not even the Oracle DBA team ...
    But for most others, it is just a data repository and nothing more, applications SHOULD always be coded transparently
    (aka by ignoring) any Oracle-specific knowledge, for making
    Oracle replaceable at any given moment when a manager will
    decide so, at least for preserving his position as a "manager in general", without any focus on any specific technology ...

    When I hear a manager saying "I don't care on which database my application is working"... I think that trying to explain
    to such one how great are PL/SQL, APEX or Oracle in general,
    is like a dialogue with a deaf partner ...

    So, indeed, the very few "traditional" PL/SQL developers
    will gradually retire, while the new ones will probably have never heard about it at all ...

    This is how things look to me in this specific part of the world ... as far as I could see on LinkedIn, in many countries,
    US included, this is much different.

    Best Regards,
    Iudith Mentzel

  2. Oracle has neglected PL/SQL for a number of years (imho). The last release with any significant language improvements was 9.2 (or maybe 10.1). There have been enhancements - optimisations, privileges and SQL interaction - but little to improve PL/SQL itself.

    PL/SQL is such a useful and practical language - easy to write (and read), embedded SQL, free with the database.

    But it can be a very frustrating language to program in. Too many times I find myself writing 'longhand' code, because PL/SQL doesn't provide a suitable solution, instead of writing the functional code.

    If it had more of the bells and whistles (and simple shortcuts) of modern languages then maybe more people could be drawn to it.

  3. Kevan, I hear you and I feel your pain. It is my pain too. Feel free whenever you have a moment to send me your list of top most desired bells and whistles (and simple shortcuts) - steven dot feuerstein at oracle dot com.

  4. We don't hire PL/SQL developers. We hire .NET, Java, and Delphi Developers and expect them to code PL/SQL as part of there responsibility. PL/SQL is so easy to learn and use that we rarely have difficultly with this approach.

    1. Very interesting! I assume you also train them up on SQL? Could you offer any insights as to the approach you've taken to training, what seems to work best, which resources are most helpful?

    2. Robert, I tend to agree that it is relatively easy to learn. However, evidence has shown that not many master the true power.

  5. During the interviews we expect excellent SQL knowledge and usually test on DML Statements with sample schemas. I typically spend a couple of hours on the basics of the syntax and tools with a new hire. Then we let them go, a good developer can switch languages easily and is not type cast into a just a single language. Then we have weekly cross trainings where we cover all sorts of topics and have covered PL/SQL topics many times. Code reviews are also great times to teach, as we can identify easier/better ways to do the same code.

  6. Weekly cross trainings and code reviews? I want to work for YOUR company! :-)

  7. Hello,
    @Robert, if so, then why not go also the other way around,
    namely, hire developers who know SQL & PL/SQL and teach them Java,
    .NET and all the rest ?

    For each developer, the language(s) that he has used for a longer time
    and exercised more seem to be the easiest.
    Though, I would seriously hesitate to hire a Java or Delphi programmer
    if my purpose is to have him perform database backend development ...

    Those are simply but completely different ways of thinking.

    Learning should happen at least "one step before" it is effectively needed in a production project, and NOT on the fly ...

    Best Regards,

  8. Hi ,
    Reading the comments made me think that i should switch to another technology. I have been working in PL/SQL for last 3 years. Sometimes it feels discouraged when people from other technology like java/dot net made the point that there is not much scope for pl/sql anymore. So start learning something else too.
    Looking forward for your suggestions.

  9. Afsar, for a while databases were the "center of the universe" and large numbers of people could make a living specializing only in database technologies. That is less the case today, as the shift to apps (especially mobile) have dominated the software world. But even "back then", many database developers knew other technologies, and there is always a danger to specializing too deeply in one language (I am lucky enough to be an exception in this regard). So you shouldn't be too discouraged by this - or at least not too surprised! There is still lots of activity for PL/SQL developers, especially if you an find a hotbed of Application Express development!

    You have been working with PL/SQL for 3 years - and now? Still doing that? Or are you looking for new opportunities?

    1. I am working as Oracle sql/plsql developer for last 5 years, But I think as per salary is concerned Java and python programming language having high salary and Scope.
      Company which are hiring they don't need so much experience developer for pl/sql they are more in BI and front end technologies. they acquire database developer having 2-3 years of experience.
      Yes , I love the beauty of Oracle sql pl/sql and will try to find new jobs in that only.

  10. "We don't hire PL/SQL developers. We hire .NET, Java, and Delphi Developers and expect them to code PL/SQL as part of there responsibility" In my experience this never works. The average Java or .Net programmers are poor at writing good SQL. Note I said good SQL! Anybody can write SQL but very few understand it. It's deceptively simple. It needs a different mind set. I think the role SQL in enterprise application is always underrated (I am not only talking about Oracle but SQL Server as well). People talk about .NET but not SQL or T-SQL, they talk about Java but not SQL or PL/SQL (and sometime worked in SQL Server as well). I have worked with people (.NET experts) whose approach to fetching data from Oracle database is writing a C# program and access records sequentially!

    I have been working in SQL and PL/SQL for quite a long but I write (occasionally) code in C# and Java as well. Probably I want to learn something about Big data (Spark and Hive) and their integration with Oracle and others.

  11. Hello Steven,

    could you elaborate more on why choosing PL/SQL now is super-smart decision despite no "immediate prospects"? What are long-term prospects then? I worked for 3 years as Oracle Developer and now I'm speeding up with JS and React Native (mobile), but I'm still not fully sure it is good decision. I see Oracle Apex as compelling technology because there is small competition and I see that Oracle tries to make PL/SQL and SQL more popular (Dev Gym, Live SQL, hiring you :) ).Is it only my feeling or actually Oracle will push PL/SQL forward to make it more attractive for developers?


  12. Przemek, regarding:

    "could you elaborate more on why choosing PL/SQL now is super-smart decision despite no "immediate prospects"?"

    You mashed up parts of two different sentences and contexts. "Super-smart" referred to a person who was already successfully leveraging their SQL and PL/SQL skills. Their prospects were, indeed, immediate. They were using the technologies in their jobs right now.

    If you do not have this skill set and do not see any openings (this can vary greatly by geography for example), then yes it is harder to make the argument.

    I do think that Oracle Application Express activity is going to grow steadily and maybe explosively, giving Oracle Database developers a strong path into the future.

    In terms of Oracle making PL/SQL more attractive, I sure hope we are and will be doing this, yes.

  13. I have rarely met (not to say never) a Java or .Net developer that has any idea how Oracle works and how SQL and PL/SQL statements are executed behind the scenes. For example to know what is the difference between a soft parse and a hard parse, what is a latch, how Oracle implements the locking mechanism, what is a context switch, etc. If you don't have this kind of knowledge you will never,ever be able to write SQL and PL/SQL code that scales up in large production environments regardless how many tables you are able to join. And then you will blame the database for the bad performance.

    PL/SQL code written by non-database developers can be recognized from the distance, and not in the good way. But to be honest I don't mind the situation as I get a lot of credit when I am able to optimize poor written PL/SQL batch processes and reduce the execution time from 30 minutes to 30 seconds. And this is usually not because I am that good, but because the code is that bad.

    To get back to the point, I also think the future of PL/SQL doesn't look so bright, not because it's getting replaced by something better, but because it's not a cool technology and many will chose to implement most of the functionalities outside the database.

    My humble opinion is that if you are an IT manager and your application is built on top an Oracle database, an database developer should be a mandatory role in your team. Of course I am being subjective (I really hate the word biased) as I love coding in PL/SQL but remember this: the only way you can write code that performs better that SQL and PL/SQL when it comes to data manipulation is if you write it in C and modify directly the data in database blocks.

  14. Hello Steven,

    I believe there are several problems with PL/SQL that make it not so attractive for new developers either young or not - its lacks:
    1) Lacks of syntax.
    In 2016 we still have to code while loop to get through the collection.
    It doesn't have reference types (even PL/SQL only), so object-relational paradigm is not usable (just imagine you want to execute a method on each object in collection).
    Poor exception handling, this one is a pain for everyone developing enterprise level application.
    Multithreaded, async runs, lambdas. Thinks that are common to other developing languages have no signs of being implemented.
    2) Lack of functionality.
    The set of libraries available grows really slowly. As the programs written on PL/SQL are highly immobile you can find very few projects on Github.
    Lots of functionality can be implemented and improved via standard shipped code-base. Logging, outputting. Even debugging process has difficulties even using the most resent DB version when you operate on collections of records or objects, or pipelined functions.
    The EBR was introduced in 11g. How many applications use it? I don't think there are a lot.
    3) Testing. Have you heard we develop the new version of utPLSQL framewrk with Robert Love? We'll do our best to get the maximum of the language but there things that are simple impossible to overcome. Mocking for example, especially for data, is a very inconvinient topic. With its lack the test coding frequently takes multiple times more time than the tested code writing. Because of difficulties with environment preparation as you can't mock it.

    12.2 release brings only minor enchancements to the language. I believe the language itself can sagnificantly improve if Oracle focuses on not-SQL part of it.

    P.S. Sometimes I dream weather it is time to start developing something like OL/SQL? How cool would it be if the OOP paradigm can envade the thick-DB paradigm without need of ORMs, compiling SQL right near the code. No doubts the logic is likely better expressed in OOP (not data manipulation), maybe Oracle can find a way to borrow that ideas, patterns, designs and push it towards the data as it did with PL/SQL years ago.


  15. Hi All,

    I dont know through what all links but i found myself on this article after 2 hrs of googling.So wanted to take advice from people as you with high knowledge and experience.

    I am working as PL/SQL Developer and Database tuning/query optimization.I have total of 2.5 years experience.Looks like i am stuck with PL/SQL technology. I want to enhance my skills and my pay as well. What should i do to satisfy both. I see people in Java,android switching companies and getting pay as 4 times more than me. If you suggest skills out of oracle, how should i clear the interviews and show my work experience on that skill.

    Please help :)


    1. Ankit, first, why would you be stuck with PL/SQL technology? It's not all that hard to pick up new programming languages. Having said that, though, I have never seen any surveys indicating that an Oracle Database developer (PL/SQL, SQL and related) is paid anything like 4x less than a Java developer. Generally, the pay scales are close and Oracle Database development pays on the high side compared to many others.

      So I am surprised, but I am not denying your experience. I would say, though, that you should take a look at Oracle Application Express ( That gives you a way to leverage your database programming skills directly into website and app development.

    2. Thanks Steven for your prompt reply. I am from India and here the hot skill jumping in is Android and Java where job opportunities and pay is more than of a PL/SQL resource.

      Also, I know basics of java and android but i am not able to crack interviews and without showing that much knowledge they wont take me in these skills (thats y said stuck).

      Just as you said Oracle Apllication Express, are there other skills where i can take on that technology and crack interviews. Thanks for your guidance.

  16. Hi Steve....I am .net developer with exp exp in IT is 6. now i want to learn PL/SQL and switch to PL/SQl developer.I this good idea??

    1. Well, that's kind of the topic of this whole post and the discussion swirling around it. I think it's a great idea. But it depends on your local market for jobs or whether it is possible to find remote work in the same.

  17. I feel so strange reading all this problems, maybe the difference is that i'm working in homogeneous enviroment with superior role of oracle ? Anyway currently i'm with pl/sql over eight years (or even more). Not having problems with looping over collection and mostly my opinion is close to Ionut S. PL/SQL is mostly not cool because you are not producing any visual representation of your work, PL/SQL can't do this alone (maybe asci pictures). When my directors saying that relational databases will die soon this is pretty much same feeling when they citing one-page articles about PL/SQL ada similarity and basically it is 1970. They saying this year after year "Frameworks are better". But when it comes to details - i'm still here - doing more new things with oracle that they not expected it can be done. If you are programming with PL/SQL from some time you are already familiar with java so this is open natural way. When it comes to cool things and visual representation of your job there are also APEX and Formspider that my boss never expected they exists .There are such of things like ords and rest services jquery and so. If someone will say he is stuck with PL/SQL, my guess is that is a pressure of new trends and low ability to use potential. Summarizing - there are multiple ways that we can slowly prepare when PL/SQL will be gone (it will be about 20 years :-), and even then there will be someone urgently needded).

    1. Jareeq, thanks for contributing your thoughts and experience. Pretty clearly, one's expectation of finding high quality, challenging work developing apps on Oracle Database (SQL, PL/SQL and more) can vary greatly.

      And yes in today's world of "apps in your hand", without question a "backend only" language like PL/SQL is less enticing. Hurray APEX!

  18. Hi,
    I'm one of those manager who decided to go ALL IN ( Apex, PLSQL, ORDS - in 2012 ).

    It was the best decesion I ever did. We are faster and cheaper then any Java, .net team. We have already a prototype ready when they are still thinking about the framework and java version they want to use.

    I hab to say - Oracle - PLSQL, Apex, ORDS Oracle is the best for refactoring COBOL, PL1, DB2 - mainframe applications, with focus on business logic, or were the logic is used in Online and Batch.

    1. This comment is a great way to start my week (it is now Monday 6 AM). I am curious: did you actually "compete" with the other teams to produce that prototype? Did it involve refactoring mainframe applications?

  19. I have 14 years of app development, mostly Java, J2EE and recently had to learn PL/SQL (I used Oracle and SQL before, but not so much PL/SQL). I found it fairly simple and small area to learn. So IMHO it is much easier to hire Java developer with basic SQL skills and train him PL/SQL then to hire PL/SQL and train him Java/.NET . Java on it's own is not that big... But adding frameworks, Spring, J2EE, some UI's frameworks,patterns, Hibernate... That's much bigger cake to eat. PL/SQL can be mastered within 6 months of intense learning and working with. IMHO it may be much harder to do same with Java and framework that works on it. So from enterprise applications standpoint PL/SQL is usually very small stack of technology used to run modern applications. If written in JAVA there is much bigger piece.

    1. I agree. PL/SQL is much easier to learn and write than Java. The SQL inside it....perhaps more on par from a different perspective.

  20. Hi Steven,
    I still feel very positive about PL SQL and its future. some of the largest bank or the newer emerging banks around the globe still rely on ORACLE, and PL SQL to solve their mundane issues.May be in this new era, firms are adapting to EDB (Enterprise PostGres SQL based), still when it comes to scalability,reliability and data security, ORACLE still is at its apex.

    1. Thanks for your thoughts. That comment in my In Box was a nice way to start my day.

  21. Hi Steven Sir,

    I am following you since last 2 year in your practically perfect PL/SQL YouTube channel.

    I have 4 year experience in PLSQL, Oracle forms and Java as well, but I want to go with only PLSQL because I am perfect in PLSQL not like you just little bit, should I focus on other then PLSQL or concentrate only in PLSQL advance techniques. Please guide me.

    1. Golu, glad to hear you have some Java. I think it will be relatively rare in the coming years for anyone to just have backend database experience. You will need to handle more. I would look into Application Express and more than PL/SQL advanced techniques, build a very solid knowledge of SQL and advanced SQL. That will help you all OVER the place. Use the site to build your skillset.

  22. Hi Steven,

    I work for a Government Client which has a Big oracle workshop(Systems) comprising oracle Forms, Reports, SQL and PL/SQL . The Oracle Forms(11G) Based Applications here are tightly integrated with backend pl/sql development and are running effectively since 20 years.
    Since the recent years .NET Technologies Introduced here and are slowly replacing/building with .NET Technologies. Am an Oracle Developer 28 years old who has 5 Years of experience and has expertise in Forms,Reports,SQL and PL/SQL Development. What would you do if you were in my shoes?

  23. I would learn about ways to build APIs via PL/SQL Packages and REST (ORDS) to provide to the .Net developers (that is, assuming their apps are running against Oracle). Open a line of communication with them about how the database experts on the team can and should "team up" with the UI developers to make sure their use of the database is optimal.

    Besides that, generally I suggest you broaden your knowledge: get basic familiarity with Java and JavaScript, definitely explore adding Application Express (which may have a brighter future than Oracle Forms).

  24. Hi Steve
    I have now more than 10 years of experience in plsql working in India , given the problems occuring in IT market , what skills related to DB do you suggest me to learn or add to stay relevant in the coming times

    1. I am not sure I have much to add beyond my answers to previous comments along these same lines. To summarize:

      1. Learn Oracle Application Express and some JavaScript to go with that.

      2. Make sure your SQL skills are sharp: analytic functions, pattern matching, ANSI joins.

      3. Get really good at building REST APIs with ORDS.

    2. I really appreciated your suggestion for PL/SQL and Oracle APEX developer like me. There are many places where we must have to use javascript in APEX applications so as a APEX developer you should know javascript as well. Thanks.

  25. For my part, I certainly love PL/SQL and see a well-crafted Oracle application as a thing of beauty. Unfortunately, Oracle has lost us with their optimizer. Over and over again, we see random queries running just fine until one day without warning, and with no structural change in the database, and with only daily incremental increases in the amount of data in the underlying table, they go from 5 seconds to 2 hours. This has happened often enough that we are moving our high-performance applications off of Oracle and into a different architecture such as Redis or other in-memory tool. I don't really understand how the optimizer could be so bad; prior to Oracle 11, I never saw this kind of sudden performance tank. Yes I know you can freeze plans but it is quite complex and should not be necessary. Oracle should know how fast the query runs with different plans (especially when it picks a bad execution plan). When a query decrases speed by a factor of 100x, Oracle should be smart enough to revert to an earlier plan and see if it works better than the new one that it selected.

    And yes I know this is somewhat off-topic, but only marginally, because if Oracle was consistently high performing it would maintain a greater market share and this would lead to a greater popularity of all technologies Oracle.

    --Bill Pribyl

  26. Hi Steven,
    I've experience in plsql for 8 years, currently working in a project which is a sole project in my company for Kolkata, India Location, has Oracle plsql in backend. But I've to hear from my mangers and senior company personnels that changed or upgrade my skillset (especially for niche technology).

    But I want to stay in oracle and learn more plsql techniques. Please guide me, I'm getting confused day by day.

  27. Great Post! Thanks for sharing.

  28. PL/SQL is the best programming language out there.

    It is also very underrated.

    It's easy to learn and is basically a 4GL.

    You can concentrate on the business functions and logic rather than worry about frameworks and other stuff other languages like Java have.

  29. My prediction is: the javascript world will eat up a large chunk of the java world. But the need for SQL (and plsql) developers will even increase. Especially when the database will be the rest service provider (JSON) and the javascript world will be the rest consument.

    Nowadays I see more an more Java developers starting to learn angular.js (because angular feels extremly close to Java).

    Plsql developers need to enhance their knowledge also to other technologies. Main reason is that with plsql alone you can not build a full application. You need at least some additional parts of the webstack (html, css, javascript). And APEX of cause.

    Now with MLE in the database we might see other langagues take over some part of the previous plsql domain. I still believe there is a need for plsql in the forseeable future.

    Also I do see new young plsql developers. Often they started out learning Apex and now extent their knowledge to the database side.

    We live in interesting times.

  30. I am moving my career from PLSQL to Oracle Apex

  31. Sounds great! But I don't see it as a "from -- to" scenario. You are extending the reach of your PL/SQL knowledge to also build websites with APEX. That is a powerful, winning combination. I wish you the very best.

  32. Hi Steven,
    I have working in Oracle for last 7 years. I am very much interested in working with "DATA".
    I love writing SQL and PL/SQL code. I am continuously learning new stuffs in oracle.

    So far the only disadvantage I am seeing in Oracle is, that it could not handle the large volume of data (in terms of performance). I feel Oracle is best for OLTP systems. What about the future of oracle in Analytical systems ? Most of the time, I felt, why oracle is not moving into Parallel processing architecture ? Like MPP (Teradata), Node clusters (like AWS) ? etc..

    Rather having the database server as a centralized one, if it is converted into de-centeralized multiple parallel servers, it would be better. can you put some lights on this ?

    1. Oracle moved into parallel processing architectures a long, long time ago and I expect we will support increasingly de-centralized models as well. But I have no specific light to shine about this right now.


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers.

In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply.

All the code shown in this example may be found in this LiveSQL script.
How to Get a Mutating Table ErrorI need to implement this rule on my employees table:
Your new salary cannot be more than 25x th…

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, reducing context switchi…

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 perspective…