To refresh multiple Materialized Views you can use the DBMS_MVIEW package, with the procedure aptly named Refresh. One method is to provide a comma-separated list of Materialized View names, or you can use an DBMS_UTILITY.UNCL_ARRAY to achieve the same objective.
When using the latter method, I stumbled upon this oddity (which is not in the current documentation, or at least I couldn't find it).
The procedure that I initially wrote was the following:
Last week I saw a PL/SQL teaser tweet by Steven Feurestein (@sfonplsql):
The question sounded simple: can you build a valid package with header and body with more than one procedure – that can compile but can never run. That was a really interesting question (more...)
Instrumentation of PL/SQL code is essential. My favourite tool to instrument PL/SQL is Logger.
Martin Giffy D'Souza wrote a few blogs on how to speed up development with Logger and I want to chime in with my own productivity booster.
What I have written is a PL/SQL package that will generate a Procedure or Function body with all the calls to Logger. This includes all the arguments, or at least the IN and IN/OUT arguments.
Recently someone asked me: "How did you do that? When I include an APEX page in a Page Group, the correct tab is automatically highlighted"
When I setup an application, I usually use Dimitri Gielis' method,
so instead of using "real tabs", I use a List and display that list as Tabs.
For each of the "Tabs", I also create Page Groups, just to keep things organized.
Each of the List Entries will have a (more...)
The PL/SQL Utility Library, codenamed "Alexandria", now has a new home at GitHub
. This will make it easier to collaborate on the project. If you want to contribute, just fork and submit a pull request.
The liibrary contains around 50 PL/SQL packages as well as a few types. There are no table dependencies, which reflects the general-purpose nature of the utilities. Jeffrey Kemp has a great guided tour of the library
which highlights some of (more...)
It seems impossible to raise an exception when handling no_data_needed.
create or replace function demo return sys.odciNumberList pipelined
exception when others then
dbms_output.put_line('in exception handler, sqlcode: ' || sqlcode);
firstname.lastname@example.org > select * from table(demo());
ORA-06501: PL/SQL: program error
ORA-06512: at "SOKRATES.DEMO", line 8
ORA-01476: divisor is equal to zero
no rows selected
in exception handler, sqlcode: -1476
If you want to be able to create contemporary high-performing PL/SQL code, welcome to my training event in NYC (hosted by NYOUG) on November 7, 2014:
I think, it is as important as ever to follow best practices for server-side development. There are lots of options that could get the job done "now", but very few that would survive for months/years. Also, there is a constant pressure from various "alternative" solutions to (more...)
My session on "Oracle 12c for Developers" is done.Afterwards someone asks the question:
What happens when you use DBMS_REDACT with a complete row update?
My guess was that it would place the redacted data in the column, but I haven't tried it, so here goes:
create table emp
,to_char (abs (dbms_random.random)) credit_card
(object_schema => 'A'
,object_name => 'EMP'
,policy_name => 'Hide Creditcard'
It seems to me that I talked about my most recent book everywhere except for my own blog... So, let me introduce you a one and only performance tuning book dedicated to PL/SQL
This book took me much longer to write than I initially anticipated - and to be fair, that's the reason for staying quiet at the blog. I just didn't have time/energy to write everywhere :-) Although, it was fun writing - (more...)
The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. (more...)
While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.
When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that (more...)
Sorry for staying quiet for some time, but for some good reasons. Some of these reasons will be explained in later posts, but for now I would like to share the story I wrote upon request of Steven Feuerstein
, who asked to tell how PL/SQL impacted our life. Here is my story:
I started to work with databases already in college, but more from the data modelling/business analysis angle. When I moved to (more...)
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.
© Eddie Awad's Blog, (more...)
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;
SQL> create table test_array as
2 select level num_col from dual
3 connect by level <= 10;
SQL> select * from test_array;
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...)
In my previous blog post
, I took a look at the new APEX_JSON package that ships with Apex 5.0 and its capabilities for parsing JSON.
In this blog post, I am going to look at how the APEX_JSON package can be used to generate JSON from data in your database using PL/SQL.
There are multiple ways of creating JSON output using the APEX_JSON package. There are several overloaded write() procedures which can output (more...)
As I mentioned a year ago
, it looks like Apex 5.0 will include a new package called APEX_JSON for parsing and generating JSON. In this blog post, I will take a quick look at the parsing, and the next blog post will be about generating JSON output.
Since Apex 5.0 is still in Early Adopter (beta) mode and not yet released for download, we can run a query against the data dictionary (more...)
Do you need to create PDF reports from PL/SQL?
There are a number of options:
- Anton Scheffer wrote a PL/SQL package called AS_PDF3 that covers all the basics: Different fonts (including TrueType), images, and tables.
- There's the commercial PL/PDF package that has more features, such as the ability to use existing PDF documents as templates for new documents, compression, encryption, and more. There's also an extra Toolkit component which allows you to extract pages from (more...)