Re: Remove record padding/whitespace after import

From: Jeff Hunter <jeffh_at_btitelecom.net>
Date: Tue, 12 Jan 1999 10:56:44 -0800
Message-ID: <77fran$752$1@defiant.btitelecom.net>

You could write an "ON INSERT" trigger that changes the :new value to a value that is trimmed DURING the import. This field must, however, be defined as a varchar2.

SQL> create table jh1 (

  2  keyval number(5) constraint pk_jh1 primary key,
  3  charval1 char(20),
  4  charval2 char(20),

  5 charval3 varchar2(20),
  6 charval4 varchar2(20))
  7 /

Table created.

SQL> create or replace trigger jh1_tr1

  2     before insert on jh1
  3     referencing new as new for each row
  4  declare
  5     tmpstr1 varchar2(20);

  6 begin
  7

  7     tmpstr1 := rtrim(:new.charval2);
  8     :new.charval2 := tmpstr1;
  9
  9     tmpstr1 := rtrim(:new.charval3);
 10     :new.charval3 := tmpstr1;

 11
 11 end;
 12 /

Trigger created.

SQL> insert into jh1 (keyval, charval1, charval2, charval3, charval4)   2 values (1,'Scooby ','Scooby ','Scooby ','Scooby ')
  3 /

1 row created.

SQL> select keyval, '|' || charval1 || '|' charval1,
  2  '|' || charval2 || '|' charval2,
  3  '|' || charval3 || '|' charval3,
  4  '|' || charval4 || '|' charval4

  5 from jh1
  6 /

   KEYVAL CHARVAL1 CHARVAL2 CHARVAL3 CHARVAL4

--------- ---------------------- ---------------------- --------------------
-- ----------------------
        1 |Scooby              | |Scooby              | |Scooby|
|Scooby         |

SQL> drop table jh1
  2 /

Table dropped.

Jurgen Fieouren wrote in message <369b1b15.0_at_194.65.24.2>...
>I've recently used an application to export directly to an Oracle 8 table
by
>means of an append query and ODBC. Unfortunately, I cannot prevent the
>records from being padded with spaces to their extents. How can I remove
>this waste? Many thanks for any advice . . .
>
>
>
Received on Tue Jan 12 1999 - 12:56:44 CST