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.

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

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.

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

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!

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.

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.

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;
/

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.

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.

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!

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

This page is powered by Blogger. Isn't yours?