Thursday, June 30, 2005

OOP in PL/SQL? Yep

I was recently speaking with someone who was stunned to find out that object-oriented programming is available in PL/SQL (as of version 9, basically).

I guess I was stunned he didn't know this, but then again, I guess there isn't much fanfare about it.

Inheritance? Yep.
Polymorphism? Yep.
Encapsulation? Yep.

I gave him this basic, bare-bones template. It isn't much, but it can get someone started.

CREATE OR REPLACE TYPE some_object AS OBJECT (

some_variable NUMBER(10),

MEMBER FUNCTION member_function RETURN NUMBER,
MEMBER FUNCTION member_function (l_overloading IN NUMBER) RETURN NUMBER,
MEMBER PROCEDURE member_procedure,

-- Static functions can be used with an instance of the object
-- They can NOT reference any other non-static member functions or variables
STATIC FUNCTION static_function (l_value IN NUMBER DEFAULT 1) RETURN NUMBER,

-- Constructors must return self. Constructors are optional
CONSTRUCTOR FUNCTION some_object (some_variable NUMBER) RETURN SELF AS RESULT,

-- Used for comparison purposes: GROUP BY, ORDER BY, DISTINCT
-- No parameters allowed, returns NUMBER, DATE, VARCHAR2, CHAR or REAL
MAP MEMBER FUNCTION map_member_function RETURN NUMBER

-- ORDER takes one parameter of same type, and returns NUMBER
-- You may only have EITHER MAP OR ORDER
-- ORDER MEMBER FUNCTION order_member_function (some_other_object IN some_object) RETURN NUMBER

)
INSTANTIABLE -- Or "NOT INSTANTIABLE" if this is a base class only
NOT FINAL -- Or "FINAL" if this class will NOT have a sub-class
;

CREATE OR REPLACE TYPE composition_object AS OBJECT (

composed_object some_object

);

CREATE OR REPLACE TYPE derived_object
UNDER some_object (

OVERRIDING MEMBER PROCEDURE member_procedure

);

CREATE OR REPLACE TYPE BODY some_object AS

MEMBER FUNCTION member_function RETURN NUMBER
IS
BEGIN
-- The "SELF" isn't necessary, but is always available by default in member functions
RETURN SELF.some_variable;
END member_function;

MEMBER FUNCTION member_function (l_overloading IN NUMBER) RETURN NUMBER
IS
BEGIN
RETURN l_overloading;
END member_function;

MEMBER PROCEDURE member_procedure
IS
BEGIN
NULL;
END member_procedure;

-- Note: Unlike with packages, no private functions or declarations are allowed.
-- MEMBER FUNCTION hidden_proc RETURN NUMBER ...

-- Remember, static functions can't access SELF variables
STATIC FUNCTION static_function (l_value IN NUMBER DEFAULT 1) RETURN NUMBER
IS
BEGIN
RETURN l_value;
END static_function;

CONSTRUCTOR FUNCTION some_object (some_variable NUMBER) RETURN SELF AS RESULT
AS
BEGIN
SELF.some_variable := some_variable;

-- It will automatically return self, don't even try to return anything else
RETURN;

END some_object;

MAP MEMBER FUNCTION map_member_function RETURN NUMBER IS
BEGIN
RETURN SELF.some_variable;
END map_member_function;

-- ORDER MEMBER FUNCTION order_member_function (some_other_object IN some_object) RETURN NUMBER IS
-- BEGIN
-- IF some_other_object.some_variable < SELF.some_variable THEN RETURN 1;
-- ELSIF some_other_object.some_variable > SELF.some_variable THEN RETURN -1;
-- ELSE RETURN 0;
-- END IF;
-- END order_member_function;

END;

CREATE OR REPLACE TYPE BODY derived_object AS

OVERRIDING MEMBER PROCEDURE member_procedure
IS
BEGIN
NULL;
END member_procedure;

END;


