René Nyffenegger's collection of things on the web
Most wanted - Feedback -
 

René Nyffenegger on Oracle

Hello! I am René Nyffenegger and this is the site where I write articles on Oracle. I hope that, if time permits and I am not on holidays, I can write at least one article per week. An rss feed can be found here.

November 14, 2009: On DAO, ODBC and Oracle

Here are a few notes on DAO and ODBC with Oracle.
I have conducted the experiment on a freshly installed Windows XP Machine. In order to run the VBA code below, I also installed Excel in order to have a VBA environment where I can dim variables; that is, I could also have used Visual Basic Script, but its disadvantage is that I have to create «objects» with CreateObject instead of set obj_var = new obj_type. Also, Excel's VBA environment is aware of the methods and constants (aka intellisense) for dao and the like which was a bit beneficial to me since I usually don't do any DAO.
So, first I downloaded Oracle's Instant Client for Microsoft Windows (32-bit) from Oracle Technology Network Instant Client page. Actually, to be more precise, I downloaded the following two zip files: instantclient-basic-win32-11.1.0.7.0.zip and instantclient-odbc-win32-11.1.0.7.0.zip. The former is needed for anything related to the Instant Client (as it contains for example oci.dll) while the latter has the ODBC related stuff.
Both zip files were extracted into C:\oracle\instantclient_11_1.
After the extraction, I installed ODBC for Oracle:
C:\>cd /d c:\oracle\instantclient_11_1

C:\oracle\instantclient_11_1>odbc_install.exe
Oracle ODBC Driver is installed successfully.
In order to be able to connect to an Oracle Instance, I needed a net service name which is configured in the tnsnames.ora file. Here, I chose ora11 as my net service name:
c:\oracle\instantclient_11_1>notepad c:\oracle\tnsnames.ora
tnsnames.ora
ora11 = (
  DESCRIPTION = (
    ADDRESS_LIST = (
      ADDRESS = (PROTOCOL=TCP)
                (HOST=xxx.xx.x.xx)
                (PORT=1521)
      )
   )(
      CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=newora11)
  )
)

Setting the required environment variables

In order to make the ODBC installation complete, I need to add the TNS_ADMIN environment variable and to point the PATH variable to the directory where the oci.dll is located. This is done by the following VBA script:
add_env_vars.bas
' See http://support.microsoft.com/kb/q184608/
' ---
' Must be run with administrator privileges on Vista
'

option explicit

private const REG_SZ             =          1
private const REG_EXPAND_SZ      =          2
private const HKEY_LOCAL_MACHINE = &H80000002

' Needed for SendMessageTimeout
private const HWND_BROADCAST   = &HFFFF
private const WM_SETTINGCHANGE = &H1A
private const SMTO_ABORTIFHUNG = &H2

private declare function RegCreateKey Lib "advapi32.dll" Alias _
     "RegCreateKeyA"                                           _
    (byVal hKey       as long                                , _
     byVal lpSubKey   as string                              , _
           phkResult  as long                                  _
) as long

private declare function RegSetValueEx Lib "advapi32.dll" Alias _
   "RegSetValueExA"                                             _
  (byVal hKey         as long                                 , _ 
   byVal lpValueName  as string                               , _
   byVal Reserved     as long                                 , _
   byVal dwType       as long                                 , _
         lpData       as Any                                  , _
   byVal cbData       as long                                   _
)  as long

private declare function RegQueryValueEx Lib "advapi32.dll" alias _
  "RegQueryValueExA"                                                _
 (byVal  hKey          as   long , _
  ByVal  lpszValueName as string, _
                   ByVal lpdwRes as long, _
                   lpdwType as long, _
                   ByVal lpDataBuff as string, _
                   nSize as long) as long

private declare function RegCloseKey Lib "advapi32.dll"         _
       (byVal hKey as long                                      _
)  as long


private declare function SendMessageTimeout lib "User32.dll" alias _
   "SendMessageTimeoutA"                                           _
  (byVal hWnd         as long                                 ,    _
   byVal msg          as long                                 ,    _
         wParam       as any                                  ,    _
   byVal lParam       as string                               ,    _
   byVal fuFlags      as long                                 ,    _
   byVal uTimeout     as long                                 ,    _
         lpdwResult   as long                                      _
) as long


