From: RompStar on
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" <timjwilli...(a)comcast.net> 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" <romps...(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.- Hide quoted text -
>
> - Show quoted text -