-- Test!
DECLARE
-- You MUST instantiate it to use it. A NULL object is hard to use.
my_some_object some_object := some_object(0);
my_composition_object composition_object := composition_object(my_some_object);
my_number NUMBER;
BEGIN
my_number := my_composition_object.composed_object.member_function;
my_number := some_object.static_function(my_number);
END;

Constraints

No blog from me. But here is an excellent one from Jeff Hunter that I wish I'd written.

http://marist89.blogspot.com/2005/06/deferrable-constraints_29.html

Also, check his Comments to see Doug Burns' link to an article by Chris Date.

Monday, June 27, 2005

Using Bad Names in Oracle

In Oracle, you can't start a table, procedure or variable name with a number.

SQL> CREATE TABLE 123Table (aval NUMBER);
CREATE TABLE 123Table (aval NUMBER)
*
ERROR at line 1:
ORA-00903: invalid table name

You can get around this simply using quotes.

SQL> CREATE TABLE "123Table" (aval NUMBER);

Table created.

This will take the name very literally. So literally, in fact, that you can no longer rely on Oracle's typical case-insensitivity.

SQL> drop table "123TABLE";
drop table "123TABLE"
*
ERROR at line 1:
ORA-00942: table or view does not exist

You can also use quotes to use reserved words.

SQL> CREATE TABLE "TABLE" ("NUMBER" NUMBER);

Table created.

Friday, June 24, 2005

Oracle Client

Here are the simple steps required for setting up an Oracle Client on a PC.

You will need:
- A working Oracle Server :)
- A compatible Oracle Client
- The service name, domain name, host name and port

1. Install Oracle SQL*Plus client on your PC

This should be quick and easy, accept all defaults.

2. Modify %ORACLE_HOME%\network\admin\tnsnames.ora


SRV_NAME.WHATEVER.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = srv_name)
)
)



3. Check %ORACLE_HOME%\network\admin\sqlnet.ora


NAMES.DEFAULT_DOMAIN = whatever.com
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)



4. Modify %ORACLE_HOME%\network\admin\listener.ora


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = srv_name)
)



5. Test connection

sqlplus username/password@srv_name

Thursday, June 23, 2005

PL/SQL Books

There are 3 PL/SQL books considered above the pack.

I settled on Scott Urman's book, but that's only because I got a deal on it when Nexus Computer Books in Calgary went out of business. I like it, and it is ranked #1 among PL/SQL books on Amazon.com.

Oracle9i PL/SQL Programming
Scott Urman
32.99
664 Pages
4 on 7 reviews
Rank in sales: #5460

I really like Steven Feuerstein's work, most of which can be found on the web. He's got Q&A, Puzzlers, and regular articles on Oracle's site. You can find sample chapters on-line. Despite the fact that I already have a good PL/SQL book, I might get this one, too. He also has a "Best Practises" book.

Oracle PL/SQL Programming, Third Edition
Steven Feuerstein
34.62
1018 Pages
4 on 66 reviews
Rank in sales: #17659

Connor McDonald of the Oak Table Network has a highly rated PL/SQL book as well. He also has an Internet presense. His site is awesome, he's got some great stuff, so his book ought to be great, too. I wouldn't mind picking this up.

Mastering Oracle PL/SQL: Practical Solutions
Connor McDonald
32.99
648 Pages
4.5 on 5 reviews
Rank in sales: #106559

There are other PL/SQL books out there, but any of these 3 is probably your best bet. Leave some comments if you have a preference for the 3, or know of any other really great PL/SQL books to consider.

Wednesday, June 22, 2005

Expert One-on-One

The general consensus is that Tom Kyte's "Expert One-on-One Oracle" is the best Oracle book available, and most Oracle professionals have a copy on their shelves.

In Chapter 1 of the first edition, you'll see a scheduling algorithm that is supposed to avoid double-bookings. Here is an example of how to double-book a room:

CREATE TABLE resources (resource_name VARCHAR2(25) primary key);
CREATE TABLE schedules (resource_name VARCHAR2(25) references resources, start_time DATE, end_time DATE);

