How to Download Metalink SRs Programmatically (with Appendix: How to Restore Indentation of Metalink Documents) Metalink (http://metalink.oracle.com) SRs (service requests), previously called Tars (technical assistance requests), are tickets you open with Oracle for technical support. Unlike Metalink notes, bug reports or forum messages, SRs can't be searched. How do you find an SR you opened with Oracle some time ago that had the words, say, "ORA-04031" and "PRODDB2", and you forget the SR title and obviously the number? If the SR was opened not too long ago, you may be able to spot it when you show the titles of the latest SRs (default 30, maximum 365 days). Nevertheless, the fact that you can't search within SRs, and you can only view one SR at a time is very annoying. (Ironically, I opened an SR asking Oracle whether they plan to add the SR search functionality to Metalink. The analyst filed an enhancement request for me.) This note is about downloading all your SRs so that you can search for a string on your local hard drive. There're two extreme approaches to the download and some approaches in the middle. But using wget or curl is not one of them, because of the convoluted login, multiple redirects, and hyperlink to "javascript:;" instead of "http://...". The sophiscated approach would be to write a program to login Metalink, accept cookies, parse the HTML code and follow the redirects, extract the real hyperlinks in "onClick" Javascript events. (This approach is taken by freeware HPL; see http://yong321.freeshell.org/freeware/hplhcpl.html) The approach I want to describe is a think-out-of-the-box one. We'll use Microsoft VBScript to simulate a real person's clicking and typing. Obviously this only works on Windows. These are the steps. 1. Install Mozilla Firefox (download from http://firefox.com). This is the only browser I know that has an unambiguous way to set focus to the Address box. If you insist on using Internet Explorer, the following procedure may still work and useless images will be downloaded. (Control-L is undocumentedly equivalent to Control-O in IE and you can type a URL and hit enter.) 2. Login Metalink manually. Then go to https://metalink.oracle.com/metalink/plsql/tar_main.tar_list?p_csi=ctrycsi&p_csi=END&p_productCode=-1&p_platformCode=-1&p_priority=ALL&p_status=ALL&p_lastUpdate=&p_sortOption=update_date+desc&p_show=Show++ where is your country code (840 for USA, e.g.; see http://en.wikipedia.org/wiki/ISO_3166-1_numeric), is your company's CSI number, and is the number of days in the past during which you opened SRs. I set this to 1000 as a starting point. Without this "hack", you can only show at most 365 days's recent SRs with a Web browser. 3. Extract all SR numbers with your text editor. Select all text and paste into your favorite editor. I use vi and I type :%v/^[0-9]/d :%s/ .*$// and save the file as SRList.txt, which has all SR numbers, one on each line. 4. Run the program below to generate our VBScript. I use Windows Perl (from http://activestate.com). If you only have Perl on a UNIX/Linux box, run it there and change the #! line accordingly and ftp the result file to your Windows machine. If you use IE instead of Firefox as web browser, change "Mozilla Firefox" to "Internet Explorer", but I didn't test IE though. ----- begin cut: ml.pl ----- #!perl -w print 'Set WshShell = WScript.CreateObject("WScript.Shell") WScript.Sleep 500 WshShell.AppActivate "Mozilla Firefox" '; while (<>) { chomp; print 'WshShell.sendkeys "^lhttps://metalink.oracle.com/metalink/plsql/f?p=110:20:10936019409021334251::::P20_TARNUM,P20_CTRYCODE:' . $_ . ',840;~" WScript.Sleep 16000 WshShell.sendkeys "%fa' . $_ . '.html~" WScript.Sleep 500 WshShell.sendkeys "{ESC}" WScript.Sleep 500 '; } ----- end cut: ml.pl ----- Some notes about this Perl program ml.pl. Change the country code (P20_CTRYCODE) to yours (840 here). I don't know what the other numbers in the long URL mean (110, 20, the big number). I suggest you try that URL with a real SR first. That is, assuming country code 840 and Tar 1234567.999, try https://metalink.oracle.com/metalink/plsql/f?p=110:20:10936019409021334251::::P20_TARNUM,P20_CTRYCODE:1234567.999,840; If it works, go on. If it doesn't, just go to an SR in web browser, look at its URL and modify the string here. Most likely 110 and 20 are essential but that big number may be different and country code may even be omitted. Now run the program as perl ml.pl SRList.txt > ml.vbs Make sure ml.vbs looks like this (1234567.999 is my latest SR). ----- begin cut: ml.vbs ----- Set WshShell = WScript.CreateObject("WScript.Shell") WScript.Sleep 500 WshShell.AppActivate "Mozilla Firefox" WshShell.sendkeys "^lhttps://metalink.oracle.com/metalink/plsql/f?p=110:20:10936019409021334251::::P20_TARNUM,P20_CTRYCODE:1234567.999,840;~" WScript.Sleep 16000 WshShell.sendkeys "%fa1234567.999.html~" WScript.Sleep 500 WshShell.sendkeys "{ESC}" WScript.Sleep 500 [repeat lines like the above 6 lines for another SR...] ----- end cut: ml.vbs ----- 5. Make sure your Firefox browser already logs in Metalink; doesn't matter what page the browser is currently showing. Make absolutely sure your PC won't be used, I mean, literally, touched, for any other use in the following few hours. Run your VBScript either by double clicking the ml.vbs program in Windows Explorer or, as I prefer, go to DOS (Start -> Run -> cmd) and type cscript //nologo ml.vbs Now have a cup of coffee or tea ready, sit back and watch the invible hand working on your computer. It will open the Web page for the latest SR, wait 16 seconds (adjust it in ml.pl as needed) for the download to complete, save the page as .html in the currently selected download folder, go to the next SR and do the same,... After all the SRs are downloaded, it's up to you as to how you implement keyword search. I like Microsoft Windows Desktop Search. Some prefer Google's. If you don't like them, just use Windows Explorer's word search, or DOS command findstr. Appendix: How to Restore Indentation of Metalink Documents Some Metalink pages are hard to read because text indentation is lost. This is definitely a problem for forum messages regardless what browser you use, and a problem for bug reports if you don't use IE. (It's easy to restore indentations in bug reports viewed in IE. You can simply copy the plan into Notepad for proper view.). When you read an execution plan or TNS errors in listener.log that require indentation in a Metalink forum message, you can view source in the browser and delete
tags. But this doesn't work in SRs. Instead you have to replace each HTML
tag with a newline to restore indentation and separate jumbled one-line into multiple lines. This can be done in UltraEdit or vi but not Notepad.