ADO/VBA examples for Oracle

Anonymous block

The following example executes an anonymous PL/SQL block that takes a string (varchar2) and a number (double) as input parameters, creates a string from these values and then passes the result as an out parameter back to the caller.

' \lib\runVBAFilesInOffice\runVBAFilesInOffice.vbs -word anonymous_block -c go rene rene ORA_MANUALLY_CREATED

option explicit

sub go(dbUser as string, dbPassword as string, dbName as string) ' {

' ADODB
'
' call addReference(application, "{2A75196C-D9EB-4129-B803-931327F72D5C}")
'
'    Alternatively, when already testing in Excel, without runVBAFilesInOffice:
'
' Call application.workbooks(1).VBProject.references.addFromGuid("{2A75196C-D9EB-4129-B803-931327F72D5C}", 0, 0)
'

  dim cn as ADODB.connection
  set cn = openConnection(dbUser, dbPassword, dbName)

  dim plsql as string

' Obviously, a block cannot start with declare
'
' See also http://stackoverflow.com/questions/2373401/with-ado-how-do-i-call-an-oracle-pl-sql-block-and-specify-input-output-bind-var
'
  plsql =         "begin "

  plsql = plsql & "declare"
  plsql = plsql & "  string_in varchar2(100) := ?;"
  plsql = plsql & "  num_in  number := ?;"
  plsql = plsql & "  string_out varchar2(100); "
  plsql = plsql & "begin null;"
  plsql = plsql & "  string_out := string_in || to_char(trunc(sysdate)+ num_in, 'dd.mm.yyyy');"
  plsql = plsql & "  ? := string_out;"
  plsql = plsql & "end;"

  plsql = plsql & "end;"

  dim cm as ADODB.command
  set cm = new ADODB.command
  set cm.activeConnection = cn
  cm.commandText = plsql
  cm.commandType = adCmdText
    cm.parameters.append cm.createParameter(, adVarChar, adParamInput , 100, "Three days from now is: ")
    cm.parameters.append cm.createParameter(, adDouble , adParamInput ,    ,  3)
    cm.parameters.append cm.createParameter(, adVarChar, adParamOutput, 100)

  cm.Execute , , adExecuteNoRecords

  MsgBox (cm.parameters(2))

End Sub ' }

private function openConnection(dbUser as string, dbPassword as string, dbName as string) as ADODB.connection ' {

  dim cn as    ADODB.connection
  set cn = new ADODB.connection

  cn.open ( _
     "User ID="     & dbUser       & _
    ";Password="    & dbPassword   & _
    ";Data Source=" & dbName       & _
    ";Provider=MSDAORA.1")

  set openConnection = cn

  exit function

error_handler:
  if   err.number = -2147467259 then
              msgBox("Oracle error while opening connection: " & err.description)
  else 
              msgBox(err.number & " " & err.description)
  end if

end function ' }

Github repository about-adodb, path: /Oracle/anonymous_block.bas

Calling a stored procedure

This example calls a stored procedure.

First, the procedure to be called must be installed in an Oracle database:

drop table     tq84_table;
drop procedure tq84_procedure;

create table tq84_table (a number);

create procedure tq84_procedure(a in number, b in number) as
begin

  for i in  a .. b loop
    insert into tq84_table values (i);
  end loop;

end;
/

Github repository about-adodb, path: /Oracle/stored_procedure.sql

Then, the procedure can be called:

' \lib\runVBAFilesInOffice\runVBAFilesInOffice.vbs -word stored_procedure -c go rene rene ORA_MANUALLY_CREATED
'
' Prior run
'    stored_procedure.bas
' for this example.
'
' ADODB
' call addReference(application, "{2A75196C-D9EB-4129-B803-931327F72D5C}") 

option explicit

sub go(dbUser as string, dbPassword as string, dbName as string) ' {


  dim cn as ADODB.connection
  set cn = openConnection(dbUser, dbPassword, dbName)

  dim cm as ADODB.command
  set cm = new ADODB.command
  set cm.activeConnection = cn
  cm.commandText = "tq84_procedure"
  cm.commandType = adCmdStoredProc

  cm.parameters.append cm.createParameter(, adDouble, adParamInput,, 10)
  cm.parameters.append cm.createParameter(, adDouble, adParamInput,, 20)

  cm.execute,,adExecuteNoRecords

  msgBox ("Check tq84_table, it has been filled with values")

end sub ' }

private function openConnection(dbUser as string, dbPassword as string, dbName as string) as ADODB.connection ' {

  on error goto error_handler

  dim cn as    ADODB.connection
  set cn = new ADODB.connection

  cn.open ( _
     "User ID="     & dbUser       & _
    ";Password="    & dbPassword   & _
    ";Data Source=" & dbName       & _
    ";Provider=MSDAORA.1")

  set openConnection = cn

  exit function