INSERT INTO resources (resource_name) VALUES ('Room A');
INSERT INTO schedules (resource_name, start_time, end_time) VALUES ('Room A', '01-Jan-04', '04-Jan-04');

VARIABLE new_start_time VARCHAR2(32)
EXEC :new_start_time := '02-Jan-04'

VARIABLE new_end_time VARCHAR2(32)
EXEC :new_end_time := '03-Jan-04'

VARIABLE room_name VARCHAR2(25)
EXEC :room_name := 'Room A'

-- If the count comes back 0, the room is yours
SELECT COUNT (*) FROM schedules WHERE resource_name = :room_name
AND (start_time BETWEEN :new_start_time AND :new_end_time
OR
end_time BETWEEN :new_start_time AND :new_end_time);

This returns 0, which means the room is double-booked!!

Here is the fixed version from the 2nd edition:

ops$tkyte@ORA10GR1> select count(*)
2 from schedules
3 where resource_name = :room_name
4 and (start_time <= :new_end_time)
5 and (end_time >= :new_start_time);

According to Tom, "the only [other] notable fix is with regards to function based indexes where I said erroneously that the to_date() function was "broken" with respect to the YYYY format - it is not (can you see why :)"

Here it is:

CREATE TABLE t (y VARCHAR2(32));

CREATE INDEX t2 ON t(to_date(y,'yyyy'));
ORA-01743: only pure functions can be indexed.

As a workaround in the book, Tom created his own "to_date" function. But there is a far simpler reason why this doesn't work (and a far simpler solution). Even though it was staring us all in the very same pages, not very many people could figure out why this function-based index was disallowed:

"My conclusion in the book is wrong because to_date with the 'yyyy' format is not deterministic."

That means that for the same input, you can get a different output. But how can that be?

ops$tkyte@ORA10GR1> select to_date( '2005', 'yyyy' ) from dual;

