Excel download and update records to oracle


i would like to be able to download data from Oracle into excel via jcdb extract but thereafer be able to writeback to oracle with webservices, any clues where I can obtain this information?

thanks in advance
Who is Participating?
Steven HarrisConnect With a Mentor PresidentCommented:
See if the attached Oracle Fusion PDF will help any.

Reference 3-19 (aka pg, 43)

If you are looking into a VBA route, you will need Oracle Objects for OLE (OO4O), which can be used from Visual Basic, Visual C++, VBA in Excel, Active Server Pages, etc.

Than you could use VBA such as:

Sub uploadListOfItems()
'Oracle connection variables
Dim dbUserName As String
Dim dbPassword As String
dbPassword = "ORACLE_PASSWORD"

Dim dbDSN As String

'Create and Set Session / Create Dynaset = Column Names
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

Dim OraDatabase As Object
Set OraDatabase = OraSession.OpenDatabase("" & dbDSN & "", "" & dbUserName & " / " & dbPassword & "", 0&)

Dim Oradynaset As Object
Set Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM MY_ORACLE_TABLE", 0&)

'Upload data variables
Dim site As String
Dim item As String

'Initialize upload
order_id = 1

'Iterate through each cell and upload to MY_ORACLE_TABLE
Do Until Selection.Value = ""
site = ActiveCell.Value
item = ActiveCell.Offset(0, 1).Value

'Add new record
Oradynaset.Fields("USERNAME").Value = Application.UserName
Oradynaset.Fields("SITE").Value = site
Oradynaset.Fields("ITEM").Value = item

Selection.Offset(1, 0).Select

order_id = order_id + 1
End Sub

Open in new window

Alexander Eßer [Alex140181]Software DeveloperCommented:
Why so complicated?! Just use ADODB, as shown here: http://www.vb6.us/tutorials/oracle-and-visual-basic-using-ado
Steven HarrisPresidentCommented:
Never used it myself...  No since steering him toward something I have no experience with and therefore could not help him with if he ran into more questions.
rutgermonsAuthor Commented:
works for me
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.