René Nyffenegger's collection of things on the web | |
Most wanted - Feedback
- Follow @renenyffenegger
|
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 testdao_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:
Permalink: http://www.adp-gmbh.ch/blog/2009/11/14.php.
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:
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
Permalink: http://www.adp-gmbh.ch/blog/2009/10/08.php.
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»:
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
Also, the read record is appended to the result set nodes which now looks like:
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:
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:
The read record is appended to the result set nodes:
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_) :
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:
Append the read record to nodes:
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:
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:
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_) :
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
Permalink: http://www.adp-gmbh.ch/blog/2009/09/29.php.
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
... 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.
Permalink: http://www.adp-gmbh.ch/blog/2008/03/15.php.
Older on Oracle articles
Mar 11, 2008: On setting commit_write
Jan 17, 2008: On forcing a nested loop join instead of a hash join
April 22, 2007: Emulating tables with PL/SQL
April 14, 2007: sprintf and printf in PL/SQL
February 01, 2007: On importing data from Oracle into Excel with CopyFromRecordset
January 24, 2007: On importing data from Oracle into Excel with a Visual Basic for Application macro
December 31, 2006: On finding changed values in a table with flashback
December 3, 2006: On parametrizing views with contexts
November 30, 2006: On creating an AJAX Application with Oracle
August 20, 2006: On splitting a string into words with regular expressions
August 19, 2006: On generating HTML output with SQL*Plus
August 13, 2006: On out parameters
June 4, 2006: Comments, finally!
May 9, 2006: On solving workspace conflicts
March 24, 2006: On emulating tables with PL/SQL (Part II)
February 5, 2006: On reading trace files with PL/SQL
January 24, 2006: On a breakable Oracle
January 8, 2006: On emulating tables with nested tables
December 28, 2005: On the lazy evaluation of decode and the eager evaluation of nvl
December 26, 2005: On comparing mulitple SQL statements' statistics
December 22, 2005: On making Oracle's regular expressions a bit easier to use
December 19, 2005: On triggers, sequences, autonumbers and failing inserts
December 11, 2005: On a table's first five rows
November 28, 2005: On identifiying «periods of activity»
November 22, 2005: On creating bitmaps with pure PL/SQL only
November 20, 2005: On reading blobs from and writing blobs to a file
November 16, 2005: On mixing outer joins with inner joins
November 12, 2005: On improving a better describe
November 4, 2005: On eliminating outliers in avg() queries
October 18, 2005: On storing hierarchical data
October 3, 2005: On generating ERDs with desc_table and neato
September 25, 2005: On a better describe
September 22, 2005: On the missing product() aggregate function
September 19, 2005: On using dbms_utility.name_resolve
September 6, 2005: On solving a sudoku with Oracle
August 30, 2005: On a member function calling a member procedure
August 27, 2005: On an integer not always being an integer
August 22, 2005: On max and group by
August 16, 2005: On storing objects in a table
August 8, 2005: On tracking data changes in a table
August 6, 2005: On identifiying parent and child tables
July 26, 2005: On verifying if an index is used
July 24, 2005: On to_char, dates and weekdays
July 20, 2005: On shrinking table sizes
July 18, 2005: On the missing super keyword in PL/SQL
June 29, 2005: On lifting the 255 character output limit of dbms_output
June 24, 2005: On case insensitive queries in 10g
May 27, 2005: On making Oracle start a batch file (.bat) on Windows
May 11, 2005: On using regexp_replace to format data
May 5, 2005: On EVAL'ing an expression
May 2, 2005: On creating a similar user
April 21, 2005: On table sizes
April 17, 2005: On profiles
Older news
See also older news.
|