Oracle Packages that include Java

Here's a cheat sheet for including Java stored procedures  in packages.

Write a class:

    public class OracleFoo {
        public static void myproc(int port, String s) throws  Exception {
        }
    }

Compile and Load:

    javac OracleFoo.java
    loadjava  -u scott/tiger OracleFoo.class

Declare a package:

    create or replace package oraclefoo
    as
        procedure myproc(port number, s varchar2)
          as language java name 'OracleFoo.myproc(int, java.lang.String)';
    end;

Call:

    begin
        oracletbslow.myproc(7728,'[1,"a","b1",{}]');
        oracletbslow.myproc(7727,'[1,"a","b2",{}]');
    end;   

Permissions:

    need to set these, double-check notes...

Six Keys to Being Excellent at Anything - Tony Schwartz - The Conversation - Harvard Business Review

Six Keys to Being Excellent at Anything - Tony Schwartz - The Conversation - Harvard Business Review:

1. Pursue what you love.
2. Do the hardest work first.
3. Practice intensely
4. Seek expert feedback, in intermittent doses.
5. Take regular renewal breaks.
6. Ritualize practice.

An AskTom about database APIs

An interesting note by Tom regarding APIs in the database.

API's -- all about API's. The applications don't do table level stuff, the apps don't even really *know* about tables. I have a schema. On top of schema we have an API that does the data stuff. In the application we call this API but have our own application logic as well (only application logic is in the application, data logic is -- well, right where it belongs -- next to the data, waiting for the 'next great programming paradigm to come along') [...] Applications -- a dime a dozen. They come, they go, they have programming paradigm of the week. Data -- priceless. Most of it has been here for a long long long time. Most all of it will be here after we are dead.

What does "flatdown" mean?

It's referenced in the OPML spec. Here's an explanation:

First, flatdown, in UserLand's outliner terminology, is a direction. The complete list of directions is: up, down, left, right, flatup, flatdown and nodirection. They are used as parameters to the op.xxx verbs which take a direction as a parameter. The first four are structured directions. Going up from an outline element takes you to its previous sibling, down takes you to the next sibling. Left takes you to the parent, right takes you to the first child. The flat directions, flatup and flatdown view the outline as a linear sequence, not structured. Imagine the outline displayed on a screen. To go flatup you'd go to the outline element displayed immediately before, to go flatdown you'd go to the outline element displayed immediately after.

Note to a Software Vendor, regarding Document Managment Integration

Hi ----, Thanks for getting back so soon. I hope you can look at this issue over your entire product line. Here's the situation as I see it. Ancient Times:

• I had "my" computer, "my" files, I managed everything myself. It was all about me, Me, ME! If you wanted a file, I would mail it to you or copy it to a network share.

Modern Times:

• I have at least two computers, a desktop and a laptop. My files are under revision control and stored in a central repository. I sync files to my local box and work on them. Sometimes I work on the same file on two boxes -- my desktop coz it has a big screen, and my laptop coz I can take it to meetings. If you want a file, I can just tell you where it is on the central server and you can access it at your leisure. And good news for you, I have two copies each of [your product].

Most existing software works well in the first case. Software that works in the second case will, IMHO, start to replace software that does not, even if the other software is better in most other regards. As an example, we've installed [a web-based package] on our wiki. Butt ugly and limited, but certainly matching the modern time requirements and very "friction free" with regards to sharing files. Of course, it's hard for any local app to beat a web app in the sharing arena, but I hope that all your products become "so close" that there's no temptation for people to switch. As a practical thing, here's what I would love to see for all your products.

• easy to check in / check out in revision control system.

This means either

• - making documents into single files
• - adding support for various revision systems
• - adding hooks so revision system plugins can be created

• easy multi-machine usage.

This is a big deal for something like [the project tracker package]. I'm sad to say I've got two copies but I stopped using it, because I would leave it running on my desktop, and then I couldn't run it on my laptop at home. Or I would add some action items in a meeting on my laptop, close the lid, and at my desktop, not have them show up. For programs like this, you should look at how IMAP email works. It is very convenient, I have my desktop mail open, my laptop mail open, my PDA mail open, and no matter what I do they all just magically stay in sync. Anyways, all IMHO, of course, and I hope this is useful feedback to you. Regards, Mark

begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin insert into jobtest(ztime) values(systimestamp); end; '
,start_date => trunc(sysdate, 'CC')
,repeat_interval => 'FREQ=MINUTELY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;

begin
DBMS_SCHEDULER.DROP_JOB('DEMO_JOB_SCHEDULE');
end;

