Afraid to COMMIT; Going through some old documents, I found this little gem I had uncovered doing some analysis of the source code.

I found this crazy enough to save, so I hope you enjoy it.

I found 14 or 15 commits in a 115 line procedure. I was shocked and stunned. It was wrong on so many different levels. I share with you the pain I went through.

We'll start off with a call to the logging table (just a quick note, this was not a stored procedure but a INSERT statement).

log_something;

Creating a record in the THIS_TABLE, it will have a status of A. Everything in there has a status of A.

INSERT INTO this_table (my_id, start_date) 
VALUES ( l_my_id, sysdate)
RETURNING table_id INTO l_table_id;

I know some could argue for COMMITs being inside stored procedure, but it was hammered into my head at an early age that the calling application should perform the COMMIT.

COMMIT;

THIS_TABLE had multiple "rules" tables. I understood the concept, but the implementation was not so good. This is the first "rules" table.

UPDATE rule_tab
  SET status = 'D'
WHERE my_id = l_my_id
  AND status = 'A';

Just so you are aware, that STATUS column had no constraint on it other than the size VARCHAR2(1).

Guess what time it is?

COMMIT;

Here is the second

UPDATE other_rules_table
  SET status='D'
WHERE my_id = l_my_id
  AND status='A';

Guess what?

COMMIT;

OK...here comes the other awesome part...to switch the status back to A (Active), we're going to create a job...in 30 minutes

dbms_job.submit
  ( job => ln_jobno, 
    what => 'update rule_table set status=''A'' where my_id = '||l_my_id||' AND status = ''D'';', 
    next => sysdate+(.5/24));

COMMIT;

I kind of understand that one...for the job to go into the queue you have to issue the COMMIT.

For some reason, it's now cool to use a nested block.

BEGIN
  dbms_job.submit
    ( job => ln_jobno, 
      what => 'update other_rule_table set status=''A'' where my_id = '||l_my_id||' AND status = ''D'';', 
      next => sysdate+(.5/24) );

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
     insert into errors (name,error_date, text) 
     values ('it broke', sysdate, ' MY_ID '||l_my_id||' creating job  to set to a ');
     COMMIT;
END;

Now we have 2 jobs created, one to update THIS_TABLE and one to update my first "rules" table. -10 for using DML in a job. -10 for not putting them into a single job that could fail together. -30 for not creating a stored procedure to do this.

But wait, it's not over yet. We're getting to the very best part I think.

