SQL & PL/SQL

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.

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?

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.

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

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?

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.