"FC" <flavio_at_tin.it> wrote in message
news:gJ%Ia.122178$pR3.2727535_at_news1.tin.it...
>
> 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 Sun Jun 22 2003 - 01:27:17 CDT
