Friday, July 29, 2005
Oracle By Example
I would like to elaborate on one point from my recent blog on Using Views:
http://thinkoracle.blogspot.com/2005/07/use-views.html
The example in question is a recent case where I used Views to very easily perform a complex query.
Let's set it up. We have a simple company with expenses and revenues broken up by type and department. I'm ignoring many columns/constraints and the expensetype table for simplicity.
Okay now I'd like to insert a little sample data so we can test some queries
INSERT INTO department VALUES ('DEPT_A', 'Expenses Only');
INSERT INTO department VALUES ('DEPT_B', 'Revenues Only');
INSERT INTO department VALUES ('DEPT_C', 'Expenses and Revenues');
INSERT INTO department VALUES ('DEPT_D', 'No Expenses Nor Revenues');
INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_1', 'Expense 1', 10.00);
INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_2', 'Expense 2', 12.50);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_1', 'Expense 3', 44.90);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_3', 'Expense 4', 92.75);
INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 1', 14.60);
INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 2', 15.80);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_4', 'Revenue 3', 47.75);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_5', 'Revenue 4', 6.15);
We want a query that will show us every department, and its total expenses and revenues. We want dept_name, sum(expenses), sum(revenues), regardless of type.
Doing a single sum would be easy, we could just "GROUP BY" a particular column. Even if we wanted to add dept_description, we could just use Connor McDonald's trick:
http://thinkoracle.blogspot.com/2005/07/extra-columns-in-group-by.html
For simplicity, by the way, we won't include dept_description - we know we can using that technique.
Go ahead and write the query. I'm sure its possible! But not everyone can figure it out, you might wind up with something really complex.
So how will views help us? Well, we can create views that give us the sum for expenses and values:
Nothing could be easier. That is also some pretty handy views to have. As you recall, a view can be thought of as a "virtual table" or as a stored query. A stored query we are about to put to use.
Here are the results ... oops!
Why did we only get DEPT_C? Because, of course, either expense_sum or revenue_sum was NULL in the other 3 departments. I'm only showing this mistake so I can show the application of an outer join. An outer join will give you all possible rows. Just add a (+) next to the column you're joining on. Symbolically that means you want to add (+) rows where none exist.
Perfect.
We could have done this without views. Indeed, we could have embedded the simple queries we used to make the views directly into this final query. Maybe in an example as simple as this that would be ok. But these kinds of things can get complex. Plus, now other queries can take advantage of those views.
As a final note, how do you figure we can replace the NULLs up there with zeros? The answer is something like DECODE or NVL.
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
http://thinkoracle.blogspot.com/2005/07/use-views.html
The example in question is a recent case where I used Views to very easily perform a complex query.
Let's set it up. We have a simple company with expenses and revenues broken up by type and department. I'm ignoring many columns/constraints and the expensetype table for simplicity.
CREATE TABLE department (
dept_name VARCHAR2(32) PRIMARY KEY,
dept_description VARCHAR2(64)
);
CREATE TABLE expenses (
dept_name REFERENCES department(dept_name),
expense_type VARCHAR2(32),
expense_description VARCHAR2(64),
expense_amount NUMBER(10,2)
);
CREATE TABLE revenues (
dept_name REFERENCES department(dept_name),
revenue_type VARCHAR2(32),
revenue_description VARCHAR2(64),
revenue_amount NUMBER(10,2)
);
Okay now I'd like to insert a little sample data so we can test some queries
INSERT INTO department VALUES ('DEPT_A', 'Expenses Only');
INSERT INTO department VALUES ('DEPT_B', 'Revenues Only');
INSERT INTO department VALUES ('DEPT_C', 'Expenses and Revenues');
INSERT INTO department VALUES ('DEPT_D', 'No Expenses Nor Revenues');
INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_1', 'Expense 1', 10.00);
INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_2', 'Expense 2', 12.50);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_1', 'Expense 3', 44.90);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_3', 'Expense 4', 92.75);
INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 1', 14.60);
INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 2', 15.80);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_4', 'Revenue 3', 47.75);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_5', 'Revenue 4', 6.15);
We want a query that will show us every department, and its total expenses and revenues. We want dept_name, sum(expenses), sum(revenues), regardless of type.
Doing a single sum would be easy, we could just "GROUP BY" a particular column. Even if we wanted to add dept_description, we could just use Connor McDonald's trick:
http://thinkoracle.blogspot.com/2005/07/extra-columns-in-group-by.html
For simplicity, by the way, we won't include dept_description - we know we can using that technique.
Go ahead and write the query. I'm sure its possible! But not everyone can figure it out, you might wind up with something really complex.
So how will views help us? Well, we can create views that give us the sum for expenses and values:
CREATE VIEW expensesview AS
SELECT dept_name, sum(expense_amount) expense_sum
FROM expenses
GROUP BY dept_name;
CREATE VIEW revenuesview AS
SELECT dept_name, sum(revenue_amount) revenue_sum
FROM revenues
GROUP BY dept_name;
Nothing could be easier. That is also some pretty handy views to have. As you recall, a view can be thought of as a "virtual table" or as a stored query. A stored query we are about to put to use.
SELECT d.dept_name, e.expense_sum, r.revenue_sum
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name
AND d.dept_name = r.dept_name;
Here are the results ... oops!
DEPT_NAME EXPENSE_SUM REVENUE_SUM
-------------------------------- ----------- -----------
DEPT_C 137.65 53.9
Why did we only get DEPT_C? Because, of course, either expense_sum or revenue_sum was NULL in the other 3 departments. I'm only showing this mistake so I can show the application of an outer join. An outer join will give you all possible rows. Just add a (+) next to the column you're joining on. Symbolically that means you want to add (+) rows where none exist.
SELECT d.dept_name, e.expense_sum, r.revenue_sum
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name(+)
AND d.dept_name = r.dept_name(+);
DEPT_NAME EXPENSE_SUM REVENUE_SUM
-------------------------------- ----------- -----------
DEPT_A 22.5
DEPT_B 30.4
DEPT_C 137.65 53.9
DEPT_D
Perfect.
We could have done this without views. Indeed, we could have embedded the simple queries we used to make the views directly into this final query. Maybe in an example as simple as this that would be ok. But these kinds of things can get complex. Plus, now other queries can take advantage of those views.
As a final note, how do you figure we can replace the NULLs up there with zeros? The answer is something like DECODE or NVL.
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
SELECT d.dept_name,
NVL(e.expense_sum,0) tot_expense,
NVL(r.revenue_sum,0) tot_revenue
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name(+)
AND d.dept_name = r.dept_name(+);
DEPT_NAME TOT_EXPENSE TOT_REVENUE
-------------------------------- ----------- -----------
DEPT_A 22.5 0
DEPT_B 0 30.4
DEPT_C 137.65 53.9
DEPT_D 0 0
Tuesday, July 26, 2005
Use Constraints
I was ready to explain to you why you should take advantage of Oracle's ability to manage the integrity of your data rather than rely on your applications, but I found a much better explanation in the Oracle Data Warehousing Guide. Read the section "Why Integrity Constraints are Useful in a Data Warehouse" in Chapter 7 on "Integrity Constraints."
So instead, let me give you a just a really quick primer from my own experience, and a couple of treats.
There are many types of constraints, including primary key, unique, referential (foreign key) and check constraints. I'll talk about check constraints.
There are basically three ways to set up your table constraint. Check a reference (like Dan Morgan's http://www.psoug.org/reference/constraints.html) for more detail, but I will review them here.
1. On the Same Line
CREATE TABLE ConstraintTable (
MyNumber NUMBER(1) CHECK (MyNumber < 5)
);
Using this method (only), you can't reference other columns in the table in your check constraint. Try it:
CREATE TABLE ConstraintTable (
MyNumber1 NUMBER(1),
MyNumber2 NUMBER(1) CHECK (MyNumber2 > MyNumber1)
);
ORA-02438: Column check constraint cannot reference other columns
It is also not obvious what name the constraint takes so it's more difficult to alter it later. But here is how:
SQL> SELECT constraint_name
2 FROM user_constraints
3 WHERE table_name = 'CONSTRAINTTABLE';
CONSTRAINT_NAME
------------------------------
SYS_C007536
2. During table creation
Example:
CREATE TABLE ConstraintTable (
MyNumber NUMBER(1)
CONSTRAINT c_my_number CHECK (MyNumber < 5)
);
Doing it this way allows you to reference other columns in the table:
SQL> CREATE TABLE ConstraintTable (
2 MyNumber1 NUMBER(1),
3 MyNumber2 NUMBER(1),
4 CONSTRAINT c_my_number CHECK (MyNumber2 > MyNumber1)
5 );
3. Alter table
You can create your table as normal, and then add your constraints separately. I don't think there is any actual difference to Oracle between method #2 and #3.
CREATE TABLE ConstraintTable (MyNumber Number(1));
ALTER TABLE ConstraintTable ADD CONSTRAINT c_my_number check (MyNumber < 5);
There is actually a 4th way, kind of. See, CHECK constraints can not include sub-queries, and you can't reference other tables in them. You also can't use package-defined constants or variables. All you can basically do is simple things, like <>=, and [not] between/in/like/equals
I get around all of this by using triggers. I add a simple constraint to the table (possibly a "NOT NULL", if applicable) and then write a trigger which will do my check - referencing other tables, writing subqueries and using package-defined constants as I please - and then deliberately set off the simple constraint.
Of course, this may look silly to a user, who gets an error on inserting a row with a NULL value when it clearly isn't NULL. So I usually write something to a log, or name my simple constraint, and I certainly document the source code. But that's a topic for another day.
Here is an article where I describe the concept in more detail, picking especially on using variables in constraints. But apply the same logic for complex integrity constraints when you want to reference other tables.
http://thinkoracle.blogspot.com/2005/06/variable-constraints.html
Ok, let me wrap it up by saying that one of the other advantages of constraints is that you can disable them when you need to:
SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);
ORA-02290: check constraint (SYS.SYS_C007536) violated
(By the way, that's the other way to find out the name of your constraint: violate it!)
SQL> ALTER TABLE ConstraintTable DISABLE CONSTRAINT SYS_C007536;
SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);
And you're ok! Of course, you better make sure the data is ok by the time you turn it back on, or you'll get this error:
SQL> ALTER TABLE ConstraintTable ENABLE CONSTRAINT SYS_C007536;
ORA-02293: cannot validate (SYS.SYS_C007536) - check constraint violated
Which is a great segue into my closing, which includes two of my favourite discussions on disabling and deferring constraints. Check them out:
Jeff Hunter
http://marist89.blogspot.com/2005/06/deferrable-constraints_29.html
Doug Burns
http://doug.burns.tripod.com/oracle/index.blog?entry_id=1170846
So instead, let me give you a just a really quick primer from my own experience, and a couple of treats.
There are many types of constraints, including primary key, unique, referential (foreign key) and check constraints. I'll talk about check constraints.
There are basically three ways to set up your table constraint. Check a reference (like Dan Morgan's http://www.psoug.org/reference/constraints.html) for more detail, but I will review them here.
1. On the Same Line
CREATE TABLE ConstraintTable (
MyNumber NUMBER(1) CHECK (MyNumber < 5)
);
Using this method (only), you can't reference other columns in the table in your check constraint. Try it:
CREATE TABLE ConstraintTable (
MyNumber1 NUMBER(1),
MyNumber2 NUMBER(1) CHECK (MyNumber2 > MyNumber1)
);
ORA-02438: Column check constraint cannot reference other columns
It is also not obvious what name the constraint takes so it's more difficult to alter it later. But here is how:
SQL> SELECT constraint_name
2 FROM user_constraints
3 WHERE table_name = 'CONSTRAINTTABLE';
CONSTRAINT_NAME
------------------------------
SYS_C007536
2. During table creation
Example:
CREATE TABLE ConstraintTable (
MyNumber NUMBER(1)
CONSTRAINT c_my_number CHECK (MyNumber < 5)
);
Doing it this way allows you to reference other columns in the table:
SQL> CREATE TABLE ConstraintTable (
2 MyNumber1 NUMBER(1),
3 MyNumber2 NUMBER(1),
4 CONSTRAINT c_my_number CHECK (MyNumber2 > MyNumber1)
5 );
3. Alter table
You can create your table as normal, and then add your constraints separately. I don't think there is any actual difference to Oracle between method #2 and #3.
CREATE TABLE ConstraintTable (MyNumber Number(1));
ALTER TABLE ConstraintTable ADD CONSTRAINT c_my_number check (MyNumber < 5);
There is actually a 4th way, kind of. See, CHECK constraints can not include sub-queries, and you can't reference other tables in them. You also can't use package-defined constants or variables. All you can basically do is simple things, like <>=, and [not] between/in/like/equals
I get around all of this by using triggers. I add a simple constraint to the table (possibly a "NOT NULL", if applicable) and then write a trigger which will do my check - referencing other tables, writing subqueries and using package-defined constants as I please - and then deliberately set off the simple constraint.
Of course, this may look silly to a user, who gets an error on inserting a row with a NULL value when it clearly isn't NULL. So I usually write something to a log, or name my simple constraint, and I certainly document the source code. But that's a topic for another day.
Here is an article where I describe the concept in more detail, picking especially on using variables in constraints. But apply the same logic for complex integrity constraints when you want to reference other tables.
http://thinkoracle.blogspot.com/2005/06/variable-constraints.html
Ok, let me wrap it up by saying that one of the other advantages of constraints is that you can disable them when you need to:
SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);
ORA-02290: check constraint (SYS.SYS_C007536) violated
(By the way, that's the other way to find out the name of your constraint: violate it!)
SQL> ALTER TABLE ConstraintTable DISABLE CONSTRAINT SYS_C007536;
SQL> INSERT INTO ConstraintTable (MyNumber) VALUES (6);
And you're ok! Of course, you better make sure the data is ok by the time you turn it back on, or you'll get this error:
SQL> ALTER TABLE ConstraintTable ENABLE CONSTRAINT SYS_C007536;
ORA-02293: cannot validate (SYS.SYS_C007536) - check constraint violated
Which is a great segue into my closing, which includes two of my favourite discussions on disabling and deferring constraints. Check them out:
Jeff Hunter
http://marist89.blogspot.com/2005/06/deferrable-constraints_29.html
Doug Burns
http://doug.burns.tripod.com/oracle/index.blog?entry_id=1170846
Monday, July 25, 2005
Use Views
Very often the solution to a problem involves using a view. Use Views!
What is a View?
Most people think of a view either as a stored, named query, or as a "virtual table."
Here are two definitions from Oracle, along with references to the key documents on Views to review.
"A logical representation of a table or combination of tables."
- Oracle Application Developer's Guide, Section 2.11 (Views)
"A view takes the output of a query and presents it as a table."
- Oracle Concepts Guide, Section 10.16 (Views)
How do you create a View?
Nothing could be easier. Here:
CREATE OR REPLACE VIEW MyView AS
(Insert Query Here!)
For more, you can always reference Dan Morgan's library:
http://www.psoug.org/reference/views.html
How do I use Views?
Treat it just like a table. You can query them, as well as insert, update and delete*. Bear in mind that these actions will update the base table(s). Likewise, any changes to the base table(s) will automatically update the View*.
*This is true in general. Some Views can't be updated, and you can make a View READONLY.
Here is how to tell what Views exist:
SELECT view_name FROM user_views;
Here is how to tell what the View is:
SELECT text FROM user_views WHERE view_name = 'MyView';
Why are they useful?
I use them for many reasons, here are the most common situations where I will advocate their use:
1. Denormalizing the database
Sometimes its nice to pull related data from several tables into a single table. Using views allows you to satisfy both the Fabian Pascalesque relational database purist, and the pragmatic user.
2. Making things look better
For example, you can use a view to substitute a generic ID number with the name of the individual. You can leave out columns that are rarely interesting. You can use a view to add information that is derived from other data, for example figure out everyone's salary in Canadian dollars in an international organisation.
3. Complex queries/query simplification
You might have several queries that have to perform similar logic. You can just create a view for that logic, and make the queries far simpler. Also, when I can't figure out how to write a really complex query, sometimes I just create views as intermediate steps.
For example, say you have a table of expenses, with "department", "type" and "total", a revenue table with the same columns, and a department table. Now you have to put together a query that shows each department, and their total expenses and revenues, regardless of type.
Rather than write a really complex query, just write a view (or two) that contains the sum of the expenses and revenues, by department. Then you can create a query to put them all into a single row by (outer) joining on those views. Simple!
4. Restrict access to a column or row
Say you want a user to have access to only part of a table (either by row or column). Here's what you do: restrict his access to the table completely, then create a view that contains the information the user is allowed to access, and then grant that user access to the view.
5. Rename a column
Create a view which is exactly like the table, but with the column renamed. That is really easy, and it will save you from having to possibly update lots of other tables or applications because you left the base table alone. No, you can't write indexes on views, but queries get optimized as normal, and it will use the base table's indexes at that time.
6. Change schema, but still support an old version
Just like renaming a column. You can completely re-design your database schema, but create views to support legacy applications who still rely on the structure and naming of the original. That will save a lot of hassle.
I will close with a really interesting aspect of Views.
SQL> CREATE TABLE BaseTable (MyNumber NUMBER(1));
Table created.
SQL> INSERT INTO BaseTable (MyNumber) VALUES (1);
1 row created.
SQL> CREATE VIEW MyView AS SELECT * FROM BaseTable;
View created.
SQL> ALTER TABLE BaseTable ADD (MyString VARCHAR2(32));
Table altered.
SQL> INSERT INTO BaseTable (MyNumber, MyString) VALUES (2, 'Hello');
1 row created.
SQL> SELECT * FROM MyView;
MYNUMBER
----------
1
2
SQL> SELECT * FROM BaseTable;
MYNUMBER MYSTRING
---------- --------------------------------
1
2 Hello
The View is NOT updated when the BaseTable is, even when you SELECT *. When you write SELECT * the view chooses its columns then and there.
What is a View?
Most people think of a view either as a stored, named query, or as a "virtual table."
Here are two definitions from Oracle, along with references to the key documents on Views to review.
"A logical representation of a table or combination of tables."
- Oracle Application Developer's Guide, Section 2.11 (Views)
"A view takes the output of a query and presents it as a table."
- Oracle Concepts Guide, Section 10.16 (Views)
How do you create a View?
Nothing could be easier. Here:
CREATE OR REPLACE VIEW MyView AS
(Insert Query Here!)
For more, you can always reference Dan Morgan's library:
http://www.psoug.org/reference/views.html
How do I use Views?
Treat it just like a table. You can query them, as well as insert, update and delete*. Bear in mind that these actions will update the base table(s). Likewise, any changes to the base table(s) will automatically update the View*.
*This is true in general. Some Views can't be updated, and you can make a View READONLY.
Here is how to tell what Views exist:
SELECT view_name FROM user_views;
Here is how to tell what the View is:
SELECT text FROM user_views WHERE view_name = 'MyView';
Why are they useful?
I use them for many reasons, here are the most common situations where I will advocate their use:
1. Denormalizing the database
Sometimes its nice to pull related data from several tables into a single table. Using views allows you to satisfy both the Fabian Pascalesque relational database purist, and the pragmatic user.
2. Making things look better
For example, you can use a view to substitute a generic ID number with the name of the individual. You can leave out columns that are rarely interesting. You can use a view to add information that is derived from other data, for example figure out everyone's salary in Canadian dollars in an international organisation.
3. Complex queries/query simplification
You might have several queries that have to perform similar logic. You can just create a view for that logic, and make the queries far simpler. Also, when I can't figure out how to write a really complex query, sometimes I just create views as intermediate steps.
For example, say you have a table of expenses, with "department", "type" and "total", a revenue table with the same columns, and a department table. Now you have to put together a query that shows each department, and their total expenses and revenues, regardless of type.
Rather than write a really complex query, just write a view (or two) that contains the sum of the expenses and revenues, by department. Then you can create a query to put them all into a single row by (outer) joining on those views. Simple!
4. Restrict access to a column or row
Say you want a user to have access to only part of a table (either by row or column). Here's what you do: restrict his access to the table completely, then create a view that contains the information the user is allowed to access, and then grant that user access to the view.
5. Rename a column
Create a view which is exactly like the table, but with the column renamed. That is really easy, and it will save you from having to possibly update lots of other tables or applications because you left the base table alone. No, you can't write indexes on views, but queries get optimized as normal, and it will use the base table's indexes at that time.
6. Change schema, but still support an old version
Just like renaming a column. You can completely re-design your database schema, but create views to support legacy applications who still rely on the structure and naming of the original. That will save a lot of hassle.
I will close with a really interesting aspect of Views.
SQL> CREATE TABLE BaseTable (MyNumber NUMBER(1));
Table created.
SQL> INSERT INTO BaseTable (MyNumber) VALUES (1);
1 row created.
SQL> CREATE VIEW MyView AS SELECT * FROM BaseTable;
View created.
SQL> ALTER TABLE BaseTable ADD (MyString VARCHAR2(32));
Table altered.
SQL> INSERT INTO BaseTable (MyNumber, MyString) VALUES (2, 'Hello');
1 row created.
SQL> SELECT * FROM MyView;
MYNUMBER
----------
1
2
SQL> SELECT * FROM BaseTable;
MYNUMBER MYSTRING
---------- --------------------------------
1
2 Hello
The View is NOT updated when the BaseTable is, even when you SELECT *. When you write SELECT * the view chooses its columns then and there.
Saturday, July 23, 2005
Oracle BOOLEAN
There is no BOOLEAN datatype in Oracle, as far as tables are concerned.
CREATE TABLE BooleanTable (MyBool BOOLEAN);
ORA-00902: invalid datatype
But there is a BOOLEAN datatype in PL/SQL.
CREATE OR REPLACE PROCEDURE BoolProc (in_bool IN BOOLEAN)
AS
my_bool BOOLEAN := TRUE;
BEGIN
IF (in_bool = my_bool) THEN
DBMS_OUTPUT.PUT_LINE('True');
ELSE
DBMS_OUTPUT.PUT_LINE('False or NULL');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END BoolProc;
Why is there no Boolean in Oracle for tables? What should we do instead? This:
CREATE TABLE BoolTable (MyBool CHAR(1) CHECK (MyBool IN ( 'Y', 'N' )));
As for the first question, as usual, let's Ask Tom:
http://asktom.oracle.com/pls/ask/f?p=4950:8:2109566621053828525::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:6263249199595
CREATE TABLE BooleanTable (MyBool BOOLEAN);
ORA-00902: invalid datatype
But there is a BOOLEAN datatype in PL/SQL.
CREATE OR REPLACE PROCEDURE BoolProc (in_bool IN BOOLEAN)
AS
my_bool BOOLEAN := TRUE;
BEGIN
IF (in_bool = my_bool) THEN
DBMS_OUTPUT.PUT_LINE('True');
ELSE
DBMS_OUTPUT.PUT_LINE('False or NULL');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END BoolProc;
Why is there no Boolean in Oracle for tables? What should we do instead? This:
CREATE TABLE BoolTable (MyBool CHAR(1) CHECK (MyBool IN ( 'Y', 'N' )));
As for the first question, as usual, let's Ask Tom:
http://asktom.oracle.com/pls/ask/f?p=4950:8:2109566621053828525::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:6263249199595
Thursday, July 21, 2005
Oracle Blogs
If you enjoy this blog, there may be other Oracle-related blogs you will also enjoy reading. You may be surprised to learn just how many good Oracle blogs are out there. I follow quite a few on a daily or casual basis. Here are my favourites.
Brian Duff hosts OraBlogs, which picks up several of the below Oracle blogs, so it can be a one-stop blog.
http://www.orablogs.com/orablogs/
Tom Kyte, respected author of "Ask Tom." Covers a pretty wide spectrum of Oracle-related topics, including new things and "best practises." This is on practically everyone's favourite blog list, and every post gets dozens of comments.
http://tkyte.blogspot.com/
Niall Litchfield has one of the first Oracle blogs I read. He has interesting posts on a variety of topics including htmldb, and Oracle 10g.
http://www.niall.litchfield.dial.pipex.com/
Howard Rogers is the master of the Dizwell Forum. The discussions therein are generally the inspiration for his posts. His passionate Oracle rants give his blog a lot of flavour.
http://www.dizwell.com/blogindex.html
David Aldridge, is an all-purpose Oracle warehouse specialist. I love his posts on warehouse design and maintenance. He also posts regularly about Oracle 10g and its features.
http://oraclesponge.blogspot.com/
Mark Rittman is a database warehouse developer and covers a lot of stuff, like modelling. He has a link to all the Oracle blogs that are out there (good or bad), so you can go through there and pick your favourites.
http://www.rittman.net/
Jeff Hunter is an Oracle DBA with a relatively new blog. He posts a wide variety of topics, and he is on a lot of people's favourites list.
http://marist89.blogspot.com/
Tim Hall is an Oracle DBA, designer and developer. Posts on a variety of topics and is not shy about sharing his opinions. Despite the fact that he doesn't use IDEs, he's one of the few people who mention them.
http://oracle-base.blogspot.com/
Doug Burns, another Oracle DBA, reads and writes papers and books. I count on him to post about the latest news and papers/books of interest.
http://doug.burns.tripod.com/oracle/
Pete Finnigan, an Oracle security expert, provides the latest news of bugs and security flaws. Even though I'm not into security that much, I still enjoy his posts.
http://www.petefinnigan.com/weblog/entries
Eddie Awad, an Oracle application developer who posts interesting things about Oracle as he finds them. Also talks about ColdFusion and Biztalk occasionally. Has me in his blogroll! :)
http://awads.net/wp/
Peter Scott is a manager (!) in charge of an Oracle data warehouse. As a result his posts are generally about the situation of the day, which is usually of significance to everyone.
http://pjs-random.blogspot.com/
Lisa Dobson, an Oracle DBA whose brand new blog focuses on the Newbie perspective.
http://newbiedba.blogspot.com/
Amis Technology Corner has a number of Oracle professionals from various backgrounds that post on a variety of topics, including Oracle-related events and publications, and various designs and features.
http://technology.amis.nl/blog/
Mike Ault, a Burleson consultant and published author, posts on a variety of Oracle-related topics related to his interesting adventures while consulting. No comments allowed, ostensibly for legal reasons but people posted a lot of corrections when they were allowed.
http://mikerault.blogspot.com/
Robert Freeman, another Burleson consultant and a 15-year Oracle DBA, posts on a variety of Oracle-related topics of interest to DBAs, including the types of problems he sees and solves and things he finds in the latest releases.
http://robertgfreeman.blogspot.com/
If you know of any other Oracle-related blogs that you enjoy, please add a Comment so I can check it out. Please spread the word of these great blogs!
Brian Duff hosts OraBlogs, which picks up several of the below Oracle blogs, so it can be a one-stop blog.
http://www.orablogs.com/orablogs/
Tom Kyte, respected author of "Ask Tom." Covers a pretty wide spectrum of Oracle-related topics, including new things and "best practises." This is on practically everyone's favourite blog list, and every post gets dozens of comments.
http://tkyte.blogspot.com/
Niall Litchfield has one of the first Oracle blogs I read. He has interesting posts on a variety of topics including htmldb, and Oracle 10g.
http://www.niall.litchfield.dial.pipex.com/
Howard Rogers is the master of the Dizwell Forum. The discussions therein are generally the inspiration for his posts. His passionate Oracle rants give his blog a lot of flavour.
http://www.dizwell.com/blogindex.html
David Aldridge, is an all-purpose Oracle warehouse specialist. I love his posts on warehouse design and maintenance. He also posts regularly about Oracle 10g and its features.
http://oraclesponge.blogspot.com/
Mark Rittman is a database warehouse developer and covers a lot of stuff, like modelling. He has a link to all the Oracle blogs that are out there (good or bad), so you can go through there and pick your favourites.
http://www.rittman.net/
Jeff Hunter is an Oracle DBA with a relatively new blog. He posts a wide variety of topics, and he is on a lot of people's favourites list.
http://marist89.blogspot.com/
Tim Hall is an Oracle DBA, designer and developer. Posts on a variety of topics and is not shy about sharing his opinions. Despite the fact that he doesn't use IDEs, he's one of the few people who mention them.
http://oracle-base.blogspot.com/
Doug Burns, another Oracle DBA, reads and writes papers and books. I count on him to post about the latest news and papers/books of interest.
http://doug.burns.tripod.com/oracle/
Pete Finnigan, an Oracle security expert, provides the latest news of bugs and security flaws. Even though I'm not into security that much, I still enjoy his posts.
http://www.petefinnigan.com/weblog/entries
Eddie Awad, an Oracle application developer who posts interesting things about Oracle as he finds them. Also talks about ColdFusion and Biztalk occasionally. Has me in his blogroll! :)
http://awads.net/wp/
Peter Scott is a manager (!) in charge of an Oracle data warehouse. As a result his posts are generally about the situation of the day, which is usually of significance to everyone.
http://pjs-random.blogspot.com/
Lisa Dobson, an Oracle DBA whose brand new blog focuses on the Newbie perspective.
http://newbiedba.blogspot.com/
Amis Technology Corner has a number of Oracle professionals from various backgrounds that post on a variety of topics, including Oracle-related events and publications, and various designs and features.
http://technology.amis.nl/blog/
Mike Ault, a Burleson consultant and published author, posts on a variety of Oracle-related topics related to his interesting adventures while consulting. No comments allowed, ostensibly for legal reasons but people posted a lot of corrections when they were allowed.
http://mikerault.blogspot.com/
Robert Freeman, another Burleson consultant and a 15-year Oracle DBA, posts on a variety of Oracle-related topics of interest to DBAs, including the types of problems he sees and solves and things he finds in the latest releases.
http://robertgfreeman.blogspot.com/
If you know of any other Oracle-related blogs that you enjoy, please add a Comment so I can check it out. Please spread the word of these great blogs!
Monday, July 18, 2005
Which instance am I in?
Here's an easy one a colleague asked me.
You are logged into your default session (example: sqlplus scott/tiger with no @instance)
You want to know which instance you are logged into.
You can't query V$ tables directly, because you're not logged in as SYS.
So while looking around at similar questions on Jonathan Lewis' cooperative FAQ:
http://www.jlcomp.demon.co.uk/faq/MySessID.html
I saw SYS_CONTEXT. That looked like it would do the trick. So I looked it up in my favourite reference, Dan Morgan's:
http://www.psoug.org/reference/sys_context.html
And came up with this:
SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;
You can also get the 'INSTANCE' using this, but that doesn't do you any good if you can't query the V$INSTANCE table.
That solves the problem, but I'm sure that's just one of many ways to do it:
1. Some other queries on some system tables, and/or
2. Some command-line command, and/or
3. Some environment file somewhere.
Also, I know some people who put this directly into their sqlplus prompt.
You are logged into your default session (example: sqlplus scott/tiger with no @instance)
You want to know which instance you are logged into.
You can't query V$ tables directly, because you're not logged in as SYS.
So while looking around at similar questions on Jonathan Lewis' cooperative FAQ:
http://www.jlcomp.demon.co.uk/faq/MySessID.html
I saw SYS_CONTEXT. That looked like it would do the trick. So I looked it up in my favourite reference, Dan Morgan's:
http://www.psoug.org/reference/sys_context.html
And came up with this:
SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;
You can also get the 'INSTANCE' using this, but that doesn't do you any good if you can't query the V$INSTANCE table.
That solves the problem, but I'm sure that's just one of many ways to do it:
1. Some other queries on some system tables, and/or
2. Some command-line command, and/or
3. Some environment file somewhere.
Also, I know some people who put this directly into their sqlplus prompt.
Thursday, July 14, 2005
Oracle Docs
We have seen articles on where to go for help, and how to ask for help:
http://thinkoracle.blogspot.com/2005/06/asking-for-help.html
Recently Tom Kyte wrote an article on Reading the F'n Manual:
http://tkyte.blogspot.com/2005/07/rtfm.html
Which begs the F'n question. Where are the F'n manuals? Which manuals should you F'n read?
The first question is easy, it's all here:
http://www.oracle.com/technology/documentation/index.html
Personally, I use Oracle9, so I have this page bookmarked:
http://www.oracle.com/technology/documentation/oracle9i.html
For the novice, you want to go to View Library and Getting Started
There you will find links for the Installer, the Administrator, and the Developer. These links give you recommendations for which F'n manuals to read.
Being an application developer the key F'n manual for me, without dispute, is:
Oracle9i Application Developer's Guide - Fundamentals
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590.pdf
I won't include all the links, because a list of all F'n manuals for Oracle9i can be found here:
http://www.oracle.com/pls/db92/db92.docindex?remark=homepage
Here are the F'n manuals that are recommended for Application Developers:
Oracle9i Database Concepts Guide
Oracle9i SQL Reference
Oracle9i Data Warehousing Guide
PL/SQL User's Guide and Reference
Oracle9i Supplied PL/SQL Packages and Types Reference
Oracle9i Database Performance Tuning Guide and Reference
Oracle9i Database Error Messages
Beyond that, there are also F'n manuals for more advanced programming (Java, C++, XML, Object-Related).
Please feel free to leave me some F'n comments with other F'n good manuals and books.
http://thinkoracle.blogspot.com/2005/06/asking-for-help.html
Recently Tom Kyte wrote an article on Reading the F'n Manual:
http://tkyte.blogspot.com/2005/07/rtfm.html
Which begs the F'n question. Where are the F'n manuals? Which manuals should you F'n read?
The first question is easy, it's all here:
http://www.oracle.com/technology/documentation/index.html
Personally, I use Oracle9, so I have this page bookmarked:
http://www.oracle.com/technology/documentation/oracle9i.html
For the novice, you want to go to View Library and Getting Started
There you will find links for the Installer, the Administrator, and the Developer. These links give you recommendations for which F'n manuals to read.
Being an application developer the key F'n manual for me, without dispute, is:
Oracle9i Application Developer's Guide - Fundamentals
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590.pdf
I won't include all the links, because a list of all F'n manuals for Oracle9i can be found here:
http://www.oracle.com/pls/db92/db92.docindex?remark=homepage
Here are the F'n manuals that are recommended for Application Developers:
Oracle9i Database Concepts Guide
Oracle9i SQL Reference
Oracle9i Data Warehousing Guide
PL/SQL User's Guide and Reference
Oracle9i Supplied PL/SQL Packages and Types Reference
Oracle9i Database Performance Tuning Guide and Reference
Oracle9i Database Error Messages
Beyond that, there are also F'n manuals for more advanced programming (Java, C++, XML, Object-Related).
Please feel free to leave me some F'n comments with other F'n good manuals and books.
Wednesday, July 13, 2005
Stored Procedure template
Since there was some interest in the "object" template,
http://thinkoracle.blogspot.com/2005/06/oop-in-plsql-yep.html
and since I've been dry on ideas lately because I haven't been working on non-Oracle things the last week or two, I decided to share another one of my templates. This time: stored procedures. Enjoy!
SET SERVEROUTPUT ON;
-- 'NOCOPY' makes it a pointer (pass by reference) - faster.
-- 'DETERMINISTIC' means the output is the same for every input - allows caching (for return type tables)
-- CREATE OR REPLACE FUNCTION MyFunc (p_something IN OUT NOCOPY Transactions.number%TYPE)
-- RETURN BOOLEAN DETERMINISTIC
-- 'DEFAULT' is the same as ':=' for both here and DECLARE
CREATE OR REPLACE PROCEDURE MyProc (p_something IN Transactions.number%TYPE DEFAULT 1)
-- The following says it is independent of anything calling it (eg: rollbacks, etc)
-- IS PRAGMA AUTONOMOUS_TRANSACTION
AS
[[BlockName]]
-- If its an anonymous block:
-- DECLARE
v_datetime TIMESTAMP;
v_transaction Transactions.number%TYPE := 0;
v_the_date_is CONSTANT VARCHAR2(12) := 'The date is ';
-- Create an exception, the pragma is optional
v_my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(v_my_exception, 100);
-- subprograms (functions) MUST be declared last
PROCEDURE NothingReally IS BEGIN NULL; END NothingReally;
BEGIN
-- SET TRANSACTION READ ONLY; -- Use consistent snapshot of the database
-- SET TRANSACTION READ WRITE; -- The default. Turns "off" the Read Only
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- SET TRANSACTION USE ROLLBACK SEGMENT
-- This is a comment
SELECT systime INTO BlockName.v_datetime FROM dual;
DBMS_OUTPUT.PUT_LINE(v_the_date_is || v_datetime);
[[LoopName]]
IF 1=1
THEN NULL;
ELSIF 1=2
THEN RAISE NO_DATA_FOUND;
ELSE
THEN NothingReally;
END IF;
CASE
WHEN 1=1 THEN NULL;
-- Application Errors have to be -20000 to -20999 inclusive and can't be caught specifically
WHEN 1=2 THEN RAISE_APPLICATION_ERROR(-20000, 'Error: '||v_the_date_is||' BAH');
ELSE NULL;
END CASE;
LOOP
EXIT WHEN 1=1;
END LOOP;
FOR v_transaction IN 0 .. 10
LOOP
NULL;
END LOOP;
WHILE 1=2
LOOP
RAISE v_my_exception;
END LOOP;
COMMIT;
EXCEPTION
-- This only covers errors raised after BEGIN (but NOT in 'DECLARE'!)
-- You can "OR" exceptions.
-- An exception can't be in more than 1 block
WHEN v_my_exception
THEN NULL;
-- This is optional but good practice.
-- Unhandled exceptions fall through to the next block or statement
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END MyProc;
/
http://thinkoracle.blogspot.com/2005/06/oop-in-plsql-yep.html
and since I've been dry on ideas lately because I haven't been working on non-Oracle things the last week or two, I decided to share another one of my templates. This time: stored procedures. Enjoy!
SET SERVEROUTPUT ON;
-- 'NOCOPY' makes it a pointer (pass by reference) - faster.
-- 'DETERMINISTIC' means the output is the same for every input - allows caching (for return type tables)
-- CREATE OR REPLACE FUNCTION MyFunc (p_something IN OUT NOCOPY Transactions.number%TYPE)
-- RETURN BOOLEAN DETERMINISTIC
-- 'DEFAULT' is the same as ':=' for both here and DECLARE
CREATE OR REPLACE PROCEDURE MyProc (p_something IN Transactions.number%TYPE DEFAULT 1)
-- The following says it is independent of anything calling it (eg: rollbacks, etc)
-- IS PRAGMA AUTONOMOUS_TRANSACTION
AS
[[BlockName]]
-- If its an anonymous block:
-- DECLARE
v_datetime TIMESTAMP;
v_transaction Transactions.number%TYPE := 0;
v_the_date_is CONSTANT VARCHAR2(12) := 'The date is ';
-- Create an exception, the pragma is optional
v_my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT(v_my_exception, 100);
-- subprograms (functions) MUST be declared last
PROCEDURE NothingReally IS BEGIN NULL; END NothingReally;
BEGIN
-- SET TRANSACTION READ ONLY; -- Use consistent snapshot of the database
-- SET TRANSACTION READ WRITE; -- The default. Turns "off" the Read Only
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- SET TRANSACTION USE ROLLBACK SEGMENT
-- This is a comment
SELECT systime INTO BlockName.v_datetime FROM dual;
DBMS_OUTPUT.PUT_LINE(v_the_date_is || v_datetime);
[[LoopName]]
IF 1=1
THEN NULL;
ELSIF 1=2
THEN RAISE NO_DATA_FOUND;
ELSE
THEN NothingReally;
END IF;
CASE
WHEN 1=1 THEN NULL;
-- Application Errors have to be -20000 to -20999 inclusive and can't be caught specifically
WHEN 1=2 THEN RAISE_APPLICATION_ERROR(-20000, 'Error: '||v_the_date_is||' BAH');
ELSE NULL;
END CASE;
LOOP
EXIT WHEN 1=1;
END LOOP;
FOR v_transaction IN 0 .. 10
LOOP
NULL;
END LOOP;
WHILE 1=2
LOOP
RAISE v_my_exception;
END LOOP;
COMMIT;
EXCEPTION
-- This only covers errors raised after BEGIN (but NOT in 'DECLARE'!)
-- You can "OR" exceptions.
-- An exception can't be in more than 1 block
WHEN v_my_exception
THEN NULL;
-- This is optional but good practice.
-- Unhandled exceptions fall through to the next block or statement
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END MyProc;
/
Monday, July 11, 2005
Specifying INSERT Columns
Here is a quick one that I touched on briefly in one of my first blogs:
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
It can be important to specify which columns you plan on providing values for in an INSERT statement. Repeating Dan Morgan's quote:
"By not specifying column names you are signifying that you are providing values for ALL columns. This is why it is a very bad practice as doing an ALTER TABLE ADD immediately invalidates all SQL statements."
Here is an example. I created a table, and then wrote a procedure that will insert a row WITHOUT specifying the values. Works fine if the table doesn't change:
SET SERVEROUTPUT ON;
CREATE TABLE MyTable (MyInt NUMBER);
CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN NUMBER)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END InsertIntoMyTable;
/
EXEC InsertIntoMyTable(1);
Inserting 1 into MyTable
PL/SQL procedure successfully completed.
So let's try modifying the table, and try again.
ALTER TABLE MyTable ADD (MyString VARCHAR2(32));
EXEC InsertIntoMyTable(2);
BEGIN InsertIntoMyTable(2); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.INSERTINTOMYTABLE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The procedure fails, and can't recompile. Perhaps you want that behaviour, because you want to require all your procedures to be checked every time a related table has changed. But, then again, maybe you don't.
In that case, modify the stored procedure to specify which value you are inserting, and then you're good to go. Let's repeat this test that way:
CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN NUMBER)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable (MyInt) VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END InsertIntoMyTable;
/
SQL> EXEC InsertIntoMyTable(2);
Inserting 2 into MyTable
PL/SQL procedure successfully completed.
SQL> ALTER TABLE MyTable ADD (MyOtherInt NUMBER);
Table altered.
SQL> EXEC InsertIntoMyTable(3);
Inserting 3 into MyTable
PL/SQL procedure successfully completed.
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
It can be important to specify which columns you plan on providing values for in an INSERT statement. Repeating Dan Morgan's quote:
"By not specifying column names you are signifying that you are providing values for ALL columns. This is why it is a very bad practice as doing an ALTER TABLE ADD immediately invalidates all SQL statements."
Here is an example. I created a table, and then wrote a procedure that will insert a row WITHOUT specifying the values. Works fine if the table doesn't change:
SET SERVEROUTPUT ON;
CREATE TABLE MyTable (MyInt NUMBER);
CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN NUMBER)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END InsertIntoMyTable;
/
EXEC InsertIntoMyTable(1);
Inserting 1 into MyTable
PL/SQL procedure successfully completed.
So let's try modifying the table, and try again.
ALTER TABLE MyTable ADD (MyString VARCHAR2(32));
EXEC InsertIntoMyTable(2);
BEGIN InsertIntoMyTable(2); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object SCOTT.INSERTINTOMYTABLE is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The procedure fails, and can't recompile. Perhaps you want that behaviour, because you want to require all your procedures to be checked every time a related table has changed. But, then again, maybe you don't.
In that case, modify the stored procedure to specify which value you are inserting, and then you're good to go. Let's repeat this test that way:
CREATE OR REPLACE PROCEDURE InsertIntoMyTable (InValue IN NUMBER)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Inserting ' || InValue || ' into MyTable');
INSERT INTO MyTable (MyInt) VALUES (InValue);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END InsertIntoMyTable;
/
SQL> EXEC InsertIntoMyTable(2);
Inserting 2 into MyTable
PL/SQL procedure successfully completed.
SQL> ALTER TABLE MyTable ADD (MyOtherInt NUMBER);
Table altered.
SQL> EXEC InsertIntoMyTable(3);
Inserting 3 into MyTable
PL/SQL procedure successfully completed.
Tuesday, July 05, 2005
Regular Expressions in Oracle
I was recently asked to do a blog about Regular Expressions in Oracle 10, because they were cool.
They are cool.
Except to people that get spooked by a bunch of [:whatever:] in their code, and feel their lunch come up when they see '[^:]+,\?.{3} (tip: if that is you, don't ever read Perl code).
Anyway I can not do a blog on Regular Expressions in Oracle 10 for two reasons:
1. I only blog about what I'm currently working on and studying, and
2. I use Oracle 9.
But no worries. There is a great article on Oracle Regular Expressions:
http://www.oracle.com/technology/oramag/webcolumns/
2003/techarticles/rischert_regexp_pt1.html
Hmph, looks like I blogged about Oracle Regular Expressions after all.
Oh yes, and here is your Regular Expression reference, courtesy of (who else) Dan Morgan:
http://www.psoug.org/reference/regexp.html
He covers REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR.
They are cool.
Except to people that get spooked by a bunch of [:whatever:] in their code, and feel their lunch come up when they see '[^:]+,\?.{3} (tip: if that is you, don't ever read Perl code).
Anyway I can not do a blog on Regular Expressions in Oracle 10 for two reasons:
1. I only blog about what I'm currently working on and studying, and
2. I use Oracle 9.
But no worries. There is a great article on Oracle Regular Expressions:
http://www.oracle.com/technology/oramag/webcolumns/
2003/techarticles/rischert_regexp_pt1.html
Hmph, looks like I blogged about Oracle Regular Expressions after all.
Oh yes, and here is your Regular Expression reference, courtesy of (who else) Dan Morgan:
http://www.psoug.org/reference/regexp.html
He covers REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR.
Monday, July 04, 2005
SQLCODE and SQLERRM in INSERTs
Here is an interesting foible. Normally you can build strings on the fly from functions and insert them into a table. Like so:
CREATE TABLE LogTable (logString VARCHAR2(128));
CREATE OR REPLACE FUNCTION MyFunc RETURN VARCHAR2
AS
BEGIN
RETURN 'Hello';
END;
CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (MyFunc || ' ' || MyFunc);
END;
But you can't do this with SQLCODE and SQLERRM.
CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (SQLCODE || ' ' || SQLERRM);
END;
PL/SQL: ORA-00984: column not allowed here
You can always put the values in a string for a workaround.
CREATE OR REPLACE PROCEDURE MyProc
AS
errString LogTable.logString%TYPE;
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
errString := SQLCODE || ' ' || SQLERRM;
INSERT INTO LogTable (logString) VALUES (errString);
END;
Procedure created.
For reference, look up SQLCODE and SQLERRM in the PL/SQL User's Guide and Reference.
Bonus. I found the following blog while reviewing OraBlogs:
http://blog.niftypaint.nl/
Which pointed me to Eddie Awad's great blog:
http://awads.net/wp/
Enjoy!
CREATE TABLE LogTable (logString VARCHAR2(128));
CREATE OR REPLACE FUNCTION MyFunc RETURN VARCHAR2
AS
BEGIN
RETURN 'Hello';
END;
CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (MyFunc || ' ' || MyFunc);
END;
But you can't do this with SQLCODE and SQLERRM.
CREATE OR REPLACE PROCEDURE MyProc
AS
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
INSERT INTO LogTable (logString) VALUES (SQLCODE || ' ' || SQLERRM);
END;
PL/SQL: ORA-00984: column not allowed here
You can always put the values in a string for a workaround.
CREATE OR REPLACE PROCEDURE MyProc
AS
errString LogTable.logString%TYPE;
BEGIN
NULL;
EXCEPTION
WHEN OTHERS
THEN
errString := SQLCODE || ' ' || SQLERRM;
INSERT INTO LogTable (logString) VALUES (errString);
END;
Procedure created.
For reference, look up SQLCODE and SQLERRM in the PL/SQL User's Guide and Reference.
Bonus. I found the following blog while reviewing OraBlogs:
http://blog.niftypaint.nl/
Which pointed me to Eddie Awad's great blog:
http://awads.net/wp/
Enjoy!
Friday, July 01, 2005
Extra Columns in a GROUP BY
Happy Canada Day. I have a really good one today.
My problem:
I have a table 'ASSIGNMENTS' that keeps track of each assignment, who holds which assignment, and as of which date they have held it.
It would be very handy to have a view that shows all assignments, and who is currently on that assignment.
CREATE TABLE ASSIGNMENTS(ASSIGNMENT VARCHAR2(32), EMPLOYEE VARCHAR2(32), EFFECTIVE DATE);
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE) VALUES ('Prime Minister', 'Jean Chretien', '04-Nov-93');
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE) VALUES ('Governor General', 'Adrienne Clarkson', '07-Oct-99');
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE) VALUES ('Prime Minister', 'Paul Martin', '12-Dec-03');
You must be familiar with GROUP BY.
SELECT ASSIGNMENT, MAX(EFFECTIVE) AS_OF FROM ASSIGNMENTS GROUP BY ASSIGNMENT;
ASSIGNMENT AS_OF
-------------------------------- ---------
Governor General 07-OCT-99
Prime Minister 12-DEC-03
2 rows selected.
Ok, now we would like to add in the name of the person. But whoops, doesn't work as you'd expect.
SELECT ASSIGNMENT, EMPLOYEE, MAX(EFFECTIVE) AS_OF FROM ASSIGNMENTS GROUP BY ASSIGNMENT;
ORA-00979: not a GROUP BY expression
You see, we can't include any columns that aren't part of the GROUP BY. And, we can't include it in the group by, because then we'd get a result no different from assignments.
There is an answer. Connor McDonald has it:
http://www.oracledba.co.uk/tips/9i_first_last.htm
Alright, what is Connor talking about? Basically he is grouping the "extra" column (empno), and making it a secondary grouping to the main grouping (sal).
But, really, MIN(EMPNO)? Why are we including an aggregate function on empno? Well, Connor is doing it because he wants to break ties.
Even though we don't need to break ties, we still need to use an aggregate function, otherwise it becomes part of the group by. Any aggregate function will do since we shouldn't have duplicates.
DENSE_RANK , FIRST, ORDER BY
- ORDER BY will sort a set of rows by a particular column. In Connor's case it is salary, in our case, it will be 'effective'.
- DENSE_RANK provides the positioning of a particular row within an ordered list of rows.
- FIRST goes hand-in-hand with DENSE_RANK and will naturally provide us with the first, ranked row in a ordered list of rows.
Note that the ORDER BY defaults to ascending order, and so in our case we either want to choose LAST or put the ORDER BY in descending order instead.
Essentially he is creating an ordered list of all salaries, and choosing the empno that shows up first on that list. Sounds like what we want!
Here is Dan Morgan's reference on numeric functions like these:
http://www.psoug.org/reference/number_func.html
KEEP is just a clever Oracle trick to keep the work we're doing in the shared pool because we're using it twice in the same query.
So applying Connor's teachings to our situation, we get:
CREATE OR REPLACE VIEW CURRENT_ASSIGNMENTS AS
SELECT ASSIGNMENT,
MAX(EMPLOYEE) KEEP (DENSE_RANK LAST ORDER BY EFFECTIVE) EMPLOYEE,
MAX(EFFECTIVE) AS_OF
FROM ASSIGNMENTS
GROUP BY ASSIGNMENT;
SELECT * FROM CURRENT_ASSIGNMENTS;
ASSIGNMENT EMPLOYEE AS_OF
-------------------------------- -------------------------------- ---------
Governor General Adrienne Clarkson 07-OCT-99
Prime Minister Paul Martin 12-DEC-03
2 rows selected.
Coming up in some future blog, Tom Kyte mentioned the awesome "MEMBER OF" feature that is in Oracle 10g. It will tell you easily whether a value is contained in a particular set/table. I'm looking for a way to do this in Oracle 9.
http://thinkoracle.blogspot.com/2005/05/enum-in-oracle.html
My problem:
I have a table 'ASSIGNMENTS' that keeps track of each assignment, who holds which assignment, and as of which date they have held it.
It would be very handy to have a view that shows all assignments, and who is currently on that assignment.
CREATE TABLE ASSIGNMENTS(ASSIGNMENT VARCHAR2(32), EMPLOYEE VARCHAR2(32), EFFECTIVE DATE);
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE) VALUES ('Prime Minister', 'Jean Chretien', '04-Nov-93');
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE) VALUES ('Governor General', 'Adrienne Clarkson', '07-Oct-99');
INSERT INTO ASSIGNMENTS(ASSIGNMENT, EMPLOYEE, EFFECTIVE) VALUES ('Prime Minister', 'Paul Martin', '12-Dec-03');
You must be familiar with GROUP BY.
SELECT ASSIGNMENT, MAX(EFFECTIVE) AS_OF FROM ASSIGNMENTS GROUP BY ASSIGNMENT;
ASSIGNMENT AS_OF
-------------------------------- ---------
Governor General 07-OCT-99
Prime Minister 12-DEC-03
2 rows selected.
Ok, now we would like to add in the name of the person. But whoops, doesn't work as you'd expect.
SELECT ASSIGNMENT, EMPLOYEE, MAX(EFFECTIVE) AS_OF FROM ASSIGNMENTS GROUP BY ASSIGNMENT;
ORA-00979: not a GROUP BY expression
You see, we can't include any columns that aren't part of the GROUP BY. And, we can't include it in the group by, because then we'd get a result no different from assignments.
There is an answer. Connor McDonald has it:
http://www.oracledba.co.uk/tips/9i_first_last.htm
Alright, what is Connor talking about? Basically he is grouping the "extra" column (empno), and making it a secondary grouping to the main grouping (sal).
But, really, MIN(EMPNO)? Why are we including an aggregate function on empno? Well, Connor is doing it because he wants to break ties.
Even though we don't need to break ties, we still need to use an aggregate function, otherwise it becomes part of the group by. Any aggregate function will do since we shouldn't have duplicates.
DENSE_RANK , FIRST, ORDER BY
- ORDER BY will sort a set of rows by a particular column. In Connor's case it is salary, in our case, it will be 'effective'.
- DENSE_RANK provides the positioning of a particular row within an ordered list of rows.
- FIRST goes hand-in-hand with DENSE_RANK and will naturally provide us with the first, ranked row in a ordered list of rows.
Note that the ORDER BY defaults to ascending order, and so in our case we either want to choose LAST or put the ORDER BY in descending order instead.
Essentially he is creating an ordered list of all salaries, and choosing the empno that shows up first on that list. Sounds like what we want!
Here is Dan Morgan's reference on numeric functions like these:
http://www.psoug.org/reference/number_func.html
KEEP is just a clever Oracle trick to keep the work we're doing in the shared pool because we're using it twice in the same query.
So applying Connor's teachings to our situation, we get:
CREATE OR REPLACE VIEW CURRENT_ASSIGNMENTS AS
SELECT ASSIGNMENT,
MAX(EMPLOYEE) KEEP (DENSE_RANK LAST ORDER BY EFFECTIVE) EMPLOYEE,
MAX(EFFECTIVE) AS_OF
FROM ASSIGNMENTS
GROUP BY ASSIGNMENT;
SELECT * FROM CURRENT_ASSIGNMENTS;
ASSIGNMENT EMPLOYEE AS_OF
-------------------------------- -------------------------------- ---------
Governor General Adrienne Clarkson 07-OCT-99
Prime Minister Paul Martin 12-DEC-03
2 rows selected.
Coming up in some future blog, Tom Kyte mentioned the awesome "MEMBER OF" feature that is in Oracle 10g. It will tell you easily whether a value is contained in a particular set/table. I'm looking for a way to do this in Oracle 9.
http://thinkoracle.blogspot.com/2005/05/enum-in-oracle.html
