Monday, August 31, 2009

Oracle: Day 1

I've been wanting to translate some of the concepts of Oracle to pictures for quite some time. Pictures have a way of telling a story that words cannot express.

So, inspired by Michael and his Eternal Sunshine series, I'm going to give it a go.

My First Day With Oracle


Perhaps I should have made myself much smaller? I remember what an overwhelming experience it was, "Here's your tnsnames.ora and SQL*Plus, get started..."

Saturday, August 29, 2009

ORACLENERD T-Shirts

Thanks to Justin, Jake, Billy (twice) and everyone else who has taken up "my" cause.

If you don't know what the cause is, you've probably been on a boat or something for the past week.

So I've spent some time expanding the available T-Shirts this weekend. Much thanks to John for pointing me to Spreadshirt. Also thanks to Michael (future guest blogger) for help (well, he created it and sent it to me) with the white version of the logo.

Below you'll find some of the shirts I've made along with the "classic" black on white or white on black.

I'm kind of digging the black on yellow. All my IDEs (SQL Developer, JDeveloper and Notepad++) have a yellow background.

From Jake:
These shirts will be the must-have item at #oow09, so get yours now...I can’t wait to see action shots from the conference.
I love this idea. What would be awesome, besides the natural "In The Wild" shots, would be a group shot...perhaps at the Blogger Meet-Up?

Friday, August 28, 2009

Random Things: Volume 5

Oracle OpenWorld
Earlier this week I threw in the towel in my attempt to go to OpenWorld. I applied for and received the Oracle blogger pass, but nothing else had fallen into place (flight, hotel, expenses).

Then Justin stepped in. Then Jake. Then Billy.

I've been absolutely overwhelmed at the support:



I can't really say it enough, but thank you.

oraclenerd T-Shirts in the Wild



Get yours today. I've even heard you can order your own colors!

Data Modeling
I began following @datachick recently. She is the first person, I've seen, who tweets almost exclusively about the art of data modeling. I keep trying to get her to do a guest post, but I've been unsuccessful.

In the years that I've been reading blogs, I can't say I've found someone who writes consistently on the importance of design, specifically, data modeling. It's definitely an under appreciated skill.

Oracle In The News
Deadline for Early Bird registration ends today, so go get your Oracle OpenWorld pass now.

Larry Ellison is now making only $1 a year! I can't say I've ever had a problem with him making gobs of money, I like the gesture though. He's allowed me and many others to make a pretty good living and I'm certainly thankful for that.

Department of Justice greenlights the Oracle/Sun deal. Not real sure why they are involved, I suppose it has something to do with anti-trust.

Thursday, August 27, 2009

A Very Humbling Thank You

I don't really know what to say...other than Thank You!

It started here:



Then here:



And then it continued...















How effin' awesome is that?!

We're not through yet though, Justin Kestelyn, aka @oracletechnet, also wrote up a nice little blurb on his blog.

As I just realized, Jake beat him to the punch.

After giving up on the effort just a couple of days ago...I'm working twice as hard to go now.

1. Oracle OpenWorld Blogger Pass? Check.
2. Time off from work? Check (finally).
3. Air-fare, hotel and expenses...in progress.

I don't know how to automagically update the count on the number of shirts sold...so it will be manual. The new site has a better margin of profit (plus lower-priced shirts), so pick one up and help a guy out. :)

I can't tell you enough how much this means to me. Thank you all so very much.

Blogging, FTW!

Tuesday, August 25, 2009

Job: Oracle DBA

This one is from a recruiter (seems to make a difference to some). I believe this position is in the Lakeland, FL area. If you are at all interested, let me know at chet at oraclenerd.

Job Title: Oracle DBA

ESSENTIAL DUTIES AND RESPONSIBILITIES include the following:

Provide installation, configuration and instance management on Corporate Oracle Databases and support SQL / PLSQL development.

SUPERVISORY RESPONSIBILITIES

None listed.

QUALIFICATIONS

To perform this job successfully, an individual must be able to perform each essential duty satisfactorily. The requirements listed below are representative of the knowledge, skill, and/or ability required. Reasonable accommodations may be made to enable individuals with disabilities to perform the essential functions.
  • Administration of Oracle databases 8i through I0g
  • Troubleshoot and make necessary modifications to Oracle database components as directed
  • Perform database daily monitoring and maintenance on testing, development and production instances
  • Analyze and Manage Database Architecture
  • Mapping and refinement of logical design and data model
  • Tuning of SQL and Stores Procedures
  • Refinement of physical database architecture
  • Development of SQL and PL/SQL for in support of the application
  • Support deployments of software upgrades into testing and production environments
  • Apply database patch sets to development, testing and production databases
  • Controlling access permissions and privileges on test and production databases
  • Provide timely and accurate status updates as directed
  • Design, document, test and perform database back up and recover procedures
EDUCATION and/or EXPERIENCE
  • RDBMS Concepts - 3-5 Working Yrs.
  • RDBMS Administration Concepts - 3-5 Working Yrs.
  • SQL - 3-5 Working Yrs.
  • Oracle 10G Backup and Recovery 3-5 Working Yrs
  • 3rd Party SQL and Database Management Tools – Basic 1-2 Yrs.
  • IT Networking Concepts – Basic 1-2 Yrs.
  • Server Hardware Architecture - Basic 1-2 Yrs.
  • Windows Server Administration - Basic 1-2 Yrs.
  • PL/SQL or T/SQL - Basic 1-2 Yrs.
  • TOAD and/or similar tools - Basic 1-2 Yrs.

Job: Contract Data Warehouse DBA

This is from a former colleague of mine. If you are interested, drop me a line or apply directly through their web site.

Contract Data Warehouse DBA needed to work with one of the world’s largest Data Warehouses!

Catalina Marketing is directly (i.e. this is not a recruiter, well...a Catalina Marketing Recruiter) seeking a Data Warehouse DBA to participate in our world renowned Netezza Data Warehouse, nearly a petabyte in size! This would be a 3-6 month contract role with an opportunity for a full-time position at the end of the contract period.

This Data Warehouse DBA is responsible for enterprise-wide administration of a Netezza Data Warehouse. Responsibilities include database administration, data modeling, and ETL support.

QUALIFICATIONS:

EDUCATION: Bachelors' degree or equivalent experience

EXPERIENCE:
- 2+ years DBA experience with a Data Warehouse and significant ETL experience.
- Expertise in the following areas is required: Data modeling, star schema database design, SQL, UNIX (AIX, linux), Korn shell, bash, C/C++ programming, design & implementation based on established standards, database backup and recovery; database connectivity and security, software development lifecycle including requirements gathering, design, coding, testing and production support.

Nice to have areas include: performance monitoring and tuning; disk space management; software patches and upgrades; automate manual tasks; conduct database training; provide on-call support & troubleshoot technical issues.

