Re: OO4O - Excel to Oracle - Array Processing

From: george lewycky <gelewyc_at_nyct.com>
Date: 30 Jul 2002 06:17:58 -0700
Message-ID: <68aecc05.0207300517.168c7452_at_posting.google.com>

Paul,

Look into these below, I use the TEXT WRITE PROGRAM and its fantastic. I dont know if it handles array processing

you should also try some of microsofts newsgroups for this problem

good luck

george

Using spreadsheet files:
· Save the spreadsheet &#8220;CSV&#8221; to then import the file using SQL Loader or UTL_FILE

·	SQL*XL – add-in for Excel             oraxcel.com
·	TEXT WRITE PROGRAM (excel macro)  

http://tushar-mehta.com/excel/software/text_write_program/index.html has a real useful Macro &#8220;Text Write Program&#8221; to delimit &#8220;,&#8221; for a text file to be loaded into oracle using SQL, etc Excel will only do commas !!!

"dti" <dti_anonymous_at_yahoo.com> wrote in message news:<8Mm19.3551$nm.235814_at_bin5.nnrp.aus1.giganews.com>...
> Is there a way to pass a multidimension array back and forth between
> Excel and Oracle instead of using a separate AddTable for every
> column? Below is the example from the documentation.
>
> Thanks,
>
> Paul
>
> Dim OraSession As OraSession
> Dim OraDatabase As OraDatabase
> Dim EmpnoArray As OraParamArray
> Dim EnameArray As OraParamArray
> Set OraSession = CreateObject("OracleInProcServer.XOraSession")
> Set OraDatabase = OraSession.OpenDatabase("exampledb", "scott/tiger",
> 0&)
> OraDatabase.Parameters.Add "ArraySize", 3, ORAPARM_INPUT
> OraDatabase.Parameters.AddTable "EMPNOS", ORAPARM_INPUT,
> ORATYPE_NUMBER ,3,22
> OraDatabase.Parameters.AddTable "ENAMES",
> ORAPARM_OUTPUT,ORATYPE_VARCHAR2,3,10
> Set EmpnoArray = OraDatabase.Parameters("EMPNOS")
> Set EnameArray = OraDatabase.Parameters("ENAMES")
> 'Initialize the newly created input parameter table EMPNOS
> EmpnoArray(0) = 7698
> EmpnoArray(1) = 7782
> EmpnoArray(2) = 7654
> 'Execute the PLSQL package
> OraDatabase.ExecuteSQL ("Begin
> Employee.GetEmpNamesInArray(:ArraySize,:EMPNOS,:ENAMES); End;")
> 'Print out Enames
> MsgBox EnameArray(0)
> MsgBox EnameArray(1)
> MsgBox EnameArray(2)
Received on Tue Jul 30 2002 - 15:17:58 CEST