Forum Stats

  • 3,836,937 Users
  • 2,262,206 Discussions
  • 7,900,148 Comments

Discussions

PL/SQL package could manipulate Microsoft Word/Excel?

Vincelogic
Vincelogic Member Posts: 32 Bronze Badge

Is there any PL/SQL package that in Microsoft Word / Excel, and do operations such as the following tasks:

  1. Parse and go through the whole document
  2. Find a specific string, (ie. A), and replaces it with another string (ie. B)
  3. Add an image
  4. Operate the word table: create a new line, etc.


Tagged:

Best Answers

  • GregV
    GregV Member Posts: 3,086 Gold Crown
    Answer ✓

    No problem. If you need help with VBA interacting with the Oracle Database, I can help as I've done it before.

  • GregV
    GregV Member Posts: 3,086 Gold Crown
    edited Feb 15, 2021 7:13PM Answer ✓

    Once you've installed the Oracle OLE DB driver on the machine the Excel file resides, you need to reference it from the "Tools -> References" menu of the VBA editor menu:

    In the path I can see the directory where I installed the OLE DB driver. You also need to tick the ADO Library driver if it's not the case:

    Once this is done, the way to interact with the DB is pretty simple.

    First, you need to connect to the DB:

    Dim Conn As ADODB.Connection
    Dim vHost As String
    Dim vPort As Integer
    Dim vService As String
    Dim vUser As String
    Dim vPwd As String
    On Error GoTo ErreurConn
    With Conn
       .ConnectionString = "PROVIDER=OraOLEDB.Oracle;USER ID=" & vUser & ";PASSWORD=" & vPwd & ";DATA SOURCE=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST = " & vHost & ")(PORT = " & vPort & ")))(CONNECT_DATA =(SERVICE_NAME = " & vService & ")))"
       .Open
    End With
    

    The variables needed to connect can be set in the code, or they can be inputs from the worksheet. For example:

    vHost = Range("B1").value
    vPort = Range(« B2 »).value
    vService = Range(« B3 »).value
    vUser = Range(« B5 »).value
    vPwd = Range(« B6 »).value
    

    If you want to test that the connection is ok, you can do:

    If Conn.State = 1 Then
      MsgBox "Connection Successful"
       Conn.Close
       Set Conn = Nothing
    End If
    

    Now any command you want to pass can use this connection. For example, if you want to perform a SELECT statement with bind variables, you do as follows:

    'variables
    Dim vQuery As String
    Dim SQLCommand As ADODB.Command
    Dim Param As ADODB.Parameter
    Dim Rs As ADODB.Recordset
    
    vQuery = "SELECT * FROM T WHERE id = :id"
    
     
    Set SQLCommand = New ADODB.Command
    With CommandSQL
      .ActiveConnection = Conn
      .CommandText = vQuery
      .CommandType = adCmdText 
    
      'value for the bind variable
      Set param = .CreateParameter("id", adNumeric, adParamInput)
      param.value = 1
     .Parameters.Append param 
    
      Set Rs = .Execute
    End With
    
    'Browse the recordset
    Do While Not Rs.EOF
    
        'Retrieve the column values
    
        vVal = Rs.Fields("val")
        vCreationDate = Rs.Fields("creation_date") 
    
        Rs.MoveNext
    Loop
    
    Set Rs = Nothing
    

    If you have more than 1 bind variable, then add another .CreateParameter construct and append it to the Parameters collection.

    The logic is similar for other kind of commands (DML statements, calling stored procedures and functions). I can provide you with examples if necessary.

