stored procedure to change excel to csv

To my knowledge, this is Not possible yet within the power of PL/SQL.

Pl/SQL utl_file package provides access to text file (prior to Oracle9i ) , and binary file (only available in Oracle9i).
but here I think you are trying to convert a Binary file (excel file) to a text file (csv file), which is out of its reach...


With that being said, you can still pull this off using PL/SQL combined with the power of JAVA and a 3rd party tool

1. find a open-source or buy a commercial tool for converting excel to csv , I think there are plenty in the market.
2. write a java stored procedure to call this program to do that.

some one tried this and it works. I did not use it. Just try

Is there a I/O PL/SQL function to read EXCEL file
=========================================

DECLARE

-- Declare handles to OLE objects
application ole2.obj_type;
workbooks ole2.obj_type;
workbook ole2.obj_type;
worksheet ole2.obj_type;
cell ole2.obj_type;

-- Declare handles to OLE argument lists
args ole2.list_type;

Check_file text_io.file_type;
no_file exception;
PRAGMA exception_INIT (no_file, -302000);
cell_value varchar2(2000);

beGIN

-- Check the file can be found, if not exception no_file will be raised
Check_file := TEXT_IO.FOPEN('C:\test.XLS','R');
TEXT_IO.FCLOSE(Check_file);

application:= ole2.create_obj('Excel.Application');
workbooks := ole2.get_obj_property(application, 'Workbooks');

-- Open the required workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'C:\test.XLS');
workbook := ole2.invoke_obj(workbooks, 'Open', args);
ole2.destroy_arglist(args);

-- Open worksheet Sheet1 of that Workbook
args:= ole2.create_arglist;
ole2.add_arg(args, 'Sheet1');
worksheet := ole2.get_obj_property(workbook, 'Worksheets', args);
ole2.destroy_arglist(args);

-- Get value of cell A1 of worksheet Sheet1
args:= ole2.create_arglist;
ole2.add_arg(args, 1);
ole2.add_arg(args, 1);
cell:= ole2.get_obj_property(worksheet, 'Cells', args);
ole2.destroy_arglist(args);
cell_value :=ole2.get_char_property(cell, 'Value');
message(cell_value);

args:=ole2.create_arglist;
ole2.add_arg(args,'C:\Ora.prn');
ole2.add_arg(args,'Formatted text(Space delimited)(*.prn)|*.prn|');
ole2.invoke(worksheet,'SaveAs',args);
ole2.destroy_arglist(args);
message('Hi am passed');

ole2.invoke(application,'Quit');
message('Hi am still passed');
-- Release the OLE2 object handles
ole2.release_obj(cell);
ole2.release_obj(worksheet);
ole2.release_obj(workbook);
ole2.release_obj(workbooks);
ole2.release_obj(application);
message('Hi am in safer side');

exception
WHEN no_file THEN
MESSAGE('file not found.');
WHEN OTHERS THEN
MESSAGE(sqlerrm);
PAUSE;
FOR i IN 1 .. tool_err.nerrors LOOP
MESSAGE(tool_err.message);
PAUSE;
tool_err.pop;
END LOOP;

END;

evvsupport

sorry... forgot to mention... the above code is a front end code and not a stored proc code.

I would only mention that Oracle 10g incorporates Excel tables into the database (see HTML DB component)