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:
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 .
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;