This content has been marked as final. Show 8 replies
can external tables handle xls files??
I know they can work on csv files, but I am not sure about xls files.
Thank you for the link...
Seems like it is using java :-(
But can this api (or available jXLS api) be placed on database server(If so where I can place this api on server) and can I write java stored procedure directly??
My concern here is I dont want to create an intermediary csv file...I want to automate the reading from xls and putting into database.
Well, something that may help is Chris Poole's XUTL_FTP package:1 person found this helpful
This would certainly allow you to FTP the .xls file to a location where you can then read it.
As for reading it, your best option, as it's a .xls file and not a flat file, is to use heterogeneous services (i.e. connect to the Excel workbook as if it's a database using an Excel ODBC connection), thus treating it as a database over a database link). Note though that this will require the .xls file to reside on a Windows server.
1- Go to Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN and create a data source with appropriate driver. Name it EXCL.
2- In %ORACLE_HOME%\Network\Admin\Tnsnames.ora fie add entry:
Here SID is the name of data source that you have just created.
EXCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.0.24)(PORT = 1521)) ) (CONNECT_DATA = (SID = EXCL) ) (HS = OK) )
3- In %ORACLE_HOME%\Network\Admin\Listener.ora file add:
under SID_LIST_LISTENER like:
(SID_DESC = (PROGRAM = hsodbc) (SID_NAME = <hs_sid>) (ORACLE_HOME = <oracle home>) )
Dont forget to reload the listener
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = d:\ORA9DB) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = ORA9DB) (ORACLE_HOME = d:\ORA9DB) (SID_NAME = ORA9DB) ) (SID_DESC = (PROGRAM = hsodbc) (SID_NAME = EXCL) (ORACLE_HOME = D:\ora9db) ) )
4- In %ORACLE_HOME%\hs\admin create init<HS_SID>.ora. For our sid EXCL we create file initexcl.ora.
c:\> lsnrctl reload
In this file set following two parameters:
5- Now connect to Oracle database and create database link with following command:
HS_FDS_CONNECT_INFO = excl HS_FDS_TRACE_LEVEL = 0
Now you can perform query against this database like you would for any remote database.
SQL> CREATE DATABASE LINK excl 2 USING 'excl' 3 / Database link created.
Or refer to this Article...
SQL> SELECT table_name FROM all_tables@excl; TABLE_NAME ------------------------------ DEPT EMP
Thanks for that info...
One small question...
does this HS connectivity works for oracle db on HP unix server??
And also each time do I need to create a data source connection for each and every excel work book?
Because we will be receiving a number of generated reports and throughout the year. We dont want to restart the listener often because there are few other applications running on the server which might get affected.
Unix server makes it a little more difficult, although I think (I've not done it myself) you can get ODBC drivers for unix too, but you'd have to look into that.
Unfortunately, it is one ODBC connection for one Excel workbook, as they are being treated as databases. You wouldn't expect to set up several normal databases all with the same connection string, so why would seperate workbooks be any different. ;)
Perhaps you could use the FTP package to copy the file across and rename it to a standard fixed name so that you only require one connection to that and just replace the underlying workbook each time.