SPECIAL SKILLS: Netezza, ERwin ER Diagramming Tool, AutoSys Job Scheduling Tool - SyncSort for unix - SAS • Exposure to Business Intelligence tools (MicroStrategy, Hyperion IRS query and reporting tool) • Application Development experience preferred

The intent of this job description is to describe the major duties and responsibilities performed by incumbents of this job. Incumbents may be required to perform other job-related tasks other than those specifically presented in this description.

All duties and responsibilities are essential job functions and requirements and are subject to possible modification to reasonably accommodate individuals with disabilities.

We are proud to be an EEO/AA employer M/F/D/V. We maintain a drug-free workplace and perform pre-employment substance abuse testing.

Blame Drew's Cancer

If you haven't heard of it, go here. I hadn't heard the story until Jake mentioned it to me.

Here's the short of it, this guy, Drew, finds out he has Hodgkins Lymphoma, Stage 3. Instead of feeling sorry for himself, he starts this...well, movement. The essence of the movement is to blame everything on his cancer. If you use Twitter, tell us what happened, and then use the hashtag #blamedrewscancer.

The example provided: I lost my keys today. #blamedrewscancer

Simple huh?

It will eventually show up on BlameDrewsCancer.com (some fancy technology thing).

Check out Blame-a-Thon as well.
When Drew beats Cancer we hope to have sponsors that will donate a dollar for every participant to our partner, LIVESTRONG.
It reminded me of myself post Kate incident. I'd jokingly try to get away with all kinds of stuff saying something to the effect of, "But my daughter is in the hospital, she almost died."

I'm pretty sure I didn't use that until she was actually home though and well on her way to recovery. I'm also pretty sure I used it for quite some time at work after that. Apparently it didn't work too well for me. :)

Monday, August 24, 2009

No OpenWorld For Me

Sadly, I will not be able to attend Oracle OpenWorld this year.

Among the various excuses I could come up with:

1. Lack of vacation time. I might be able to get the time off, but I would not be able to replace that paycheck.
2. Lack of a plane ticket. If not for #1, I think I could have eventually scored a ticket by begging or borrowing.
3. Lack of a place to say. Same as #2. I'm sure I could have found somewhere to crash. I could move around from friend to friend's hotel room so I would not wear out my welcome. I've never had a problem sleeping on the floor.
4. Lack of beer money. I actually don't believe this would have been much of a problem, if COLLABORATE 09 was any indication.

I was hoping to meet thousands upon thousands of people, but I'll have to wait until next year.

I'll still keep up the page with all the links that I find to the event.

Perhaps I'll have a Oracle OpenWorld unconference session at my house?

Sunday, August 23, 2009

How To Kill a Code Review

Today's guest post is from Gary Myers from Igor's Oracle Lab. I was first introduced to Gary via comments left here. I can't find the first one of course...but he has left plenty of them. All well thought out and informative. Most recently he introduced me to the ability to do a bulk bind using %ROWTYPE here.

This is a topic that is all too often ignored, as you all know.


Steven Feuerstein states here that "Everyone knows that code review is a good idea"

The problem is what happens after the review.

Once upon a time there was a piece of code. That code had been in production for a long time, ran pretty slow but not slow enough that it had reached the top of the pile of stuff people complained about.

A change was done to that code for a new enhancement, and in one of those bursts of enthusiasm that sometimes hits a development team, it got subjected to a code review.

The whole structure of that code was ugly, with unnecessary nested cursor loops. The big kicker for performance was that at the end of one of these inner loops was a TRUNCATE TABLE. Because when you are deleting all the rows from a table, every-one *KNOWS* that a truncate is fastest, right ? Of course, as the TRUNCATE is DDL, it meant that all the SQL using that table inside that loop was getting re-parsed each and every time through the loop.

There were other problems in the code too. I believe one was with variables not being re-initialized at various points in the loop, so there was a risk of incorrect results in some unlikely cases.

The verdict of the review was that the code needed a re-write. The problem was, since it was already in production, no-one wanted to admit that there were bugs in it (and the users hadn't spotted any incorrect results). The new code would go into a future patch, but that wouldn't go live for months. However it had been promised for delivery to a test environment. A rewrite would mean missing the drop deadline.

A quick-fix could be done to improve performance. A rewrite could be done and the deadline missed. The quick-fix could be done and still meet the deadline, then a later rewrite to fix the underlying problems (but those problems probably would have been blamed on the quick fix).

A compromise was reached. Since the change to the code didn't actually introduce any new bugs, it would be allowed to go through to test with no changes from the review. And there was a promise to actually rewrite the code.

Of course, once the delivery was done, lots of other priorities came ahead. I don't know whether the code ever got the rewrite, but I suspect not. Definitely, for at least six months, there was a batch job taking hours when a five minute code change could have cut it to minutes.

At least the developers who participated in the review learnt that a TRUNCATE has drawbacks. The code reviews pretty much never happened again though.

Friday, August 21, 2009

Writing Maintainable Code

The last few years I've inherited quite a bit of code. The vast majority of it reads terribly.

I get newbies; those who have yet to find their style. Those that I run into I highly encourage to find a style and stick with it...even if the commas are in the front of the line. Of course I wish my style would be the standard. Wait a minute, it already is. Check out this snippet from the Official Oracle documentation:



Let me live in my own fantasy world...

What about the rest though? A friend recently told me he thinks it takes about 2 years for someone to develop their own distinctive style. So why doesn't it seem to happen? Or, do I just see all newbie code?

I doubt that's the case.

I try to write code so that it is readable. Others may not like my style, but it is consistent. A former colleague once said that my code (a 5,000 line ETL package) read very easily. It had a natural flow to it. That was one of the best compliments I have ever received. The point is, I write code with the assumption that someone else will (have to) read it.

I find myself in SQL*Plus, ad-hoc queries, writing them the way I would if I were to check it into source control.
SELECT OWNER, TABLE_NAME FROM DBA_TABLES
...that's pretty rare. I find myself backspacing to fix it
SELECT owner, table_name
FROM dba_tables
That might not be the best example (I have a script for that anyway), but you get the point.

"Practice like you play" is a common sports phrase. Perhaps that's why I write ad-hoc queries in that fashion.

I would offer tips, but that would just lead me back to style. I don't want to force people into using my style (that's a lie, but I accept that I am not Supreme Ruler of Code Style), I just want them to choose.

At a minimum, run your code through one of the various code formatters. Toad has one, SQL Developer has one, I think most tools have one. You can even set them up to match your personal preferences (which should align perfectly with mine).

When asking for help from someone else, don't use the default font in your email client (unless your a plain text email person), highlight the code and put it in Courier New. Please.

Finally, write your code as if someone else has to read it!

Thursday, August 20, 2009

Random Things: Volume 4

It's been a dry week in the writing arena for me.

