Square DBA – Oracle DBA Talk

Poor performance using public synonyms

I have heard the argument that says don’t use public synonyms if you have a large user base.

Public Synonyms are a good thing for most of us, they are not evil. I use public synonyms, but I do not over use them. Use them when you need them, but don’t make a public synonyms for every darn object in your schema.

It is generally a good idea to try an interact with your tables via packages. End users do not need to see the tables much of the time, especially in an OLTP environment. Public synonyms may still be used, but in most cases you will just want to use the package to do your work.

Even the database makes use of public synonyms when access many of your SYS packages. Like all things moderation is a good idea.

Sacramento Area Oracle Group

There are no Oracle user groups in the Sacramento, CA area. If any is interested in started one, let me know. There was a group years ago, but it looks like it has not been around for a while. Sac Francisco is just too far to drive.

Clean up phone numbers

I needed to clean up a list of phone numbers today. The phone numbers where in a varchar2 column in the database and were listed like this: (916) 555 -1212.

I knew I could used some instr and substr functions to cut the data out, but I wanted to use some of the regex functions found in Oracle 10G and above. This handy little piece of code simply removed all non numeric digits from a string.

select REGEXP_REPLACE(CALLNUMBER,'[^[:digit:]]', NULL)
from temp_cima where rownum < 11;

GTD Tasks in Outlook

As I have posted before I am always looking to streamline my to-do lists.  I think I have come up with the ultimate solution (at least for me).  Anything I use must sync with Outlook.  We use MS Exchange here at work.  People send me tasks through Outlook and I receive Outlook tasks from our MS Project server so I cannot get away from Outlook.  A great deal of my tasks come from emails I receive so again Outlook is central to creating tasks.

I try to follow a lot of the GTD methodologies.  Since a fair amount of tasks are based on emails I was looking for a way to streamline that process.  I created macros that help me classify emails on my to-do list.  I actually created 3 macros (Next actions, Waiting, & Someday).
These macros do the following:
1, Takes the items that is selected and creates a task
2. Attaches the selected item to the task
3. Sets the task’s category
4. Sets a reminder for certain tasks

Here are the instructions for creating the macros. (The directions are slightly different for Outlook 2007)

1. Start Outlook
2. Tools | Macros | Security
3. Choose “Medium”, which will prompt you on whether or not you want to run macros (VBA). You may need to restart Outlook at this point in order for that setting to take effect, I’m not sure.
4. Tools | Macros | Visual Basic Editor
5. Doubleclick on This Outlook Session on the left, which will open the code window on the right
6. In the code window, paste the code from the attached file (it is attached to this post)
7. View | Immediate to make the immediate window show up

Next I added these macros to my standard toolbar.

1. View | Toolbars | Customize
2. On the Commands tab, click Macros on the left-hand side
3. Drag the appropriate macro to the standard toolbar (say, next to the “Send/Receive“ button).
4. Right click on the button and change the Name field if you want to shorten the text or assign a different accelerator key or an image
5. Click Close

Now when I get an email (or any other Outlook item) I can click one of these new buttons and create a task with the item attached.

Stupid Errors

Stupid errors are always the most frustrating.  Today while running a SQL statement for an adhoc report I received a “ORA-12801: error signaled in parallel query server P050” followed by “ORA-01722: invalid number”.  I knew the invalid number error was the real culprit and not the parallel query error.  The statement runs for 15 minutes before erroring out so I am thinking the error occurs on some row in the results.  My SQL statement uses several functions, some Oracle functions and some created by myself.  So my first thought was one of my functions was not dealing with the data properly.  Looking at each function I could not find any issues.  My next step was going to be put my SQL into a procedure so I could handle the exception and find the problem row(s).  Then I spotted the problem.

Right there at the beginning of my SQL statement I wrote “select substr(summary_date, ‘YYYYMM’)”, instead of “select to_char(summary_date, ‘YYYYMM’)”.  Since this is not a syntax error I did not see it right away.  It also did not produce the error right away since the substr function is not applied until after the items in the where clause are evaluated.

In the end sometimes issues are so simple they are right in front of your face.  For the rest of today I will be telling myself to keep it simple stupid.

Oracle Podcasts

I am looking for a god Oracle podcast.  Do you know of any?  I did subscribe to one podcast fro OTN, but I am looking for others.  I spend about two hours in the car daily and normally devote that time to news, talk radio, and music.  I catch a couple of podcasts on photography and I thought an Oracle Podcast would be a good addition.