Prev: File owner attributes through Excel VBA
Next: Range Defined names loop
From: RompStar on 21 Sep 2009 21:46
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.
From: Tim Williams on 22 Sep 2009 01:58
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" <rompstar(a)gmail.com> wrote in message
news:420823c5-c504-478c-b81f-c75c94ebedab(a)12g2000pri.googlegroups.com...
> 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.


From: RompStar on 22 Sep 2009 12:44
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" <timjwilli...(a)comcast.net> 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" <romps...(a)gmail.com> wrote in message
>
> news:420823c5-c504-478c-b81f-c75c94ebedab(a)12g2000pri.googlegroups.com...
>
> > 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.

From: RompStar on 22 Sep 2009 12:58
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" <timjwilli...(a)comcast.net> 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" <romps...(a)gmail.com> wrote in message
>
> news:420823c5-c504-478c-b81f-c75c94ebedab(a)12g2000pri.googlegroups.com...
>
> > 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.

From: Tim Williams on 22 Sep 2009 23:08
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" <rompstar(a)gmail.com> wrote in message
news:534bc3de-03ad-4bad-bc9b-503babaf9431(a)l35g2000vba.googlegroups.com...
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" <timjwilli...(a)comcast.net> 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" <romps...(a)gmail.com> wrote in message
>
> news:420823c5-c504-478c-b81f-c75c94ebedab(a)12g2000pri.googlegroups.com...
>
> > 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.


 |  Next  |  Last
Pages: 1 2
Prev: File owner attributes through Excel VBA
Next: Range Defined names loop