public sub add_env_vars()

   dim TNS_Admin_path as string
   dim OCI_DLL_path   as string

   dim lResult        as long
   dim hKeyHandle     as long

   TNS_Admin_path = "c:\Oracle"
   OCI_DLL_path   = "c:\oracle\instantclient_11_1"


   lResult = RegCreateKey(                                                   _
             HKEY_LOCAL_MACHINE                                            , _
            "System\CurrentControlSet\Control\Session Manager\Environment" , _
             hKeyHandle                                                      _
   )


   lResult = RegSetValueEx(      _
             hKeyHandle        , _ 
            "TNS_ADMIN"        , _
             0&                , _
             REG_SZ            , _
       byVal TNS_Admin_path    , _ 
         Len(TNS_Admin_path)     _
   )


   ' get current value for %PATH%

   ' First, length of current %PATH%

   dim lenPath as long

   lResult = RegQueryValueEx(        _
             hKeyHandle       , _
            "PATH"            , _
             0                , _
             REG_EXPAND_SZ    , _
             0                , _
             lenPath )
             
    dim path as string
        path = string(lenPath, chr(0))

   ' then: actual value of PATH
   lResult = RegQueryValueEx(   _
             hKeyHandle       , _
            "PATH"            , _
             0                , _
             REG_EXPAND_SZ    , _
             path             , _
             lenPath )

   ' chop trailing chr(0) from path
   path = left$(path, lenPath - 1)

   ' append path to oci.dll
   path = path & ";" & OCI_DLL_path

   lResult = RegSetValueEx(     _
             hKeyHandle       , _ 
            "PATH"            , _
             0&               , _
             REG_EXPAND_SZ    , _
       byVal path             , _ 
         Len(path)              _
   )

   lResult = RegCloseKey(hKeyHandle)

       dim dwReturnValue as long

   ' Propagate changes in environment variables
   ' http://support.microsoft.com/kb/104011
   lResult = SendMessageTimeout(     _
               HWND_BROADCAST    ,   _
               WM_SETTINGCHANGE  ,   _
               0                 ,   _
              "Environment"      ,   _
               SMTO_ABORTIFHUNG  ,   _
               5000              ,   _
               dwReturnValue         _
    )

    
end sub
In order to execute this script in Excels VBA environment, I created another (visual basic script) that basically opens excel and loads my desired script and executes it:
runVBAFilesInExcel.vbs
' In order to prevent a "Programmatic access to Visual Basic Project is not trusted":
'   Tools -> Macro -> Security... -> Trusted Publishers -> check "Trust access to Visual Basic Project"
'
' Or
'   /path/to/excel.exe /unregserver
'   /path/to/excel.exe /regserver

option explicit

dim excel
dim workbook
dim vb_editor
dim shell_obj
dim cur_dir
dim args
dim function_name_to_call
dim vbs_name_to_import

Set args = WScript.Arguments

if args.count = 0 then
  WScript.echo ("no argument specified")
  WScript.Quit
end if

function_name_to_call = args(0)
vbs_name_to_import    = args(0) & ".bas"

set shell_obj = CreateObject("WScript.Shell")

cur_dir = shell_obj.currentDirectory

set excel = CreateObject("Excel.Application")
excel.visible = true
set workbook = excel.Workbooks.Add

set vb_editor = excel.vbe
dim vb_comps
set vb_comps = vb_editor.ActiveVBProject.VBComponents

