Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to load excel files into Oracle DB using COMAutomation

930808Jul 1 2015 — edited Jul 8 2015

Hi,

I have a requirement where n number of excel files (xls or xlsx) containing multiple tabs needs to be loaded to oracle.

Available solutions in hand.

1 PHP script to convert to csv and load using sqlloader. tested and verified for both xls and xlsx formats with single and multi tabs.

2. PERL script to convert to csv and load using sqlloader. Tested and verified for xls.(single and multi tab). For xlsx, successfully converted, but issue with data wherever it is date.

I(actually the client) am looking for a solution in PLSQL. I came across the package called COM Automation.

But I see that the examples show, that it loads data into the excel rather loading from excel from oracle tables.

If anyone has used COM Automation, please clarify me the following.

1. Does this help in loading data from excel files(XLS and XLSX) from a predefined folder structure.

2. If so, does the excel file need to have data in some specific format or any format of data can be loaded. i.e, merged columns, formulas, first row and first column being blank etc.

3. Does it help in extracting each and every tab of the excel file and load it into a table.? or Is it possible to selectively load specific tabs.?

4. With respect to maintenance, which one is a better option. PERL, PHP or PLSQL.

Correct me if I have asked anything wrong.

Ready to answer any further questions.

Thanks,

Anand

Comments

285751
Try adding a parameter 0 before your oraParmSessionID parameter. That will give the function someplace to return its result.

OraParameter oraReturn = new OraParameter();
oraReturn.ParameterName = "justaboutanything";
oraReturn.OraDbType = OraDbType.Date;
oraReturn.Direction = ParameterDirection.ReturnValue;
oraCommand.Parameters.Add(oraReturn );




Any ideas as to what's causing the following error:
---
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Oracle.DataAccess.Client.OraException:
/---
I'm simply calling an Oracle function which returns a date. Code snippet:
---
FUNCTION func_get_session_date(v_session_id VARCHAR2, v_session_key VARCHAR2) RETURN DATE IS
...etc
/---
...employing the OraDataReader to extract the value (unnecessary code extracted for clarity):
---
DateTime returnDateTime;
string commandText = "SELECT pkg_css_session_state.func_get_session_date(:v_session_id, :v_session_key) FROM dual";
//string commandText = "SELECT pkg_css_session_state.func_get_session_date('" + this.AspxSessionID + "', '" + sessionKey + "') FROM dual";
OraConnection oraConnection = new OraConnection(connectionString);
OraCommand oraCommand = new OraCommand();
oraCommand.Connection = oraConnection;
oraCommand.CommandText = commandText;
OraParameter oraParmSessionID = new OraParameter();
oraParmSessionID.ParameterName = "v_session_id";
oraParmSessionID.OraDbType = OraDbType.Varchar2;
oraParmSessionID.Value = this.AspxSessionID;
oraParmSessionID.Direction = ParameterDirection.Input;
oraCommand.Parameters.Add(oraParmSessionID);
OraParameter oraParmSessionKey = new OraParameter();
oraParmSessionKey.ParameterName = "v_session_key";
oraParmSessionKey.OraDbType = OraDbType.Varchar2;
oraParmSessionKey.Value = sessionKey;
oraParmSessionKey.Direction = ParameterDirection.Input;
oraCommand.Parameters.Add(oraParmSessionKey);
oraConnection.Open();
OraDataReader oraDataReader = oraCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (oraDataReader.Read())
{
if (! oraDataReader.IsDBNull(0))
{
returnDateTime = oraDataReader.GetDateTime(0);
}
}
/--
Interestingly, it works fine when I don't use input parameters (see commented-out commandText string).
Thanks ahead o' time!
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 5 2015
Added on Jul 1 2015
24 comments
3,574 views