|
|
|
|
|
|
|
|
| Re: ".." Character coming instead of "." while creating an excel spreadsheet from Oracle 10g [message #623611 is a reply to message #597049] |
Thu, 11 September 2014 17:45  |
 |
djbjr70
Messages: 1
Registered: September 2014
Location: Las Vegas
|
Junior Member |
|
|
I started running into this problem and have searched high and low for a solution and this is the only forum thread where I see the same problem.
I know this thread is a little old but thought I would share my solution since this is the only other mention of it I could find....
So I finally figured out that the decimal was being repeated every time there was a chunk being written to a file that started with a decimal. The example above stated that "l_step" was set to 24573. That means every time the next position was a decimal, it would be repeated.
I rewrote the loop to check for this occurrence and adjusted the "l_step" every time it was found.
Variables Added:
l_step_default -- Currently set to 12000
l_step_max -- Set to 24573. This is to ensure we don't go over the max allowed
p_check -- BOOLEAN set to TRUE, value set to false when confirmed that the next character is no '.'
v_length -- Set to the length of the CLOB because the for i.. loop won't work here
v_parsed -- Placeholder to keep track of how many characters we have loaded.
Code Change Summary
So before that for loop opens, I check the length of the clob and place in v_length.
Then instead of the for loop I created a WHILE loop checking that the length of the CLOB is < what has been parsed already.
Then inside the loop I check if the next set to be appended to the file will start with a decimal. If this returns true, I increment the l_step by 3 until the next chunk will not start with a decimal.
I then reset the BOOLEAN and add the current l_step to the parsed variable so it will exit when complete.
The only downside here would be if every 3rd character was a decimal for 12573 characters. If that happens to be the case, I just exit out of the loop and allow the double decimal. I can't imagine a case where that would happen though.
Hope this helps, here is my code:
PROCEDURE send_mail ( p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 ,
p_attach_name IN VARCHAR2,
p_attach_clob IN CLOB)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
v_length NUMBER;
v_parsed NUMBER := 0;
l_step PLS_INTEGER := 12000;
l_step_default PLS_INTEGER := 12000;
l_step_max PLS_INTEGER := 24573;
p_check BOOLEAN :=TRUE;
CURSOR cur_to IS
WITH T AS (SELECT p_to col1 from dual)
SELECT
REGEXP_SUBSTR(col1,'[[:alnum:]]+[@]+[[:alnum:]]+[.]+[[:alnum:]]+',1,lvl) email
FROM
(
SELECT col1,level lvl
FROM
t CONNECT BY level <= LENGTH(col1) - LENGTH(REPLACE(col1,',')) + 1
);
BEGIN
--REMOVED CODE NOT RELEVANT--
v_length := DBMS_LOB.getlength(p_attach_clob);
WHILE v_length > v_parsed LOOP
l_step :=l_step_default;
WHILE p_check LOOP
IF
DBMS_LOB.substr(p_attach_clob, 1, v_parsed + l_step + 1) = '.'
THEN
l_step := l_step + 3;
ELSE
p_check := FALSE;
END IF;
IF l_step = l_step_max
THEN
p_check := FALSE;
END IF;
END LOOP;
p_check := TRUE;
UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(p_attach_clob, l_step , (v_parsed + 1)));
v_parsed := v_parsed + l_step;
END LOOP;
--REMOVED CODE NOT RELEVANT-- |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