Not feeling the love from outside...a couple of promised guest posters are MIA. :)

No worries though, I can always find something to talk about.

Oracle OpenWorld (OOW)
I started tagging all OOW related posts and tweets using Google Reader. Cool thing I like about it is that I can share that information. If you want to see all that I see in relation to the event, check it out here.

As to my attendance...no one has come forward to pay for any of the following:
1. Plane Ticket
2. Hotel Room
3. One week's pay

Number 4 would be getting time off of work, which I'll asking for tomorrow. Fingers crossed.

11gR2
Oracle has done an incredible job keeping this release quiet. I read...a lot...and I've hardly seen a thing about it over the past 6 months. I fully expected it to be announced at OOW, but I've heard as early as September 1. According to this latest article, that is highly unlikely.

Baseball Data Model(ing)
One person. One person has signed up. I'm a little shocked. Either my "following" is mostly based outside of the US or I no one likes baseball anymore. WTF? Regardless of whether you know baseball, the exercise of building out a fairly complex data model should be fun? Shouldn't it?

Oracle won't (hasn't) offered up any licenses, but @hillbilytoad has. He's offered at least one. I might be able to talk him into more. @DataChick expressed mild interest. Of all the people I follow on twitter, she, by far, talks about modeling more than anyone else...something I've rarely seen.

Is modeling a lost art? (via Dom Brooks). I'm shocked at how little I see out there...perhaps I'm just not looking in the right places? If you know of good sites, please let me know.

Other News
The Department of Justice (I wish I had had the foresight to buy that domain) approved Oracle's acquisition of Sun today.

Miladin Modrakovic [ oraclue ] has an interesting piece on tracking DDL changes in 11g. Miladin was initially the "inspiration" behind PMDV. OK, maybe not inspiration...he told me to find some way to do it. Now he seems to have a much better way. Oh well. Good exercise none-the-less.

Wednesday, August 19, 2009

PL/SQL: Coding Practices

I've struggled this week to write. Lately I've had lots of technical content and not much philosophical content. I have lots of philosophical content now, but the blog isn't the place for it...at least not now.

The big project I'm working on now is refactoring our payment processing system. We interface with multiple gateways for redundancy purposes. The code is comprised of 3 stand-alone procedures. One of those procedures has (had) 17 private procedures/functions in it. On one hand, it made sense, since everything was a stand-alone procedure or function, you didn't want all these dependencies on other objects. On the other hand, testing was virtually impossible. If you needed to make a change to private procedure #13, you had to run through an almost infinite number of test cases to ensure that you covered that particular case.

I've now moved those 3 procedures into a package. Theoretically, it's hot deployable now as long as we don't change the package signature. That's a big win in my book. I've also moved those 17 private functions into their own individual procedures and functions that can be exposed via the package specification for unit testing purposes (in production they will be private as nothing else needs access). The hardest part of that effort was there were no parameters being passed to the procedure, it just relied on the declared variables. So for each and every one of those I had to figure out what it relied on to work and what variables it set. No small task.

Here is the table:
CREATE TABLE t
(
id NUMBER PRIMARY KEY,
col_1 NUMBER(1) DEFAULT 0 NOT NULL,
col_2 NUMBER(1) DEFAULT 0 NOT NULL,
col_3 NUMBER(1) DEFAULT 0 NOT NULL,
start_date DATE DEFAULT SYSDATE NOT NULL,
end_date DATE
);
and a procedure (which does nothing obviously)
CREATE OR REPLACE
PROCEDURE update_t
( p_id IN NUMBER,
p_col_1 IN INTEGER DEFAULT 0,
p_col_2 IN INTEGER DEFAULT 0,
p_col_3 IN INTEGER DEFAULT 0 )
IS
BEGIN
NULL;
END update_t;
What's the best way to integrate the procedure into your code? I've seen this:
CREATE OR REPLACE
PROCEDURE some_other_procedure
( p_id NUMBER,
p_variable VARCHAR2(1) )
IS
BEGIN
IF p_variable = 'A' THEN
update_t
( p_id => p_id,
p_col_3 => 1 );
ELSIF p_variable = 'B' THEN
update_t
( p_id => p_id,
p_col_2 => 1,
p_col_3 => 1 );
ELSIF p_variable = 'C' THEN
update_t
( p_id => p_id,
p_col_1 => 1 );
ELSIF p_variable = 'D' THEN
update_t
( p_id => p_id,
p_col_1 => 1,
p_col_3 => 1 );
END IF;
END some_other_procedures;
Since I default the input parameters to 0, I didn't have to specify each individual parameter every time I called it. I like that.

I don't much like having 4 separate calls to UPATE_T though.

1. It makes it difficult (without further logging), to determine where exactly it's being called in the control statement.
2. Seems like a waste of space. P_ID is always going to be the same, why set it 4 times?

I decided to make just one call to UPDATE_T. I create local variables, then set them in the control statement, and then make the call to UPDATE_T.
CREATE OR REPLACE
PROCEDURE some_other_procedure
( p_id NUMBER,
p_variable VARCHAR2(1) )
IS
l_col_1 INTEGER := 0;
l_col_2 INTEGER := 0;
l_col_3 INTEGER := 0;
BEGIN
IF p_variable = 'A' THEN
l_col_3 := 1;
ELSIF p_variable = 'B' THEN
l_col_2 := 1;
l_col_3 := 1;
ELSIF p_variable = 'C' THEN
l_col_1 := 1;
ELSIF p_variable = 'D' THEN
l_col_1 := 1;
l_col_3 := 1;
END IF;

update_t
( p_id => p_id,
p_col_1 => l_col_1,
p_col_2 => l_col_2,
p_col_3 => l_col_3 );

