Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Unanswered: Using VBA to connect to Oracle vs SQL Server

    Hey guys, extreme newbie to Oracle, but intermediate user to SQL Server. I have a question with a (hopefully) simple answer.

    I have written some code in VB that opens a connection to a SQL Server database and performs a BULK INSERT from a text file to a SQL table.

    My question is this....if I had a duplicate table in Oracle and wanted to switch to using this table vs SQL Server, would I only need to change the connection string in the code snippet below? What would the Oracle connection string look like?

    Code:
        Set conn = New ADODB.Connection 
        conn.ConnectionString = "DRIVER=SQL Server;DATABASE=DbMain;SERVER=serv1\pro" 
        conn.Open 
         
        Dim strSQL As String 
         
        strSQL = "BULK INSERT [RU].[MainTab] FROM 'C:\OracleTest\Combined\Combined.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n') GO" 
        conn.Execute (strSQL) 
        conn.Close
    Thanks
    Joe

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL syntax is not compatible
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2013
    Posts
    3
    How would I go about altering the code for Oracle?

    Any help would be appreciated.

    Thanks

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle does not have any equivalent to INSERT into table from external file via SQL

    You would have to create an EXTERNAL TABLE to map the file as an existing table.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    ...or use VBA to run a SQL *Loader script. This is technically a bit more challenging, but it fits better with the mindset of a SQL Server, MySQL, or DB2 user.

    FWIW, most Oracle installations won't allow an application this much access to the database engine. At least among my clients, it is considered to be too large a security risk. If you can re-architect your application to an N-tier design, an app server would probably be a better fit for this kind of task in an Oracle environment (at least it would be for my clients).

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Jul 2013
    Posts
    3
    I've been reading up on this quite a bit. Will something like this work?

    Code:
    Dim SQLShell
        
        SQLShell = Shell("c:\app\OrcUser\bin\sqlldr.exe userid=user123/Password@mydb control=C:\OracleTest\InvLoad.ctl bad=C:\OracleTest\InvLoad.bad log=C:\OracleTest\InvLoad.log")
    I created my control file (.CTL) that tells where the flat file is located.

    Control file here:

    Code:
    LOAD DATA
    INFILE 'C:OracleTest\Combined\Combined.txt'
    INTO TABLE MainTab
    FIELDS TERMINATED BY "'"
    (
    Field1,
    Field2,
    Field3,
    Field4
    )
    Please advise. I am having a heck of a time trying to get SQL Loader working so I cannot test.

    Thanks
    Joe

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when all else fails, Read The Fine Manual

    Contents

    SQL*Loader is documented in manual listed above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •