1

Topic: CLOB-text partition for the lines in table fields

I start to program. The Oracle version 10g. Decided to write the program which will take the text from one field of one table and to scatter it in the lines in fields of other table.
Here only somewhere an error... All time only 2nd line of the text Is deduced. Help, please
I take the data from the table Source , in it 2 columns - ID , Content . In the last field of a column Content record:
Content
I
I not
I am not able
I am not able to write
I am not able to write on
I am not able to write on PL\SQL

I scatter in the table Source_out .
The code:

DECLARE
c clob; - a variable for reading of characters
m integer; - length of record of field Source. Content
f integer: = 1; - an amount of lines of record in the field Source. Content
l integer; -  for count of line end characters "chr (10)"
n integer; - dimensionality of array MY_LENGTH
k INTEGER: = 1; - a variable for reading in the lines
TYPE is_length IS TABLE OF INTEGER
INDEX BY BINARY_INTEGER;
MY_LENGTH is_length;
TYPE is_source IS TABLE OF SOURCE%ROWTYPE
INDEX BY BINARY_INTEGER;
MY_SOUR is_source;
BEGIN
-- Count of an amount of lines
SELECT ID, CONTENT, 1 INTO MY_SOUR (1)
FROM SOURCE
WHERE ID = (select count (*) from source);
m: = LENGTH (MY_SOUR (1).CONTENT);
for i in 1. m loop
c: = substr (MY_SOUR (1).CONTENT, i, 1);
if c = chr (10) then
f: = f+1;
end if;
end loop;
-- Partition on fields in label SOURCE_OUT
FOR i IN 1. f LOOP
select instr (MY_SOUR (1).CONTENT, chr (10), 1, i) into l
from dual;
IF L> 0 THEN
MY_LENGTH (i): =l;
END IF;
END LOOP;
MY_LENGTH (f): = m;
n: = MY_LENGTH.COUNT;
FOR j IN 1. n LOOP
INSERT INTO SOURCE_OUT
VALUES (j, SUBSTR (MY_SOUR (1).CONTENT, k, MY_LENGTH (j)-k+1));
k: = MY_LENGTH (j);
END LOOP;
-- Removal of superfluous line end characters
for i in 2. f loop
update source_out
set content = (select ltrim (content, chr (10))
from source_out
where id = i);
end loop;
END;

2

Re: CLOB-text partition for the lines in table fields

davidoffpk, 5/3/2010, 20:10, post2147133 wrote:

to take the text from one field of one table and to scatter it in the lines in fields of other table.

create table src (id primary key, val)
as select 1, to_clob (' I
I not
I am not able
I am not able to write
I am not able to write on
I am not able to write on PL\SQL
') from dual
union all select 2, to_clob (' it is time
It is time 
For a long time it is time
Aha ')
from dual;
Table created
create table dst (src_id number
,line_num number
,line varchar2 (4000)
,primary key (src_id, line_num)
,foreign key (src_id) references src (id)
);
Table created
declare
start_pos number;
next_pos number;
lob_len number;
line dst.line%type;
line_num number;
itt number;
begin
for i in (select * from src)
loop
lob_len: = nvl (dbms_lob.getlength (i.val), 0);
start_pos: = 1;
line_num: = 0;
while start_pos <lob_len
loop
itt: = 1;
next_pos: = nvl (dbms_lob.instr (i.val, chr (10), start_pos), 0);
if next_pos <1
then
next_pos: = start_pos + greatest (0, lob_len - start_pos + 1);
end if;
if next_pos - start_pos> = 4000
then
next_pos: = start_pos + 4000;
itt: = 0;
end if;
exit when next_pos <= 0;
line_num: = line_num + 1;
line: = dbms_lob.substr (lob_loc => i.val
,amount => next_pos - start_pos
,offset => start_pos);
insert into dst values (i.id, line_num, line);
start_pos: = next_pos + itt;
end loop;
end loop;
end;
/
PL/SQL procedure successfully completed
select * from dst;
SRC_ID LINE_NUM LINE
---------- ---------- --------------------------------------------------------------------------------
1 1 I
1 2 I not
1 3 I am not able
1 4 I am not able to write
1 5 I am not able to write on
1 6 I am not able to write on PL\SQL
2 1 it is time
2 2 it is time 
2 3 for a long time it is time
2 4 aha
10 rows selected
davidoffpk, 5/3/2010, 20:10, post2147133 wrote:

Here only somewhere an error... All time only 2nd line of the text Is deduced. Help, please

In your initial text there is no line which would not cause , therefore error instructions .
In first three cycles - excess.
In the second does not follow  in pl/sq calls like "slect instr (var, val) into result from dual". SQL and PL/SQL it different engines and the majority of functions SQL have a prototype in PL/SQL
In the third with LOB work as with LOB instead of with implicitly transformed in varchar values
In the fifth. Here it killed :

WHERE ID = (select count (*) from source);

3

Re: CLOB-text partition for the lines in table fields

Many thanks! I of it yet did not know all. PL\SQL I only start to study, and programs are necessary already "today".