END some_other_procedures;
Not much savings in space (and sometimes you'll actually have more), but for me, this is much easier to read. If I have to debug this, it feels a lot easier to concentrate on the control statement without the calls to UPDATE_T.

What do you do in these kinds of situations? Same as me? Different? Think I'm off my rocker (yeah, I know some of you do)?

Thursday, August 13, 2009

Baseball Data Modeling

Anyone out there like baseball? Ever had a desire to model a baseball game?

I do and I have tried a few times in the past. It get's pretty hairy down at the game game/inning/player level. If I remember correctly, substitutions tripped me up a bit. There there's the whole datawarehouse side, I'd like that to be part of the project as well.

I started a project on Google Code here, the name is pretty vanilla, baseball-database. If you join you can suggest a better name.

I'd like to try and talk Oracle into giving a few licenses to the recently released production version of SQL Developer Data Modeler. I've hit up @krisrice on Twitter, but he has no control over licenses, just development. I've also hit up Justin Kestelyn (@oracletechnet) who said he would look into it.

I've had no time lately to bother him; perhaps with a few more people...

If you are interested, just drop me a line chet at oraclenerd or message me through twitter.

Tuesday, August 11, 2009

%ROWTYPE, Part II

In the last entry on the subject, Gary Meyers pointed me to this link. Not having used %ROWTYPE that often I was not aware of some of the features.

The 2 that interested me the most were the INSERT and UPDATE:
DECLARE
l_table MY_TABLE%ROWTYPE;
BEGIN
INSERT INTO my_table
VALUES l_table;
END;

DECLARE
l_table MY_TABLE%ROWTYPE;
BEGIN
UPDATE my_table
SET ROW = l_table
WHERE my_table_id = l_table.my_table_id;
END;
So I began to use them (despite the bad feeling I get...).

I started to receive errors however, NOT NULL constraint violations in fact. How could that be? Here's the definition of MY_TABLE
CREATE TABLE my_table
(
my_table_id NUMBER(10)
CONSTRAINT pk_mytableid PRIMARY KEY,
status VARCHAR2(1) DEFAULT 'A'
CONSTRAINT nn_status_mytable NOT NULL
CONSTRAINT ck_aori_status_mytable CHECK ( status IN ( 'I', 'A' ) ),
start_date DATE DEFAULT SYSDATE
CONSTRAINT nn_startdate_mytable NOT NULL
);
See the column DEFAULTs?

I would imagine these are functioning as expected, but I don't like it. One benefit I see to this method is that it saves a little bit of typing (despite the SELECT * feel), or so I thought. If you don't explicitly set the value like this:
  l_table.status := 'A';
l_table.start_date := SYSDATE;
you are hosed. Plus, what if the calling application had already set those, now you have this:
IF l_table.status IS NULL THEN
l_table.status := 'A';
END IF;

IF l_table.start_date IS NULL THEN
l_table.start_date := SYSDATE;
END IF;
So where is the savings in that?

Monday, August 10, 2009

PL/SQL: Parse URL Strings

Finally, I can put it all together now.

PL/SQL: Split URL Parameters

PL/SQL: Split Key-Value Pairs

REGEXP_REPLACE - Credit Card (CC) Numbers

PL/SQL: Pipelined Function

It all culminates in this (hopefully final) post.

The goal was to be able to take in a URL string and parse it out accordingly. I ultimately decided that persisting that data was not of importance, so I built a fairly flexible function which returns a user-defined-type (UDT), also known as a SQL object.

We'll start with the UDTs:
CREATE OR REPLACE TYPE r_key_value_record IS OBJECT
(
orderof NUMBER(5),
key_string VARCHAR2(1000),
value_string VARCHAR2(1000)
)
/

CREATE OR REPLACE TYPE t_key_value_table AS TABLE OF R_KEY_VALUE_RECORD
/
Nothing fancy there. I'm not exactly sure why I added the ORDEROF column, other than I think it will come in good use down the road.
  FUNCTION parse_url
( p_url IN VARCHAR2,
p_token_delimiter IN VARCHAR2,
p_keyvalue_delimiter IN VARCHAR2,
p_enclosed_by IN VARCHAR2 DEFAULT NULL,
p_line_start IN VARCHAR2 DEFAULT NULL,
p_line_end IN VARCHAR2 DEFAULT NULL ) RETURN T_KEY_VALUE_TABLE PIPELINED;
I tried to make this as flexible as possible. Different payment gateways return different response strings so this seemed necessary. I suppose I could build one for each...but that wouldn't be as fun.

P_URL - self-explanatory
P_TOKEN_DELIMITER - For most URL strings, this will be the ampersand (&) that separates the key/value pairs.
P_KEYVALUE_DELIMITER - Usually the equals (=) sign, but can vary.
P_ENCLOSED_BY - occasionally a string will be enclosed by quotes (")
P_LINE_START - much more rare, the string has one or more characters at the beginning of the line
P_LINE_END - much more rare, the string has one or more characters at the end of the line

The declaration:
  IS
l_table T_KEY_VALUE_TABLE := T_KEY_VALUE_TABLE();
l_string VARCHAR2(4000) := p_url;
l_token_delimiter_length INTEGER := LENGTH( p_token_delimiter );
l_key_value_delimiter_length INTEGER := LENGTH( p_keyvalue_delimiter );
l_key VARCHAR2(100);
l_value VARCHAR2(100);
l_keyvalue VARCHAR2(200);
l_counter INTEGER := 0;
l_new VARCHAR2(4000);
l_start INTEGER := 1;
l_end INTEGER := 0;
BEGIN
Nothing special here.
    IF p_line_start IS NOT NULL THEN
l_string := SUBSTR( l_string, LENGTH( p_line_start ) + 1 );
END IF;

IF p_line_end IS NOT NULL THEN
l_string := SUBSTR( l_string, 1, INSTR( l_string, p_line_end, -1 ) - 1 );
END IF;
Getting rid of any start or end characters. Next up, my kludge:
    IF SUBSTR( l_string, -1, l_token_delimiter_length ) != p_token_delimiter THEN
l_string := l_string || p_token_delimiter;
END IF;
Just adding the delimiter to the end of the string. Apparently I'm too lazy to figure out a better way.

Finally, the meat of the process:
    LOOP
l_counter := l_counter + 1;
IF p_keyvalue_delimiter IS NOT NULL THEN
l_end := INSTR( l_new, p_token_delimiter, 1 );
l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
EXIT WHEN l_keyvalue IS NULL;
l_key := SUBSTR( l_keyvalue, 1, INSTR( l_keyvalue, p_keyvalue_delimiter ) - 1 );
l_value := SUBSTR( l_keyvalue, INSTR( l_keyvalue, p_keyvalue_delimiter ) + 1 );
l_start := l_start + ( l_end + ( l_token_delimiter_length - 1 ) );
l_new := SUBSTR( l_string, l_start );
ELSE
l_end := INSTR( l_new, p_token_delimiter, 1 );
l_keyvalue := SUBSTR( l_new, 1, l_end - 1 );
EXIT WHEN l_new = p_token_delimiter;
l_key := l_counter;
l_value := l_keyvalue;
l_start := l_start + l_end ;
l_new := SUBSTR( l_string, l_start );
END IF;

l_table.EXTEND(1);
l_table( l_counter ) := R_KEY_VALUE_RECORD( l_counter, l_key, l_value );
PIPE ROW ( R_KEY_VALUE_RECORD( l_counter, l_key, l_value ) );
END LOOP;
RETURN;
END parse_url;
What does all that do? Let's see.
SET DEFINE OFF
BEGIN
:C := '&&AUTH_CODE XXXXXX;&AVS_CODE N;&CMRCL_FLAG 5;&CMRCL_TYPE 3;&INTRN_SEQ_NUM
9999999;&PAYMENT_MEDIA AMEX;&REFERENCE 9211258897;&RESPONSE_TEXT PERFECT MATCH;&RESULT
SUCCESS;&RESULT_CODE 3;&TERMINATION_STATUS SUCCESS;&TRANS_DATE 2009.08.10;&TRANS_SEQ_NUM
999;&TRANS_TIME 15:24:51;&TROUTD 99999999;';
END;
/

SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, ';&', ' ', NULL, '&&', ';' ) );

