Just a quick post to point out that the Alexandria PL/SQL Library has been updated to v1.7, including updates to the Amazon S3 package and a new package for generating iCalendar objects – more details on Morten’s blog.
I recently saw this question on StackOverflow (“Is there any way to determine if a package has state in Oracle?”) which caught my attention.
You’re probably already aware that when a package is recompiled, any sessions that were using that package won’t even notice the change; unless that package has “state” – i.e. if the package has one or more package-level variables or constants. The current value of these variables and constants is kept in the PGA for each session; but if you recompile the package (or modify something on which the package depends), Oracle cannot know for certain whether the new version of the package needs to reset the values of the variables or not, so it errs on the side of caution and discards them. The next time the session tries to access the package in any way, Oracle will raise ORA-04068, and reset the package state. After that, the session can try again and it will work fine.
Side Note: There are a number of approaches to solving the ORA-04068 problem, some of which are given as answers to this question here. Not all of them are appropriate for every situation. Another approach not mentioned there is to avoid or minimize it – move all the package variables to a separate package, which hopefully will be invalidated less often.
It’s quite straightforward to tell whether a given package has “state” and thus has the potential for causing ORA-04068: look for any variables or constants declared in the package specification or body. If you have a lot of packages, however, you might want to get a listing of all of them. To do this, you can use the new PL/Scope feature introduced in Oracle 11g.
select object_name AS package, type, name AS variable_name from user_identifiers where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type in ('VARIABLE','CONSTANT') and usage_context_id in ( select usage_id from user_identifiers where type = 'PACKAGE' );
If you have compiled the packages in the schema with PL/Scope on (i.e.
alter session set plscope_settings='IDENTIFIERS:ALL';), this query will list all the packages and the variables that mean they will potentially have state.
Before this question was raised, I hadn’t used PL/Scope for real; it was quite pleasing to see how easy it was to use to answer this particular question. This also illustrates a good reason why I like to hang out on Stackoverflow – it’s a great way to learn something new every day.
What’s the biggest clue you can give that your database is vulnerable to SQL injection? When your list of “forbidden words” looks suspiciously like a sample of SQL / PL/SQL keywords:
I notice that they haven’t forbidden BEGIN, CREATE, MERGE, or TRUNCATE …
Congressman Peters, your IT staff are doing it wrong.
1. Overly-generic parameter types
When you send an email, it will have at least one recipient, and it may have many recipients. However, no email will have more than one sender. Yet, I wrote the package procedure like this:
TYPE address_type IS RECORD (name VARCHAR2(100) ,email_address VARCHAR2(200) ); TYPE address_list_type IS TABLE OF address_type INDEX BY BINARY_INTEGER; PROCEDURE send (i_sender IN address_list_type ,i_recipients IN address_list_type ,i_subject IN VARCHAR2 ,i_message IN VARCHAR2 );
Why I didn’t have i_sender be a simple address_type, I can’t remember. Internally, the procedure only looks at i_sender(1) – if a caller were to pass in a table of more than one sender, it raises an exception.
2. Functional programming to avoid local variables
Simple is best, and there’s nothing wrong with using local variables. I wish I’d realised these facts when I wrote functions like this:
FUNCTION address (i_name IN VARCHAR2 ,i_email_address IN VARCHAR2 ) RETURN address_list_type; FUNCTION address (i_address IN address_list_type ,i_name IN VARCHAR2 ,i_email_address IN VARCHAR2 ) RETURN address_list_type;
All that so that callers can avoid *one local variable*:
EMAIL_PKG.send (i_sender => EMAIL_PKG.address('joe','email@example.com') ,i_recipients => EMAIL_PKG.address( EMAIL_PKG.address( 'jill', 'firstname.lastname@example.org') ,'bob', 'email@example.com') ,i_subject => 'hello' ,i_message => 'world' );
See what I did there with the recipients? Populating an array on the fly with just function calls. Smart eh? But rather useless, as it turns out; when we need to send multiple recipients, it’s usually populated within a loop of unknown sized, so this method doesn’t work anyway.
Go ahead – face your past and dig up some code you wrote 5 years ago or more. I think, if you don’t go “WTF!” every now and then, you probably haven’t learned anything or improved yourself in the intervening years. Just saying :)
Ever since I downloaded the Alexandria PL/SQL library, I haven’t been able to put it down. Just recently I decided I wanted to serve up a whole lot of media files directly from Amazon’s S3 simple storage service, instead of serving them from within my EC2 (elastic compute) instance. They were just wasting my linux server’s time responding to http requests.
So, I quickly wrote the following code to transfer them:
DECLARE l_blob BLOB; BEGIN /* initialise my AWS session */ ALEX.amazon_aws_auth_pkg.init ( 'yyy-my-aws-id-yyy' , 'xxx-not-telling-xxx' , p_gmt_offset => -8); FOR rec IN ( SELECT id, filename, mime_type, location FROM myfiles WHERE location = 'http://myserver/media/' ) LOOP /* read the file from its current location */ l_blob := ALEX.http_util_pkg.get_blob_from_url (rec.location || rec.filename); IF DBMS_LOB.getLength(l_blob) > 0 THEN /* upload the file to Amazon S3 */ ALEX.amazon_aws_s3_pkg.new_object ( 'mybucket' , rec.filename , l_blob , rec.mime_type , ALEX.amazon_aws_s3_pkg.g_acl_public_read); UPDATE myfiles SET location = 'https://mybucket.s3-ap-southeast-1.amazonaws.com/' WHERE id = rec.id; COMMIT; END IF; END LOOP; END;
After a short while, all the files had been copied across to my bucket on S3, and my table updated so that my web site now points people’s browsers to the new location for those files.
Of course, I could have used UTL_FILE to read the files from disk, but then I’d have to first create a directory, and write a loop to read the file in chunks into the BLOB. Why bother with all that when I can just call http_util_pkg.get_blog_from_url and get it all in one go?
That’s the trouble with powerful utilities like Alexandria: they’re too easy to use, make tasks like this trivial, and you start finding all sorts of uses for them. All of a sudden, Alexandria is your hammer, and the world is full of nails.
See also: this quick intro to using Alexandria’s API for Amazon S3.