JR Screen Ruler and PixieI’m no web designer but I’ve done quite a bit of modification for this site and these two little free tools help a lot.

The first one is JR Screen Ruler. It’s a small app which does just what you see. You can place a ruler, measured in pixels, anywhere on your screen. You can have it vertically or horizontally and you can adjust the length.

This image actually shows one ruler vertical and one horizontal. It will stay on top of all open windows. There are other screen rulers out there but this one has all the features I’m looking for.

The second app is Pixie. Pixie shows you a swatch of the pixel your mouse is currently over. It also gives you the hex, HTML, RGB, CMYK, and HSV values of the color making it easy to capture the color for reproduction in your favorite app. You can even hit control-alt-c to copy the HTML value for the color you’re looking at straight to the clipboard.

Thanks again to Zach (who is a web designer) for introducing me to these tools.

development, web development, graphic design, graphics, html

EditPlus in actionWhile there are many text editors out there offering a broad set of features my favorite right now is EditPlus.

EditPlus is a Windows shareware application designed for text, HTML, Java, PHP, etc. It’s very thin requiring little (nearly no) load time and has many great features such as:

  • Syntax hilighting for many languages
  • EditPlus on Windows right-click
  • FTP and SFTP integration
  • Templates
  • Line Numbering
  • Column Selection
  • Optional Spell Checker

The list of features is long and every revision brings more. Check out the Features page for more.

I’ve been using the Oracle 9iR2 syntax file from the user files section of EditPlus.com and it seems to pick up all the SQL and PL/SQL syntax I use, although many other syntax files exist and you could always make your own.

As mentioned above, EditPlus is shareware. A single user license is only $30, and with discounts for buying in bulk there is no reason not to pay, but for now, download it, try it, and see why you can’t live without it.

Thanks to Zach for showing this to me a couple years ago.

text, text editing, editing, editor, edit, sql, plsql, pl/sql, sftp, ftp, notepad, wordpad, oracle, unix, php, perl, programming, database programming, dba, database administration, systems administration, c, c++, java, javascript, css, html

OracleOracleAndy, a relative newcommer to the Oracle blogging community, has a fantastic article on resumable transactions.

Here’s what Andy has to say about them:

For those of you who have not come across this feature of Oracle, it was introduced in 9iR2. It allows you to set up your session so that if a transaction hits a storage problem e.g. out of temp, tablespace is not big enough,or a table reaches max extents, the transaction is suspended. All you have to do is fix the space problem and and transaction will resume. This saves you all the time of waiting for the rollback, adding some space and starting again hoping you’ve got it right this time.

He goes on to explain in great deal how to setup for and use resumable transactions. I didn’t even know you could do this (damn lack of training budget.) Great article Andy!

database, database administration, dba, database development, oracle, sql, plsql, pl/sql

OracleAfter pointing out how not to create auto increment columns in Oracle, I suppose I should point out how to create auto increment columns in oracle.

Many will gripe about this not being a standard feature in Oracle, but when it’s as easy as two more commands after your CREATE TABLE command I can’t see any good reason to use fancy SQL on every insert.

First let’s create a simple table to play with.

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Table created.

Now we’ll assume we want ID to be an auto increment field. First we need a sequence to grab values from.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Sequence created.


Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;
/

Trigger created.

This trigger will automatically grab the next value from the sequence we just created and substitute it into the ID column before the insert is completed.

Now we’ll do some inserts:

SQL> INSERT INTO test (name) VALUES ('Jon');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Bork');

1 row created.

SQL> INSERT INTO test (name) VALUES ('Matt');

1 row created.

SQL> SELECT * FROM test;

ID NAME
---------- ------------------------------
1 Jon
2 Bork
3 Matt

Because the sequence is updated independent of the rows being committed there will be no conflict if multiple users are inserting into the same table simultaneously.

If you need to capture the value of the auto increment column you’ll want to check out my other article Oracle Auto increment Columns – Part 2

Matt has posted a similar method in which he uses a sequence in the insert eliminating the need for the trigger. That will work just as well without the need for the trigger. The only drawback to this method is slightly longer SQL commands.

This article is about how not to create auto increment columns. If you’re looking for a more acceptable way, you’ll want to check out this article.

Matt has posted some code on his blog for accomplishing an auto increment in Oracle. While his solution may seem like an easy way out of creating triggers and sequences, it will fall apart in practical use.

Here’s what Matt suggested:

INSERT INTO table (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ‘bork’ FROM table);

Basically he is taking the max value currently in the ID column of the table and adding one for the new ID value. Matt mentions on his site that he is not sure how efficient this would be. While it wouldn’t be very efficient, it will probably not be too slow if the ID column has an index on it (which it will automatically have if it is a primary key.)

The bigger problem will come when you have multiple users connected. To illustrate that we’ll create a small test table:

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));
Table created.

Now let’s insert a couple rows in the table:

SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'bork' FROM test);1 row created.

INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Jon' FROM test);

1 row created.

SQL> select * from test;

ID NAME
---------- ------------------------------
1 bork
2 Jon

OK, so far so good, the incrementing works. Now let’s open a second session to the database and insert a row.

Session 2
SQL> INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test);

This session hangs! If you dig into the data dictionary (or Enterprise Manager) you will find that the first session has a lock on the table that is blocking the second transaction! So session 2 is waiting for session 1 to commit. Let’s do that and see what happens.

Session 1
SQL> commit;Commit complete.

Session 2
INSERT INTO test (id,name) (SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, 'Matt' FROM test)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C002948) violated

Now we see the bigger problem. Since the subquery is evaluated before session 1 commits the subquery which is incrementing the ID value based on the MAX(id) is grabbing old data.

So sorry Matt, there’s no bulletproof way around using triggers and sequences for this.

database, database administration, dba, database development, oracle, sql, plsql, pl/sql

« Previous PageNext Page »