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:
Back in January 2014, I wrote a script to cleanup an Oracle
student schema. It worked well until I started using APEX 4 in my
student schema. You create the following 75 objects when you create an APEX 4 schema.
OBJECT TYPE TOTAL
Here’s the modified script that ignores the objects created automatically by Oracle APEX when you create a
De Java SIG (Special Interest Group) van AMIS organiseert op donderdag 16 april een bijeenkomst (publiek toegankelijk) over asynchrone interacties in Java EE (web) applicaties. Synchrone interacties in de echte wereld en in IT applicaties kunnen de boel behoorlijk ophouden. Synchroon betekent wachten – en resources vasthouden. Dat kan een probleem vormen voor schaalbaarheid en performance. In deze sessie leer je dit probleem te tackelen.
Deze SIG is interessant voor ontwikkelaars met JAVA kennis en (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.
Thanks to twitter I found this post
by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.ORA-01489: result of string concatenation is too long
I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search (more...)
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...)
Steven Feuerstein runs a great site at PL/SQL Challenge
that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.
PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.
Unfortunately do to a timing
I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha
Of course, oracle-base
is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause
). As a developer I'm pretty excited about these in particular.
Creating inline functions (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);
email@example.com > 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
Do you write PL/SQL? Me too!
Trouble is, sometimes it's hard to decide how to structure your packages - particularly in an APEX project. Over at the PL/SQL Challenge
website run by Steven Feuerstein and friends there is a page dedicated to roundtable discussions.
I submitted my question not so long ago and hope to get some interesting responses. Why don't you give the site a visit and contribute? The discussions usually last for about (more...)
Disclaimer: I’m not posting to make me look better, we’ve all written code that we’re later ashamed of, and I’m no different!
This is some code from a system I was maintaining some time ago. I’ve kept it since then because it illustrates a number of things NOT to do:
(in_password IN VARCHAR2)
-- do NOT copy this code!!! ...
RETURN VARCHAR2 IS
CURSOR cur_rules IS
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: