Author: francis

Some non-obvious things to do with PL/SQL tables

1. Test a lookup

PL/SQL tables can be indexed by varchars as well as simple integers, so,


type id_lookup is table of integer index by emp.name%type ;
emp_lookup id_lookup ;

— create a cache in a global package variable somewhere
for e in (select emp_id, name from emp)
loop
  emp_lookup(e.name) := e.emp_id
end loop ;

— Later define functions
function emp_exists( ename in emp.name%type )  return boolean is
begin
   return emp_lookup.exists(ename) ;
end emp_exists ;
function get_emp_id( ename in emp.name%type )  return integer is
begin
    if emp_exists(ename)
   then
      return emp_lookup(ename);
  else
    return null ;
  end if ;
end get_emp_id ;

Of course, you’ll have to do things like maybe force the ename to be upper case or some such to make it work properly but I didn’t want to clutter this up.

2. (more to follow)

US Social Security Numbers – a basis for fraud?

In reply to this article:

I think a lot of UK/European readers won’t get why the social security number thing is such a big deal. If memory serves some genius back in the early days of US IT decided that, rather than give everybody their own customer number, they’d just use the guaranteed unique SS number. This soon became common practice.

So, it’s not that McNealy’s SS number is compromised particularly, more that a knowedgeable hacker can use this number when they break into other systems to find out things about him and also pretend to be him and commit fraud.

In the UK I don’t think most of us would give a toss if someone knew our NI number because it isn’t plastered all over our credit card vendor’s internal systems. I do wonder if this will change if the UK government manage to get their crackpot id card scheme off the ground, will this number then start mattering because it will be plastered everywhere like it is in the US? Then the hackers will find committing fraud (sorry, “identity theft”) much easier. I bet no-one’s thought about it at all.

Regards,

Francis Fish

Fun with XPath: Combining many fields in a query.

This is one of those things that are so obvious I felt like kicking myself when I realised how to do it.

First, some XML:

<?xml version="1.0" standalone="yes"?> 
<styling_rules >
<rule column="NAME" >
<features style="MAG:C.WASSNAME.CHOCOLATE"> NAME=‘T BAR BOO’ </features>
<label column="NAME" style="MAG:T.STREET NAME">1</label>
</rule>
<rule column="NAME" >
<features style="MAG:C.WASSNAME.CORAL"> NAME=‘UP A CREEK’ </features>
<label column="NAME" style="MAG:T.STREET NAME">1</label>
</rule>
<rule column="NAME" >
<features style="MAG:C.WASSNAME.CORNFLOWERBLUE"> NAME=‘UNHAPPY VALLEY’ </features>
<label column="NAME" style="MAG:T.STREET NAME">1</label>
</rule>
</styling_rules>

This has been anonomised for the purposes of this discussion. The tool that uses the data flattens the two elements inside the rule element out so fine. I needed an XPath that would get me a particular rule node so I can manipulate it.

I put my SQL head on and looked for two sets that I could combine:

xPath = "/styling_rules/rule[column='NAME']/features[style=‘MAG:C.WASSNAME.CORNFLOWERBLUE’ ” + 
” and /styling_rules/rule[column='NAME']/features/[text() = &quot; NAME='UNHAPPY VALLEY' &quot; &quot; ;</pre><br />Nah, don't even go there, it doesn't choke but it doesn't do anything. Instead think directory path and the *nix <em>find </em>command comined together:<br /><pre>xPath = &quot;/styling_rules/rule[column=‘NAME’]/features[@style=‘MAG:C.WASSNAME.CORNFLOWERBLUE’ ” +
” and ./text() = " NAME=‘UNHAPPY VALLEY’ " ]/parent::” ;

This should get me the whole node and I can then update the elements in it to my heart’s content.

Java: finding nodes with xPath and how to Dump out XML DOM as a string

After some google hacking (and reading Building Oracle XML Applications, which I thought was way out of date, my first edition of Java and XML was useless) I found this kind of stuff:

XPath

To use XPath you need an XMLDocument object:

  DOMParser p = new DOMParser();
p.parse(new StringReader(xml));
  Document doc = p.getDocument();
  XMLDocument xmldoc = (XMLDocument)doc ;
  NodeList nl = xmldoc.selectNodes(xPath) ;

Now we have a loverly list of nodes, and off we go.  Note that this suffers from the usual Java nonsense of returning a null if there’s nothing there.

Getting a String version of a DOM Document

toString()

Hmmm – just gives the object ID. How useful is that? Not very.

I noticed that the XMLDocument class has a print method that you can pass an OutputStream to. OK, I think, pass it a StringWriter?

        StringWriter sw = new StringWriter() ;
        xmldoc.print(sw);
        System.out.println(sw.toString());

