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

  Code Comment WTF? Part 209 Found this in a snippet today:

-- ********************************
-- End of Package Body

END package_pkg ;
/

Seriously? Was that necessary? Could I possibly be under the illusion that it is not the end of the package?

Stop it.

Now.

Labels: code, funny, style, wtf

  ¶ 3/09/2010 01:38:00 AM   6 Comments Links to this post

  DBA_TABLES vs DBA_OBJECTS

CJUSTICE@TESTING>SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Last night I was installing the Unit Testing repository for SQL Developer for a fun little post. After installing the repository, I just did a brief sanity check.

CJUSTICE@TESTING>SELECT owner, COUNT(*) c
  2  FROM dba_objects
  3  GROUP BY owner
  4  ORDER BY 1;
  
  OWNER                                   C
------------------------------ ----------
...snip
SI_INFORMTN_SCHEMA                      8
SYS                                 22970
SYSMAN                               1341
SYSTEM                                454
S_NQ_SCHED                              3
TSMSYS                                  3
WMSYS                                 242
XDB                                   682

27 rows selected.

Strange.

I created a user, UNIT_TESTING, to house the data and fed it to SQL Developer. Did I create the user?

CJUSTICE@TESTING>SELECT COUNT(*) 
FROM dba_users 
WHERE username = 'UNIT_TESTING';

  COUNT(*)
----------
         1

Yeah.

I check DBA_OBJECTS using UNIT_TESTING as the predicate:

CJUSTICE@TESTING>SELECT * FROM dba_objects WHERE owner = 'UNIT_TESTING';

no rows selected

Really?

CJUSTICE@TESTING>SELECT table_name
  2  FROM dba_tables
  3  WHERE owner = 'UNIT_TESTING';

TABLE_NAME
------------------------------
UT_LIB_TEARDOWNS
UT_LOOKUP_CATEGORIES
UT_LOOKUP_DATATYPES
UT_LOOKUP_VALUES
UT_METADATA
UT_TEST
UT_TEST_ARGUMENTS
UT_TEST_IMPL
UT_VALIDATIONS
UT_TEST_IMPL_ARGUMENTS
UT_LIB_STARTUPS
UT_LIB_VALIDATIONS
UT_LIB_DYN_QUERIES
UT_SUITE
UT_SUITE_TEST
UT_TEST_IMPL_VAL_RESULTS
UT_TEST_IMPL_ARG_RESULTS
UT_TEST_IMPL_RESULTS
UT_TEST_COVERAGE_STATS
UT_TEST_RESULTS
UT_SUITE_RESULTS
UT_SUITE_TEST_RESULTS

22 rows selected.

WTF?

Does this mean that my data dictionary is corrupted? This is a sandbox so it is very well possible...just never seen this kind of thing before.

Labels: dba, sql developer, wtf

  ¶ 3/02/2010 10:38:00 AM   0 Comments Links to this post

  PRIMARY KEY and NOT NULL I've seen this far too often. A table with a primary key (good) and a check constraint (NOT NULL) on the same column.

Stop doing it. Watch.

CREATE TABLE t
(
  id NUMBER
    CONSTRAINT pk_id PRIMARY KEY
);

SH@I_HAVE_NO_IDEA>INSERT INTO t ( id ) VALUES ( 1 );

1 row created.

Elapsed: 00:00:00.33
SH@I_HAVE_NO_IDEA>INSERT INTO t ( id ) VALUES ( NULL );
INSERT INTO t ( id ) VALUES ( NULL )
                              *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SH"."T"."ID")

As HillbillyToad said,



It is better than no constraint, that's for sure. The heart was in the right place...

Labels: constraints, development, sql, wtf

  ¶ 2/03/2010 07:46:00 PM   15 Comments Links to this post

  PL/SQL APIs I was once told by a colleague, after sending out a note encouraging them, that they were not convinced of the effectiveness of PL/SQL APIs. I really didn't know what to say. Further, their past experience with them was bad. Huh?

As I went to do a little research on PL/SQL APIs, the first result I got was



