11gR2: Performance with NVL or NVL2 is slow

My Oracle Support article ID 2093132.1 for the document "Slow Performance With NVL or NVL2 Referencing PL/SQL Function Even When Ignoring the Function's Result" directs us not to an issue but to a basic fundamental part of PL/SQL that is certainly worth checking out!  Without looking at the document, can you guess why this is happening?

Using an array update statement may cause row or block corruption

While advanced PL/SQL programming structures like an array can be advantageous at times over row by row (Cary Millsap's slow by slow), the article "ALERT: Bug 7662491 - Array Update can corrupt a row. ORA-600 [kghstack_free1] ORA-600 [kddummy_blkchk][6110/6129]" found on My Oracle Support by way of note ID 861965.1 shows that not every decision we make is without consequences!  Think you do not need to know about this if you are in Support?  Think again, unless you already know what table holds data about blocks which get corrupted and in that case you probably already have an alert built looking at that table to see when corruption occurs right?

Weekend Learning: ALL_ARGUMENTS table

Of course having joined Steven Feuerstein's webinar about creating readable PL/SQL code I knew that I was going to be learning something, but I did not think it would be something that was probably very simple and well known to many people yet I have never had to use it before!  Have you ever had to query the ALL_ARGUMENTS table before?  From the name, I bet you can guess what the contents are but have you used it before?  This was my introduction to it!  Maybe I could have learned about it when I posted a link to the Oracle Data Dictionary, but again this was a first for me so I'm excited to see how to use it in the future.

A lesson on resetting EBS passwords

If you have ever reset a password in the E-Business Suite for an FND_USER record, you know the typical password parameters required to get it to be accepted by the application.  Yet what happens when you forget those rules, and try to reset the password using a script?  Let me tell you, the results are not good!

Remember my post a few days ago about not jumping to conclusions?  I wish I could tell you that I did not jump to a conclusion during our DR testing when I had just reset a password for a FND_USER record, and had a problem when logging into the account, but honestly I jumped right to the conclusion that the DR project must be part of the problem here.  When I was logging in with the account, I would get the login prompt but when I should have been presented with the EBS application Navigation landing page all I received was an error message indicating that the page could not be reached.  Of course I had logged into the DR environment with my credentials an hour or two ago, so of course something in DR had changed which is preventing me from logging in with the other account right?  WRONG.

What was the problem?  I reset the password for the account using a PL/SQL script that I really have not used a lot in the past, so I did not realize the same rules applied here when creating a password!  What I did was to create a password with consecutive duplicate characters (for instance, the zz in pizza are consecutive duplicate characters so that word cannot be used in a EBS password), and since there was no application validation as I was using the script, it apparently saved but the EBS application did NOT like it one bit.  After I had asked my co-workers about this problem, it was suggested I make the password contain no consecutive duplicate characters in a row, and after doing this with the script I was now able to log into the EBS application with the other account!

Whitespace and SQL formatting

Today I share something I learned from Ric Van Dyke while continuing the blog spotlight, in that the PL/SQL engine actually breaks down the SQL bits of code into machine readable (formatted) text by standardizing the spacing so that the engine can digest it in a uniform manner.  I have been aware that the comments and spacing were removed or ignored, but I never knew why and now I do.  It even created a good exchange in the comments section too and you may get some new ideas there!