This compilies but throws a Null Pointer Exception. Must be the interfaces matching but not doing what they’re supposed to. I dig around in the class documentation and find that I can pass a PrintWriter – this works:

    StringWriter sw = new StringWriter() ;
PrintWriter pw = new PrintWriter(sw );
xmldoc.print(pw);
System.out.println(sw.toString());

No idea why, haven’t got time to mess around finding out. It works. I think this is superior to a solution I saw where people were calling the serialize method on some weird class or writing your own code that dumps out the contents of a node and passing the root node to it (there are lots of examples on the Web if you look around). Note that this doesn’t work with fragments, at least it doesn’t seem to, have a play with it.

Coming up Fun with XPath: Combining many fields in a query. (when you aren’t trying to write some XSL).

Forwarding mail from a webmail account using python

NOTE: The code here is provided for discussion purposes, if you choose to use it yourself on your own head be it! I used Python 2.4 with emacs and the python.el mode file.

POP mail from my Macmail account hasn’t worked since December. I’ve emailed the support people a few times to no avail. It’s a free service and I don’t think anyone’s home any more. I decided to try and write a program that would pretend to be a browser and just forward everything on to my gmail account.

This was surprisingly easy.

I decided to use Python, because I know it a bit. CLisp was my second choice but there don’t seem to be the wealth of examples on the ‘net. I’ve done stuff like this in Java, but you have to do crazy things like run it through jtidy first and treat the html as xml, which is a complete pain. The Python SGMLlib just works.

I read the HTML processing chapter of dive into python, which gave me a grounding for the what I needed to do with SGML processors and stuff.

But first, I needed to learn how to log onto the mail service using cookies. I found this on the ClientCookie module, and that did the trick.

I then made a big messy file that I could run in emacs and keep stuffing prototype code into the Python interpreter. After some work I came up with this for the logon:

macMailURL = "http://mail.macmail.com"

def login2Macmail():
    request = ClientCookie.Request(macMailURL)
    # note we’re using the urlopen from ClientCookie, not urllib2
    response = ClientCookie.urlopen(request)
    firstPage = response.read();
    #print firstPage
    # Now we need the sessionID
    data = "login=aname&name=aname&pwd=apass&password=apass"
    # let’s say this next request requires a cookie that was set in response
    request2 = ClientCookie.Request(macMailURL + "/logon.php?logoff=1")
    response2 = ClientCookie.urlopen(request2,data)
    return response2.read()

This returns the inbox html from the second response. I’ve left the commented debug statement in.

So, now we need to take this page, rip out the <a> tags that point to emails, and use this info to forward the mails:

class collectTags(SGMLParser):<br /><br />&nbsp; def reset(self):<br />&nbsp;&nbsp;&nbsp; SGMLParser.reset(self)<br />&nbsp;&nbsp;&nbsp; self.urls = []<br /><br />&nbsp; def start_a(self, attrs):<br />&nbsp;&nbsp;&nbsp; href = [v for k, v in attrs if k=='href']<br />&nbsp;&nbsp;&nbsp; if href:<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; #print href<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if href[0].find(&quot;/member/mail.php&quot;) != - 1:<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; self.urls.extend(href)

Extending SGMLParser – if it finds an <a> tag it will run the start_a method I and do what I want.

This little class will stick all of these URLs (which have an href of the form /member/mail.php&id=1234) into the urls list. Note the v for k with the if statement, this lovely one liner is why I like Python so much. The only problem is that it returns a list which will only have one element. I’m sure there’s a way of changing the one-liner but I don’t know what it is yet. Still, think of the equivalent Java, or PL/SQL! Having to reference the first element of the returned list is a small price to pay for this expressive power. Of course, some Python god will say I’m talking out of my rear end and I just need to do …, whatever that is.

Next I need to forward this mail. This is quite complex because I need to get the page displaying the forward, parse out the text of the mail and any other arguments, and then submit this as a post command back to the web server afdter substituting my forward mail into the string.

class getMailBody(SGMLParser):

def reset(self):
SGMLParser.reset(self)
self.data = ""
self.inForm = 0
self.inTextArea = 0
self.textAreaName = ""
self.textAreaID = ""
self.textAreaText = []

def start_form(self, attrs):
theForm = [v for k, v in attrs if k==‘action’]
if theForm:
self.inForm = theForm0.find("/member/send_mail.php") != – 1

def end_form(self):
self.inForm = 0

def getValue( self, val, attrs ):
return [v for k, v in attrs if k==val]

def appendData(self,value):
amp = ""
if self.data:
amp = "&"
self.data = amp + value

def processAttribs(self,attrs):
if self.inForm:
name = self.getValue(‘name’,attrs)
idVal = self.getValue(‘id’,attrs)
value = self.getValue(‘value’,attrs)
if not value:
value.append( "" )
if name:
# print "name" + name0
self.appendData( urlencode( {name0:value0 } ))
if idVal and idVal != name:
# print "idval" + idVal0
self.appendData( urlencode( {idVal0:value0 } ) )