error_handler:
  if   err.number = -2147467259 then
              msgBox("Oracle Fehler beim �ffnen der Datenbankverbindung: " & err.description)
  else 
              msgBox(err.number & " " & err.description)
  end if

end function ' }

Github repository about-adodb, path: /Oracle/stored_procedure.bas

Ref cursor

This example attempts to demonstrate how ref cursors can be accessed or used with ADODB.

First, we need to create some objects in the database:

create table refcursor_test_tbl (
  site_id         number(2)       not null,
  location        varchar2(12)
);


insert into refcursor_test_tbl values (1, 'Paris'     );
insert into refcursor_test_tbl values (2, 'Boston'    );
insert into refcursor_test_tbl values (3, 'London'    );
insert into refcursor_test_tbl values (4, 'Stockholm' );
insert into refcursor_test_tbl values (5, 'Ottawa'    );
insert into refcursor_test_tbl values (6, 'Washington');
insert into refcursor_test_tbl values (7, 'La'        );
insert into refcursor_test_tbl values (8, 'Toronto'   );
insert into refcursor_test_tbl values (3, 'Zuerich'   );


create or replace package tq84_refcursor_test_pck as -- {
     
  procedure proc_1 (
          Pmyid      in number,
          Pmycursor  out sys_refcursor, -- use cursor
          x          out sys_refcursor,
          Perrorcode out number);    
         
  procedure proc_2 (
          Pmyquery   in  varchar2,
          Pmycursor  out sys_refcursor,
          Perrorcode out number);

end tq84_refcursor_test_pck; -- }
/


create or replace package body tq84_refcursor_test_pck as -- {

   procedure proc_1 (
       Pmyid      in number,
       Pmycursor  out sys_refcursor, -- use cursor
       x          out sys_refcursor, -- use cursor
       Perrorcode out number) is
   begin
          Perrorcode := 0;
          -- Open the ref cursor
          -- Use Input Variable "PmyID" as part of the query.

     open pmycursor for
          select 'foo' foo, location
          from refcursor_test_tbl
                where site_id = pmyid;

     open x for select * from refcursor_test_tbl where site_id < 5;
            
   exception when others then
       Perrorcode := SQLCODE;  
   end proc_1; 
    
   procedure proc_2 (
        Pmyquery in varchar2,
        Pmycursor out sys_refcursor,
        Perrorcode out number) 
   is 
   begin
         Perrorcode := 0;
         -- Open the REF CURSOR 
         -- This procedure uses a query
         -- which is passed in as a parameter.
  
         open pmycursor for pmyquery;
    
   exception when others then
           Perrorcode := sqlcode;
   end proc_2;

end tq84_refcursor_test_pck; -- }
/

Github repository about-adodb, path: /Oracle/ref_cursor_db_objects.sql

Then, we can use the ref cursor from VBA:

' \lib\runVBAFilesInOffice\runVBAFilesInOffice.vbs -word ref_cursor -c go rene rene ORA_MANUALLY_CREATED

'  Priorly run
'    ref_cursor_db_objects.sql
'  for this example.
'  
' 
' ADODB
' call addReference(application, "{2A75196C-D9EB-4129-B803-931327F72D5C}") 

option explicit


sub go(dbUser as string, dbPassword as string, dbName as string) ' {


  dim cn as ADODB.connection
  set cn = openConnection(dbUser, dbPassword, dbName)


  dim rs as ADODB.recordSet

  dim cm as ADODB.command
  set cm = new ADODB.command
  set cm.activeConnection = cn

  cm.commandText = "tq84_refcursor_test_pck.proc_1"

  cm.commandType = adCmdStoredProc
  cm.parameters.append cm.createParameter("justAName", adDouble, adParamInput,, 3)
  cm.parameters.append cm.createParameter("justAName", adVarChar, adParamOutput,10)

  set rs = cm.execute ' , , adExecuteNoRecords

  do while not rs.eof
     
     dim i as long
     for i = 0 to rs.fields.count -1
         msgBox i & ": " & rs.fields(i)
     next
     rs.moveNext

  loop

end sub ' }

private function openConnection(dbUser as string, dbPassword as string, dbName as string) as ADODB.connection ' {

  on error goto error_handler

  dim cn as    ADODB.connection
  set cn = new ADODB.connection

  cn.open ( _
     "User ID="     & dbUser       & _
    ";Password="    & dbPassword   & _
    ";Data Source=" & dbName       & _
    ";Provider=MSDAORA.1")

  set openConnection = cn

  exit function

error_handler:
  if   err.number = -2147467259 then
              msgBox("Oracle Error while opening the database: " & err.description)
  else 
              msgBox(err.number & " " & err.description)
  end if

end function ' }

Github repository about-adodb, path: /Oracle/ref_cursor.bas