Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: blank fields in Oracle-testing only

Re: blank fields in Oracle-testing only

From: blackgold <blackgold_at_yahoo.com>
Date: Sun, 22 Jun 2003 14:27:17 +0800
Message-ID: <bd3ha5$sa8$1@reader01.singnet.com.sg>

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US