Pythian Group

Just got an interesting note on Twitter that you can’t call a stored procedure dynamically in Oracle from a PL/SQL block like passing the procedure name in a variable.

Well, yes we can!

And the answer is EXECUTE IMMEDIATE — it can be used to run anonymous PL/SQL blog and not just a SQL statement. However, you will want to think many many times before doing so… if you love your data.

Let’s create the test procedures:

SQL> create or replace procedure bingo as
  2  begin
  3  dbms_output.put_line('Bingo!');
  4  end;
  5  /

Procedure created.

SQL> create or replace procedure bambam as
  2  begin
  3  dbms_output.put_line('BAM BAM!');
  4  end;
  5  /

Procedure created.

Now let’s create a wrapper that we will call passing a procedure name: Read the rest of this entry . . .

I’m back again with another in what I hope will be a long line of “Quick Tips for Newbies” series.

At The Pythian Group, we have employees all over the globe, from our headquarters in Ottawa to regional offices in Boston, Prague, India and Sydney, and a few scattered remote workers in Seattle, Paris, Kiev, Brazil, South Africa and Wisconsin, among other places. In other words, we are spread across multiple timezones, and since it wasn’t too long ago that everyone was in Ottawa, this is something that still presents little quirks.

One such quirk involved email generated by one of our internal Oracle instances—via a stored procedure that used UTL_SMTP to send the messages—did not have timezone information in the “Date” email header. As a result, they would be stamped with the hour in Eastern timezone (Ottawa time), but the mail clients would think that hour was local. Depending on where you are relative to Ottawa, this could be many hours in the past or future. Of course, this wouldn’t be noticed if you were in Ottawa or even Boston, both in Eastern. For the rest, it was at the very least, an annoyance—but one that is easily fixed.

Read the rest of this entry . . .

In the process of scripting a database migration, I was in need of something akin to the GNU basename utility that I know and love on Linux. basename is most famous for taking a full file path string and stripping away the leading path component, returning just the name of the file. This can be emulated in PL/SQL with calls to SUBSTR and INSTR, like this:

substr(dirname,instr(dirname,'/',-1)+1)

(Thanks to Ian Cary, who shared this logic on oracle-l)

As you can see, this simply finds the last occurence of /, which is our directory separator on *nix and Solaris operating systems. On Windows, it would be \. It then returns a substring beginning one character after that last separator until the end of the string. Voila, a basic basename routine!

Upon reading the basename man page again, I found that basename also takes an optional parameter, a suffix string. If this suffix string is provided, basename will also truncate that string from the end. For example:

Read the rest of this entry . . .