vb_comps.Import(cur_dir & "\" & vbs_name_to_import)

' Type Libs
' --------
'   Find them in the registry under HKEY_CLASSES_ROOT\TypeLib...
'
' Microsoft Access 11.0 Object Library
'call addReference(workbook, "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}")
' Microsoft DAO 3.6 Object Library 
call addReference(workbook, "{00025E01-0000-0000-C000-000000000046}")
' Microsoft Scripting Runtime
call addReference(workbook, "{420B2830-E718-11CF-893D-00A0C9054228}")

excel.Run(function_name_to_call)
set excel = nothing


sub addReference(workbook, guid)

  on error resume next

  dim refs
  set refs = workbook.VBProject.References
  refs.AddFromGuid guid, 0, 0

  if err.number <> 0 then

     if err.number <> 32813 then
          msgBox "Error: " & err.number & vbNewLine & err.description
     end if

  end if
end sub
This second script (runVBAFilesInExcel.vbs) takes one parameter which is the name of the function to call and the name of the file in which the function is found without the .bas suffix. Thus, I can execute add_env_vars with a:
c:\oracle\instantclient_11_1>runVBAFilesInExcel.vbs add_env_vars

Setting up the ODBC Datasource

Now, I am ready to set up my ODBC datasource. Again, I do that with a VBA script:
create_odbc_datasource.bas
' See http://support.microsoft.com/kb/q184608/
' ---
' Must be run with administrator privileges on Vista
'

option explicit

private const REG_SZ             =          1
private const HKEY_LOCAL_MACHINE = &H80000002

private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
     "RegCreateKeyA"                                           _
    (byVal hKey       As long                                , _
     byVal lpSubKey   As string                              , _
           phkResult  As long                                  _
) as long

private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
   "RegSetValueExA"                                             _
  (byVal hKey         as long                                 , _ 
   byVal lpValueName  As string                               , _
   byVal Reserved     As long                                 , _
   byVal dwType       As long                                 , _
         lpData       As any                                  , _
   byVal cbData       As long                                   _
)  as long

private Declare Function RegCloseKey Lib "advapi32.dll"         _
       (byVal hKey As long                                      _
)  as long


public sub create_odbc_datasource()
     
   dim ODBCName       as string
   dim description    as string
   dim driverPath     as string
   dim driverName     as string
   dim lastUser       as string
   dim TNSName        as string

   dim lResult        as long
   dim hKeyHandle     as long


   ODBCName       = "DAOTest"
   TNSName        = "ora11"
   Description    = "Dynamically created datasource for Oracle"
   DriverPath     = "c:\oracle\instantclient_11_1\sqora32.dll"
   user_id        = "RENE"
   DriverName     = "Oracle in instantclient11_1" ' See HKLM\SOFTWARE\ODBC\ODBCINST.INI

   'Create the new DSN key.

   lResult = RegCreateKey(                                _
             HKEY_LOCAL_MACHINE                         , _
            "SOFTWARE\ODBC\ODBC.INI\"  & ODBCName       , _
             hKeyHandle                                   _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _ 
            "ServerName"                                , _
             0&                                         , _
             REG_SZ                                     , _
       byVal TNSName                                    , _ 
         Len(TNSName)                                     _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _
            "Description"                               , _
             0&                                         , _
             REG_SZ                                     , _
       byVal Description                                , _
         Len(Description)                                 _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _
            "Driver"                                    , _
             0&                                         , _
             REG_SZ                                     , _
       byVal DriverPath                                 , _
         Len(DriverPath)                                  _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _ 
            "UserID"                                    , _
             0&                                         , _
             REG_SZ                                     , _
       byVal user_id                                    , _
         Len(user_id)                                     _
   )

'  lResult = RegSetValueEx(                               _
'            hKeyHandle                                 , _ 
'           "Server"                                    , _
'            0&                                         , _
'            REG_SZ                                     , _
'      byVal Server                                     , _
'        Len(Server)                                      _
'  )

   lResult = RegCloseKey(hKeyHandle)

   lResult = RegCreateKey(                                _ 
            HKEY_LOCAL_MACHINE                          , _
           "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"   , _
            hKeyHandle                                    _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _
             ODBCName                                   , _
             0&                                         , _
             REG_SZ                                     , _
       byVal DriverName                                 , _
         Len(DriverName)                                  _
   )

   lResult = RegCloseKey(hKeyHandle)
    
end sub
I can verify the success by opening the «ODBC control panel», and going to System DSN:
C:\oracle\instantclient_11_1>odbccp32.cpl

Running the test

dao_test.bas
option explicit

sub dao_test()

  dim ws            as DAO.workspace
  dim db            as DAO.database

  dim strConnection as string
  dim odbcName      as string
  dim userName      as string
  dim password      as string

  odbcName     = "DAOTest"
  UserName     = "rene"
  password     = "rene"

  ' you need to create a DSN in  Control Panel ODBC

      strConnection =    _
     "ODBC" &            _
    ";DSN=" & odbcName & _
    ";UID=" & UserName & _
    ";PWD=" & password

  set ws=DBEngine.Workspaces(0)
  set db=ws.OpenDatabase("", false, false, strConnection)

  msgBox("Database opened")

  call create_table    (db)
  call add_column      (db)
  call add_primary_key (db)
  call add_index       (db)
  call insert_records  (db)
  call autoincrement_01(db)
  call autoincrement_02(db)
  call transaction     (db)

' remove_table()

end sub

private sub create_table(db as DAO.Database)

  dim table_def   as dao.tableDef
  dim table_field as dao.Field

  set table_def = db.createTableDef("some_table")

  ' First column
  set table_field = table_def.createField("theAutonumberField", dbLong)
      table_field.attributes = dbAutoIncrField
  table_def.Fields.append table_field

  ' Second column
  set table_field = table_def.createField("aDateColumn"       , dbDate)
  table_def.Fields.append table_field

' ' Third column
' set table_field = table_def.createField("aCurrencyColumn"   , dbCurrency)
' table_def.Fields.append table_field
'
' Oracle seems not to support dbCurrency

  ' Fourth column
  set table_field = table_def.createField("aTextColumn"       , dbText, 20)
  table_def.Fields.append table_field
  
  ' 
  db.tableDefs.append table_def

  set table_field = nothing
  set table_def   = nothing

  msgBox ("Table created")

end sub

private sub add_column(db as DAO.Database)

  dim table_def   as dao.tableDef
  dim table_field as dao.Field

  set table_def = db.tableDefs("some_table")

  set table_field = table_def.createField("fifthColumn", dbText, 20)
  table_def.fields.append table_field

  set table_field = nothing
  set table_def   = nothing

  msgBox ("Column added")

end sub


private sub add_primary_key(db as DAO.Database)

  dim table_def   as dao.tableDef
' dim table_field as dao.field
  dim ix          as dao.index

  set table_def = db.tableDefs("some_table")

  set ix        = table_def.createIndex("pk_some_table")
  ix.fields.append ix.createField("theAutonumberField")
  ix.unique     = false ' ???

' can obviously not be set in Oracle
' ix.primary    = true
  ix.primary    = false

  table_def.indexes.append ix

  set ix          = nothing
  set table_def   = nothing

  msgBox("Primary key added")
end sub

private sub add_index(db as DAO.Database)

  dim table_def   as dao.tableDef
  dim ix          as dao.index

  set table_def = db.tableDefs("some_table")

  set ix        = table_def.createIndex("ix_unique")
  ix.fields.append ix.createField("aTextColumn")
  ix.unique     = true
  ix.primary    = false ' default ?

  table_def.indexes.append ix

  set ix          = nothing
  set table_def   = nothing

  msgBox ("Index added")
end sub

private sub insert_records(db as DAO.Database)

  dim rs as DAO.recordSet

' dbOpentable seems not to be supported in Oracle
' set rs = db.openRecordSet("some_table", dbOpentable)
  set rs = db.openRecordSet("some_table")
 
  call insert_a_record (rs, #08/28/1970#, 10.02, "some text", "more text")
  call insert_a_record (rs, #01/01/2001#, 11.11, "one"      , "Apple"    )
  call insert_a_record (rs, #02/02/2002#, 22.22, "two"      , "Pear"     )
  call insert_a_record (rs, #08/28/1970#, 10.02, "some text", "more text") ' Same record as first, won't be inserted.

end sub

private sub insert_a_record (              _
  rs           as dao.recordSet ,  _
  date_col     as date          ,  _
  curr_col     as currency      ,  _
  txt_col      as string        ,  _
  col_5        as string) 

  on error goto insert_fail

    rs.addNew
  
    rs("aDateColumn"    ) = date_col
'   rs("aCurrencyColumn") = curr_col
    rs("aTextColumn"    ) = txt_col
    rs("fifthColumn"    ) = col_5
  
    rs.update

    msgBox ("Record inserted")

    exit sub

  insert_fail:
    dim err_desc as string

    err_desc = "record" &              vbCrLf & _
               "  "     & date_col  &  vbCrLf & _
               "  "     & curr_col  &  vbCrLf & _
               "  "     & txt_col   &  vbCrLf & _
               "could not be inserted" 

    select case err.number
      case 3022
        err_desc = err_desc & vbCrLf & "because a unique index was validated"

      case else
        err_desc = err_desc & vbCrLf & "Error No: "   & err.number & vbCrLf & _
                                       "Error Text: " & err.description
    end select

    msgBox err_desc

end sub


private sub select_record()

  dim sqlText as string

  ' "  aCurrencyColumn, "     & _

  sqlText =                     _
    "select "                 & _
    "  theAutonumberField, "  & _
    "  aDateColumn, "         & _
    "  aTextColumn, "         & _
    "  fifthColumn "          & _
    "from "                   & _
    "  some_table"

' msgBox sqlText

  set rs = db.openRecordSet(sqlText, dbOpenDynaset)
   
  do until rs.eof

'           rs("aCurrencyColumn"   ) & vbCrLf & _
     
     msgBox rs("theAutonumberField") & vbCrLf & _
            rs("aDateColumn"       ) & vbCrLf & _
            rs("aTextColumn"       ) & vbCrLf & _
            rs("fifthColumn"       )
     
     rs.moveNext
  loop

end sub

private sub autoincrement_01(db as DAO.Database)

  ' insert a biiiig autoincrement (autonumber)
  ' into the table

  dim rs as DAO.RecordSet

' set rs = db.openRecordSet("some_table", dbOpentable)
  set rs = db.openRecordSet("some_table")

  rs.addNew
  
    rs("theAutonumberField") = 999999999
    rs("aDateColumn"       ) = #10/31/1517#
'   rs("aCurrencyColumn"   ) = 68.65
    rs("aTextColumn"       ) = "biiiig autonumber"
    rs("fifthColumn"       ) = "Chanel, no 5"
  
  rs.update

  set rs = nothing

end sub


public sub autoincrement_02(db as DAO.Database)

  ' insert a biiiig autoincrement (autonumber)
  ' into the table

  dim rs as dao.recordSet

' set rs = db.openRecordSet("some_table", dbOpentable)
  set rs = db.openRecordSet("some_table")

  rs.addNew
  
'   rs("theAutonumberField") = ??? (Deliberatly omitted)
    rs("aDateColumn"       ) = #11/01/1517#
'   rs("aCurrencyColumn"   ) = 100.01
    rs("aTextColumn"       ) = "autonumber, 2nd part"
    rs("fifthColumn"       ) = "still Chanel, no 5"

    msgBox "The autonumber field to be inserted is: " & rs!theAutonumberField

  rs.update

  set rs = nothing

end sub

private sub transaction(db as DAO.Database)

  call do_transaction(db, false)
  call do_transaction(db, true )

end sub

private sub do_transaction(db as dao.Database, commit as boolean)

  dim wrk    as Dao.Workspace
  set wrk =  DBEngine.Workspaces(0)

  call wrk.beginTrans()
  call select_count(db, "At begin of transaction")

  ' Note the [] instead of the "" in order to prevent a 3450 incomplete query clause.
  db.Execute("delete from [some_table] where [aTextColumn] in ('one', 'two')")
  call select_count(db, "After delete")

  if commit then
    call wrk.commitTrans()
    call select_count(db, "After Commit")
  else
    call wrk.rollback()
    call select_count(db, "After Rollback")
  end if

  set wrk = nothing

end sub

private sub select_count (db as Dao.Database, txt as string)

  dim rs as dao.recordSet

  set rs = db.OpenRecordSet("select count(*) as cnt from some_table")
  msgBox txt & vbCrLf & "Count: " & rs("cnt")

  set rs = nothing

end sub
Running the test, I noticed the following strange behaviour:
  • After opening the database, it takes several seconds to create the table, and I wonder if this is just my environment.
  • The field theAutonumberField is neither a primary key, nor not null nor does dao automatically insert any value into it.

October 8, 2009: On XML Queries

I've been playing with Oracle's implementation for XQueries recently. Here are a few queries to demonstrate some features of XQueries. By no means are they complete, but they're meant to give a quick overwiev on some possibilities.
First, I create an XDB resource for the XML document (named an_xml_doc.xml) on which I want to execute the XQueries:
set serveroutput on size 10000

declare
  success boolean;
begin

  success := dbms_xdb.createResource(
    -- Name of resource:
    '/public/an_xml_doc.xml',
    -- Document content:
    '<numbers>
      <number id= "1">one</number>
      <number id= "2">two</number>
      <number id="10">ten</number>
      <number id= "6">six</number>
    </numbers>');

  if success then
     dbms_output.put_line('success');
  else
     dbms_output.put_line('no success');
  end if;

end;
/
success
Extract all <number> nodes under a <numbers> node whose id is equal to 2.
select XMLQuery ('doc("/public/an_xml_doc.xml")/numbers/number[@id = 2]'
                 returning content) 
  from dual;
<number id="2">two</number>
Same thing, but use a variable and explicitely return the variable's value:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number[@id = 2]
                  return $num'
                  returning content) 
  from dual;
<number id="2">two</number>
Return all <number> nodes under a <numbers> node without the id restriction:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number
                  return $num'
                  returning content) 
  from dual;
<number id="1">one</number><number id="2">two</number><number id="10">ten</numbe
It turns out that SQL*Plus cuts the returned string at position 80. This is the default maximum displayed length for longs. So, we have to increase the maximum length:
set long 10000
Same query...
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number
                  return $num'
                  returning content) 
  from dual;
... but now with entire result:
<number id="1">one</number><number id="2">two</number><number id="10">ten</numbe
r><number id="6">six</number>
I am only interested in the nodes' content (that is, without the <number>...</number>). The text() operator comes in handy:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number
                  return $num/text()'
                  returning content) 
  from dual;
onetwotensix
The text() operator can also be placed elsewhere:
select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number/text()
                  return $num'
                  returning content) 
  from dual;
onetwotensix
Unfortunately, the boundaries between the words are not clear. So, I use the for .. in construct and append a whitespace to each word:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number/text()
                  return ($num, " ")'
                  returning content) 
  from dual;
