This discussion is archived
5 Replies Latest reply: Aug 23, 2011 4:03 AM by orafad RSS

Create SQL in VBA

883350 Newbie
Currently Being Moderated
Hi,

I wan to create a create sql in VBA. I read a some sites but I haven't found why my script doesn't work.
This is my script:

Sub Create_Temp_Table()

Dim username
Dim password
Dim sid
Dim objSession
Dim objdatabase

Dim OraDynaset
Dim oraSqlStmt
Dim strSQL

Dim stdout
Dim i

Set stdout = WScript.stdout
username = ""
password = ""
SERVICE_NAME = "TEST"

' connect to database
stdout.WriteLine "Connecting to database . . ."
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objdatabase = objSession.OpenDatabase(SERVICE_NAME, username & "/" & password, 0)

strSQL = "DROP TABLE TTT_DATEPAR"
strSQL = "COMMIT"
strSQL = "CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)"
strSQL = "COMMIT"
strSQL = "INSERT INTO TTT_DATEPAR VALUES (FROMDATTE, TODATE)"
strSQL = "COMMIT"
' This command takes a long time to execute when doing ranks/order bys
Set OraDynaset = objdatabase.DBCreateDynaset(strSQL, 0&)
Do While OraDynaset.EOF = False
Loop
Set OraDynaset = Nothing
Set objSession = Nothing
objdatabase.Close
Set objdatabase = Nothing
End Sub

The error is at line
Set objSession = CreateObject("OracleInProcServer.XOraSession")

Can somebody help me? thx
  • 1. Re: Create SQL in VBA
    orafad Oracle ACE
    Currently Being Moderated
    Please describe the environment in which you trying to make this run. (What OS version and edition, Oracle Client package and version, VBA host app, etc.)
    Tyrus wrote:
    The error is at line
    Set objSession = CreateObject("OracleInProcServer.XOraSession")
    What error?
  • 2. Re: Create SQL in VBA
    883350 Newbie
    Currently Being Moderated
    I have created another scrip:

    Sub Create_table()

    Dim OraSession As OraSession
    Dim OraDatabase As OraDatabase
    Dim OraDynaset As OraDynaset

    +' Use OO4O+
    Set objSession = CreateObject("OracleInProcServer.XOraSession")
    Set objdatabase = objSession.OpenDatabase("XE", "SYSTEM/BCG", 0&)

    Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)
    Set OraDynaset = OraDatabase.CreateDynaset("CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)", 0&)
    Set OraDynaset = OraDatabase.CreateDynaset("INSERT INTO  TTT_DATEPAR VALUES ('01-JAN-2010', '01-JUN-2010')", 0&)

    Set OraDynaset = objdatabase.DBCreateDynaset(Sql, 0)

    Set objSession = Nothing
    Set objdatabase = Nothing
    End Sub

    My OS is Windows7 with Office 2010. I have installed Oracle client 11.2.0 and Oracle Express Edition 10gE. The database name is XE (default name). The error is:
    Run-time error '91': Object variable or With block variable not set with debug on
    Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)

    I wrote also this command and I get the same error:
    Set OraDynaset = OraDatabase.CreateDynaset("select table_name from all_tables", 0&)

    The script model is this one
    http://download.oracle.com/docs/cd/E11882_01/win.112/e17727/clients.htm#CIHBHJDF

    thx
  • 3. Re: Create SQL in VBA
    883350 Newbie
    Currently Being Moderated
    i tried now other methods. Like this one from http://www.orafaq.com/wiki/OO4O_FAQ

    rowcount = OraDatabase.ExecuteSQL("create table X (A date)")
    rowcount = OraDatabase.ExecuteSQL("insert into X values (sysdate)")
    OraSession.CommitTrans ' or OraSession.Rollback

    Error run time 424 Object required.

    the only script that does something is this one:


    Sub GetEmployees()
    +' Use OO4O+
    Set objSession = CreateObject("OracleInProcServer.XOraSession")
    Set objdatabase = objSession.OpenDatabase("XE", "SYSTEM/BCG", 0)

    Sql1 = "DROP TABLE TTT_DATEPAR"
    Sql2 = "CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)"
    Sql3 = "INSERT INTO  TTT_DATEPAR VALUES ('01-AUG-2010', '01-JAN-2011')"

    Set OraDynaset = objdatabase.DBCreateDynaset(Sql1, 1)
    Set OraDynaset = objdatabase.DBCreateDynaset(Sql2, 0)
    Set OraDynaset = objdatabase.DBCreateDynaset(Sql3, 0)

    Set objdatabase = Nothing
    End Sub

    Does anyone know which command should i use for create, insert and drop? Is it DBCreateDynase or OraDatabase.ExecuteSQL

    thx
  • 4. Re: Create SQL in VBA
    883350 Newbie
    Currently Being Moderated
    found the solution using this post Insert Range into table using vba

    This is my script:

    Sub test1()

    Dim OraDatabase As Object
    Dim OraDynaSet As Object
    Dim OraSession As Object

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")
    Set OraDatabase = OraSession.OpenDatabase("XE", "SYSTEM/BCG", 0)

    OraDatabase.ExecuteSQL "DROP TABLE TTT_DATEPAR"
    OraDatabase.ExecuteSQL "CREATE TABLE TTT_DATEPAR(STARTDATE date,ENDDATE date)"
    OraDatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ('01-JUN-2010', '01-JAN-2011')"

    OraDatabase.Close
    'OraSession.Close
    Set OraDynaSet = Nothing
    Set OraDatabase = Nothing
    Set OraSession = Nothing
    End Sub
  • 5. Re: Create SQL in VBA
    orafad Oracle ACE
    Currently Being Moderated
    Tyrus wrote:
    Run-time error '91': Object variable or With block variable not set with debug on
    Set OraDynaset = OraDatabase.CreateDynaset("DROP TABLE TTT_DATEPAR", 0&)
    Seems you forgot to set OraDatabase variable (in previous line objdatabase was set).

    Add option explicit to your code to help avoid such mistakes.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points