def start_input(self, attrs):
self.processAttribs( attrs )

def start_textarea(self, attrs):
if self.inForm:
self.textAreaName = self.getValue(‘name’,attrs)
self.textAreaID = self.getValue(‘id’,attrs)
self.inTextArea = 1

def end_textarea(self):
if self.inTextArea:
self.inTextArea = 0
if self.textAreaName:
# print "text area name" + self.textAreaName0
self.appendData( urlencode( {self.textAreaName0:" ".join(self.textAreaText) } ))
if self.textAreaID and self.textAreaID != self.textAreaName:
# print "text area idval" + self.textAreaID 0
self.appendData( urlencode( {self.textAreaID0:" ".join(self.textAreaText ) } ))

def handle_data(self,text):
if self.inTextArea:
self.textAreaText.append( text)
#print text

This class will parse the forward mail page out into the data member so that I can then use it to send an http post request to the remote server, thus:

def forwardMail(url):

replyTag = "/member/reply.php"

# of the form /member/mail.php?id=3298
splitURL = url.split("?")

data = "%s&btn=Forward" % splitURL1
request = ClientCookie.Request(macMailURL + replyTag)
response = ClientCookie.urlopen(request,data)
page = response.read()
#print page
mb = getMailBody()
mb.feed(page)
mb.close()
forwardTag = "/member/send_mail.php"
data = mb.data.replace("to=","[email protected]")
request = ClientCookie.Request(macMailURL + forwardTag)
response = ClientCookie.urlopen(request,data)
page = response.read()

Of course, replacing fred with your mail. I’ll leave working this out to the reader.

Macmail does delete though the move command. I reused the urllist from the last page:

def deleteMail(urlList):
deleteTag="/member/move.php"
amp = ""
deleteData = []
for val in urlList:
bits = val.split("=")
#print bits
theID = bits1
deleteData.append(theID)
data = "delete[]=" + "&delete[]=".join(deleteData)
#print data
request = ClientCookie.Request(macMailURL + deleteTag)
response = ClientCookie.urlopen(request,data)
return response.read()

This will return the next page after all of the mail displayed on the current one is deleted.  Here we glue it all together:

###################################
# Processing body
###################################

page = login2Macmail()

while True:
parser = collectTags()
parser.feed(page)
parser.close()
if not parser.urls:
break
for url in parser.urls:
forwardMail(url)

# Submit delete request for all URL’s from first page
# get page again

page = deleteMail(parser.urls)

#print page

## Now delete all sent mail

request = ClientCookie.Request(macMailURL + "/member/index.php?folder=SentItems")
response = ClientCookie.urlopen(request)
page = response.read()

while True:
parser = collectTags()
parser.feed(page)
parser.close()
if not parser.urls:
break

page = deleteMail(parser.urls)

#print page

request = ClientCookie.Request(macMailURL + "/member/empty_trash.php")
response = ClientCookie.urlopen(request)
page = response.read()

This little control block loops through all of the inbox pages until there are no more mail viewing url’s left, as it goes it deletes them all. Then it goes to the outbox and deletes all of that, then it calls the empty trash function to be polite. I don’t want to leave a load of junk on that server, the mail account has been very useful over the years and I wouldn’t like to annoy the people running it.

For the record, the import statement at the top is this:

import ClientCookie, urllib2
from sgmllib import SGMLParser
import htmlentitydefs
import sys
from urllib import urlencode

Have fun, and don’t eat too much Java, it’s bad for you and takes too long, life is short, use proper powerful tools.

Updating and inserting an xml attribute

Wrote a little java function that does what I need. Oracle’s JDeveloper version of XMLDom doesn’t support Xpath so I have to search every node by hand and then find the one with the attribute set to the value I want to find, sigh. Then I update the attribute I need to update. However it does work so fine.

Ok says I, now that I know what to do let’s translate it into PL/SQL.

I’m convinced that the two functions are logically equivalent but no, they return different results, the PL/SQL one being wrong.

Will try again on Monday. Joy.

Not that I don’t have a whole ruck of more important stuff to do, of course.

This really shouldn’t be that hard and XPath should be implemented everywhere, not just for the XMLtype. But that would be too easy, wouldn’t it.

I will post the code here once I’ve got it working…

Joy.

Update: I think that you can do the XPath thing with one of the document methods that gets lists of nodes (I remember this from ages ago) but haven’t had time to look into it properly.

Oracle XMLDB – is it a bag of spanners?

This is driving me nuts

There’s a new type called XMLtype.

I can retrieve data from it no problem.