one  two  ten  six
Ordering the returned value:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/text()
                  return ($num/text()," ")'
                  returning content) 
  from dual;
one  six  ten  two
Still ordering the returned value, but returning the value of the attribute id instead of the node's content:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/text()
                  return ($num/text()/@id," ")'
                  returning content) 
  from dual;
1  6  10  2
I want the result ordered by the id, not by the text value:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/@id
                  return ($num/text()," ")'
                  returning content) 
  from dual;
one  ten  two  six
This is, however, not exactly what I wanted. Although the query ordered the result by the id, it used the id's ascii value, not the numerical value (that is "1" < "10" < "2" < "6").
So, I am going to force using the numerical value by applying the number operator:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by number($num/@id)
                  return ($num/text()," ")'
                  returning content) 
  from dual;
one  two  six  ten
Finally, I only want values whose id is greater than 5:
select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number 
                  where number($num/@id) > 5
                  order by number($num/@id)
                  return ($num/text()," ")'
                  returning content) 
  from dual;
six  ten

September 29, 2009: On summing up values in nodes of a hierarchical query

Here's a table that stores hierarchical information. Its hierarchical because its column id_p (standing for id of parent) references the column id of the same table.
create table hierarchical_values (
  id    number   primary key,
  id_p           references hierarchical_values,
  info  varchar2(10),
  amt   number
);
This table is filled with some data. Note that the column amt is only filled for leaves.
insert into hierarchical_values values ( 1, null, 'a'                    , null);
insert into hierarchical_values values ( 4,    1,   'ad'                 , null);
insert into hierarchical_values values (10,    4,     'adj'              , null);
insert into hierarchical_values values (32,   10,         'ADJ1'         ,    6);
insert into hierarchical_values values (33,   10,         'ADJ2'         ,    7);
insert into hierarchical_values values (34,   10,         'ADJ3'         ,    3);
insert into hierarchical_values values (35,   10,         'ADJ4'         ,    1);
insert into hierarchical_values values (11,    4,     'adk'              , null);
insert into hierarchical_values values (29,   11,         'ADK1'         ,    3);
insert into hierarchical_values values (30,   11,         'ADK2'         ,    4);
insert into hierarchical_values values (31,   11,         'ADK3'         ,    5);
insert into hierarchical_values values (12,    4,     'adl'              , null);
insert into hierarchical_values values (21,   12,       'adlu'           , null);
insert into hierarchical_values values (27,   21,         'ADLU1'        ,    1);
insert into hierarchical_values values (28,   21,         'ADLU2'        ,    2);
insert into hierarchical_values values (22,   12,       'adlv'           , null);
insert into hierarchical_values values (36,   22,         'ADLV1'        ,    2);
insert into hierarchical_values values (37,   22,         'ADLV2'        ,    7);
insert into hierarchical_values values (38,   22,         'ADLV3'        ,    1);
insert into hierarchical_values values (39,   22,         'ADLV4'        ,    4);
insert into hierarchical_values values ( 5,    1,   'ae'                 , null);
insert into hierarchical_values values (13,    5,     'aem'              , null);
insert into hierarchical_values values (45,   13,       'AEM1'           ,   22);
insert into hierarchical_values values (46,   13,       'AEM2'           ,   18);
insert into hierarchical_values values (47,   13,       'AEM3'           ,   20);
insert into hierarchical_values values (14,    5,     'aen'              , null);
insert into hierarchical_values values (43,   14,       'AEN1'           ,   14);
insert into hierarchical_values values (44,   14,       'AEN2'           ,    6);
insert into hierarchical_values values (15,    5,     'aeo'              , null);
insert into hierarchical_values values (48,   15,       'AEO1'           ,   20);
insert into hierarchical_values values ( 6,    1,   'af'                 , null);
insert into hierarchical_values values (49,    6,     'AF1'              , 1000);
insert into hierarchical_values values ( 2, null, 'b'                    , null);
insert into hierarchical_values values ( 7,    2,   'bg'                 , null);
insert into hierarchical_values values (16,    7,     'bgp'              , null);
insert into hierarchical_values values (50,   16,       'BGP1'           ,   25);
insert into hierarchical_values values (51,   16,       'BGP2'           ,   75);
insert into hierarchical_values values (17,    7,     'bgq'              , null);
insert into hierarchical_values values (53,   17,       'BGQ1'           ,    5);
insert into hierarchical_values values (18,    7,     'bgr'              , null);
insert into hierarchical_values values (52,   18,       'BGR1'           ,   27);
insert into hierarchical_values values ( 8,    2,   'bh'                 , null);
insert into hierarchical_values values ( 3, null, 'c'                    , null);
insert into hierarchical_values values ( 9,    3,   'ci'                 , null);
insert into hierarchical_values values (19,    9,     'cis'              , null);
insert into hierarchical_values values (23,   19,       'cisw'           , null);
insert into hierarchical_values values (24,   23,         'ciswx'        , null);
insert into hierarchical_values values (26,   24,           'ciswxz'     , null);
insert into hierarchical_values values (40,   26,             'CISWXZ1'  ,   15);
insert into hierarchical_values values (41,   26,             'CISWXZ2'  ,   16);
insert into hierarchical_values values (42,   26,             'CISWXZ3'  ,   14);
insert into hierarchical_values values (25,   23,         'ciswy'        , null);
insert into hierarchical_values values (55,   25,           'CISWY1'     ,   30);
insert into hierarchical_values values (20,    9,     'cit'              , null);
insert into hierarchical_values values (54,   20,       'CIT1'           ,    9);
In order to make it possible to view the hierarchical data in the table more appealing for the human eye, I create a view:
create view hierarchical_values_v as
  select 
    rownum  rownum_,
    level   level_,
    id,
    info,
    substr(lpad (' ', (level-1)*2) || info,1,30) info_,
    amt
  from
    hierarchical_values
  start with
    id_p is null
  connect by
    prior id = id_p