ORDEROF KEY_STRING VALUE_STRING
------- -------------------- ------------------------
1 AUTH_CODE XXXXXX
2 AVS_CODE N
3 CMRCL_FLAG 5
4 CMRCL_TYPE 3
5 INTRN_SEQ_NUM 9999999
6 PAYMENT_MEDIA AMEX
7 REFERENCE 9211258897
8 RESPONSE_TEXT PERFECT MATCH
9 RESULT SUCCESS
10 RESULT_CODE 3
11 TERMINATION_STATUS SUCCESS
12 TRANS_DATE 2009.08.10
13 TRANS_SEQ_NUM 999
14 TRANS_TIME 15:24:51
15 TROUTD 99999999
How about a different flavor of URL string?
BEGIN
:C := 'transaction_id=0983450982340985sada0384098098234&error_code=999&auth_response_text=Nice Job!';
END;
/

SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, '&', '=' ) );

ORDEROF KEY_STRING VALUE_STRING
------- -------------------- ------------------------------------
1 transaction_id 0983450982340985sada0384098098234
2 error_code 999
3 auth_response_text Nice Job!
OK, one more. For this one, there will be no key/value pair, it's simply ordered (yes, you need the API to decipher it).
BEGIN
:c := '1,1,1,This is a test transaction.,999999,Y,999999999,,,1.00,CC,authorization,,Jake,Kuramoto,,123 Main
Street,Somewhere,CA,93063,US,,,,,,,,,,,,,,,,,XXXXXXXXXXXXXXXX
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,';
END;
/

SELECT *
FROM TABLE( pkg_payment_processing.parse_url( :c, ',', NULL ) );
ORDEROF KEY_STRING VALUE_STRING
------- -------------------- -------------------------------------------
1 1 1
2 2 1
3 3 1
4 4 This is a test transaction.
5 5 999999
6 6 Y
7 7 999999999
8 8
9 9
10 10 1.00
11 11 CC
12 12 authorization
13 13
14 14 Jake
15 15 Kuramoto
16 16
17 17 123 Main Street
18 18 Somewhere
19 19 CA
20 20 93063
21 21 US
22 22
...SNIP
37 37
38 38 XXXXXXXXXXXXXXXX
...SNIP
63 63
64 64
65 65
66 66

Sunday, August 9, 2009

Large-Scale Solutions for Small Enterprises: a Brief How (and Why) To

This is the first (technically, second I guess) in what will (hopefully) be a series of guest posts, from Ted and others.

One thing I really liked was the small shop that Ted talks about. So often you see IT become the cost center typically due to a lack of planning. 5 people supporting 3000? That's pure awesome. Speaks volumes for Ted and the choices he has made.


Wouldn't it be great if you could effectively run a major ERP suite on a tight budget with only a handful of staff? That is one of the things I do, so Chet asked me to write a bit about it. Specifically, he asked me "how have you integrated Oracle into a small shop? How much work? Hard? Easy?" Hopefully this post will cover those questions.

The What

The enterprise I manage runs a large and scalable ERP application suite: Oracle PeopleSoft Financials, HCM, Campus Solutions, and Enterprise Portal (all on application version 9.0 on PeopleTools 8.49). We have those applications running on an essentially Microsoft technology stack (Windows servers, SQL Server, Active Directory, ILM, and so forth). Our environment is heavily virtualized using VMware. Our mission has always been to maximize functionality and minimize cost. We do a fair job at that, as we deliver this ERP suite to a user base of ~3000 with an IT staff of 5 and a very small budget.

The How

Our modus operandi is basically to run our applications very near vanilla and to keep them as current as possible. This allows us to take full advantage of vendor and peer support and leverage the latest delivered functionality. Staying vanilla keeps operating costs down (no development costs, quick patch cycles, etc.) and allows rapid upgrades. We also do not diversify the technology within our enterprise architecture unless absolutely necessary. That allows our staff to use one skill set across multiple applications. That, in turn, allows us to hire ambitious generalists who are comfortable moving across applications. We minimize training costs by maximizing peer collaboration (very common in the Education and Research industry) and staying very active in user groups (our industry has an exceptional user group, -1 for my bias). We also rarely use consultants and never use implementation/upgrade partners anymore.

The Why

A colleague from a large university asked me recently why, for an organization our size, we did not use a much smaller solution (I think he suggested QuickBooks, +1 for snark). It is a fair question with a simple answer. While our organization is smaller than a larger university, the complexity of our business requirements is comparable. For example, our payroll contains all of the variations of a larger university: full time and part time staff, faculty contracts of every imaginable period of time, student employees, contingent workers, and so on. To use a smaller solution to handle that complexity would require a larger and far more specialized payroll staff, at least some custom application development, and a different IT support structure. All of those things are costly. Instead, we let Oracle worry about providing the functionality to meet those requirements, we let our payroll staff adjust their business processes around that functionality (we are mean that way), provide general IT support from our small pool of staff, and leverage our user group for strategic direction and answers to tough questions. So, to answer my colleague's question, we use a large solution like Oracle PeopleSoft to accomplish our mission: to maximize functionality and minimize cost.

The Importance

