Obsessed with Oracle PL/SQL

PL/SQL is a key enabling technology in Oracle Database. You should make sure that you are aware of and take advantage appropriately key features of the PL/SQL language focused on performance. I offer a short list of those features below, along with links to related resources. Most of this is also capture in this  slide deck : Key Performance Features All of these are covered in the slide deck above; the links will take you to the documentation on these features. Click here for the overall section in the doc on performance and optimization. Bulk processing with FORALL and BULK COLLECT The function result cache Optimized execution of user-defined functions from SQL The NOCOPY Hint Automatic compiler optimization But Don't Forget About SQL Optimization Chances are you could take full advantage of all the features listed above and more in PL/SQL, and still end up with a slow application. That's because at the heart of every application built on Oracle Database...

Connor McDonald offered a handy reminder of the ability to declare cursors at the package level in his Public / private cursors blog post. I offer this follow-on to make sure you are aware of the use cases for doing this, and also the different behavior you will see for package-level cursors. Generally, first of all, let's remind ourselves of the difference between items declared at the package level ("global") and those declared within subprograms of the package ("local"). Items declared locally are automatically cleaned up ("closed" and memory released) when the block terminates.   Items declared globally are kept "open" (memory allocated, state preserved) until the session terminates. Here's are two LiveSQL scripts that demonstrates the difference for a numeric and string variable (not that the types matter). Global vs Local variable  (community contribution) Global (Package-level) vs Local (Declared in block) Variables ...

SQL is a set-oriented, declarative language. A language or statement is declarative if it describes the computation to be performed (or in SQL, the set of data to be retrieved/modified) without specifying how to compute it. PL/SQL is a procedural language , tightly integrated with SQL, and intended primarily to allow us to build powerful, secure APIs to underlying data (via SQL). If I try hard, I can maximize the procedural side of PL/SQL and minimize the declarative aspect of SQL (primarily by ignoring or discounting the set-orientation of SQL). That's generally a bad idea. Instead, we Oracle Database developers make the most of the many powerful features of SQL (think: analytic functions, pattern matching, joins, etc.), and minimize processing in PL/SQL. What we should also  do, though, is recognize the make the most of the declarative features of PL/SQL. There are two big reasons to do this: 1. When you don't tell the PL/SQL compiler how to do things, the optimiz...

A recent Twitter thread delved into the topic of the best way to enable PL/SQL warnings for program units, including this recommendation from Bryn Llewellyn , Distinguished Product Manager for PL/SQL: which then led to Bryn volunteering me to delve into the details of PL/SQL compiler settings in an AskTOM PL/SQL Office Hours session.  Which I will do. But I figured I could start right off by writing this post. So let's explore how to set and modify PL/SQL compiler settings. First, you might wonder what those settings are or could be. The best way to check is by examining the USER_PLSQL_OBJECT_SETTINGS view (and of course the ALL* version to examine attributes of code you do not own but can execute): The values that are stored for a PL/SQL unit are set every time it is compiled—in response to "create", "create or replace", "alter", invoking a utility like Utl_Recomp, or implicitly as a side effect of trying to execute an invalid PL...

Got this plea for help via our AskTOM PL/SQL Office Hours program: Dear Experts, I have below written below code: ---------------------------------------------- Declare v_Table all_tables.TABLE_NAME%type; v_Mnt varchar2(2):='08'; Type Cur_type is Ref Cursor; C Cur_type; Begin v_Table:='ddi_ticket_10_1018'; Open C for 'SELECT * from bill.'||v_Table||v_Mnt||'Where called_nbr=123'; End; ------------------------------------------------------------------- When executing this code, I face this Error message. ORA-00933-SQL Command not properly ended ORA-06512: At Line 9. Please check the above code and modify for syntax correction I could, at a glance, pretty well guess what the problem is. Can you? I am not trying to boast. I just encourage you to not read further and instead examine the code. What could be causing his pro...