;
Selecting from the view gives me:
set pagesize 5000

select
  substr(info_,1,30),
  amt
from 
  hierarchical_values_v 
order by
  rownum_ /*desc*/
;
SUBSTR(INFO_,1,30)                    AMT
------------------------------ ----------
a
  ad
    adj
      ADJ1                              6
      ADJ2                              7
      ADJ3                              3
      ADJ4                              1
    adk
      ADK1                              3
      ADK2                              4
      ADK3                              5
    adl
      adlu
        ADLU1                           1
        ADLU2                           2
      adlv
        ADLV1                           2
        ADLV2                           7
        ADLV3                           1
        ADLV4                           4
  ae
    aem
      AEM1                             22
      AEM2                             18
      AEM3                             20
    aen
      AEN1                             14
      AEN2                              6
    aeo
      AEO1                             20
  af
    AF1                              1000
b
  bg
    bgp
      BGP1                             25
      BGP2                             75
    bgq
      BGQ1                              5
    bgr
      BGR1                             27
  bh
c
  ci
    cis
      cisw
        ciswx
          ciswxz
            CISWXZ1                    15
            CISWXZ2                    16
            CISWXZ3                    14
        ciswy
          CISWY1                       30
    cit
      CIT1                              9
Now, I'd like to sum up amt for each node. For instance, the sum of amt for node adlv should be 14 (Which is the sum of the leaves ADLV1, ADLV2, ADLV3 and ADLV4, or 2+7+1+4 respectively). In the same spirit, the sum of amt for node cisw should be 75 which is the sum of the nodes ciswx and ciswy which (in recursive turn) is the some of CISWXZ1, CISWXZ2, CISWXZ3 and CISWY1 or 15+16+14+30.
I can achieve this goal by selecting from the tree if it is turned upside-down (by ordering by rownum_ desc)...
set pagesize 5000

