Thursday, April 4, 2013

Fun with CHAR

I'm busy deriving file layouts from PL/SQL. Probably close to 100 file definitions...each of them slightly different, each of them defined in the code. Fun!

There are a mixture of types too, fixed width, csv, etc. Thankfully, I've read enough of the code now that it's relatively easy to figure out. The fixed width variety is what this is about though.

In much of the code, there's a type that's defined, something like this:
type my_record is record
(
  column_01 CHAR(10),
  column_02 CHAR(10),
  column_03 CHAR(10)
);
That's then used to receive assignments from incoming variables. I'll hardcode my variables for this exercise.
declare
  type my_record is record
  (
    column_01 CHAR(10),
    column_02 CHAR(10),
    column_03 CHAR(10)
  );
  l_rec my_record;
begin
  l_rec.column_01 := '1';
  l_rec.column_02 := '3';
  l_rec.column_03 := '6';
end;
Littered throughout those assignments though, are things like LPAD and RPAD. You're going to say, "well, yeah, if it's a number, you may want it right aligned or something." Fair enough. But I'm not talking about those, I'm talking about this:
  l_rec.column_01 := rpad( ' ', 10 );
  l_rec.column_02 := '3';
  l_rec.column_03 := RPAD( ' ', 10 );
Ostensibly, these columns once held data. Instead of forcing the client (application, business, whatever) to change their processing bit, the file was left the same. Makes sense.

Then I started to think about it...it's a CHAR. CHAR is already fixed width. To wit:
drop table t purge;

create table t
(
  x CHAR(10)
);

insert into t ( x ) values ( ' ' );
insert into t ( x ) values ( null );

select 
  rownum, 
  length( x ), 
  x
from t;

    ROWNUM  LENGTH(X) X        
---------- ---------- ----------
         1         10            
         2                       
I inserted a single space in the first record. It has a length of 10 despite only inserting a single character there.

So what's the purpose of those RPAD( ' ', 10 ) calls? I'm not sure.

The only reason I even began to think about it was that I ran across one type set up with VARCHAR data types. There it makes sense, using RPAD I mean. With the CHAR field, it's a waste of typing IMO. Perhaps it was just for readability...who knows?

2 comments:

Jeffrey Kemp said...

Probably cargo-cult code by someone who doesn't understand how CHAR behaves; or, maybe the table definition changed at some point from VARCHAR2 to CHAR.

oraclenerd said...

heh...I had never heard the term cargo-cult programming before.

Sounds like a very plausible explanation.