Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best way to execute an SQL Query on an Excel Worksheet | Excel Programming | |||
Query Oracle from Excel | Excel Programming | |||
How to update ORACLE query definition in Excel file | Links and Linking in Excel | |||
returning a spreadsheet from code - ASP, OO4O Oracle Database connection | Excel Programming | |||
Execute a query against an excel doc and a sql server db | Excel Programming |