Answers

  • GregV
    GregV Member Posts: 3,086 Gold Crown

    Hi,

    Why would you do that in PL/SQL? A VBA macro seems more appropriate to do this kind of things. If you need input data from the database, then VBA can connect to the Oracle DB through ADO components using the Oracle OLE DB driver.

  • Vincelogic
    Vincelogic Member Posts: 32 Bronze Badge

    Thanks GregV, the main reason is that PL/SQL is the primary skillset for our team, and any suggestion about how we could leverage OLE DB Driver based on PL/SQL skill to implement this?

  • GregV
    GregV Member Posts: 3,086 Gold Crown

    Ok but pl/sql resides in the database, so it's best used to process data within the database, this data could be from tables, files on the db server, webservices, etc.

    Using PL/SQL to perform Word/Excel purely related-tasks is not the way to go. The OLE DB driver will let you connect to the Oracle DB thus interact with it (DML statements, calling procedures/functions). So the purpose is to either push data from the document/worksheet to the DB or retrieve data from the DB to feed some variables.

    Amending text, inserting images, moving stuff, ... is better dealt with some VBA code, IMO.

  • GregV
    GregV Member Posts: 3,086 Gold Crown
    Answer ✓

    No problem. If you need help with VBA interacting with the Oracle Database, I can help as I've done it before.

  • Vincelogic
    Vincelogic Member Posts: 32 Bronze Badge
    edited Feb 15, 2021 6:23PM

    Thanks, and what's your suggestion to call the VBA codes to interact with Oracle DB, I mean for the PL/SQL, what the execution entry point will be?

  • GregV
    GregV Member Posts: 3,086 Gold Crown
    edited Feb 15, 2021 7:13PM Answer ✓

    Once you've installed the Oracle OLE DB driver on the machine the Excel file resides, you need to reference it from the "Tools -> References" menu of the VBA editor menu:

    In the path I can see the directory where I installed the OLE DB driver. You also need to tick the ADO Library driver if it's not the case:

    Once this is done, the way to interact with the DB is pretty simple.

    First, you need to connect to the DB:

    Dim Conn As ADODB.Connection
    Dim vHost As String
    Dim vPort As Integer
    Dim vService As String
    Dim vUser As String
    Dim vPwd As String
    On Error GoTo ErreurConn
    With Conn
       .ConnectionString = "PROVIDER=OraOLEDB.Oracle;USER ID=" & vUser & ";PASSWORD=" & vPwd & ";DATA SOURCE=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST = " & vHost & ")(PORT = " & vPort & ")))(CONNECT_DATA =(SERVICE_NAME = " & vService & ")))"
       .Open
    End With
    

    The variables needed to connect can be set in the code, or they can be inputs from the worksheet. For example:

    vHost = Range("B1").value
    vPort = Range(« B2 »).value
    vService = Range(« B3 »).value
    vUser = Range(« B5 »).value
    vPwd = Range(« B6 »).value
    

    If you want to test that the connection is ok, you can do:

    If Conn.State = 1 Then
      MsgBox "Connection Successful"
       Conn.Close
       Set Conn = Nothing
    End If
    

    Now any command you want to pass can use this connection. For example, if you want to perform a SELECT statement with bind variables, you do as follows:

    'variables
    Dim vQuery As String
    Dim SQLCommand As ADODB.Command
    Dim Param As ADODB.Parameter
    Dim Rs As ADODB.Recordset
    
    vQuery = "SELECT * FROM T WHERE id = :id"
    
     
    Set SQLCommand = New ADODB.Command
    With CommandSQL
      .ActiveConnection = Conn
      .CommandText = vQuery
      .CommandType = adCmdText 
    
      'value for the bind variable
      Set param = .CreateParameter("id", adNumeric, adParamInput)
      param.value = 1
     .Parameters.Append param 
    
      Set Rs = .Execute
    End With
    
    'Browse the recordset
    Do While Not Rs.EOF
    
        'Retrieve the column values
    
        vVal = Rs.Fields("val")
        vCreationDate = Rs.Fields("creation_date") 
    
        Rs.MoveNext
    Loop
    
    Set Rs = Nothing
    

    If you have more than 1 bind variable, then add another .CreateParameter construct and append it to the Parameters collection.

    The logic is similar for other kind of commands (DML statements, calling stored procedures and functions). I can provide you with examples if necessary.

  • Vincelogic
    Vincelogic Member Posts: 32 Bronze Badge
    edited Mar 10, 2021 3:27AM

    Thanks, Greg, appreciate your kind and professional advice, we will try this if we decide on implementing the VBA approach!