You can check the column length for the field you are about to insert into...
/* Get storage space available for generic sample data */
CURSOR sd_colInfo_c
IS
SELECT DATA_LENGTH
FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME LIKE 'VALUE'
AND TABLE_NAME LIKE 'SAMPLE_DATA';
.
.
.
/* Get storage space available for generic sample data */
OPEN sd_ColInfo_c;
FETCH sd_ColInfo_c INTO l_collength;
IF sd_ColInfo_c%notfound THEN
l_Collength := 4000; -- set the default to 4000 bytes
END IF;
CLOSE sd_ColInfo_c;
.
.
.
/* Insert record into sample_data */
l_length := LENGTH( p_i_value);
LOOP
/* Increment sequence number */
l_sequence := l_sequence + 1;
/* Get the first pace of data */
IF (l_length > L_COLLENGTH ) THEN
/* Start at previouse position */
l_value := SUBSTR(p_i_value,l_position, 4000);
/* Rest position */
l_position := l_position + L_COLLENGTH;
/* Reset length */
l_length := l_length - L_COLLENGTH;
ELSE
l_value := SUBSTR(p_i_value,l_position,L_COLLENGTH);
END IF;
/* Insert the record */
INSERT
INTO sample_data
(
SAMPLE_DATA_ID
,ATTRIBUTE_ID
,SEQUENCE
,VALUE
,LENGTH
)
VALUES (
p_i_sample_data_id
,NVL(p_i_attribute_id ,l_attrib_id)
,l_sequence
,l_value
,p_i_LENGTH
);
/* Break out of loop if length is creater than 4000 char */
EXIT WHEN l_length <= 4000;
.
.
.
Paul in VT
Received on Wed Sep 01 1999 - 11:17:30 CDT
