Thursday, March 27, 2008
How Does Oracle Make Development Easier?
Continuing on the theme of late, what are the basic things that you can do to reduce the amount of code that needs to be written?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
In the post linked above, I mentioned Constraints as probably the easiest way to reduce the amount of coding. For example:
ID - is just a sequence generated key, no big deal there.
CREATE TABLE t
(
id NUMBER(10)
CONSTRAINT pk_id_t PRIMARY KEY,
first_name VARCHAR2(30)
CONSTRAINT nn_firstname_t NOT NULL,
middle_name VARCHAR2(30),
last_name VARCHAR2(40)
CONSTRAINT nn_lastname_t NOT NULL,
gender VARCHAR2(1)
CONSTRAINT nn_gender_t NOT NULL
CONSTRAINT ck_morf_gender_t CHECK ( gender IN ( 'M', 'F' ) ),
ssn VARCHAR2(9)
CONSTRAINT nn_ssn_t NOT NULL
CONSTRAINT ck_9_ssn_t CHECK ( LENGTH( ssn ) = 9 )
CONSTRAINT ck_numeric_ssn_t CHECK ( REGEXP_INSTR( ssn, ?, ?, ? ) )
CONSTRAINT uq_ssn_t UNIQUE
);
FIRST_NAME - is not optional you hence the NOT NULL constraint.
MIDDLE_NAME - is optional (no constraint).
LAST_NAME - is not optional (NOT NULL).
GENDER - is not optional (NOT NULL). Also, you want to exclude everything but 'M' or 'F', thus the CHECK constraint.
SSN - is not optional (NOT NULL). The length of the value must be 9 characters (CHECK). The characters may only be numeric (CHECK). Unfortunately I don't yet know the REGEXP_INSTR function yet to truly demonstrate. Finally, the UNIQUE constraint on SSN since they shouldn't duplicate across people.
This is a simple demonstration of how you can potentially use constraints to reduce the amount of code necessary. Though I would probably check/validate these as well in code because the error that is generated will not be unique so it would difficult to tell. The point is, if you make a mistake in your validation code it will be easily caught by the constraints forcing you to fix it.
This will give you much more reliable data, which as we all know, is the most important thing.
I'd like to do more of the posts pointing out the easiest methods to reduce the amount of code you have to write by using Oracle.
What kind of solutions do you have or do you use?
Labels: code, constraints, database, oracle, simplicity
¶ 3/27/2008 10:28:00 PM 0 Comments Links to this post
Wednesday, December 5, 2007
Instrumentation: DEBUG/LOGGING
In my previous entry on instrumenting code I detailed the way in which you could use the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure to help monitor your long running code.
In this one, I will detail my home grown version of Tom Kyte's debug routine. I do know that others have similar code but can't seem to find them right now.
You can find the source code here.
Contents:
2 tables
2 sequences
1 package
1 procedure
1 build file
debug_tab allows you to turn the debugging on and off. debug_details_tab will store each line that you write to the debug routine when turned on.
Here's an example of it in practice:
I mentioned in the previous article that I had had difficulty grasping this concept initially. I think once I related it to DBMS_OUTPUT.PUT_LINE it became much more clear to me.
This simple debug routine has helped me tremendously in the last year or two that I have used it. Especially when you get nested levels of logic. It gets very hard to keep track of where you are, but with this you can run your procedure or function and then issue a SELECT against the debug_details_tab and see what was happening when.
I even began using this in my APEX applications. I would preface each line with "APEX: " and then write whatever was necessary so that I could step through the various pieces of code. It became crucial when I was doing validation on a table of records in a collection...oh so much easier.
On large systems this will generate a lot of data. I definitely would not put this inside a loop doing some sort of batch processing, but it is helpful to find where things fail out.
It can certainly be improved on. I basically took the idea of DBMS_OUTPUT.PUT_LINE and wrote it to a table, nothing fancy there. Mr. Kyte mentions writing it to the file system as well. Since I don't have ready access to the database file system, this was the easiest.
Make your life easier and use a debug/logging routine in your code. No longer will you have to comment, the debug statements should do it for you!
In this one, I will detail my home grown version of Tom Kyte's debug routine. I do know that others have similar code but can't seem to find them right now.
You can find the source code here.
Contents:
2 tables
2 sequences
1 package
1 procedure
1 build file
debug_tab allows you to turn the debugging on and off. debug_details_tab will store each line that you write to the debug routine when turned on.
Here's an example of it in practice:
CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
l_age_in_months INTEGER;
BEGIN
--instrumentation calls
debug( 'GET_AGE_IN_MONTHS' );
debug( 'P_ID: ' || p_id );
debug( 'select value into variable' );
SELECT age_in_months
INTO l_age_in_months
FROM people_tab
WHERE id = p_id;
debug( 'L_AGE_IN_MONTHS: ' || l_age_in_months );
RETURN l_age_in_months;
EXCEPTION
WHEN no_data_found THEN
debug( 'no data found' );
RETURN NULL;
END get_age_in_months;
/
show errors
I mentioned in the previous article that I had had difficulty grasping this concept initially. I think once I related it to DBMS_OUTPUT.PUT_LINE it became much more clear to me.
This simple debug routine has helped me tremendously in the last year or two that I have used it. Especially when you get nested levels of logic. It gets very hard to keep track of where you are, but with this you can run your procedure or function and then issue a SELECT against the debug_details_tab and see what was happening when.
I even began using this in my APEX applications. I would preface each line with "APEX: " and then write whatever was necessary so that I could step through the various pieces of code. It became crucial when I was doing validation on a table of records in a collection...oh so much easier.
On large systems this will generate a lot of data. I definitely would not put this inside a loop doing some sort of batch processing, but it is helpful to find where things fail out.
It can certainly be improved on. I basically took the idea of DBMS_OUTPUT.PUT_LINE and wrote it to a table, nothing fancy there. Mr. Kyte mentions writing it to the file system as well. Since I don't have ready access to the database file system, this was the easiest.
Make your life easier and use a debug/logging routine in your code. No longer will you have to comment, the debug statements should do it for you!
Labels: code, development, howto, instrumentation, plsql, tools, utilities, work
¶ 12/05/2007 09:03:00 PM 1 Comments Links to this post
Wednesday, October 31, 2007
Code Style: Functions
Functions
Functions are used to return a value. This value can take the form of a set of records (ref cursor), a collection, or a single value (NUMBER, VARCHAR2, etc.).
Functions (or procedures for that matter) should be logically grouped within a package. Only on the rare occasion should you store them individually.
That said, here is the syntax to create a function in Oracle:
The "debug" call is just instrumentation of the code. It will allow you to step through the code more easily.
In the above function, you would also need to be aware of too_many_rows, but since I know that "id" is the primary key on the table, I have ommitted it.
I tend to name variables thusly (I think I got that from Mr. Kyte):
Functions are used to return a value. This value can take the form of a set of records (ref cursor), a collection, or a single value (NUMBER, VARCHAR2, etc.).
Functions (or procedures for that matter) should be logically grouped within a package. Only on the rare occasion should you store them individually.
That said, here is the syntax to create a function in Oracle:
CREATE OR REPLACE
FUNCTION get_age_in_months( p_id IN NUMBER ) RETURN NUMBER
IS
l_age_in_months INTEGER;
BEGIN
--instrumentation calls
debug( 'GET_AGE_IN_MONTHS' );
debug( 'P_ID: ' || p_id );
debug( 'select value into variable' );
SELECT age_in_months
INTO l_age_in_months
FROM people_tab
WHERE id = p_id;
debug( 'L_AGE_IN_MONTHS: ' || l_age_in_months );
RETURN l_age_in_months;
EXCEPTION
WHEN no_data_found THEN
debug( 'no data found' );
--here you need to decide what exactly you want to do. If this is
--used in a SQL statement then you probably don't want to halt
--processing, so returning NULL will work just fine. However, if
--this function is called by another function or procedure and you
--need this value to continue processing, you WOULD want to know
--that the value is not there and you would issue a RAISE after you've
--logged the error
RETURN NULL;
END get_age_in_months;
/
show errors
The "debug" call is just instrumentation of the code. It will allow you to step through the code more easily.
In the above function, you would also need to be aware of too_many_rows, but since I know that "id" is the primary key on the table, I have ommitted it.
- Instrument your code.
- Name the function something descriptive. There is a 30 character limit so don't be lazy.
- Name your functions something descriptive. I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.
- Name your variables something descriptive. There is a 30 character limit so don't be lazy. Please just spell out "no". Is it "No" or "Number?" Why make the next person think, just spell it out. They'll thank you for it.
- If there are more than 1 input parameters, start at the next line.
- CREATE OR REPLACE goes on the top line, nothing else
- Use spaces liberally
- Comment where necessary. If you name things descriptively though, you'll find you won't need a lot of comments.
I tend to name variables thusly (I think I got that from Mr. Kyte):
- p_ = parameters passed (though you could use i_ for in and o_ for out as well)
- l_ = local variables
- g_ = global variables
Labels: code, development, style
¶ 10/31/2007 10:59:00 PM 1 Comments Links to this post
Thursday, October 11, 2007
Code Style: Package Specification
Package Specification
CREATE OR REPLACE
PACKAGE p_foo
AS
FUNCTION get_something
( p_id IN NUMBER,
p_thing_im_looking_for VARCHAR2 ) RETURN NUMBER;
PROCEDURE do_something( p_id IN NUMBER );
PROCEDURE insert_something
( p_first_name IN PEOPLE_TAB.FIRST_NAME%TYPE,
p_last_name IN PEOPLE_TAB.LAST_NAME%TYPE,
p_email_address IN PEOPLE_TAB.EMAIL_ADDRESS%TYPE,
p_middle_initial IN PEOPLE_TAB.MIDDLE_INITIAL%TYPE DEFAULT NULL,
p_prefix IN PEOPLE_TAB.PREFIX%TYPE DEFAULT NULL );
PROCEDURE update_something
( p_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2 );
PROCEDURE delete_something( p_id IN NUMBER );
END p_foo;
/
show errors
- Name your package something descriptive. There is a 30 character limit so don't be lazy.
- Name your procedures and functions something descriptive. There is a 30 character limit so don't be lazy. UPDATE this or INSERT that. Try to give a small clue as to what it does. If it's a function, I typically use get_ then whatever it is I'm doing. get_calculated_amount, get_as_of_date, etc.
- Name your variables something descriptive. There is a 30 character limit so don't be lazy. Please just spell out "no". Is it "No" or "Number?" Why make the next person think, just spell it out. They'll thank you for it.
- Use the package name after the END statement.
- If there are more than 1 input parameter, start at the next line.
- CREATE OR REPLACE goes on the top line, nothing else
- Use spaces liberally
- Comment where necessary. If you name things descriptively though, you'll find you won't need a lot of comments.
- Declare parameters using %TYPE. If the parameter doesn't map to a table column declare SUBTYPEs somewhere.
Labels: code, development, style
¶ 10/11/2007 10:16:00 PM 2 Comments Links to this post
Code Style: Index
My ongoing contribution to...pretty much myself. Perhaps you can find this useful, or amusing, whichever.
Labels: code, development, style
¶ 10/11/2007 01:00:00 AM 0 Comments Links to this post
Code Style: Tables
Tables are easy.
For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.
For child tables:
This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).
As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).
To recap:
Remember to always name your constraints. While I am at, use constraints as much as humanly possible, at least in your OLTP systems. You'll be able to reduce the amount of code you need to write and actually let the database do it's job. I'd much rather let the database do it than rely on code to maintain my data integrity.
CREATE TABLE t
(
col1 NUMBER(10,0)
CONSTRAINT pk_col1 PRIMARY KEY,
col2 VARCHAR2(32)
CONSTRAINT nn_col2_t NOT NULL
CONSTRAINT uq_col2_t UNIQUE,
col3 VARCHAR2(400),
col4 VARCHAR2(1) DEFAULT 'N'
CONSTRAINT ck_yorn_col4_t CHECK ( col4 IN ( 'Y', 'N' ) )
CONSTRAINT nn_col4_t NOT NULL
);
For the datawarehouse, you'll need to think about constraints a bit more as it may slow down load times. I'm still all for constraints, but I would never say always use them.
For child tables:
For Foreign Key constraints, you do not have to declare the type as it will be inherited from the parent table.
CREATE TABLE s
(
col5 NUMBER(10,0)
CONSTRAINT pk_col5 PRIMARY KEY,
col1
CONSTRAINT fk_col1_s REFERENCES t( col1 )
CONSTRAINT nn_col1_s NOT NULL,
col6 VARCHAR2(30)
);
This would be helpful if someone up and decided to change the NUMBER(10,0) to a VARCHAR2(10) or something (please don't ever do that!).
As for STORAGE or other table options, I typically leave that up to the DBA or work with them to add them. They may have a particular setup for certain tables that you can't possibly know (if you don't talk to them).
To recap:
- Use constraints as much as possible
- Always name your constraints
- Work with your DBA for table options
- Always name your constraints
Labels: code, constraints, development, discipline, style
¶ 10/11/2007 12:37:00 AM 0 Comments Links to this post