-- show all jobs

select *
from dba_scheduler_jobs
where owner='MHTEST';


-- job history

select log_date
, job_name
, status
from dba_scheduler_job_log
where owner='MHTEST';

-- running jobs

select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs

using dbms_application_info

exec DBMS_APPLICATION_INFO.SET_MODULE( NULL,NULL );
exec DBMS_APPLICATION_INFO.SET_MODULE('my module', 'my action');
exec DBMS_APPLICATION_INFO.SET_ACTION('my action 2');
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO('my client info');

-- exec DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(rindex, slno,
--      'Test Operation', obj, 0, sofar, totalwork, 'Contract', 'Contracts');

select sid,program,module,action,client_info from v$session where username='MH'; 

Oracle/PLSQL: Oracle System Tables

Oracle/PLSQL: Oracle System Tables: "an alphabetical listing of the Oracle system tables that are commonly used."

Desc - Describe an Oracle Table, View, Synonym, package or Function

Desc - Describe an Oracle Table, View, Synonym, package or Function: "An alternative to the DESC command is selecting directly from the data dictionary - DESC MY_TABLE is equivalent to SELECT column_name 'Name', nullable 'Null?', concat(concat(concat(data_type,'('),data_length),')') 'Type' FROM user_tab_columns WHERE table_name='TABLE_NAME_TO_DESCRIBE';"

python xml-rpc, supporting 64-bit ints

xml-rpc is nice, but brain-dead in that it doesn't support 64-bit integers. (Update: I was just being polite.  xml-rpc is brain-dead all around). There are a couple of extensions to the xml-rpc standard that suggest a new tag for this. If you're using the python xml-rpc library and wish to have 64-bit int support, you can comment out the two lines below in xmlrpclib.py:

def dump_long(self, value, write):
    #if value > MAXINT or value <>
    # raise OverflowError, "long int exceeds XML-RPC limits"
    write("")
    write(str(int(value)))
    write("\n")
    dispatch[LongType] = dump_long

Tracing an Oracle session in 10g

Setting permissions:

GRANT EXECUTE ON SYS.dbms_monitor TO username; 

Turn the trace on and off:

EXECUTE dbms_monitor.session_trace_enable
    (session_id=>NULL, serial_num=>NULL, waits=>TRUE, binds=>TRUE);
EXECUTE dbms_monitor.session_trace_disable
    (session_id=>NULL, serial_num=>NULL); 

Older versions of Oracle:

alter session set timed_statistics=true;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='Hello';
alter session set events '10046 trace name context forever, level 12'; 

Look at the results with tkprof. The "aggregate" flag is especially useful.

LD_DEBUG: debugging ld.so on linux

David Baraff told me about this awesome way to see what ld.so is doing on linux.

    $ LD_DEBUG=help /bin/cat
    Valid options for the LD_DEBUG environment variable are:

    libs display library search paths
    reloc display relocation processing
    files display progress for input file
    symbols display symbol table processing
    bindings display information about symbol binding
    versions display version dependencies
    all all previous options combined
    statistics display relocation statistics
    unused determined unused DSOs
    help display this help message and exit

    To direct the debugging output into a file instead of standard output
    a filename can be specified using the LD_DEBUG_OUTPUT environment variable.

John Backus on debugging

John Backus describes debugging, circa 1957:

In order to produce a program with built-in debugging facilities, it is a

simple matter for the programmer to write various PRINT statements, which

cause "snapshots" of pertinent information to be take at appropriate points in

his procedure, and insert these in the deck of cards comprising his original

FORTRAN program.  After compiling this program, running the resulting machine

program, and comparing the resulting snapshots with hand-calculated or

known values, the programmer can localize the specific area in his FORTRAN

program which is causing the difficulty.  After making the appropriate corrections

in the FORTRAN program he may remove the snap-shot cards and recompile

the final program or leave them in and recompile if the program is

not yet fully checked.

The FORTRAN Automatic Coding System

Proceedings of the Western Joint Computer Conference

Los Angeles, California, February 1957

Stopping in a C++ function called via Python

How do i tell totalview to stop at a line number in a particular file? This is a C++ function in a SO which is being loaded as part of a python import.

Leaf Nunes has the most awesome answer:

I 'm not sure how you tell totalview via totalview, but you can tell the C++ to stop at a line by adding:

asm("int $3");

This is the only way i've seen to pre-set a breakpoint in C code referenced by a python binary running under totalview. Once it's stopped, you should be able to look around all the libraries the python binary has loaded and set breakpoints as you desire.

Arun Rao:

