| ORA: 06512 Numeric or Value error [message #372041] |
Fri, 05 January 2001 11:04  |
Deanna
Messages: 9
Registered: December 2000
|
Junior Member |
|
|
I have a function which has two parameters : varchar2 field and a numeric field.
The function is called by a sql view.
The pupose of the function is to format a text field so that it has spaces between a set number of characters and prints only 87 characters per line.
Example:
parameter1 = aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeee
parameter 2 = 10
Then the function will format the sequence as follows
aaaaaaaaa bbbbbbbbbb cccccccccc etc...
printing only 87 characters per line then a return.
The function runs fine, but when it is called by the view I receive the ORA error
Seems to reference this line
v_crw_final := v_crw_final || chr (13) || v_crw_holder
I have replaced the chr(13) with just a test field 'RETURN', but I still get the same error
the code for the function is below
function format_sequence
(sequence_in in varchar2,
format_size in number)
RETURN varchar2 is
v_final_seq varchar2(1500);
v_init_seq varchar2(1500);
v_line_seq varchar2(100);
v_out_seq varchar2(1500);
v_work_seq varchar2(100);
v_count INTEGER (10);
v_start number(10);
v_length number(10);
v_crw_seq varchar2 (2500);
v_crw_start number (10);
v_crw_end number (15);
v_crw_holder VARCHAR2 (2500);
v_crw_final VARCHAR2 (2500);
v_crw_holder2 VARCHAR2 (1500);
BEGIN
v_final_seq := '0';
v_out_seq := '0';
v_line_seq := '0';
v_init_seq := '0';
v_work_seq := '0';
v_count := 1;
v_start:= 1;
v_length:= 0;
v_crw_end := 87;
--
-- trim spaces after and before sequence, then replace all spaces within sequence w/ ''
--
v_init_seq := replace(ltrim(rtrim(sequence_in, ' '),' '),' ','');
--
-- get a chunk of text at the variable entered, concatenate with a space and loop through concatenating
LOOP
v_work_seq := substr(v_init_seq, v_start,format_size);
exit when v_work_seq is null;
if length(v_work_seq) < format_size then
v_out_seq := v_out_seq || v_work_seq;
v_start := v_start + format_size;
else
if v_out_seq <> '0' then
v_out_seq := v_out_seq || v_work_seq || ' ';
else
v_out_seq := v_work_seq || ' ';
end if;
v_start := v_start + format_size;
end if;
end loop;
v_final_seq:=ltrim(rtrim(v_out_seq, ' '),' ');
v_count:= ceil(length(v_final_seq)/87 );
v_crw_end := 87;
v_crw_holder := 'I';
v_crw_final := 'I';
v_crw_start := 1;
FOR i IN 1 .. v_count
LOOP
IF v_crw_final = 'I' THEN
v_crw_holder := substr(v_final_seq, v_crw_start, v_crw_end);
v_crw_final := v_crw_holder;
ELSE
v_crw_holder := substr(v_final_seq, v_crw_start, v_crw_end);
v_crw_final := v_crw_final || chr (13) || v_crw_holder;
END IF;
v_crw_start := 87 + v_crw_start;
v_crw_end := 87 + v_crw_end;
END LOOP;
RETURN v_crw_final;
END;
The view syntax calls the function as follows:
format_sequence (a.probe_sequence,10) probe_sequence
Any ideas
Thanks in advance
Dee
|
|
|
|
| Re: ORA: 06512 Numeric or Value error [message #372042 is a reply to message #372041] |
Fri, 05 January 2001 12:04  |
spdevalla
Messages: 7
Registered: December 2000
Location: Rhode Island
|
Junior Member |
|
|
v_crw_final := v_crw_final || chr (13) || v_crw_holder
In the above what is chr(13)? Chr(13) should be a string or you should convert any numeric value into character before concatenation.
EX. to_char(chr(13)) try this way
|
|
|
|
| Re: ORA: 06512 Numeric or Value error [message #372043 is a reply to message #372041] |
Fri, 05 January 2001 12:14 |
Deanna
Messages: 9
Registered: December 2000
|
Junior Member |
|
|
Thanks for responding :)
chr(13) is the value for a return.(so that the program will do a carriage return and print the next line)
Even when I replace ch(13) with a sting like "RETURN' or 'DEE' I still
receive the error.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