What do you do with a response like that? Just submit it to Oracle WTF and be done?

"...not convinced of the effectiveness of PL/SQL APIs..."

Oracle has been selling that for years hasn't it? The Packages and Types Reference manual lists about 218 public packages which obviously doesn't include the internal only ones. Oracle seems to have bought into the PL/SQL API.

Steven Feuerstein wants you to build them. I personally don't like his style, but there is no arguing results. TAPI, or table based API, suggests building a simple API that performs the INSERT, UPDATE and DELETEs for each and every table and then using that TAPI in your APIs up a layer. My friend Daniel McGhan has even built a TAPI generator, tapiGen, which will create all the code for each table.

I'm pretty sure Tom Kyte is a fan as well. Unfortunately his site is down at the moment so I cannot link up to anything in particular. Hasn't he written some of the most predominant books on Oracle, specifically developing applications with Oracle?

I am still stunned that I heard that. I would expect it from Mr. M perhaps, but I think even he would appreciate the advantages of not having to write any SQL.

So how do you respond? What do you say?

Labels: database, development, oracle, wtf

  ¶ 4/11/2009 11:36:00 PM   4 Comments Links to this post

  Exception Handling WTF In homage to Oracle WTF.

Trying to remove references for a table that no longer exists (a view was created in it's place), I found this little gem:

OPEN c FOR
  SELECT 'INVALID USER'
  FROM dual;

  RETURN c;

It was nested in an IF-END IF block.

Instead of just throwing the exception or return 0 (zero) rows, this was the preferred method.

Awesome!

Labels: funny, wtf

  ¶ 12/23/2008 12:43:00 PM   2 Comments Links to this post

  A Java PL/SQL Port Scanner Saturday night I started to wonder how I could get the movies of my DVR. Recently Verizon FIOS added a boatload of HD stations so I've naturally begun to record those. Problem is, the 50 hours or so I have available are quickly eaten up by the HD versions.

We have the multi-room DVR setup, which means we have one DVR but we can view those movies from any TV (with a cable box). Naturally all these boxes are on my internal network (router provided by Verizon).

I understand that with some boxes you can go out and buy additional storage, but I wanted a DIY solution. So, if I could find an open port on one of these boxes, then perhaps I could figure out a way to get the movies onto my computer.

Java to the rescue. Well, not really, but I have always wanted to learn more about that kind of lower level programming (sockets/ports/TCP/whatever). See, I don't really know what I'm talking about which is why I did it.

Since I use JDeveloper for writing SQL and PL/SQL, creating a java class (with the Main method) is easy.

import java.io.IOException;
import java.net.Socket;
import java.net.UnknownHostException;
import java.util.Date;

public class Class1
{
  private static void p ( String text )
  {
    System.out.println( text );
  }

  public static void main(String[] args) throws UnknownHostException, IOException
  {
    Date startDate = new Date();
    String machine = "192.168.1.102";
    Thread thread = new Thread();

    Socket socket;
    int j = 0;
    int x = 0;
    p( "start date/time:  " + startDate.toString() );
    for ( int i = 1000; i < 10000; i++ )
    {
      x++;
      p( "checking port:  " + machine + ":" + i );
      try
      {
        socket = new Socket( machine, i );
        p( "Port open:  " + machine + ":" + i );
        j++;
      }
      catch (UnknownHostException e) { }
      catch (IOException e) { }
      finally
      {
      }
    }
    p( "ports checked:  " + x );
    p( "ports open:  " + j );
    p( "start date/time:  " + startDate.toString() );
    p( "end date/time:    " + new Date().toString() );
  }
}

As LC would say, "That's not so bad."

Only it was slow, 1 port at a time. I did learn that trying to open up a port connection is s l o w. How can I speed this up?

I could make it threaded right? No. That would take too much time.

But hey, I know how to do it in PL/SQL (threaded that is). UTL_TCP should work nicely. I've never had to use it, so a great opportunity to familiarize myself with it (not that it's needed very often).

So I start Oracle XE and initially just try it out to see how it works:

DECLARE
  c UTL_TCP.CONNECTION;
