Ren Nyffenegger on Oracle

Time 2020-10-12 17:04:16

Web Name: Ren Nyffenegger on Oracle

WebSite: http://www.adp-gmbh.ch

ID:79658

Keywords:

Nyffenegger,Ren,Oracle,

Description:

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. add_env_vars.bas' See http://support.microsoft.com/kb/q184608/' ---' Must be run with administrator privileges on Vistaoption explicitprivate const REG_SZ = 1private const REG_EXPAND_SZ = 2private const HKEY_LOCAL_MACHINE = H80000002' Needed for SendMessageTimeoutprivate const HWND_BROADCAST = HFFFFprivate const WM_SETTINGCHANGE = H1Aprivate const SMTO_ABORTIFHUNG = H2private declare function RegCreateKey Lib "advapi32.dll" Alias _ "RegCreateKeyA" _ (byVal hKey as long , _ byVal lpSubKey as string , _ phkResult as long _) as longprivate 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 longprivate 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 longprivate declare function RegCloseKey Lib "advapi32.dll" _ (byVal hKey as long _) 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 _ 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"' /path/to/excel.exe /unregserver' /path/to/excel.exe /regserveroption explicitdim exceldim workbookdim vb_editordim shell_objdim cur_dirdim argsdim function_name_to_calldim vbs_name_to_importSet args = WScript.Argumentsif args.count = 0 then WScript.echo ("no argument specified") WScript.Quitend iffunction_name_to_call = args(0)vbs_name_to_import = args(0) ".bas"set shell_obj = CreateObject("WScript.Shell")cur_dir = shell_obj.currentDirectoryset excel = CreateObject("Excel.Application")excel.visible = trueset workbook = excel.Workbooks.Addset vb_editor = excel.vbedim vb_compsset vb_comps = vb_editor.ActiveVBProject.VBComponentsvb_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 Runtimecall addReference(workbook, "{420B2830-E718-11CF-893D-00A0C9054228}")excel.Run(function_name_to_call)set excel = nothing 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:create_odbc_datasource.bas' See http://support.microsoft.com/kb/q184608/' ---' Must be run with administrator privileges on Vistaoption explicitprivate const REG_SZ = 1private const HKEY_LOCAL_MACHINE = H80000002private Declare Function RegCreateKey Lib "advapi32.dll" Alias _ "RegCreateKeyA" _ (byVal hKey As long , _ byVal lpSubKey As string , _ phkResult As long _) as longprivate 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 longprivate Declare Function RegCloseKey Lib "advapi32.dll" _ (byVal hKey As long _) as long 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 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 subprivate 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 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 subprivate 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 subprivate 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 subprivate 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_descend sub' 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 = nothingend sub' 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 = nothingend subprivate sub transaction(db as DAO.Database) call do_transaction(db, false) call do_transaction(db, true )end subprivate 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 = nothingend subprivate 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 = nothingend 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. 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 10000declare 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;select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number[@id = 2] return $num' returning content) from dual;select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number return $num' returning content) from dual; 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: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 /number 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;select XMLQuery ('let $num := doc("/public/an_xml_doc.xml")/numbers/number/text() return $num' returning content) from dual; 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;select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/text() return ($num/text()," ")' returning content) from dual; 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;select XMLQuery ('for $num in doc("/public/an_xml_doc.xml")/numbers/number order by $num/@id return ($num/text()," ")' returning content) from dual; 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").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;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; 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. 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: 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)... 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. add amt to all elements in total_sum_on_level in the range 1 .. last_level-1. 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. 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). 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. 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. 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 - 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 - 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 - 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: 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: 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; 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;set serveroutput on size 1000000 format wrappeddeclare 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; 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:SQL set serveroutput on size 100000 format wrappedSQL @D:\path\to\inc.sqlHello worldHello worldPL/SQL procedure successfully completed. I'd like to print any string, not just Hello world. So I create another script, print.sql, that uses the special 1:SQL set verify offSQL @D:\path\to\inc2.sqlsecond linesecond 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 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.

TAGS:Nyffenegger Ren Oracle 

<<< Thank you for your visit >>>

Hot Websites