Spooling CLOB via SQL Plus

[Thargy]

Hi,

I am trying to create a SQL Plus script which will create a data loading script for a table containing clob data. The idea is that users can edit the clob field, and I can then run my generation script to produce a script which will run in SQL plus.

The table is created as follows:-

CREATE TABLE FLAT_FILE_STORE
(
  FLAT_FILE_STORE_ID  NUMBER(38),
  FILE_NAME           VARCHAR2(12 BYTE) CONSTRAINT NN_FLAT_FILE_STORE_FILE_NAME NOT NULL,
  FILE_DATA           CLOB,
  DEPARTMENT_ID       NUMBER(10)
);

In order to load clob data into flat_file_store tables in other databases, I'm attempting to produce a script which takes 2,000 characters of each clob at a time. This is to avoid the 2,499/4,000 character limits in SQL plus. (I figure that by restricting myself to 2,000 characters, I should be safely below these limits.)

My script is as follows:-

SET ECHO OFF
SET FEEDBACK OFF
SET pagesize 0
SET LONG 32000
SET heading OFF
--set linesize 3500
SET serveroutput ON

COLUMN dml_string format a200
spool flat_file_store_SD.SQL

SELECT 'INSERT INTO FLAT_FILE_STORE ( FLAT_FILE_STORE_ID, FILE_NAME, FILE_DATA, DEPARTMENT_ID) VALUES ('
       ||flat_file_store_id||','''||file_name||''','''
	   ||file_data||''','||department_id||');' dml_string
  FROM FLAT_FILE_STORE 
 WHERE LENGTH(file_data) <= 2000;

SELECT 'INSERT INTO FLAT_FILE_STORE ( FLAT_FILE_STORE_ID, FILE_NAME, FILE_DATA, DEPARTMENT_ID) VALUES ('
       ||flat_file_store_id||','''||file_name||''','''
	   ||file_data||''','||department_id||');' dml_string
  FROM FLAT_FILE_STORE 
 WHERE LENGTH(file_data) > 2000;

SELECT 'UPDATE FLAT_FILE_STORE SET FILE_DATA=FILE_DATA||'||SUBSTR(FILE_DATA,2001,2000)
   || ' WHERE FILE_NAME = '''|| FILE_NAME||''' AND DEPARTMENT_ID = '||department_id||';' dml_string
  FROM FLAT_FILE_STORE
 WHERE LENGTH(FILE_DATA) > 2000;

SELECT 'UPDATE FLAT_FILE_STORE SET FILE_DATA=FILE_DATA||'||SUBSTR(FILE_DATA,4001,2000)
   || ' WHERE FILE_NAME = '''|| FILE_NAME||''' AND DEPARTMENT_ID = '||department_id||';' dml_string
  FROM FLAT_FILE_STORE
 WHERE LENGTH(FILE_DATA) > 4000;

SELECT 'UPDATE FLAT_FILE_STORE SET FILE_DATA=FILE_DATA||'||SUBSTR(FILE_DATA,6001,2000)
   || ' WHERE FILE_NAME = '''|| FILE_NAME||''' AND DEPARTMENT_ID = '||department_id||';' dml_string
  FROM FLAT_FILE_STORE
 WHERE LENGTH(FILE_DATA) > 6000;

SELECT 'UPDATE FLAT_FILE_STORE SET FILE_DATA=FILE_DATA||'||SUBSTR(FILE_DATA,8001,2000)
   || ' WHERE FILE_NAME = '''|| FILE_NAME||''' AND DEPARTMENT_ID = '||department_id||';' dml_string
  FROM FLAT_FILE_STORE
 WHERE LENGTH(FILE_DATA) > 8000;

SPOOL OFF;

This is quite naturally producing a huge output file, which I won't post here. However, the first entry in it is

INSERT INTO FLAT_FILE_STORE ( FLAT_FILE_STORE_ID, FILE_NAME, FILE_DATA, DEPARTME
NT_ID) VALUES (888,'bbsetup','                                                  
                                                                                
                                    99999999991388888895188888892413888896251888
89248813889628851889242424139626262519111111119999999999138888889518888889241388
88962518888924881388962885188924242413962626251911111111999999999913242424931424
242988138824988318842988881324988883142988888813988888831911111111          1 2 
2 2  1 2 2 2  8 1 8 2  8 1 8 2  8 8 1 2  8 8 1 2  8 8 8 1  8 8 8 1              
      8        1        8        1        8        1        8        1          
                              ',4);

When I attempt to run this in sql plus, it errors, saying that there is a missing comma. If I manually edit DEPARTME
NT_ID) to be DEPARTMENT_ID) it runs fine.

I realise that this is something to do with word wrapping, but I can no longer see the wood for the trees.

Can someone enlighten me as to my mistake in sql plus?

Regards

Tharg

Grinding away at things Oracular

 

[hoinz]

My guess is that increasing linesize should help.
So I cannot understand this line in your script:
--set linesize 3500
Why did you comment it out?
Or am I missing something?

 

[Thargy]

hoinz,

after 2.5 hours of banging me head against it, I finally found the solution.

The line length of 3,500 was commented out, as it was a hang over from previous attempts to sort out the problem.

Cutting a long story short, the problem was due to the fact that the 'SET LONG 32000' statement was being overridden, and reduced to 80 characters.

The Oracle doco states

A LONG, CLOB, NCLOB or XMLType column's width defaults to the value of SET LONGCHUNKSIZE or SET LONG, [i]whichever one is smaller[/i].

As my italics show, I was setting a big value of long right enough, the the longchunksize was at the default of 80, hence the unwanted wrap. This is due to the differing behaviour when handling CLOBs.

The beastie is now behaving itself.

Regards

Tharg

Grinding away at things Oracular