BEGIN
  c := utl_tcp.open_connection
        ( remote_host => '192.168.1.2',
          remote_port => 1,
          tx_timeout => 1 );
  
  utl_tcp.close_connection( c );
EXCEPTION
  WHEN others THEN
    utl_tcp.close_connection( c );
END check_port;
/

Easy enough. Now let's start use DBMS_JOB to "thread" this.

DECLARE
  TYPE r_record IS TABLE OF OPEN_PORTS.IP%TYPE INDEX BY BINARY_INTEGER;
  l_table R_RECORD;
  job_count PLS_INTEGER;
  job_number PLS_INTEGER;
BEGIN
  l_table(1) := '192.168.1.102';
  
  FOR i IN 1..l_table.COUNT LOOP
    FOR j IN 1..10000 LOOP
      SELECT COUNT(*)
      INTO job_count
      FROM user_jobs;

      WHILE job_count < 20 LOOP
      
        dbms_job.submit
          ( job => job_number,
            what => 'BEGIN check_port( ''' || l_table(i) || ''',' || j || '); COMMIT; END;' );

        COMMIT;
      END LOOP;
    END LOOP;    
  END LOOP;
END;
/

So it's running, and running, and running...I opened up another session and did a count on user_jobs. 10000. 12000. 20000. Uh, I think there's something wrong here.

I finally kill the session and there are 36,446 jobs in the queue. All checking the same port.

Then I can't even issue a simple SELECT statement. Maximum number of sessions reached or some such rubbish. Just a reminder, it was Saturday night and I was just trying to see if I could do this quickly.

Since I couldn't connect, I tried bouncing the database (a loud gasp from the DBAs out there). Come on! It's a local XE instance...there's nothing important there.

Start it back up, connect as SYS and still can't issue any queries. I do notice as I hit the / sign and the Enter key a bunch of times that once in a while it will let me do a query. So I create a quick PL/SQL block to remove all the jobs from the queue.

BEGIN
  FOR i IN ( SELECT job FROM user_jobs ) LOOP
    dbms_job.remove( i.job );
  END LOOP;
END;
/

I go through the / + Enter key about 50 times until it finally runs. Success!

Or so I think. Still receiving the max sessions error. There are still 36,446 jobs in user_jobs. I can't remove them anymore as I get "job doesn't exist" when I try to dbms_job.remove it again. Hmmm...How about?

DELETE FROM dba_jobs;

36,446 rows deleted.

What?

How can that be? I didn't expect that to work at all...but it did. I have yet to look up the particulars of my actions (deleting from dba_jobs) but it did the job.

And yes, I'm still planning on writing this (correctly) in the near future...I want more movies!

Labels: dbms_job, funny, humility, utl_tcp, wtf

  ¶ 11/18/2008 10:12:00 PM   1 Comments Links to this post

  ApEx: Oracle Marketing WTF? At the time of writing, the score is 68 have not used ApEx leaving only 8 who have. Obviously this poll isn't scientific, but it does have to represent a small bit of the community. Perhaps Oracle Mix would be a better place to ask the question?

I guess my followup poll would be why? Why haven't you used it?

Did you not know about it?

If that's the case, then it's definitely an Oracle Marketing WTF.

Did you know about it but just never got around to trying it?

I'm completely dumb(quit snickering)-founded.

I believe the very first time I heard anything about the product was when it was referred to as Marvel (Project Marvel?) on AskTom. Am I the only one who read the site for enjoyment (probably, but it was the only thing I could read at work). That must have been 4 or 5 years ago. I started using it in May of 2005, now three years back.

Yes, Oracle has boatloads of products. I still don't know what the whole Fusion thing is (please don't say middleware, I don't know what that is either).

As someone pointed out in the comment section, it's one of the busiest forums on OTN.

I wonder if John and Dmitri run into this? Probably makes cold calling difficult.

As a [ApEx] community, what can we do to help promote it?

Labels: apex, database, oracle, rant, wtf

  ¶ 5/13/2008 10:01:00 PM   4 Comments Links to this post