BEGIN
  dbms_job.submit
    ( job => ln_jobno, 
      what => 'insert into errors (name,error_date, text) 
                       values (''wow'', sysdate, ''error turning it back on') ;', 
      next => sysdate+(.5/24));

EXCEPTION
  WHEN others THEN
      insert into errors (name,error_date, text) 
      values ('doing stuff', sysdate, ' MY_ID '||l_my_id||' creating job to log errors');

    COMMIT;
END;

Did I catch a "niner" in there?

Did you catch what that final job did? It "logged" an error from the previous 2 jobs. Really? Do jobs work like that? I'm not really sure that they do.

This little snippet is just a snapshot into my life over the last few years. This kind of thing is everywhere. (I know everywhere!). I've mentioned before, but I've been reading The Daily WTF since 2005. Daily. I've learned more from that site than perhaps any other because you learn what not to do...which is just as important as what to do. It takes years to gain the necessary experience (read: screwing up) to know what not to do, The Daily WTF speeds that up significantly by allowing you to witness others mistakes. We've all made them, to be sure. It's whether we learn from them that is important.

Labels: funny, rant, wtf

  ¶ 3/14/2010 06:07:00 PM   0 Comments Links to this post

  Everything is a Bit Bucket By: Michael O'Neill
@oraclenude
oraclenude.crisatunity.com

In response to Chet's frustration over yet another encounter with a database agnostic, I wanted to contribute my first article to the oraclenerd franchise. My thoughts seemed too long for the comment stream.

I ascribe the kernel of thought behind "the database is a bit bucket" primarily to each and every database vendor that ever existed. Every database vendor, in an effort to persuade users of competitive products to adopt their product, has participated willingly in espousing some core aspect of how "same as the other guy" their product is in addition to whatever differentiation pitch they have.

Now, the generally weak-minded and lazy developer (yes, I think the majority of developers are in fact weak-minded and lazy) latches on to the vendor's selective "sameness" claims for professional and personal reasons. (full disclosure: I am both an Oracle DBA and .NET developer)

Professionally, because they are financially invested in writing third-party code not database code. To them, the less they spend learning and understanding the particulars of things like databases, operating systems, networks, human beings, etc. the better. Personally, because there is a dominate thread in the culture of developers to dismiss the database as interesting or meaningful. It is a form of heresy to show affection towards any platform in any specificity.

This is why Java's Big Lie of "write once, run any where" swoons so many. Java's Big Lie is analogous to "the database is a bit bucket" by declaring that even the language of software code should be as absolutely interchangeable as possible - even at the expense of being cost-effective or useful. There is an unquestioned faith that decoupling everything from everything is a good thing. This faith gives us code that is as far from the simplest thing that could work from the first moment writing the code is undertaken. It is a faith I reject. That's why I'm an ORACLENERD.

P.S. I know oraclenude and oraclenerd is confusing. It's supposed to be.

Labels: database, moneill, rant

  ¶ 3/10/2010 01:34:00 PM   3 Comments Links to this post

  APEX: Create and Parse Arrays It's been awhile since I've been able to work with APEX extensively, so I am rusty.

A question came up today whether we could get multiple values into a single variable (Item in APEX).

Yes we can!

APEX_UTILSNeed some data first:

CREATE TABLE t ( some_text VARCHAR2(10) );

INSERT INTO t ( some_text )
SELECT dbms_random.string( 'a', 10 ) some_text
FROM dual
  CONNECT BY LEVEL <= 5;

CJUSTICE@TESTING>SELECT * FROM t;

SOME_TEXT
----------
thrFXviVWJ
kpfGRRwctv
EVxNrcmBHC
gcBlHaKrLa
irYduOZfkS

I want that table data to be in a single item. TABLE_TO_STRING is your function.

VAR C VARCHAR2(100);

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  SELECT some_text
  BULK COLLECT INTO l_table
  FROM t;

  :c := apex_util.table_to_string( p_table => l_table );
END;
/

PL/SQL procedure successfully completed.


C
-----------------------------------------------------------
thrFXviVWJ:kpfGRRwctv:EVxNrcmBHC:gcBlHaKrLa:irYduOZfkS

Easy enough. How about converting it back to a table? STRING_TO_TABLE is your answer.

DECLARE
  l_table APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
  l_table := apex_util.string_to_table( p_string => :c );
  
  FOR i IN 1..l_table.COUNT LOOP
    d( 'value ' || i || ':  ' || l_table(i) );
  END LOOP;
END;
/

value 1:  thrFXviVWJ
value 2:  kpfGRRwctv
value 3:  EVxNrcmBHC
value 4:  gcBlHaKrLa
value 5:  irYduOZfkS

PL/SQL procedure successfully completed.

Done.

Labels: apex, apex_util, howto

  ¶ 3/10/2010 01:23:00 AM   0 Comments Links to this post

  The "Database is a Bucket" Mentality Front and center again...I just woke up from a nap, I'm grumpy, so I must write. Besides, I haven't had a good rant in quite some time.

Friend of mine asked me last week for some advice, specifically asking if there was a tool to convert Oracle SQL Syntax to the ANSI SQL syntax. (A quick search turned up this (it was the first result), if you're interested).

I had to ask why.

Client is switching to an open source database, i.e. "free." Oracle licensing is way too pricey.

I'm sure Oracle costs a lot of money, it's pretty darn good software. Quite possibly the best in the world especially in the database realm. I've written about the incredibly feature rich goodness that is the Oracle database here here...actually, just trust me. It's in my name.

Why is there even a comparison?

Could it be that everyone out there believes that the sole purpose of a database is to store data? That it can't do anything else? The storage and retrieval of data...that's all it does of course.

It's like saying the Democrats and Republicans are the same...at face value, perhaps, but the devil is in the details.

This, this "Bit Bucket" mentality is what is so incredibly frustrating.

I am no position to argue the differences between the various flavors of database, I lack the experience. But if I were using SQL Server, I would leverage the shit out of it's capabilities. If I were using MySQL, I would leverage the shit out of it's capabilities. If I were using Firebird, I would leverage the shit out of it's capabilities. Same goes for every single flavor out there. Get my point here?

The database is NOT a bit bucket!

Do I need to use more 4-letter words?

I know that Oracle is feature rich and that 99% percent of your code can live in the database...think APEX and PL/SQL. You could probably put ALL of your code inside the database if you wanted to put the javascript in BLOBs as well.

Please, please please quit telling me they are the same...they are not.

Follow up rant by Mr. O'Neill can be found on this following post Everything is a Bit Bucket

Labels: database, design, development, oracle, rant

  ¶ 3/09/2010 10:50:00 PM   10 Comments Links to this post