Re: blank fields in Oracle

Further to my last message, I just want to add a few examples of what happens with CHAR.

CREATE TABLE test_tbl (
 var_c1 VARCHAR2(10) NOT NULL,
 char_c2 CHAR(10) DEFAULT ' ' NOT NULL);

As you see, I defined I single blank as default value, however if the length of the column is 10, Oracle will always store this string as 10 spaces, automatically padding 9 more spaces.

INSERT INTO test_tbl

       (var_c1)
VALUES ('A'); COMMIT; SELECT VAR_C1, REPLACE(CHAR_C2, CHR(32), 'X') FROM test_tbl;

result: A XXXXXXXXXX

If you define the columns as NOT NULL, as I said in the previous message, you may get errors with zero length strings:

try this:

INSERT INTO test_tbl

     (var_c1, char_c2)
VALUES('B', TRIM(' ')); result: ORA-01400: cannot insert NULL into ("TEST"."TEST_TBL"."CHAR_C2")

The same happens if the column is VARCHAR2.

So, again, if you want to be 100% sure that you are not trying to store NULLs resulting in run-time errors, then you must trap the situation in a "before trigger", where you can replace the null value with a blank. Probably this is easier to implement and gives more consistent results than reviewing all the code and change it to convert null strings back and forth programmatically.

Since you are also experimenting performance problems, then you might want to try with different options and see which one is the most suitable for you specific case.

Bye,
Flavio Received on Sat Jun 21 2003 - 11:29:00 CDT