If you run the python program until it finishes, then you can set the breakpoint because the library will have been loaded, and restart the process.

Introspecting python variables (to print a debug message)

I asked about introspecting python variables. I was looking for something similar to Tcl's upvar command. I received a couple of good responses. I didn't ever end up using them for debugging, but I sure learned a lot more about Python introspection than I knew previously.

Alex Mohr:

We have to remember that an arbitrary number of variables, all with different names, can refer to the same object. For example, 

foo = 'something'

bar = foo

Now suppose I say, printvars('message', bar). The printvars function will get a reference to the 'something' string, but it doesn't know that the name of the reference I called you with was 'bar' rather than 'foo'. This function will take your argument, and look through globals() and find all the names that refer to that object, and then print those out, but this is almost certainly slow, fragile and useless:

def printvars(msg, *args):
    print msg, ':',
    for arg in args:
        matches = []
        for name, val in globals().items():
            if arg is val:
                matches.append(name)
        for match in matches:            print match+',',
        print '=', repr(arg)+';',
    print ''

If I use this as with my hypothetical example above, it produces this output:

>>> printvars('message', bar)
message : bar, foo, = 'something' 

Erick Tryzelaar:

This is doable with scary python black magic:

import sys
def foo(msg, *args):
   caller = sys._getframe(1)
   print msg, ' '.join(['%s=%s' % (x, caller.f_locals[x]) for x in args])
x = 5
y = 6
foo('hello', 'x', 'y')
hello x=5 y=6

Lars Damerow:

I agree with Erick and Alex here -- you can't get away from passing the variable names in as strings, since Python just considers variable names to be string keys for the local and global scope dictionaries. I also second Erick's opinion that using sys._getframe is not a good idea. It may look redundant, but the simplest and most bulletproof approach is probably something like this:

def printvars(msg, *args):
    print msg, ' '.join(['%s=%s' % (x, v) for (x, v) in args])
>>> x = 1
>>> y = 2
>>> printvars("hello", ("x", x), ("y", y))
hello x=1 y=2

We could make that a lot cleaner if Python had macros, but it doesn't. If we knew that all of the desired variables are in the same dictionary (which includes locals() and globals()), we could obviously clean up the calls like this:

def printvars(msg, scope, *args):
    print msg, ' '.join(['%s=%s' % (x, scope[x]) for x in args])
>>> x = 1
>>> y = 2
>>> printvars("hello", locals(), "x", "y")
hello x=1 y=2

Jim Atkinson:

Although you cannot avoid giving the name as well as the value for the variable. You can use keyword syntax to pass them and avoid having to type quotes around the name.

def printvars(msg, **kw):
    print msg,
    keys = kw.keys()
    keys.sort()
    for key in keys:
    print "%s=%r", (key, kw[key])
    print

x = 1 
y = 2 
printvars ("hello", x=x, y=y)
hello x=1 y=2 

The only downside is that you cannot control the order of the variables, it is always alphabetical.

Omnigraffle: Don't write bundles!

Sometimes omnigraffle will rewrite a file into a bundle. It can be configured not to do this:

defaults write com.omnigroup.OmniGraffle PrivateGraffleFlatFile 0
defaults write com.omnigroup.OmniGrafflePro PrivateGraffleFlatFile 0

Oracle: row-wise comparison

I had a query that needed to compare two columns in a row andwas making two queries to do so. All hail the row-wise comparison!

select id from p4_versions
where (versionof, version) = (select id, headver from p4_files
      where p4path like '%/date.txt');

How to Create a REST Protocol

"REST is an architectural style that can be used to guide the construction of web services. Recently, there have been attempts to create such services that have met with mixed success. This article outlines a series of steps you can follow in creating your protocol--guidance that will help you get all the benefits that REST has to offer, while avoiding common pitfalls."

How to Create a REST Protocol

Python: retrieving the oracle error code

Anthony Tuininga gave me this hint on retrieving the Oracle error code from a cx_python program:

try:
    cursor.execute("select 1 / 0 from dual")
except cx_Oracle.DatabaseError, e:
    print "Exception:", e    # this is what you are looking at right now
    errorObj, = e.args       # this is the actual "error" that cx_Oracle raises
    print "ErrorCode:", errorObj.code
    print "ErrorMessage:", errorObj.message

Causing Guido van Rossum to ask

This is overly subtle. Why not write

    errorObj = e.args[0]

and received the response

No particularly good reason -- it just happens to be a way of indicating that there should be one and only one entry in the list. An exception is raised if there is not whereas args[0] does not raise the exception. Its an open question which is the better code.