Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL package could manipulate Microsoft Word/Excel?

VincelogicFeb 9 2021

Is there any PL/SQL package that in Microsoft Word / Excel, and do operations such as the following tasks:
Parse and go through the whole document
Find a specific string, (ie. A), and replaces it with another string (ie. B)
Add an image
Operate the word table: create a new line, etc.

This post has been answered by GregV on Feb 15 2021
Jump to Answer

Comments

GregV

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

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

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.

Vincelogic

I got what you mean, thanks, Greg, appreciate the advice!

GregV
Answer

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

Marked as Answer by Vincelogic · Feb 16 2021
Vincelogic

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

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:
image.pngIn 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:
image.pngOnce 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

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

1 - 8

Post Details

Added on Feb 9 2021
8 comments
262 views