I can update an entire node no problem using XMLUpdate method, but it won’t let me update attributes. If it does I can’t find an example anywhere.

If an attriibute wasn’t supplied but you want to put it in .. how do you do it? Only half of the necessary methods have been implemented.

For those non-UK readers – a bag of spanners means that it looks useful but isn’t because you can’t find the right one, after you’ve tried all 50 of them none of them fit. It’s also used to mean a really ugly person, as in face like a … but I’d never describe a fellow human being that way.

I’m looking at the DBMS_XML package but the documentation and examples are total crap. I’m convinced there used to be a halfway decent example in the manuals but it isn’t there any more.

I’m going to do it in Java. I’ve had enough.

Fun with the Emacs Psychotherapist

I am the psychotherapist. Please, describe your problems. Each time you are finished talking, type RET twice.

yarbo adnf hfodnde bbdyyer a

Why do you say yarbo adnf hfodnde bbdyyer a?

yhnfhhd ddfdbb gfffasd ettetdfdsad

Is it because of your plans that you say yhnfhhd ddfdbb gfffasd ettetdfdsad?

bbdfdasdft tte fdfdf dasdfd egr gdffdasfasdf asdf d ffhhhfd hdhfd
fdhhdhfdjh hdha
asdfas dhhdjkhdd uuuasdf fhfheuexccveddasdf?

Maybe your life have something to do with this.

mmm

Can you elaborate on that?

ndfhdh fhdhf datdstfas dfgggfdgfhdasf if tger hf d dhfdasf

What makes you believe that?

befcydfe cyevvytadf yye cbbg asdfda hfbvgghh

Are you sure that befcydfe cyevvytadf yye cbbg asdfda hfbvgghh?

y

I would appreciate it if you would continue.

no

Defector

XEmacs is a nice GUI. Problem is, most of the handy little utilities you need are written for GNU-Emacs and they don’t work too well.

I wanted to get a function like the vi `dot.’ function, that simply repeats the last command. It’s not there in XEmacs. This has been in GNU-Emacs for ages, and there is a patch for XEmacs, but I don’t have the energy to mess about building one or contacting the guy that implemented it to see if there’s a windows build with it in.

I wanted a function like the gvim get-keyword (normally bound to ^X^P) that takes the characters you’ve just typed, scans backwards (or forwards if you use ^N) for a word like the one you’ve just typed a bit of. I found this for GNU Emacs and it would’t compile with XEmacs. Have’t tried it yet but I know it’ll work. Gvim has a way of getting a whole line but I don’t need it that much.

I wanted a function like gvim find word under cursor (bound to * for forward and # for backward)  and found a very simple example that I hacked to work in both directions; to be fair this worked on both. I’ve bound it to control- keypad * and -. I suppose if you’ve got tags it is’t that important, but I’ve yet to see proper tags for PL/SQL (not so sure about Python).

I don’t like the poor mouse menu stuff in XEmacs either. The mouse works like with X-Windows on GNU and if you hold down control you can get to the buffer menu, the edit menu and the mode menu from the 3 mouse buttons. XEmacs only gives you the mode menu on the third mouse button so you can’t do silly stuff like select. copy, move to another location, paste. The X-Window behaviour gives you that anyway in a much neater form. This functionality does’t work quite correctly in gvim so one up for GNU Emacs I think.

[[ Update Dec-06 most of this stuff is there but called something different, the copy existing word in buffer is

(global-set-key [C-return] ’dabbrev-expand)

– here bound to control-return. Find element under cursor, press incremental search and use ^K to select more and more of the word. Also works with reverse search as well. ^Y after incremental selects the whole of the rest of the line ]] 

Tabs and indentation

This is a total pain. Tabs are mode dependent and indentation depends upon the whims of whoever set up the mode you are using. In essence, to make it work correctly for the Enter key, do this in your run file (works with both):

(global-set-key “C-m” ‘newline-and-indent)

PL/SQL mode is’t quite right. When I have the time I’ll fix it and send it back to the guy who wrote it originally. I love being able to pick the procedures and functions from a list.

I’ve also got

(setq indent-line-function ‘indent-relative-maybe)

(setq-default tab-width 2)

But can’t remember why at the moment!

Rectangles

Not as good as gvim, I can mark a block and indent from it. I think that once I can remember the rectangle mnemonics I’ll be OK. Bought the O’Reilly GNU Emacs book with my Xmas book token – god I’m sad.

The XEmacs mouse rectangle stuff is difficult to use. I think the GNU will win out when I get used to it.

It’s been an interesting few days but I think my productivity will start to rise, particularly with the more powerful PL/SQL mode.

Just need to add all of my abbreviations in next …

 [[ Update Dec-06 dabbrev-expand makes running SQL*Plus a dream, press your bound key and up pops the column name or value from any other open buffer – brilliant ]]