I am no analyst, but it does seem to me that business requirements, regulations, and compliance issues are getting more complex. It is probably safe to assume that they will not get simpler any time soon. We are also in a massive recession and resources are scarce. It is probably safe to assume this won't change anytime very soon, either. One way to meet these challenges is to take advantage of the benefits of larger enterprise solutions (to handle increasing complexity) and operate them as efficiently as possible (to handle decreasing resources). The major enterprise technology players (Oracle, Microsoft, SAP, IBM) are spending time and money focusing on small and medium enterprises (SME) recently. I think this is mostly for sales (what isn't?) but I also get the sense that they want to know how SMEs actually run these applications. I wrote a white paper last year (Effective ERP Practices for the Small Institution) that got a good bit of attention from Oracle. I also spoke at Open World last year about this stuff [http://www.slideshare.net/badgerworks/higher-education-acheives-oracle-peoplesoft-roi-presentation] (and went for free, in true oraclenerd style).

I hope that answered some of Chet's questions and maybe raised some new ones. If you have questions or comments drop them here. You might also want to hit my blog for the top 5 FAQs that I get when I speak, write, or consult on this topic. Thanks for reading!

About
Ted [ linkedin | twitter ] is Vice President for Communications and Membership at the Higher Education User Group, MBA and MSIS student at the Johns Hopkins University, Director of Administrative Systems at MICA, and blogger at badgerworks.

PL/SQL: Pipelined Function

As with Friday's post, pipelined functions are something I use rarely, thus end up looking up how to do it.

So with a little help from Tim Hall [ blog | twitter ], I shall do a quick example.

I'm trying to integrate these 2 posts, PL/SQL: Split URL Parameters and PL/SQL: Split Key-Value Pairs. I don't want to store the parsed response or request string just yet, just the string. But in the off chance I want it parsed, I don't want to load it up into excel or something and begin the arduous task of breaking it down. I will (just remembered) have to parse the response string when I receive it though to figure out whether or not the transactions was Approved or Declined.

Let's start with the types:
CREATE OR REPLACE TYPE r_key_value_record IS OBJECT
(
orderof NUMBER(5),
key_string VARCHAR2(1000),
value_string VARCHAR2(1000)
)
/

CREATE OR REPLACE TYPE t_key_value_table AS TABLE OF R_KEY_VALUE_RECORD
/
The simple function, just for demonstration purposes:
CREATE OR REPLACE
FUNCTION get_key_value_pairs RETURN T_KEY_VALUE_TABLE PIPELINED
IS
BEGIN
FOR i IN 1..10 LOOP
PIPE ROW ( R_KEY_VALUE_RECORD( i, i, i ) );
END LOOP;
RETURN;
END get_key_value_pairs;
/
show errors
This will definitely be expanded in the days to come to include those previous posts mentioned above. I will post the final result and link it up here as well.

Friday, August 7, 2009

REGEXP_REPLACE - Credit Card (CC) Numbers

I'm starting to rewrite some of our payment processing stuff right now. One thing I want to get a handle on is what is being sent to the payment gateway (we're capturing the response, but the request isn't easily accessible).

So I created a table and I'm writing the REQUEST URL to it. Ran my first test, hey look, there is the credit card number. That won't work.

I then started to think about how to mask just the credit card number...I could look for the key(word) in each URL string, but not all gateways are the same. Regular Expressions!

A nice 5 year old example from Alice Rischert can be found here. Following that it was fairly easy to figure out, specifically the example (REGEXP_INSTR) on finding the Zip code.
[[:digit:]]{13,16}
That's it. That's all there is too it and it's applicable to each and every gateway. You could have some false positives if you're passing through 13 to 16 digit numbers, but this is essentially a logging table, so who cares. If I need to see the exact credit card number, I can look it up through the usual process (access, decrypting, etc).

I ran a bunch of tests to see how it would work:
VAR C VARCHAR2(4000);

SELECT regexp_replace( :c, '[[:digit:]]{13,16}', 'X' ) r
FROM dual;
I'm starting with the easy cases, numbers, 13 to 16 digits long:
CJUSTICE@TESTING>EXEC :C := 1234123412341234;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--15
CJUSTICE@TESTING>EXEC :C := 123412341234123;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--14
CJUSTICE@TESTING>EXEC :C := 12341234123412;
CJUSTICE@TESTING>/

R
----------------------------------------------
X

CJUSTICE@TESTING>--13
CJUSTICE@TESTING>EXEC :C := 1234123412341;
CJUSTICE@TESTING>/

R
----------------------------------------------
X
Now some of the negative cases, still just numbers:
CJUSTICE@TESTING>--17
CJUSTICE@TESTING>EXEC :C := 12341234123412341;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X1

CJUSTICE@TESTING>--18
CJUSTICE@TESTING>EXEC :C := 123412341234123412;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X12

CJUSTICE@TESTING>--19
CJUSTICE@TESTING>EXEC :C := 1234123412341234123;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X123

CJUSTICE@TESTING>--20
CJUSTICE@TESTING>EXEC :C := 12341234123412341234;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
X1234

CJUSTICE@TESTING>--30
CJUSTICE@TESTING>EXEC :C := 123412341234123412341234123412;
CJUSTICE@TESTING>/

R
------------------------------------------------------------
XX
So any number over 16 is not masked. For my purposes, I don't much care.

Numbers are easy right? Let's start adding some strings in with the Credit Card number embedded in there.
CJUSTICE@TESTING>SET DEFINE OFF
CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
card_number=X

CJUSTICE@TESTING>EXEC :C := 'test=bollocks&card_number=1234123412341234';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
test=bollocks&card_number=X

CJUSTICE@TESTING>EXEC :C := 'card_number=1234123412341234&test=bollocks';
CJUSTICE@TESTING>/

R
----------------------------------------------------------------------------
card_number=X&test=bollocks
CJUSTICE@TESTING>SET DEFINE ON
This is more for me than it is for you (another reason you should blog). I always forget how to use Regular Expressions since I encounter the opportunity to use them so rarely.

Thursday, August 6, 2009

Kate: Summer Wrap-up

Is summer over already?

It is for Kate. She's been going to camp at the Blossom Montessori School for the Deaf for the past 8 weeks or so (no, I don't know exactly, I'm Dad).

The teachers there are awesome and have really taken to Kate. Who wouldn't take to this beautiful little girl though?



It's very, very difficult to get a picture of Kate. She either closes her eyes as soon as she sees the camera or she turns away, thus, the blur.

Sadly, Friday is her last day. We were hoping to put her in school there full-time this fall, but that's not going to happen. There had been a small chance we could get her in on the McKay Scholarship, but it looks like she has to go to public school for one more year to qualify. Neither Kris or I are happy about this, but the McKay Scholarship will cover (most of) her educational costs for the next 17 years. You do what you have to do.

As I mentioned at the beginning of the summer, one of the biggest benefits of Kate attending Blossom was that I would get to spend more time with her; the school is right across the street from where I currently work.

The first hour of my mornings have been spent listening to song #6, an old DJ Laz song, over and over and over and over and over. She won't let me change the song. As soon as I do, she starts to "fly away" (I need to get video of this) and signing (her version anyway) "more." In no uncertain terms, she's pissed.

Once in a while I get to pick her up as well. I get a much better reception that Mom does, which is only natural. By the time I get home, my face is tired...from smiling so much. Awesome doesn't really do much to describe it.

Today I had to go pick up her car seat because my mom forget to grab it when she picked her up (a day off for Mom). As I walked over, I ran into one of her teachers who was leaving. She made me promise to bring Kate over during the year so that she could see her, as she (the teacher) wouldn't be at school on Friday.

We began to talk about how wonderful Kate is and I said,
Kris and I have struggled with the word, but technically, Kate is retarded (physically delayed, possibly mental...). If I have to be retarded, I hope I can be retarded like Kate.
Or something like that. Point is, you would be hard pressed to find a happier child than Kate.

The teacher later commented, "As soon as Kate walked into the door, she walked into my heart."

If I weren't so manly, I would have cried. ;)

Users and Roles - Revisited

In a previous post, How To: Users and Roles (Simple), I went through and demonstrated one way to build a simple security system using users and roles.

I'm using the Database Authentication security scheme in Apex. Patrick Wolf [ blog ] pointed out that I should create database roles instead of using my own. That is the end game of course as I've limited the role_name column to VARCHAR2(30). But for now, this will have to do.

38 lines is now 27

The previous code looked like this:
FUNCTION is_authorized
( p_username IN VARCHAR2,
p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
l_count INTEGER;
l_rolename VARCHAR2(30);
BEGIN
SELECT COUNT(*)
INTO l_count
FROM v_active_user_roles
WHERE user_name = p_username
AND role_name = p_rolename
AND rownum < 2;

IF l_count = 1 THEN
--user has this role assigned
RETURN TRUE;
ELSE
--user does not have this role assigned
--check to see if user's role inherits these privs
l_count := NULL;

SELECT COUNT( DISTINCT role_name )
INTO l_count
FROM v_roles
WHERE status = 'A'
AND end_date IS NULL
AND INSTR( role_path, p_rolename ) > 0
AND INSTR( role_path, p_rolename ) < INSTR( role_path, role_name );

IF l_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;

END IF;
END is_authorized;
and I found it didn't work too well.

Specifically, the person with the least privileges, TAB_ADMIN, could see everything that only the ADMIN could see.

There was another small problem, using INSTR was screwed up to. Can you guess why?

'ADMIN'?

That would return a hit back for 3 roles: ADMIN, SECTION_ADMIN and TAB_ADMIN.

Bad developer, bad. (Another great reason you should NOT roll your own security!)

What was the problem again?

Let me quickly recap what I am trying to do with a "pretty" picture:



I'm going to try and articulate this with words, please don't laugh. ADMIN is the all powerful, it should be able to do anything it wants to do. There are 5 authorization schemes, one for each role; placed on different objects (pages, items, tabs, etc).

If a button has the TAB_ADMIN authorization schema, ADMIN should be able to run it through inheritance. The picture above is trying to depict that with those red arrows.

So, what's the point of this post? I've forgotten, so I'm skipping to the end.

I refactored the IS_AUTHORIZED procedure from 38 lines to 27 and I believe it's a bit more intuitive than it was. The key for me was included ROLE_LEVEL which is just an alias for the pseudo-column LEVEL.

If the ROLE_LEVEL, of the enabled roles for a user, is less than the ROLE_LEVEL where ROLE_NAME is part of the ROLE_PATH (SYS_CONNECT_BY_PATH), you're in.

Or something like that.

Here's the final product:
FUNCTION is_authorized
( p_username IN VARCHAR2,
p_rolename IN VARCHAR2 ) RETURN BOOLEAN
IS
l_count INTEGER;
BEGIN
FOR i IN ( SELECT role_name, role_path, role_level
FROM v_active_user_roles
WHERE user_name = p_username )
LOOP
IF p_rolename = i.role_name THEN
RETURN TRUE;
ELSE
SELECT COUNT(*)
INTO l_count
FROM v_roles
WHERE INSTR( role_path, i.role_name, -1 ) > 0
AND role_level > i.role_level;

IF l_count > 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END IF;
END LOOP;
END is_authorized;
I'll also be updating the other post to point to this one.

Small reminder: TESTING, TESTING, TESTING

Tuesday, August 4, 2009

Apex: /apex/wwv_flow_utilities.show_as_popup_calendar was not found

Since I hate Blogger's reports, ahem, lack of reports, I decided to try out the Google Bogger API to get what I wanted. Specifically, I want a report of posts by month.

I know this is possible as I've seen this on multiple blogs in the Archive or Categories section:



So instead of manually running an anonymous block to populate a table which I will then query, I decided to throw into Apex to make my life a little bit easier.

I create an application with a blank page and create my first item, P1_FROM_DATE. I decided to use a Date Picker (use item format mask):



I then created an item, P1_DATE_FORMAT, with the YYYY-MM-DDTHH24:MI:SS set as the source. When I created the Data Picker, I was thinking there would be some place to select the item, but there wasn't. So I opted to enter the format in the Source section:



Then I ran the page and clicked on the little calendar icon, only to see this:



Off to the Application Express forum and I read a few posts mentioning bad formats.

So I removed the "weird" YYYY-MM-DDTHH24:MI:SS format and voila!

That'll teach me to try something new when I'm rusty.

As a small aside, the date format, is the RFC 3339 format, according to the Google documentation.

%ROWTYPE

The %ROWTYPE attribute provides a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table, or fetched from a cursor or strongly typed cursor variable...
I have never been fond of using %ROWTYPE, for me, it feels to much like SELECT *.

The last 2 years or so I've worked on legacy systems though, and that's forced me to reconsider %ROWTYPE.

In particular, you have various INSERT/UPDATE statements on a given table all over the place. I have typically written stored procedures to handle discrete, logical, business requirements. For example, you need to update a person's name. There are a couple of ways to do this.

1. Create a discrete procedure like update_person_name that accepts the appropriate input.
2. Create a generic procedure like update_person which will take all the possible inputs and possibly change them all (the data is UPDATEd, even if the data doesn't change).
3. Create a super generic (fancy term) procedure that accepts a row from a table, PERSON%ROWTYPE.

One advantage of using %ROWTYPE, is that your input list has shrunk considerably. Instead of
PROCEDURE update_person_name
( p_personnameid IN NUMBER,
p_namecodeid IN NUMBER,
p_titleid IN NUMBER,
p_firstname IN VARCHAR2,
p_middlename IN VARCHAR2,
p_lastname IN VARCHAR2,
p_suffixid IN NUMBER )
IS
BEGIN
...
You could have
PROCEDURE update_person_name( p_personname_row IN PERSON_NAME%ROWTYPE )
IS
BEGIN
...
Ultimately, it doesn't really save on typing because you have to assign the variables somewhere (it's just shifted from one location to another). Here's how you would call the second example:
DECLARE
l_row PERSON_NAME%ROWTYPE;
BEGIN
l_row.personnameid := 22;
l_row.namecodeid := 2;
l_row.titleid := 4;
l_row.firstname := 'Jake';
l_row.middlename := NULL;
l_row.lastname := 'Kuramoto';
l_row.suffxid := 'VI';

package_name.update_person_name( l_row );
--OR (BETTER)
package_name.update_person_name( p_personname_row => l_row );
END;
As opposed to the other way
BEGIN
package_name.update_person_name
( p_personnameid => 22,
p_namecodeid => 2,
p_titleid => 4,
p_firstname => 'Jake',
p_middlename => NULL,
p_lastname => 'Kuramoto',
p_suffixid => 'VI' );
END;
Here's where I get a little scared though. Inside the procedure which accepts the PERSON_NAME%ROWTYPE, my UPDATE looks like this:
UPDATE person_name
SET namecodeid = NVL( p_personname_row.namecodeid, namecodeid ),
titleid = NVL( p_personname_row.titleid, titleid ),
firstname = NVL( p_personname_row.firstname, firstname ),
middlename = NVL( p_personname_row.middlename, middlename ),
lastname = NVL( p_personname_row.lastname, lastname ),
suffixid = NVL( p_personname_row.suffixid, suffixid )
WHERE personnameid = p_personname_row.personnameid;
All the NVL make me feel dirty for some reason. I'm not saying it makes sense, I'm just saying.

However, it does serve a purpose, in a legacy system anyway. If you need to quickly and easily get a handle on things, without knowing all the rules of a given system, it works well...as a first step.

Thoughts? Opinions? Does anyone else out there use this? For similar reasons or completely different? Should I feel guilty about this? Or is this an acceptable method you have (and do) use?

Monday, August 3, 2009

Constraints: PK = UQ?

Another little thing I learned today, you can use uniquely constrained columns in referential integrity. Looking it up in the docs, I found this little blurb:
Foreign keys can be defined as multiple columns. However, a composite foreign key must reference a composite primary or unique key with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns.
Not really hard to miss I guess.

The only reason I found this was because I made the changes to the codes table, putting a UNIQUE constraint on the former PK, and nothing broke when I rebuilt the database.

So here are my 2 tables:
CREATE TABLE my_codes
(
mycode VARCHAR2(20)
CONSTRAINT pk_mycode PRIMARY KEY
);

CREATE TABLE t
(
tid NUMBER(10)
CONSTRAINT pk_tid PRIMARY KEY,
mycode
CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
CONSTRAINT nn_mycode_t NOT NULL
);
I want to add a surrogate key to the MY_CODES table, per this discussion. I made a compromise, essentially anything that will or could be user entered will have a surrogate key (either SYS_GUID or sequence generated).

My first step was to remove the constraint on T, then drop the PK constraint on MY_CODES, add a new column that will hold the surrogate key and finally add the PK constraint to the new column.
ALTER TABLE t DROP CONSTRAINT fk_mycode_t;

ALTER TABLE my_codes DROP CONSTRAINT pk_mycode;

ALTER TABLE my_codes ADD ( mycodeid NUMBER(10) );

ALTER TABLE my_codes
ADD CONSTRAINT pk_mycodeid
PRIMARY KEY ( mycodeid );
Since I'm changing the meaning, I want to make sure that someone doesn't enter the same code twice, so I add a UNIQUE constraint on the table.
ALTER TABLE my_codes
ADD CONSTRAINT uq_mycode
UNIQUE ( mycode );
I rebuild my tables, without referencing the new PK.
CJUSTICE@TESTING>CREATE TABLE my_codes
2 (
3 mycodeid NUMBER(10)
4 CONSTRAINT pk_mycodeid PRIMARY KEY,
5 mycode VARCHAR2(20)
6 CONSTRAINT uq_mycode UNIQUE
7 CONSTRAINT nn_mycode NOT NULL
8 );

Table created.

CJUSTICE@TESTING>CREATE TABLE t
2 (
3 tid NUMBER(10)
4 CONSTRAINT pk_tid PRIMARY KEY,
5 mycode
6 CONSTRAINT fk_mycode_t REFERENCES my_codes( mycode )
7 CONSTRAINT nn_mycode_t NOT NULL
8 );

Table created.
Wait a minute. I didn't change the FK to point to the PK.

I then posted the finding on Twitter and received a timely response from @neilkod:



So what's the point? There isn't one. I just found it interesting.

INTEGER = NUMBER(38)

I noticed something a little strange today.

I've recently been creating tables with as specific a data type as I could. In that regard, I've been using INTEGER.
CREATE TABLE t
(
x INTEGER
);
Do a describe on that table:
CJUSTICE@TESTING>@DESC T
Name Null? Type
-------------------------------------- -------- ------------
X NUMBER(38)
Running a query on USER_TAB_COLUMNS results in this:
SELECT
data_type,
data_type_mod,
data_length,
data_precision,
data_scale,
default_length
FROM user_tab_columns
WHERE table_name = 'T'
AND column_name = 'X';

DATA_TYPE DAT DATA_LENGTH DATA_PRECISION DATA_SCALE DEFAULT_LENGTH
---------- --- ----------- -------------- ---------- --------------
NUMBER 22 0
The describe says NUMBER(38) yet USER_TAB_COLUMNS says NUMBER(22). Weird.

When I view the object through the SQL Developer schema browser and go to the SQL tab I get this:
CREATE TABLE "CJUSTICE"."T" 
(
"X" NUMBER(*,0)
);
So Oracle converts it somewhere along the way?

Further, going to the source of DBA_TAB_COLUMNS took me to DBA_TAB_COLS, I really thought it was the other way around, but I digress. I began to look at the source from DBA_TAB_COLS which had many staring at all of the SYS tables. OBJ$. COL$. HIST_HEAD$. USER$. COLTYPE$. I then started to try to unravel that...but I stopped.

It obviously won't stop me from doing my work, I just found it interesting. It's similar to A NULL Observation, III.

Saturday, August 1, 2009

How To: Oracle OpenWorld Free Passes

Just wanted to list a couple of ways to get free passes to Oracle OpenWorld.

I received mine last week through the Blogger program. That's one way.

Another way is to enter the Application Express Developer Competition. David Peake also wrote about it here.
The following prizes will be awarded for the APEX Developer Competition based on the judges rankings:
  • First Place - An Unconference Session and One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
  • Second Place - One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
  • Third Place - One complimentary ticket to Oracle Openworld 2009* in San Francisco October 11 - 15.
  • Fourth through Twelfth Place - One copy of the book Pro Oracle Application Express, Apress Publishing, written by John Scott & Scott Spendolini.
You have until August 24th to get your application complete and submitted. So hurry.

I tweeted or re-tweeted this one, but hadn't read it until now. The title of the post is The Show for a Song, which I had assumed meant you could create a video or sing a song for a ticket, but that's not the case. It's the Oracle OpenWorld Discover program, which I am not familiar with at all. It wasn't real clear, nor were any links provided, on the registration page. It's not free, but it only costs $50, which is a steal. Click through to find details.

Check out the registration page for other ways to get in.

You could also just annoy @oracleopenworld on twitter for a pass. I did the same thing to @OAUG back in February. The worst thing they can say is "No."

Update 08/10/2009
Found another one today courtesy of Todd Sheetz. Todd found it via the My Oracle Support blog here. All you need to do is take a survey and give your thoughts on the My Oracle Support Community, direct link to the survey is here.

Update 09/17/2009
Just found this on the Oracle OpenWorld blog:
Just make a 30-second video describing why you want to go to Oracle OpenWorld 2009.Use any camera you have close at hand—Webcam, cell phone, handheld. Give us your best, most creative, most innovative pitch, and post your video as a response to ours above on the Oracle Web Video YouTube channel before September 30.
The post is here. Good luck!