select
  rownum_,
  level_,
  substr(info_,1,30),
  amt
from 
  hierarchical_values_v 
order by
  rownum_ desc
;
   ROWNUM_     LEVEL_ SUBSTR(INFO_,1,30)                    AMT
---------- ---------- ------------------------------ ----------
        55          4       CIT1                              9
        54          3     cit
        53          6           CISWY1                       30
        52          5         ciswy
        51          7             CISWXZ3                    14
        50          7             CISWXZ2                    16
        49          7             CISWXZ1                    15
        48          6           ciswxz
        47          5         ciswx
        46          4       cisw
        45          3     cis
        44          2   ci
        43          1 c

        ... more records snipped ...

The «trick» is to remember the last record's level_ and to compare it with the actual or current record's level_. Depending on whether the last record's level_ is smaller, equal or greater then the actual level_, three different actions must be performed. In order to keep track of the last record's level_, the variable last_level is used. This variable is initialized with 0.
Also, I need to store the sums of amt for each level_ from 1 through the actual record's level_ - 1. A collection seems to be the appropriate method to do that. So, I create a collection type sum_on_level and a variable (total_sum_on_level) of that type.
I have now everything in order to start iterating over the records:
Read one (1st) record (level_: 4, amt: 9, rownum_: 55)
level_ > last_level: This means, we have to do two «calculations»:
  1. add amt to all elements in total_sum_on_level in the range 1 .. last_level-1.
  2. set the elemens in total_sum_on_level to amt in the range last_level .. level_-1
