I’m just back from a brisk walk around the Marriott World Center Resort in Orlando, FL.

What an amazing place! It is incredibly huge. I asked “How big?” when I was checking in and it has 2004 rooms, 265,000 square feet of meeting space, multiple pools (including some with waterfalls), at least one hot tub and a golf course. Oh yeah, and an alarm clock that doesn’t work. That’s my story and I’m sticking to it.

I’m here manning the EnterpriseDB booth with Derek Rodner and, my boss, Jim Mlodgenski. Jim is giving a presentation on Friday. We’re giving away copies of my book.

Holy simoleans, Batman. I wake up this morning, write a nice little Oracle tip and then next thing I see is Josh’s post about Sun->MySQL. That was totally unexpected to me.
I browsed over to PlanetMySQL to get the scoop and what do I see, Sun buys MySQL for $1 billion to take centerstage in the web economy. I don’t often agree with Matt but on this topic, I think I have to. This just makes a lot of sense for everyone involved. Sun, MySQL and MySQL users.

Oracle today announced the contribution and a preview release of an enhanced Oracle Call Interface (OCI8) database driver for PHP. This helps bring breakthrough scalability to PHP applications, further enhancing PHP as a viable development environment for mission-critical applications. The OCI8 database driver for PHP supports important Oracle® Database features such as connection pooling and fast application notification, enabling a single industry-standard server to support tens of thousands of database connections while providing higher availability.

The enhanced OCI8 database driver for PHP provides new, improved integration between PHP and Oracle Database 11g, to allow a server-side connection pool shareable across web servers and languages, significantly enhancing the scalability of web-based systems.

News has been slow in the EnterpriseDB world lately. I haven’t posted in a while.

Bob Zurek is EnterpriseDB’s (fairly) new CTO. He is an ex-IBM exec and maintains his blog on the IBM DeveloperWorks blog area. He’s been blogging for a long time so I’m not sure how he worked out the move from IBM to EnterpriseDB and kept the same area.

Anyway, Bob posted an open letter to the Wall Street Journal about a reporter’s take on Open Source. Bob objected to the reporter, Walter K.

I think PL/SQL programmers who move to Postgres all run into the same thing, how do I run the procedure or function once I’ve created it?

Obviously, it’s easy when you are calling it from another stored procedure or function.  What most mean, is how do I call it from the command line?  That’s easy too.

Using my function and procedure from earlier posts, i.e. 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL and Creating a Procedure In PostgreSQL Using PLpgSQL.

To call either the function or the procedure, you can run:

SELECT func_1();

Or

SELECT * FROM func_1();

You must include the ().

Same for the procedure.

SELECT proc_1();

Or

SELECT * FROM proc_1();

Of course, if you are using a GUI tool like PgAdmin III, it’s a somewhat different story.  You need to open the query window but the syntax will be the same.

Technorati Tags: postgres, postgresql, procedure, create, declare, plpgsql, function, code, develop

I recently wrote about creating a function in PL/pgSQL, 10 Steps to Creating a Function In PostgreSQL Using PLpgSQL.

Today, I am going to show you how to create a procedure.  You don’t really create a procedure, you use the same basic syntax as you do for a function.  The RETURNS keyword can get a little tricky as that keyword requirement changes based on what you are trying to do.  To start, I will create a procedure that functions the same as the function in the previous entry.  Then I will make some changes.

CREATE OR REPLACE FUNCTION proc_1(
  OUT out_parameter CHAR VARYING(25) )
AS $$
DECLARE
BEGIN
  SELECT datname
    INTO out_parameter
    FROM pg_database
    LIMIT 1;

  RETURN;
END;
$$ LANGUAGE plpgsql;

If you compare this to the function from the previous entry some things jump out right away.  There is no RETURNS statement.  Postgres is able to determine the return type from the OUT parameter.

The DECLARE keyword is completely optional.

Somewhat following on the heels of my post yesterday, Sympathy For The Devil is in the Details, Tim DiChiara of SearchOracle posted The Market Battleground.

Tim mentions that with Oracle’s steady lead (41%) in the database wars, maybe Oracle doesn’t need to worry. He points out though that microsoft, while enjoying a lower market share, is growing at 28% of over 2005 and may pass DB2 in the market.

Tim didn’t even mention open source databases though. Just this week, DBA Village, an Oracle forum/news site, had a poll asking which database most people thought Oracle should worry most about.

Matt Assay has a great blog entry today, Sympathy for the Oracle. He basically describes things I have said many times, Oracle would rule the database world if it adjusted it’s licensing model; Oracle Releases 11g Pricing and A Kinder, Gentler Oracle being two examples of that.

Matt talks about Oracle losing sales to SAP because of the approach to selling. SAP starts with thoughts and ends with execution while Oracle begins at the execution step.

Because open source makes it easier to win the bottom-up war.

I don’t think Oracle needs open source. It already has the best database.

Oracle releases a setup tool (YaST), Data Integrity (DIF Standard Implementation), and the Btrfs (Linux File System) to open source. You can read the press release at theNewsRoom.com.

oracle linux open source

In2M is a company who makes an online financial productivity and personal spending management system, including MVelopes Personal which won the prestigious 2006 PC World World Class award and was selected as one of the Top 100 Best Products of 2006.

In2M recently switched from an open source database (I’m not sure which) to Oracle Standard Edition. Oracle Standard is the lower tier database (i.e. cheaper) that does not offer additional features such as partitioning or OLAP processing. Notably though, Standard Edition comes with RAC as part of it’s feature set and does not require additional licensing for it.

Don Klaiss, Senior VP of Applications at Oracle has left to to be CEO of Compiere. Compiere is the largest, and, I think, oldest, open source ERP provider. Compiere was an early adopter of Oracle as a backend for its open source database.

Klaiss says, “After 15 years with Oracle, I decided to move on. Building and running such a large applications business had been challenging and exciting, but as the industry matured and began to consolidate, I felt there was a lack of innovation happening in traditional enterprise software.”

“Why?

I started working on a task for a project and decided that there were some very good properties that I could use to help me teach people PL/SQL and XML. I expanded a bit on the idea and ended up with a scripting language, implemented in XML, that can be passed into a PL/SQL procedure. The script can execute stored procedures, it has looping logic, conditional (CASE) logic, user defined variables, etc.

The nice thing about it is that it’s very easy to read and modify. The XML structure is also very easy to read.