SQL

Load data from query to excel file in a procedure
Please execute this code...you can get proper result ...

emp table date in one sheet...dept data in one sheet in one excel book.
First login as sysdba and create a direcotry..and grant the permissions to your schema..
Open your my computer icon .. in 'C' drive create a folder as test_dir.
Then run this script....!!
As sys user:
Create user defined directory

GRANT CREATE ANY DIRECTORY TO Scott;

CREATE OR REPLACE DIRECTORY TEST_DIR AS 'c:\myfiles' /* directory on the Oracle database server */
/
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO myuser
/
As myuser:

DECLARE
v_fh UTL_FILE.FILE_TYPE;
v_dir VARCHAR2(30) := 'TEST_DIR';
v_file VARCHAR2(30) := 'myfile.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);
-- 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,'');
FOR j in 1..col_cnt
LOOP
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||rec_tab(j).col_name||'');
UTL_FILE.PUT_LINE(v_fh,'');
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'');
-- Output the data
LOOP
v_ret := DBMS_SQL.FETCH_ROWS(c);
EXIT WHEN v_ret = 0;
UTL_FILE.PUT_LINE(v_fh,'');
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,'');
UTL_FILE.PUT_LINE(v_fh,''||v_v_val||'');
UTL_FILE.PUT_LINE(v_fh,'');
WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||to_char(v_n_val)||'');
UTL_FILE.PUT_LINE(v_fh,'');
WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'');
UTL_FILE.PUT_LINE(v_fh,'');
ELSE
DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,''||v_v_val||'');
UTL_FILE.PUT_LINE(v_fh,'');
END CASE;
END LOOP;
UTL_FILE.PUT_LINE(v_fh,'');
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
END;
--
PROCEDURE start_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
PROCEDURE end_workbook IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
END;
--
PROCEDURE start_worksheet(p_sheetname IN VARCHAR2) IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
PROCEDURE end_worksheet IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
--
PROCEDURE set_date_style IS
BEGIN
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
UTL_FILE.PUT_LINE(v_fh,'');
END;
BEGIN
v_fh := UTL_FILE.FOPEN(upper(v_dir),v_file,'w',32767);
start_workbook;
set_date_style;
start_worksheet('EMP');
run_query('select * from emp');
end_worksheet;
start_worksheet('DEPT');
run_query('select * from dept');
end_worksheet;
end_workbook;
UTL_FILE.FCLOSE(v_fh);
END;

Pages

Load data from query to excel file in a procedure