Home » SQL & PL/SQL » SQL & PL/SQL » Excel created using utl_file is larger than manual file creation (Oracle apps 12.1.1 (11g))
Excel created using utl_file is larger than manual file creation [message #622759] |
Fri, 29 August 2014 03:08 |
|
shanmugait
Messages: 6 Registered: June 2012 Location: CHENNAI
|
Junior Member |
|
|
Hi,
My problem is when I am running Program its data into an excel file using utl_file, the file size is nearly 269 MB. After creating of Excel file i copied all records into another excel sheet i checked only 19 MB size. I am unable to undersatnd why there is difference in file size. I want to send that file to attachment mail by using UTL_SMTP package but only less than 5 MB.
Please help me.
-----------------------below procedure to create Excel file from multiple tables -------------------------------
Toggle Spoiler
CREATE OR REPLACE PROCEDURE apps.test_excel_sheet_prc1 (
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
p_file_name IN VARCHAR2,
p_dir IN VARCHAR2
)
IS
v_fh UTL_FILE.file_type;
-- P_DIR VARCHAR2(30) := 'TEST_IDL_DIR';
-- P_FILE_NAME VARCHAR2(30) := 'TEST_IDL.XLS';
PROCEDURE run_query (p_sql IN VARCHAR2)
IS
v_v_val VARCHAR2 (4000);
v_n_val NUMBER;
v_d_val DATE;
v_ret NUMBER;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
f BOOLEAN;
rec_tab DBMS_SQL.desc_tab;
col_num NUMBER;
BEGIN
c := DBMS_SQL.open_cursor;
-- PARSE THE SQL STATEMENT
DBMS_SQL.parse (c, p_sql, DBMS_SQL.native);
-- IF P_QUERY IN (2,3,4) THEN
-- DBMS_OUTPUT.PUT_LINE(' KEIKAKU NUMBER' || P_KEIKAKU_NUMBER || ' P_QUERY ' || P_QUERY );
-- DBMS_SQL.BIND_VARIABLE(C,'BND1',P_KEIKAKU_NUMBER);
-- DBMS_SQL.BIND_VARIABLE(C,'BND2',P_DATA);
-- END IF;
-- START EXECUTION OF THE SQL STATEMENT
d := DBMS_SQL.EXECUTE (c);
-- GET A DESCRIPTION OF THE RETURNED COLUMNS
DBMS_SQL.describe_columns (c, col_cnt, rec_tab);
-- BIND VARIABLES TO COLUMNS
FOR j IN 1 .. col_cnt
LOOP
CASE rec_tab (j).col_type
WHEN 1
THEN
DBMS_SQL.define_column (c, j, v_v_val, 4000);
WHEN 2
THEN
DBMS_SQL.define_column (c, j, v_n_val);
WHEN 12
THEN
DBMS_SQL.define_column (c, j, v_d_val);
ELSE
DBMS_SQL.define_column (c, j, v_v_val, 4000);
END CASE;
END LOOP;
-- OUTPUT THE COLUMN HEADERS
UTL_FILE.put_line (v_fh, '<ss:Row>');
FOR j IN 1 .. col_cnt
LOOP
UTL_FILE.put_line (v_fh, '<ss:Cell>');
UTL_FILE.put_line (v_fh,
'<ss:Data ss:Type="String">'
|| rec_tab (j).col_name
|| '</ss:Data>'
);
UTL_FILE.put_line (v_fh, '</ss:Cell>');
END LOOP;
UTL_FILE.put_line (v_fh, '</ss:Row>');
-- OUTPUT THE DATA
LOOP
v_ret := DBMS_SQL.fetch_rows (c);
EXIT WHEN v_ret = 0;
UTL_FILE.put_line (v_fh, '<ss:Row>');
FOR j IN 1 .. col_cnt
LOOP
CASE rec_tab (j).col_type
WHEN 1
THEN
DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
UTL_FILE.put_line (v_fh, '<ss:Cell>');
UTL_FILE.put_line (v_fh,
'<ss:Data ss:Type="String">'
|| v_v_val
|| '</ss:Data>'
);
UTL_FILE.put_line (v_fh, '</ss:Cell>');
WHEN 2
THEN
DBMS_SQL.COLUMN_VALUE (c, j, v_n_val);
UTL_FILE.put_line (v_fh, '<ss:Cell>');
UTL_FILE.put_line (v_fh,
'<ss:Data ss:Type="Number">'
|| TO_CHAR (v_n_val)
|| '</ss:Data>'
);
UTL_FILE.put_line (v_fh, '</ss:Cell>');
WHEN 12
THEN
DBMS_SQL.COLUMN_VALUE (c, j, v_d_val);
UTL_FILE.put_line (v_fh,
'<ss:Cell ss:StyleID="OracleDate">'
);
UTL_FILE.put_line (v_fh,
'<ss:Data ss:Type="DateTime">'
|| TO_CHAR (v_d_val,
'YYYY-MM-DD"T"HH24:MI:SS'
)
|| '</ss:Data>'
);
UTL_FILE.put_line (v_fh, '</ss:Cell>');
ELSE
DBMS_SQL.COLUMN_VALUE (c, j, v_v_val);
UTL_FILE.put_line (v_fh, '<ss:Cell>');
UTL_FILE.put_line (v_fh,
'<ss:Data ss:Type="String">'
|| v_v_val
|| '</ss:Data>'
);
UTL_FILE.put_line (v_fh, '</ss:Cell>');
END CASE;
END LOOP;
UTL_FILE.put_line (v_fh, '</ss:Row>');
END LOOP;
DBMS_SQL.close_cursor (c);
END;
--
PROCEDURE start_workbook
IS
BEGIN
UTL_FILE.put_line (v_fh, '<?xml version="1.0"?>');
UTL_FILE.put_line
(v_fh,
'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">'
);
END;
PROCEDURE end_workbook
IS
BEGIN
UTL_FILE.put_line (v_fh, '</ss:Workbook>');
END;
--
PROCEDURE start_worksheet (p_sheetname IN VARCHAR2)
IS
BEGIN
UTL_FILE.put_line (v_fh,
'<ss:Worksheet ss:Name="' || p_sheetname || '">'
);
UTL_FILE.put_line (v_fh, '<ss:Table>');
END;
PROCEDURE end_worksheet
IS
BEGIN
UTL_FILE.put_line (v_fh, '</ss:Table>');
UTL_FILE.put_line (v_fh, '</ss:Worksheet>');
END;
--
PROCEDURE set_date_style
IS
BEGIN
UTL_FILE.put_line (v_fh, '<ss:Styles>');
UTL_FILE.put_line (v_fh, '<ss:Style ss:ID="OracleDate">');
UTL_FILE.put_line
(v_fh,
'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>'
);
UTL_FILE.put_line (v_fh, '</ss:Style>');
UTL_FILE.put_line (v_fh, '</ss:Styles>');
END;
BEGIN
v_fh := UTL_FILE.fopen (UPPER (p_dir), p_file_name, 'W');
start_workbook;
set_date_style;
start_worksheet ('IDL HEADER');
run_query ('select * from test_IDL_HEADER where mail_flag is null ');
end_worksheet;
start_worksheet ('Application Model Parts No.');
run_query ('select * from test_IDL_APPL_MODEL where mail_flag is null ');
end_worksheet;
start_worksheet ('Keikaku No.Parts No');
run_query ('select * from test_IDL_KEIKAKU_NO where mail_flag is null ');
end_worksheet;
start_worksheet ('Parts Information Work');
run_query ('select * from test_IDL_PARTS_INFO where mail_flag is null ');
end_worksheet;
end_workbook;
UTL_FILE.fclose (v_fh);
--UPDATE ALL TABLE TO MAIL_FLAG = 'Y'
UPDATE test_idl_header
SET mail_flag = 'Y'
WHERE mail_flag IS NULL;
UPDATE test_idl_appl_model
SET mail_flag = 'Y'
WHERE mail_flag IS NULL;
UPDATE test_idl_keikaku_no
SET mail_flag = 'Y'
WHERE mail_flag IS NULL;
UPDATE test_idl_parts_info
SET mail_flag = 'Y'
WHERE mail_flag IS NULL;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (' Error in test_excel_sheet_prc ' || SQLERRM);
END test_excel_sheet_prc1;
Lalit :
1. Added code tags
2. Added spoiler to the lengthy code
3. Modified a spelling error in topic title, "then" to "than"
4. Removed superfluous lines in the end
[Updated on: Fri, 29 August 2014 03:16] by Moderator Report message to a moderator
|
|
|
|
Re: Excel created using utl_file is larger than manual file creation [message #622772 is a reply to message #622759] |
Fri, 29 August 2014 04:45 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
shanmugait wrote on Fri, 29 August 2014 13:38
excel file using utl_file, the file size is nearly 269 MB. After creating of Excel file i copied all records into another excel sheet i checked only 19 MB size. I am unable to undersatnd why there is difference in file size.
So you see a huge change in size of an excel file after copying records from one excel file to another. So how is this related to Oracle?
|
|
|
|
|
Re: Excel created using utl_file is larger than manual file creation [message #622775 is a reply to message #622774] |
Fri, 29 August 2014 05:55 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You're creating an xml file that pretends to be an xls.
XML is plain text and very verbose.
A real XLS is binary and for more compact.
I would expect a sizeable difference between the 2 for the same data. The real xls won't have all the tags for starters.
Whether or not that difference should be 250M is hard to say, but unless your xml file has a lot of whitespace I can only assume it's correct.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 21:38:00 CDT 2024
|