A recent addition to my Oracle PL/SQL library is the book Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.
I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.
In the foreword of the book, Bryn Llewellyn writes:
The UTL_FILE database package is used to read from and write to operating system directories and files. By default, PUBLIC is granted execute permission on UTL_FILE. Therefore, any database account may read from and write to files in the directories specified in the UTL_FILE_DIR database initialization parameter [...] Security considerations with UTL_FILE can be mitigated by removing all directories from UTL_FILE_DIR and using the Directory functionality instead.
I was aware that up to Oracle 11g, a PL/SQL program wasn’t allowed use an associative array in a SQL statement. This is what happens when I try to do it.
SQL> drop table test_array purge; Table dropped. SQL> create table test_array as 2 select level num_col from dual 3 connect by level <= 10; Table created. SQL> select * from test_array; NUM_COL ---------- 1 2 3 4 5 6 7 8 9 10 10 (more...)
Steven Feuerstein was dismayed when he found in a PL/SQL procedure a cursor FOR loop that contained an INSERT and an UPDATE statements.
That is a classic anti-pattern, a general pattern of coding that should be avoided. It should be avoided because the inserts and updates are changing the tables on a row-by-row basis, which maximizes the number of context switches (between SQL and PL/SQL) and consequently greatly slows the performance of the code. Fortunately, (more...)
email@example.com > create procedure p is begin null; end this_does_not_compile; 2 / Warning: Procedure created with compilation errors. firstname.lastname@example.org > show errors Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 1/32 PLS-00113: END identifier 'THIS_DOES_NOT_COMPILE' must match 'P' at line 1, column 11 email@example.com > REM expexted firstname.lastname@example.org > create function f return number is begin return null; end this_does_not_compile; 2 / Warning: Function created with compilation errors. email@example.com > (more...)
However I had a recent requirement to use a web service that makes use of Digest Authentication. If you have look at the UTL_HTTP SET_AUTHENTICATION subprogram, it only addresses Basic authentication (and, apparently, Amazon S3 which looks intriguing).
It annoyed me slightly that when I googled modify_snapshot_settings just now and all of the examples used huge numbers for the retention with (at best) a brief comment saying what the number meant. Here is a better example with slightly more readable code. Hope a few people down the road (more...)
Unfortunately, immediately got an issue. It seems to me that passing a variable to FETCH (more...)
The point of confusion was the fact that between 11gR1 and 11gR2 Oracle development team significantly re-thought this feature (more...)
As we all know, starting version 11g Oracle tracks PL/SQL dependencies in much more granular way (more...)
As one of the examples I decided to illustrate how XMLType can be used as both a collection of (more...)
It leads to a standard problem - how do you make sure (more...)
There are a mixture of types too, fixed width, csv, etc. Thankfully, I've read enough of the code now that it's relatively easy to (more...)
One thing I've seen a lot of is calls to dbms_output.put_line. Fortunately, I've seen some dbms_application_info.set_module and other system calls too. But back to that first one.
1. When I used dbms_output, (more...)
Like a middle aged man visiting the gym for the first time in 10 years, I am merely going to flex my blogging muscles here…
One of the recurring issues that annoys me on the OTN SQL & PL/SQL forum, and also the APEX forum is the misunderstanding of what (more...)
One of the most interesting things in (more...)