OO4O execute a query against Oracle, VBA Excel 2003
 

Any OO4O Oracle users out there ?

Ok, I am making this Excel VBA thing that will execute a series of
queries and bring in Analysis Data, the first step that I need to do
is execute a DDL query to create a Table:

Any variables that hold data are in the code, I just simplified it and
removed them, that all is good, I put watch on them to see what values
they got.

Sub Process_AdHoc_Analysis_Report()

Dim OraDynaset As Object
Dim objSession As Object
Dim objDataBase As Object

Dim sql1 As String

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase("database", schema & "/" &
password, 0&)

sql1 = "create table " & tablename & "_1" & " as select * from table
where cmpgn_id='" & campaignid & "';"

objDataBase.ExecuteSQL (sql1) <-- here is the error "Run-time error
'440': Automation error

End Sub

Any idea how to execute a DDL query ? All I need is to just execute
this query, I don't need nothing returned to deal with it like with a
Select Statement. One would figure this was easy, not sure, I read the
OO4O Wiki and not very well documented.

OO4O execute a query against Oracle, VBA Excel 2003
 

Have you tried using ADO instead of OO4O ?
There will be many more examples when you get stuck...

anyway...

If you debug.print the SQL, can you run it successfully in your favorite
query tool ?

Tim


"RompStar" wrote in message
...
Any OO4O Oracle users out there ?

Ok, I am making this Excel VBA thing that will execute a series of
queries and bring in Analysis Data, the first step that I need to do
is execute a DDL query to create a Table:

Any variables that hold data are in the code, I just simplified it and
removed them, that all is good, I put watch on them to see what values
they got.

Sub Process_AdHoc_Analysis_Report()

Dim OraDynaset As Object
Dim objSession As Object
Dim objDataBase As Object

Dim sql1 As String

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase("database", schema & "/" &
password, 0&)

sql1 = "create table " & tablename & "_1" & " as select * from table
where cmpgn_id='" & campaignid & "';"

objDataBase.ExecuteSQL (sql1) <-- here is the error "Run-time error
'440': Automation error

End Sub

Any idea how to execute a DDL query ? All I need is to just execute
this query, I don't need nothing returned to deal with it like with a
Select Statement. One would figure this was easy, not sure, I read the
OO4O Wiki and not very well documented.

OO4O execute a query against Oracle, VBA Excel 2003
 

Thanks, but I won't want to run the query in Toad/Or SQL*Plus, I
wanted to run it from Excel, just need to figure out how.

Oracle writes crappy instructions, the worse ever!

On Sep 21, 10:58*pm, "Tim Williams" wrote:
Have you tried using ADO instead of OO4O ?
There will be many more examples when you get stuck...

anyway...

If you debug.print the SQL, can you run it successfully in your favorite
query tool ?

Tim

"RompStar" wrote in message

...

Any OO4O Oracle users out there ?

Ok, I am making this Excel VBA thing that will execute a series of
queries and bring in Analysis Data, the first step that I need to do
is execute a DDL query to create a Table:

Any variables that hold data are in the code, I just simplified it and
removed them, that all is good, I put watch on them to see what values
they got.

Sub Process_AdHoc_Analysis_Report()

Dim OraDynaset As Object
Dim objSession As Object
Dim objDataBase As Object

Dim sql1 As String

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase("database", schema & "/" &
password, 0&)

sql1 = "create table " & tablename & "_1" & " as select * from table
where cmpgn_id='" & campaignid & "';"

objDataBase.ExecuteSQL (sql1) <-- here is the error "Run-time error
'440': Automation error

End Sub

Any idea how to execute a DDL query ? All I need is to just execute
this query, I don't need nothing returned to deal with it like with a
Select Statement. One would figure this was easy, not sure, I read the
OO4O Wiki and not very well documented.

OO4O execute a query against Oracle, VBA Excel 2003
 

I found that Oracle maintains Forums and there is one for OO4O and
Wizards:

Forum Home » Windows and .NET » OO4O and Wizards


Just to go Oracle.com

That's what I will try next.

On Sep 21, 10:58*pm, "Tim Williams" wrote:
Have you tried using ADO instead of OO4O ?
There will be many more examples when you get stuck...

anyway...

If you debug.print the SQL, can you run it successfully in your favorite
query tool ?

Tim

"RompStar" wrote in message

...

Any OO4O Oracle users out there ?

Ok, I am making this Excel VBA thing that will execute a series of
queries and bring in Analysis Data, the first step that I need to do
is execute a DDL query to create a Table:

Any variables that hold data are in the code, I just simplified it and
removed them, that all is good, I put watch on them to see what values
they got.

Sub Process_AdHoc_Analysis_Report()

Dim OraDynaset As Object
Dim objSession As Object
Dim objDataBase As Object

Dim sql1 As String

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase("database", schema & "/" &
password, 0&)

sql1 = "create table " & tablename & "_1" & " as select * from table
where cmpgn_id='" & campaignid & "';"

objDataBase.ExecuteSQL (sql1) <-- here is the error "Run-time error
'440': Automation error

End Sub

Any idea how to execute a DDL query ? All I need is to just execute
this query, I don't need nothing returned to deal with it like with a
Select Statement. One would figure this was easy, not sure, I read the
OO4O Wiki and not very well documented.

OO4O execute a query against Oracle, VBA Excel 2003
 

I wasn't suggesting you not use Excel, just that you verify the SQL is
being created correctly and that it runs under the account you're using.

Tim



"RompStar" wrote in message
...
Thanks, but I won't want to run the query in Toad/Or SQL*Plus, I
wanted to run it from Excel, just need to figure out how.

Oracle writes crappy instructions, the worse ever!

On Sep 21, 10:58 pm, "Tim Williams" wrote:
Have you tried using ADO instead of OO4O ?
There will be many more examples when you get stuck...

anyway...

If you debug.print the SQL, can you run it successfully in your favorite
query tool ?

Tim

"RompStar" wrote in message

...

Any OO4O Oracle users out there ?

Ok, I am making this Excel VBA thing that will execute a series of
queries and bring in Analysis Data, the first step that I need to do
is execute a DDL query to create a Table:

Any variables that hold data are in the code, I just simplified it and
removed them, that all is good, I put watch on them to see what values
they got.

Sub Process_AdHoc_Analysis_Report()

Dim OraDynaset As Object
Dim objSession As Object
Dim objDataBase As Object

Dim sql1 As String

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase("database", schema & "/" &
password, 0&)

sql1 = "create table " & tablename & "_1" & " as select * from table
where cmpgn_id='" & campaignid & "';"

objDataBase.ExecuteSQL (sql1) <-- here is the error "Run-time error
'440': Automation error

End Sub

Any idea how to execute a DDL query ? All I need is to just execute
this query, I don't need nothing returned to deal with it like with a
Select Statement. One would figure this was easy, not sure, I read the
OO4O Wiki and not very well documented.

OO4O execute a query against Oracle, VBA Excel 2003
 

Ohhh yes, I can execute the SQL with no problem using TOAD on Oracle
10G, but for some reason it won't using OO4O, every example that I
have read in their awesome documentation (not), is showing in create
an empty Table and then doing inserts, somehow I can't find a single
example of:

Create Table TableName As (Select * from Table where blablabla)

I mean, this is a DDL statement or not ?

It reads that it supports DDL, but like, not sure....

On Sep 22, 8:08*pm, "Tim Williams" wrote:
I wasn't suggesting you not use Excel, *just that you verify the SQL is
being created correctly and that it runs under the account you're using.

Tim

"RompStar" wrote in message

...
Thanks, but I won't want to run the query in Toad/Or SQL*Plus, I
wanted to run it from Excel, just need to figure out how.

Oracle writes crappy instructions, the worse ever!

On Sep 21, 10:58 pm, "Tim Williams" wrote:



Have you tried using ADO instead of OO4O ?
There will be many more examples when you get stuck...

anyway...

If you debug.print the SQL, can you run it successfully in your favorite
query tool ?

Tim

"RompStar" wrote in message

...

Any OO4O Oracle users out there ?

Ok, I am making this Excel VBA thing that will execute a series of
queries and bring in Analysis Data, the first step that I need to do
is execute a DDL query to create a Table:

Any variables that hold data are in the code, I just simplified it and
removed them, that all is good, I put watch on them to see what values
they got.

Sub Process_AdHoc_Analysis_Report()

Dim OraDynaset As Object
Dim objSession As Object
Dim objDataBase As Object

Dim sql1 As String

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase("database", schema & "/" &
password, 0&)

sql1 = "create table " & tablename & "_1" & " as select * from table
where cmpgn_id='" & campaignid & "';"

objDataBase.ExecuteSQL (sql1) <-- here is the error "Run-time error
'440': Automation error

End Sub

Any idea how to execute a DDL query ? All I need is to just execute
this query, I don't need nothing returned to deal with it like with a
Select Statement. One would figure this was easy, not sure, I read the
OO4O Wiki and not very well documented.- Hide quoted text -

- Show quoted text -