TO_DATE('
---------
01-JUN-05

"Today, in june, to_date(2005) returns 01-jun, last month, same function, same inputs - would return 01-may"

Wow. Never would have guessed to_date(year) would be non-deterministic.

Tom also clarified that the following function would be deterministic and therefore eligible for a function-based index:

create index t2 on t( to_date( '01'||y, 'mmyyyy') );

That, of course, would force it to choose January no matter what time of year you called the function.

To me, it is very funny that to_date:
1. IS deterministic if you leave out the day (it will always choose the 1st day)
2. IS NOT deterministic if you leave out the month (it will not choose the 1st month, but rather the current month).

Often in Oracle there is method behind the madness, but I do now know why there is this difference.

So I guess this issue is just another interesting footnote in some nerd's blog...

Tuesday, June 21, 2005

Natural vs Synthetic keys

Primary keys should be:
1. Unique
2. Never changed (or very infrequently)

This is because they are used in other tables to reference a single row.

(Definitions from www.orafaq.com)
Natural Key: A key made from existing attributes.
Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.

This topic has been discussed at great length many times. We recently re-hashed it on the Dizwell Forum.

Disadvantages:

EDIT: Please note: There are some potentially important corrections and clarifications to the below. Please see Howard's comments and follow the links to his blog or the forum discussion.

Natural keys:
- May require the concatentation of many columns, so it gets very long
- Sometimes a natural key can be hard to find (in example: names can change)
- In a sense, you are duplicating data
- Can lead to future conflicts when the database expands beyond original requirements
- Care must be taken to avoid block-splitting
- Care must be taken to avoid index range scans
- Tend to change, albeit infrequently

"You nearly always end up having to append a number to guarantee both uniqueness and existence. If I always have to append a guaranteed unique existing number to ensure I meet my requirements, why not make it the key itself."
- Niall Litchfield

Synthetic/Surrogate keys:
- Meaningless by itself (ie. no relationship to the data to which it is assigned)
- Implementation is database-dependent
- Care must be taken to avoid contention (monotically increasing and smaller indexes)
- Similar rows would not have similar keys

Gauss suggested considering using a GUID for the surrogate key. No trigger is required, uniqueness is guaranteed over space and time, but it is 16 bytes.


SQL> CREATE TABLE atable (pk RAW(16) DEFAULT SYS_GUID() PRIMARY KEY,
2 a_str VARCHAR2(32));

Table created.

SQL> INSERT INTO atable (a_str) VALUES ('One');

1 row created.

SQL> INSERT INTO atable (a_str) VALUES ('Two');

1 row created.

SQL> SELECT * FROM atable;

PK A_STR
-------------------------------- --------------------------------
5C3BCF77D55B41E78DE4016DFBE25FFA One
D3B959F3010745D3854F8FC2B09A18F3 Two

Monday, June 20, 2005

Decode

Here is another handy Oracle-only SQL tool: DECODE.

Decode allows you to do if/elseif/else logic within an SQL query, similar to a CASE WHEN statement in a PL/SQL procedure.

DECODE (expression, if_equal_to_this_1, give_me_this_1, else_if_equal_to_this_2, give_me_this_2, ..., default)

Dan Morgan's Reference on DECODE:
http://www.psoug.org/reference/decode_case.html

The default value is optional, if it is left out, it defaults to NULL. And the maximum number of comparisons is 255.

It works very much like a CASE WHEN statement, so you can already think of uses, especially if you nest your DECODEs.

Remember the article I wrote on NULL vs Nothing and on NULLs in general?

http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

Well, you can actually use DECODE like NVL. Don't believe me? I don't blame you, because I told you that NULL is not equal to NULL. But here is a quote right from the Oracle 9i SQL Reference:

"In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null."

And here is the example because like me you don't believe anything without proof:

SQL> CREATE TABLE atable (avalue VARCHAR2(32));

Table created.

SQL> INSERT INTO atable (avalue) VALUES (NULL);

1 row created.

SQL> INSERT INTO atable (avalue) VALUES ('This is not NULL');

1 row created.

SQL> SELECT DECODE(avalue, NULL, 'This is NULL', avalue) avalue
2 FROM atable;

AVALUE
--------------------------------
This is NULL
This is not NULL

If you don't believe how useful it can be, here is a FAQ that shows how to use DECODE for (among other things) avoiding divide-by-zero errors:

http://www.db.cs.ucdavis.edu/public/oracle/faq/decode.html

In practise, I have seen DECODE used for the ORDER BY clause.

If you are still not convinced, you can "Ask Tom" how DECODE can be used to have a conditional foreign key. That is, a foreign key that only applies to certain rows!

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1249800833250

Saturday, June 18, 2005

Connect By

I couldn't resist sharing this one, which I found on DBA-Support recently:

http://www.dbasupport.com/forums/showthread.php?t=47760

The question was how to create a column as the concatentation of many rows into one, without using PL/SQL: just a single query.

Here is the example given:

CREATE TABLE t1 (col1 VARCHAR2(1));
INSERT INTO t1 VALUES ('a');
INSERT INTO t1 VALUES ('b');
INSERT INTO t1 VALUES ('c');
INSERT INTO t1 VALUES ('d');
INSERT INTO t1 VALUES ('e');

Desired result of an SQL statement on test:

abcde

The answer came from Tamil Selvan:

1 SELECT REPLACE(col1, ' ')
2 FROM (SELECT SYS_CONNECT_BY_PATH(col1,' ') col1, level
3 FROM t1
4 START WITH col1 = (select min(col1) from t1) -- 'a'
5 CONNECT BY PRIOR col1 < col1
6 ORDER BY level DESC)
7* WHERE rownum = 1
SQL> /

REPLACE(COL1,'')
----------------------------------------
abcde

Here are the things that may be new to someone new to Oracle (especially Oracle 9 or 10):
- SYS_CONNECT_BY_PATH
- CONNECT BY

This is an interesting example, but it is just one use of these features.

Here is a link to Dan Morgan's reference on CONNECT BY
http://www.psoug.org/reference/connectby.html

And here is an Ask Tom article.
http://www.oracle.com/technology/oramag/oracle/05-may/o35asktom.html

Friday, June 17, 2005

Asking For Help

There are a lot of people who know Oracle. It makes sense to leverage their abilities when you're stuck. Why re-invent the wheel?

First of all, often answers are easy to come by yourself. For example, here is Jeff Hunter's suggestions on where to find answers:

http://marist89.blogspot.com/2005/06/where-can-i-find-help.html

If you want to reach a large body of Oracle professionals, the best approach is to post it to a popular newsgroup or forum. Here are a few of my favourites.

Oracle Technology Network:
http://forums.oracle.com/forums/forum.jsp?forum=75

comp.databases.oracle.server:
http://groups-beta.google.com/group/comp.databases.oracle.server

DBA-Support:
http://www.dbasupport.com/forums/

Dizwell Forum:
http://www.phpbbserver.com/phpbb/viewforum.php?f=2&mforum=dizwellforum

It is tempting to contact certain professionals directly. After all, there are several of them that spend seemingly hours answering questions in these forums, and putting together web pages to assist fellow Oracle professionals.

But be aware that some of them do not have the time (or sometimes the interest) in answering questions, like Mark Rittman or Duncan Mills.

http://www.rittman.net/archives/001276.html
http://www.groundside.com/blog/content/DuncanMills/
2005/06/16/Questions_Questions.html


And be aware that those that do like to receive questions may have a specific method of submitting them, such as Tom Kyte and Steven Feuerstein:

http://asktom.oracle.com
http://www.oracle.com/technology/pub/columns/plsql/index.html

If you do ask someone a question, it sounds like these are the ground rules to follow:

1. Don't even bother submitting urgent questions with time deadlines.
2. Even if English isn't your first language, try to make the question as clear as possible.
3. Try to keep the question brief, but ...
4. Try to provide all necessary information, including examples and error messages
5. Don't ask questions that are obviously school assignments
6. Include "why" you are doing something.

http://tkyte.blogspot.com/2005/05/why.html

But the most important thing is:

Always try to find the answer yourself first!

Loosely Related:
http://vollman.blogspot.com/2005/04/roberts-tips-on-asking-for-help-1.html

Thursday, June 16, 2005

Common Table Column Types

I have two tables which are created in an SQL file, and I want to have a column in common in both.

CREATE TABLE atable (atable_id VARCHAR2(32), atable_value NUMBER);
CREATE TABLE btable (btable_id VARCHAR2(32), btable_name VARCHAR2(32));

However, I don't want to have to rely on manual verification to make sure they are the same type. And if I want to change them for a future version, I'd rather only change it in one place.

I can't do this (abbreviated example):

CREATE TABLE atable (atable_id VARCHAR2(32));
SQL> CREATE TABLE btable (btable_id atable.atable_id%TYPE);
CREATE TABLE btable (btable_id atable.atable_id%TYPE)
*
ERROR at line 1:
ORA-00911: invalid character

And I can't do this:

CREATE OR REPLACE PACKAGE table_types
AS
SUBTYPE table_id IS VARCHAR2(32);
END;

SQL> CREATE TABLE atable (atable_id table_types.table_id);
CREATE TABLE atable (atable_id table_types.table_id)
*
ERROR at line 1:
ORA-00902: invalid datatype

So what should we do?

If there is a logical link between these two fields, we can use referential integrity constraints (thanks "hsheehan"):

SQL>CREATE TABLE atable(atable_id VARCHAR2(32), atable_value NUMBER);

Table created.

SQL>ALTER TABLE atable ADD UNIQUE(atable_id);

Table altered.

SQL>CREATE TABLE btable(btable_id REFERENCES atable(atable_id), btable_name VARCHAR2(32));

Table created.

However, this will require that every atable.atable_id be unique (ORA-02270), and it will also require that every btable.btable_id exists in atable.atable_id (ORA-02291)

This may not always be appropriate, because sometimes there is no such relationships between columns. You may simply want all columns in a database that are somehow similar (example: people's names) to be the same type. You may be doing this for convenience or because you have common functions on these columns.

In that case, you may need to resort to a 3rd-party schema modeller. After all, that's what they're for.

Otherwise, you can create a file of tags, use these tags in a "pre-SQL" file, then write a Perl script to do text substitutions to generate the SQL file. For example:

Tags.txt:
ID_TYPE VARCHAR2(32)
CreateTable.pre:
CREATE TABLE ATABLE (atable_id ID_TYPE, atable_value NUMBER);
CREATE TABLE BTABLE (btable_id ID_TYPE, btable_name VARCHAR2(32));
Execute your Perl script here
CreateTable.sql:
CREATE TABLE ATABLE (atable_id VARCHAR2(32), atable_value NUMBER);
CREATE TABLE BTABLE (btable_id VARCHAR2(32), atable_name VARCHAR2(32));

Thanks to the folks at the Dizwell Forum for helping me think this one through.
http://www.phpbbserver.com/phpbb/viewtopic.php?t=179&mforum=dizwellforum&sid=6802

Here are some loosely related previous blogs on this:
http://thinkoracle.blogspot.com/2005/05/dynamically-assigning-size-of-varchar2.html
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html

Wednesday, June 15, 2005

Variable Constraints

You can't use variables in constraints.

CREATE OR REPLACE PACKAGE test_constants IS
test_val CONSTANT NUMBER := 20;
END test_constants;

CREATE TABLE atable (
a_id NUMBER,
CONSTRAINT atable_test CHECK (a_id > test_constants.test_val)
);

ORA-00904: "TEST_CONSTANTS"."TEST_VAL": invalid identifier

Check Constraints can NOT contain subqueries, so forget about doing it that way.

It is basically only for comparisons and simple operations.

How about this convoluted way of achieving the goal. Create a trigger that will check the value upon insert or update, and then trigger a special constraint.

SQL> CREATE TABLE atable (
2 a_id NUMBER,
3 a_error NUMBER,
4 CONSTRAINT id_too_high CHECK (a_error <> 1)
5 );

Table created.

SQL> CREATE OR REPLACE TRIGGER atable_trig
2 BEFORE INSERT OR UPDATE OF a_id ON atable FOR EACH ROW
3 BEGIN
4 IF :new.a_id > test_constants.test_val THEN :new.a_error := 1;
5 END IF;
6 END;
7 /

Trigger created.

SQL> INSERT INTO atable (a_id) VALUES (1);

1 row created.

SQL> INSERT INTO atable (a_id) VALUES (21);
INSERT INTO atable (a_id) VALUES (21)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.ID_TOO_HIGH) violated

Check the comments to see if someone has a better way.

Related links:
http://thinkoracle.blogspot.com/2005/05/enum-in-oracle.html

Tuesday, June 14, 2005

Bind Variables in PL/SQL

If you learn nothing else from the Oracle experts out there but want to improve your Oracle application development knowledge, then read up on bind variables.

Bind variables are already very well explained, here are my favourite links on the subject:

Mark Rittman's Bind Variables Explained
http://www.rittmanmead.com/2004/03/24/bind-variables-explained/

Tom Kyte is one of the champions of using Bind Variables. Links to his articles and books can be found at the bottom of Mark's article.

After reading these articles, I understood that PL/SQL automatically binds variables, so you really don't have to worry. But I was still concerned that I might be missing something.

Tom has a query that you can run to determine if you are currently using bind variables or not. Find it here (Hint: select sql_test from v$sqlarea):
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1163635055580.

I posted the following question to the Dizwell forum, which is an excellent place to tap the minds of Oracle gurus directly.

http://www.phpbbserver.com/phpbb/viewtopic.php?t=169&mforum=dizwellforum
Note: no longer available

Is there any possible way to NOT use bind variables in a PL/SQL stored procedure WITHOUT using 'execute immediate' or DBMS_SQL package?

I just want to make sure that there are only 2 situations I have to look out for in my PL/SQL code with regards to bind variables.

A developer from Australia going by "gamyers" responded with ref cursors:

DECLARE
v_cur sys_refcursor;
BEGIN
OPEN v_cur FOR 'select 1 from dual where 1=2';
CLOSE v_cur;
END;

Also, you could code
IF v_value =1 then
select ... where col_a = 1;
ELSIF v_value = 2 then
select ... where col_a = 2;
....

But with that sort of code you are probably dealing with a very small set of SQL statements and are specifically wanting different plans etc.


And the champion himself, Tom Kyte, had this reply and defined some new terms: "Overbind" and "Underbind." Using these terms I was asking whether it was possible to "Underbind" using PL/SQL.

The only way to improperly bind in PL/SQL is when you use dynamic sql.

Else, you can "overbind" -- bind when you didn't need to, but you cannot "underbind", not binding when you should!

eg:

for x in ( select * from all_objects where object_type = 'TABLE' )
loop

is perfectly ok, you do NOT need to:

declare
l_otype varchar2(25) := 'TABLE';
begin
for x in ( select * from all_objects where object_type = l_otype )
loop

You do not need to bind 'TABLE' in that query since no matter how many times you run that query, it'll be 'TABLE' over and over.

But with static SQL, it's not possible to "underbind"


As an aside, visit Jeff Hunter's Oracle blog, which is fast becoming one of my favourites. Here is his recent post on where Oracle DBAs can get help:
http://marist89.blogspot.com/2005/06/where-can-i-find-help.html

Monday, June 13, 2005

Blank Lines and SQLPlus

Try creating the following table using SQLPlus Worksheet.

create table atable (

aint integer

);

You will get this error:

SP2-0734: unknown command beginning "aint integ..." - rest of line ignored.

It thinks "aint integer" is a new command. What is the problem? Repeat this test using SQLPlus at a prompt:

SQL> create table atable
2 (
3
SQL>

The blank line stops the statement.

This doesn't apply to all statements, but it does apply to select/insert/update/delete queries. It doesn't apply to creating procedures:

SQL> CREATE OR REPLACE PROCEDURE MyProc
2
3 AS
4
5 BEGIN
6
7 NULL;
8
9 END;
10 /

Procedure created.

At first I just made the blank lines a comment:

create table atable (
--
aint integer
--
);

Table created.

But eventually I took a closer look and arrived at the correct solution. Here it is.

set sqlblanklines on;

SQL> create table atable
2 (
3
4 aint integer
5
6 );

Table created.

Its just a little SQLPlus foible.

If you see something similar and you aren't using blank lines, see if you're using any special symbols and look at "set sqlprefix" instead.

So what should we do?
1. Put in comments to avoid the blank lines.
2. Use something other than sqlplus.
3. Explicitly set blanklines on at the top of your statement.

Friday, June 10, 2005

NULLs in Oracle

If you read only one sentence per posting, read this:

NULLs may not behave as you'd expect in Oracle, and as a result, NULLs are the cause of many application errors.

I got a fair bit of private feedback on my recent article on the differences between "NULL" and nothing. Here is the link to the original article, and check out Howard Rogers' comments.

http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html

Let's start with an example to demonstrate a simple property of NULL: that NULL is not equal to anything, including NULL itself.

SELECT * FROM dual WHERE NULL = NULL;

No rows selected.

SELECT * FROM dual WHERE NULL <> NULL;

No rows selected.

What happened here? How can something be both not equal and not not equal to something else at the same time? That probably doesn't make much sense, does it?

Essentially NULL means you don't know the value. Basically, you can't say whether its equal or not equal to anything else. You need to abandon the binary logic you learned in first-year math and embrace tri-value logic.

That was also a clear demonstration of how you should be careful using equals or not equals when dealing with NULLs. Instead, use "IS". Observe:

SELECT * FROM dual WHERE NULL IS NULL;

D
-
X

As an aside, you may be wondering what the heck "dual" is. And what do we do when we have a question? We ask Tom!

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1562813956388

In my mind, dual is simply a table with a single row that is guaranteed to be there. And when you use it, it makes you look clever because that's what the experts use. :)

While I'm blowing your mind with the behaviour of NULL, check out this other case, kindly provided by Tom Kyte:

IF (x = 'A') THEN something
ELSE something else
END IF;

IF NOT(x = 'A') THEN something else
ELSE something
END IF;

Do these two pieces of pseudo-code look the same to you? In many languages, yes. But not in PL/SQL. Why?

Consider the case that x is NULL. In the first case it will do "something else" because it is not equal to 'A'. In the second case it will do "something" because it is also not not equal to 'A'.

Tom knows I never believe anything without proof, so he provided me with one:

ops$tkyte@ORA9IR2> declare
2 x varchar2(1);
3 begin
4 if ( x = 'A' )
5 then
6 dbms_output.put_line( 'X=A' );
7 else
8 dbms_output.put_line( 'NOT X=A' );
9 end if;
10
11 if NOT( x = 'A' )
12 then
13 dbms_output.put_line( 'NOT X=A' );
14 else
15 dbms_output.put_line( 'X=A' );
16 end if;
17 end;
18 /
NOT X=A
X=A

PL/SQL procedure successfully completed.

Pretty bizarre, eh? Starting to understand the behaviour of NULL? Starting to see why misunderstanding NULL can lead to application errors?

While we are discussing NULLs, here a few more useful things.

First, you can use the "set null" command to change how NULL will appear in SQLPLUS. This will not change anything in the database, or equality, it will just change the appearance.

create table atable (last_name varchar(12));
insert into atable (last_name) values ('Smith');
insert into atable (last_name) values (NULL);

select * from atable;

LAST_NAME
------------
Smith


2 rows selected.

set null [NULL];

select * from atable;

LAST_NAME
------------
Smith
[NULL]

2 rows selected.

Also, you can use these two functions, nvl and nvl2:

nvl(expr_1, expr_2)
Returns expr_2 if expr_1 is null and expr_1 otherwise.

nvl2(expr_1, expr_2, expr_3)
Returns expr_3 if expr_1 is null and expr_2 otherwise.

select nvl(last_name, '[NONE]') as last_name from atable;
LAST_NAME
------------
Smith
[NONE]

2 rows selected.

select nvl2(last_name, 'Y: ' || last_name,'N') as last_name from atable;

LAST_NAME
---------------
Y: Smith
N

2 rows selected.

Those of you who, like me, work on many different databases have hopefully seen by now that NULLs are handled differently in Oracle. As a final demonstration, consider how NULLs are handled in Sybase ASE.

In Sybase ASE (and MS SQL), there is a configuration parameter that tells the database whether to treat NULLs as they are defined in the ANSI SQL standard, or whether to "bend the rules" a bit, and allow "NULL = NULL" to be true. Furthermore, the behaviour changes depending on whether you're in a join clause or a search clause.

http://sybasease.blogspot.com/2005/05/nulls-in-sybase-ase.html

That is not the case in Oracle. As confusing as NULLs may be at first, they are consistent. They will behave the same logically everywhere no matter how and where you use them.

So what should we do?
1. Understand that "NULL = NULL" and "NULL <> NULL" are both false because NULL means "I don't know"
2. Use "IS NULL" instead of "= NULL" if you are checking for NULLness.
3. Use "nvl" and "nvl2" if you want to eliminate the possibility of a NULL in a statement by assigning it a (temporary) value.

For future reference, consult Dan Morgan's page on NULL:
http://www.psoug.org/reference/null.html

This is definitely not the last article you'll see on NULL given how often it is misunderstood. I encourage you to include your own experiences of your favourite NULLisms in the "comments" section. And, naturally, I appreciate corrections and clarifications.

And as a final thanks to Tom, here are some pictures from when he visited us in Calgary this past March.
http://www.coug.ab.ca/events/05-mar.htm

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