Since last_level is now 0, nothing can be done for the first step. The second step sets the elements between 0 and 3 (last_level, level_ -1 ) to amt.
The collection total_sum_on_level now looks like
total_sum_on_level
level_: 0 1 2 3 - - - - -
cumulated sum of amt:9 9 9 9 - - - - -
Also, the read record is appended to the result set nodes which now looks like:
nodes
infoamtrownum_
CIT1 9 55
Finally, last_level is set to level_ (which is 4).
Read one (2nd) record (level_: 3, amt: -, rownum_: 54)
level_ < last_level: This means that we only put one record at the end of nodes. The member amt is set to the value of total_sum_on_level(level_) (which happens to be 9).
So, nodes now looks as:
nodes
infoamtrownum_
CIT1 9 55
cit 9 54
last_level is set to level_ (which is 3).
Read one (3rd) record (level_: 6, amt: 30, rownum_: 53)
level_ > last_level. First step: add amt to all elements in total_sum_on_level in the range 1 .. last_level-1 (1 .. 2). Second step: set the elements in total_sum_on_level to amt in the range last_level .. level_ - 1.
Thus, total_sum_on_level looks now:
total_sum_on_level
level_: 0 1 2 3 4 5 - - -
cumulated sum of amt:393939303030 - - -
The read record is appended to the result set nodes:
nodes
infoamtrownum_
CIT1 9 55
cit 9 54
CISWY1 30 53
Read one (4th) record (level_: 5, amt: -, rownum_: 52)
level_ < last_level: Put the record at the end of nodes with member set to the value of total_sum_on_level(level_):
nodes
infoamtrownum_
CIT1 9 55
cit 9 54
CISWY1 30 53
ciswy 30 52
and set last_level to 5.
Read one (5th) record (level_: 7, amt: 14, rownum_: 51)
level_ > last_level: add amt to total_sum_on_level(1 .. last_level-1) and set total_sum_on_level(last_level .. level_) to amt:
total_sum_on_level
level_: 0 1 2 3 4 5 6 7 -
cumulated sum of amt:5353534444141414 -
Append the read record to nodes:
nodes
infoamtrownum_
CIT1 9 55
cit 9 54
CISWY1 30 53
ciswy 30 52
CISWXZ3 14 51
Read one (6th) record (level_: 7, amt: 16, rownum_: 50)
level_ = last_level: This means that we have to cumulate amt to the elements in total_sum_on_level in the range 1 .. level_-1:
total_sum_on_level
level_: 0 1 2 3 4 5 6 7 -
cumulated sum of amt:6969696060303014 -
and append the read record to nodes:
nodes
infoamtrownum_
CIT1 9 55
cit 9 54
CISWY1 30 53
ciswy 30 52
CISWXZ3 14 51
CISWXZ2 16 50
Set last_level to 7.
Read one (7th) record (level_: 7, amt: 15, rownum_: 49)
level_ = last_level: again, we have to cumulate amt to the elements in total_sum_on_level in the range 1 .. level_-1:
total_sum_on_level
level_: 0 1 2 3 4 5 6 7 -
cumulated sum of amt:8484847575454514 -
and append the read record to nodes:
nodes
infoamtrownum_
CIT1 9 55
cit 9 54
CISWY1 30 53
ciswy 30 52
CISWXZ3 14 51
CISWXZ2 16 50
CISWXZ1 15 49
Read one (8th) record (level_: 6, amt: -, rownum_: 48)
level_ < last_level: Put the record at the end of nodes with member set to the value of total_sum_on_level(level_):
nodes
infoamtrownum_
CIT1 9 55
cit 9 54
CISWY1 30 53
ciswy 30 52
CISWXZ3 14 51
CISWXZ2 16 50
CISWXZ1 15 49
ciswxz 45 48
If this rules are applied to the entire set, and nodes are then displayed in reversed order, we obtain the desired sums for all nodes.
In order to do this, I create a node object type which keeps track of the summed up values of amt for each node and leaf in the table:
create or replace type node as object (
  info    varchar2(30),
  amt     number,
  rownum_ number
);
/
Also, I want a collection of nodes which will eventually store the values for each record found in hierarchical_values. So, I create an approprate type for it:
create or replace type node_t as table of node;
/
Lastly, I need a procedure that actually filles the nodes into a variable whose type is node_t. This procedure will be the member procedure do_sum of the following object:
create or replace type sum_nodes as object (
  nodes node_t,

  constructor function sum_nodes return self as result,

  member procedure do_sum
);
/
And the specification:
create or replace type body sum_nodes as

  constructor function sum_nodes return self as result is begin
    nodes := node_t();
    return;
  end sum_nodes;

  member procedure do_sum is 
    type  sum_on_level is table of number index by pls_integer;
    total_sum_on_level sum_on_level;

    last_level           number := 0;

  begin

    for r in (
      select level_, rownum_, info_, amt
      from hierarchical_values_v
      order by rownum_ desc
    ) loop

       nodes.extend;

       if      r.level_ < last_level then

               nodes(nodes.count) := node(r.info_, total_sum_on_level(r.level_), r.rownum_);

       elsif   r.level_ = last_level then

               nodes(nodes.count) := node(r.info_, r.amt, r.rownum_);

               for i in 1 .. r.level_-1 loop
                 total_sum_on_level(i) := nvl(total_sum_on_level(i), 0) + nvl(r.amt, 0);
               end loop;


       else -- r.level_ > last_level

               nodes(nodes.count) := node(r.info_, nvl(r.amt, 0), r.rownum_);

               for i in 1 .. last_level - 1 loop
                 total_sum_on_level(i) := total_sum_on_level(i) + nvl(r.amt, 0);
               end loop;

               for i in last_level .. r.level_ - 1 loop
                 total_sum_on_level(i) := nvl(r.amt, 0);
               end loop;

       end if;

       last_level := r.level_;

    end loop;

  end do_sum;

