5 Replies Latest reply: Aug 23, 2011 6:03 AM by orafad RSS

    Create SQL in VBA

    883350
      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
          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
            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
              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
                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
                  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.