Re: How would you get first 2000 chars of a Long field?

Sean Dolan wrote:

> We are having some BIG problems doing an import of data into our Oracle
> tables. The problem is that a field in our flat text file could be > 2000
> so we've decided to put the data into a temporary table where the field is
> a LONG column. Now we'd like to have a "good" table with the first 2000
> characters of the LONG field... how would one do this?
> We have tried SQL/Plus in terms of spooling it to a file, but the problem
> is that we get LOTS of EXTRA spaces in the data output.
>
> select recNumber, '|', action from testTable;
>
> It's trying to fill up a 500 character line (set in Environment settings)
> so it fills the empty space after the recNumber and the | with spaces.
> Then on the next line it outputs the action (2000 characters)... if we
> could just get rid of the extra spaces, we could (laboriously) use SQL
> Loader again to input the "cleaned-up" flat file.
>

In SQL*Plus you can set various parameters for output control.

Try this one:

set feedback off
set heading off
set termout off
set trimspool on
set linesize 2048

before the query.
You may also set the linesize longer, but some parameters depend on your Operating System. Have a look at your SQL*Plus-docu! Another way is using PL/SQL.
In Forms you can define varchar2 longer than 2000:

DECLARE
   str varchar2(10000);
   num NUMBER;
   cursor c1 is
     select recNumber, action from testTable; BEGIN
  open c1;
  LOOP
    EXIT WHEN c1%notfound;
    fetch c1 into num, str;
    insert into xyz values (num, substr(str, 1, 2000));   END LOOP;
  close c1;
  COMMIT;
END; I didn't actually tested zhis, but it should work. But the best thing you can do is upgrading to ORACLE 8.0. It is now able to handle multiple long-rows per table and varchar2 up to 2 GB or so (maybe I'm wrong, but it is able to handle longer varchar2 fields, called c_blob (character binary large object))

Marcel Received on Thu May 14 1998 - 17:28:04 CEST