end;
/
In action...
set serveroutput on size 1000000 format wrapped

declare
  s     sum_nodes := sum_nodes();
begin
  s.do_sum;

  for r in (select * from table(s.nodes) t order by t.rownum_) loop
    dbms_output.put_line(rpad(r.info, 30, '.' )|| to_char(r.amt,'9999'));
  end loop;

end;
/
a............................. 1146
  ad..........................   46
    adj.......................   17
      ADJ1....................    6
      ADJ2....................    7
      ADJ3....................    3
      ADJ4....................    1
    adk.......................   12
      ADK1....................    3
      ADK2....................    4
      ADK3....................    5
    adl.......................   17
      adlu....................    3
        ADLU1.................    1
        ADLU2.................    2
      adlv....................   14
        ADLV1.................    2
        ADLV2.................    7
        ADLV3.................    1
        ADLV4.................    4
  ae..........................  100
    aem.......................   60
      AEM1....................   22
      AEM2....................   18
      AEM3....................   20
    aen.......................   20
      AEN1....................   14
      AEN2....................    6
    aeo.......................   20
      AEO1....................   20
  af.......................... 1000
    AF1....................... 1000
b.............................  132
  bg..........................  132
    bgp.......................  100
      BGP1....................   25
      BGP2....................   75
    bgq.......................    5
      BGQ1....................    5
    bgr.......................   27
      BGR1....................   27
  bh..........................    0
c.............................   84
  ci..........................   84
    cis.......................   75
      cisw....................   75
        ciswx.................   45
          ciswxz..............   45
            CISWXZ1...........   15
            CISWXZ2...........   16
            CISWXZ3...........   14
        ciswy.................   30
          CISWY1..............   30
    cit.......................    9
      CIT1....................    9

March 15, 2008: On including files in SQL*Plus

In SQL*Plus, a script can be executed (or «included») using @ or @@. So, I might define a script that simply prints me hello world. I name that script phw.sql:
phw.sql
dbms_output.put_line('Hello world');
I create another script ,inc.sql, that includes this script twice...
inc.sql
begin

  @@phw
  @@phw

end;
/
... and execute this script:
SQL> set serveroutput on size 100000 format wrapped
SQL> @D:\path\to\inc.sql
Hello world
Hello world

PL/SQL procedure successfully completed.
As expected, it prints Hello world twice.
I'd like to print any string, not just Hello world. So I create another script, print.sql, that uses the special &1:
print.sql
dbms_output.put_line('&1');
And then, I create another script that includes print.sql twice:
inc2.sql
begin
  
  @@print 'first line'
  @@print 'second line'

end;
/
SQL> set verify off
SQL> @D:\path\to\inc2.sql

second line
second line
Strangly, both lines print second line. It seems that this is the case because the substitution variables are substited when the buffer is run, not when it is entered. So, the &1 gets replaced with the last value for it, which happens to be second line
In order to achieve what I want, I have to use some magic. I create inc3.sql:
print2.sql
.
set sqlterminator off
set termout       off
spool             c:\temp\print.spooled
prompt    dbms_output.put_line('&1');
spool             off
set termout       on
set sqlterminator on
input
@@c:\temp\print.spooled
And the script that calls it:
inc3.sql
begin
  
  @@print2 'first line'
  @@print2 'second line'

end;
/
Using the script:
SQL> @D:\path\to\inc3.sql

first line
second line
How does that work? First, I use the . (dot) to stop entering lines to the buffer. Then I turn off the sqlterminator. This is necessary because there are semicolons (;) in the scripts, and I don't want them to «believe» that they'd have to execute an SQL statement (so to speak). For cleanness, I also turn off termout because I don't want this script to print anything to my terminal. Then I use a spooled file (c:\temp\print.spooled) that will contain one single line, namely the one caused by the prompt command. spool is closed, termout and sqlterminator reset. input causes to start adding lines to the buffer again. Finally, I add the spooled file to the buffer and return to the script that called me.

Older on Oracle articles

Mar 11, 2008: On setting commit_write
August 13, 2006: On out parameters
June 4, 2006: Comments, finally!
January 24, 2006: On a breakable Oracle
December 11, 2005: On a table's first five rows
November 12, 2005: On improving a better describe
October 18, 2005: On storing hierarchical data
September 25, 2005: On a better describe
September 6, 2005: On solving a sudoku with Oracle
August 22, 2005: On max and group by
July 20, 2005: On shrinking table sizes
April 21, 2005: On table sizes
April 17, 2005: On profiles

Older news

See also older news.
bla bla