Friday, February 26, 2010

Do you know about DBMS_SQL.LAST_ERROR_POSITION

I've been prompted to this posting by a Stackoverflow question on locating the column responsible for an ORA-01722 (invalid number) error.

Tucked away in the bowels of the under-used DBMS_SQL package is a LAST_ERROR_POSITION routine.

The documentation on it is a big vague with the suggestion that you "Call this function after a PARSE call, before any other DBMS_SQL procedures or functions are called." Rubbish, I respond.

You can call it any time you like in an error handling routine and it will tell you the character offset in the SQL where the error occurred. Which is good for those errors like 'Invalid Number' and value error, not a valid month and so on. You need to count the offset from the start of the SQL. If whitespace is making it tricky, a trace would tell you the SQL as it was parsed (as might a look in v$sql).

DECLARE
    v_ret NUMBER;
    v_text varchar2(10) := 'a';
BEGIN
    insert into a_test (val1, val2) values (1,v_text);
exception
    when others then
        v_ret := DBMS_SQL.LAST_ERROR_POSITION;
        dbms_output.put_line(dbms_utility.format_error_stack);
        dbms_output.put_line(dbms_utility.format_error_backtrace);
        dbms_output.put_line('Error at offset position '||v_ret);
        raise;
END;
.
/

Wednesday, February 24, 2010

Scattered thoughts on names

Somewhere in London there is a Miss Myers wait for Marks and Spencer to tell her that she can pick her dress at their Fenchurch Street store. She's not going to get that email because my wife has it.

While there has been publicity about IPv4 addresses running out, running out of email addresses hasn't had the same publicity. One reason is that you can simply add numbers on the end of an email address. It is possible that the Miss Myers is actually Miss Myer5.

Personally I don't want an email address ending in a number. I am not a number. It took a while for me to find a gmail combination that hadn't already been taken, but I found one. Even if you can't there are enough email providers out there that you don't need that.  And as I mentioned previously, it is cheap enough to get your own host name with up to 50 email addresses (though garymyers.com and garymyers.com.au were already taken).

That's fine for personal email where you have a choice. At one place I worked, I did need a number as my 'name' address already belonged to someone else, and I don't have a middle name to act as a tie-breaker.

As a parent, you have to consider all this when naming your child. About 15 years ago I worked with a Mr Goodger. His parents had given him the name Martin, and at the place we worked he got assigned the username of Goodgerm (which I support is better then Badgerm). Try to pick a more unusual given name so that your child has a better chance of controlling their personal brand.

As a final though, I'm working on an interface to an external system which requires both a Given and Surname. Given some cultures only have a single name, I expect this to cause problems at some time.

Tuesday, February 16, 2010

New addition to my computer family

A good while back, Chris Muir said that he didn't expect to see "Oracle running on the EeePC (Oracle XEeePC, or ZeepC)" at OpenWorld.

Well I've never made it to OpenWorld, but have just added 'Middy' to my computer family, an ASUS EEEPC 1001HA.



The specs are typical of a low-end netbook, Intel Atom N270, 1 Gb of RAM (which I'll probably upgrade in due course), 160GB hard drive, wireless, 10" screen, webcam and XP Home. And, amongst the normal bunch of tools and utilities I added, I successfully installed OracleXE and VirtualBox..

The general idea is that I can use it on the train catch up on podcasts and work on material for my blog / website.

One thing I noticed as I set it up is that Windows has a screen, accessed through control panel, where you create users and their passwords. This screen offers to save a 'password hint' and at the XP login screen, there's a little question mark button which will display the hint to remind you of the password. But when you change your password the way I do (Ctrl-Alt-Del, and the Change Password button) there is no opportunity to set a new hint. As such the hint will probably be for a password which is out-of-date by several months or years. I mean, you DO change your password regularly, don't you ?

Of course it is relatively trivial to overwrite XP passwords with a rescue CD, so really the password is no deterrent to thieves, miscreants or James Bond. I guess its mostly there to protect the machine from my wife, kids, friends and colleagues (not necessarily in that order, but I am determined to keep the games off this).

Saturday, February 13, 2010

Oracle PL/SQL Programming 5th Edition - The final chapters

Following up my earlier post on the missing chapters from Oracle PL/SQL Programming, Fifth Edition, I've had a response from O'Reilly and the chapters are now available in the 'Examples' section of the books' web-page.

Once I've read through them, I'll post my review.

Friday, February 12, 2010

Sydney Oracle Meetup review - Marcel's Multimedia workshop

Last night, I enjoyed an evening of pizza and beer at the Sydney Oracle Meetup for a talk on "How Build Fast, Secure Web Apps with the PL/SQL Gateway & Oracle Multimedia".

We had an interesting talk from Marcel Kratochvil, our local Oracle Ace Director. Actually he and Richard Foote (another Ace Director) are both based in Canberra, which isn't technically part of NSW and is several hours drive away, but Sydney lost our Ace Director when Alex escaped back to Canada so we'll call Canberra 'local'. In fact, without Alex (and HJR who resigned his Ace-ship), we are down to Steve Adams (of IxOra and oracle-l) as our only Sydney ACE.

Marcel treated us to his MultiMedia workshop, the material for which is on his website.

I've worked a bit with Apex. I even got as 'multimedia' as getting a voicemail attachment from an email and letting people listen to it from an Apex page. But I did so from a 'relationist' point of view, treating it as just a BLOB in a table. Though in my defense, that was all that was required.

With all the extra 'bits' that are hidden in multi-media files (our resident photo guru Nuno was nodding when hearing about EXIF) I can see Marcel's point of view when it comes to foreign keys and referential integrity. These objects are not a simple datatype (ie one that conforms to the atomicity criteria of first normal form) and there's no benefit to trying to break the object into distinct 'content' and 'metadata' components in a relational manner. And once you've accepted and dealt with such situations, you would consider foreign key constraints from a practical 'how do I benefit in this particular case'  mindset.

That said, I generally work with more structured data so will still err on the side of more constraints rather than less.

I was impressed with the ease of getting the multimedia objects in and out of Oracle. I've done it with Apex, but it was only that one time I got into the nitty-gritty of things and it is hard to judge how much is Apex and how much is the lower level mod/plsql stuff.

I had my little play with Spatial/Locator. Now its time I did some playing with some of the multimedia objects types mentioned.

Monday, February 08, 2010

Append Values and how not to break the database

With the advent of the /*+ APPEND_VALUES*/ hint in 11gR2, I suspect we will see the feature misused and a bunch of questions on why it "doesn't work". The documentation states that "direct-path INSERT can be considerably faster than conventional INSERT.". What it should state is that it can also be considerably slower (plus that, if logging is not enforced at either the table or tablespace level, it could also render backups unusable).

Firstly, direct path inserts work by inserting data at the end of existing data, above the high water mark. If you have two conventional inserts throwing data at a table, they can each move the high-water mark as required. Session 1 can move it out by 10 blocks, then session 2 can move it out another 2, then session 1 moves it again. The high-water mark is metadata about the table. It will be stored in one place and there is the potential for contention by multiple sessions wanting to change it at once. For conventional inserts, as soon as the session has adjusted it, it releases its hold and other sessions can do their adjustments. It doesn't need to wait on session commits, and contention generally isn't a problem.

In direct-path inserts, the insert moves the HWM but cannot release its hold on that information. That's because the data it is writing between the old and new HWM is 'dodgy'. It isn't yet committed and it shouldn't be read into the buffer cache. [I suspect it is written to the data file as if it were committed rather than with lock flags and transaction identifiers. That way it avoids the need for a delayed block cleanout when it is subsequently read.] If another insert (or even an update or merge) on the table needs to move the HWM, it has to wait until the direct path transaction is committed or rolled back.  That could happen with conventional insert/update/merge, but will ALWAYS happen with another direct path insert.

Try this in one session
drop table TEST_IA purge;
create table TEST_IA (id number, val varchar2(4000));
insert /*+ APPEND */ into TEST_IA
select rownum rn, 'b' val from dual connect by level <= 1000;

And then this in another
insert /*+ APPEND */ into TEST_IA
select rownum rn, 'b' val from dual connect by level <= 1000;

See what I mean ?
If you have 11gR2, insert /*+ APPEND_VALUES */ into TEST_IA values (1,'b'); will have the same effect.

So the first failure pattern I predict will be multiple sessions all trying APPEND_VALUES into the same table and knocking heads together. This would most likely happen when developers have tried to parallelise operations outside the database. Knocking heads together may be the appropriate solution too.

I suspect this will be somewhat mitigated by the second failure pattern. Once you've done a direct path insert, if you try anything else on that table in the same session, you'll get a "ORA-12838: cannot read/modify an object after modifying it in parallel" error message. I'd say the error message was somewhat mis-leading, but a quick google will tell them that the solution is to do a commit after the insert. You can split coders into two groups, the first who understand the concept of a transaction, and the second who don't. I think the number of the latter are increasing. Even if it is okay to commit, you could still have log sync waits.

The final failure pattern I predict will be those who think "Ah, I can do inserts without generating log data. That should be faster.". The problem is that the metadata changes, moving the HWM, are logged and it is only the creation of content data that might be unlogged. In the following script, I compare several approaches to inserting a single record in a loop. When compared with a conventional part insert, it is apparent that a lot more redo is generated for the single row direct path insert with a small record size. When I used a larger record size (padding the value to several thousand characters) the redo size was comparable but there were still fewer redo entries in the conventional path inserts. I do concede logging is not the only performance impact and performance may still improve due to bypassing of the buffer cache, no need to locate free space in the table etc.

drop table TEST_ROW_IAV purge;
drop table TEST_ROW_IA purge;
drop table TEST_ROW_IV purge;
drop table TEST_ROW_I purge;

create table TEST_ROW_IAV (id number, val varchar2(4000));
create table TEST_ROW_IA  (id number, val varchar2(4000));
create table TEST_ROW_IV  (id number, val varchar2(4000));
create table TEST_ROW_I   (id number, val varchar2(4000));

clear screen
declare
  cursor c_1 is
    select rownum rn, 'b' val from dual connect by level <= 10000;
  --
  procedure rep_ext (p_seg in varchar2)
  is
    cursor c_e  is
      select rpad(segment_name,20) segment_name, tablespace_name,
             count(extent_id) cnt, sum(round(bytes/1024)) kb, sum(blocks) blocks
      from user_extents
      where segment_name  = p_seg
      group by segment_name, tablespace_name;
  begin
    for c_out in c_e loop
      dbms_output.put_line(to_char(c_out.segment_name)||

       ' '||c_out.tablespace_name||' '||to_char(c_out.cnt,'9990')||
       ' '||to_char(c_out.kb,'999,990.00')||' '||to_char(c_out.blocks,'999,990'));
    end loop;
  end rep_ext;
  --
  procedure rep_redo (p_text in varchar2)
  is
    cursor c_r is
      select    sum(case when name = 'redo entries' then value end) redo_entries,
                sum(case when name = 'redo size' then value end) redo_size
      from v$mystat s join v$statname n on n.statistic# = s.statistic#
      where name in ('redo entries','redo size');
  begin
    dbms_output.put(rpad(p_text,20)||' at '||to_char(sysdate,'hh24:mi:ss')||' ');
    for c_rec in c_r loop
      dbms_output.put_line('Entries:'||to_char(c_rec.redo_entries,'9,999,990')||

                           ' Size:'||to_char(c_rec.redo_size,'999,999,990'));
    end loop;
    end rep_redo;
    --
begin
    commit;
    rep_redo('Start');
    for c_rec in c_1 loop
      insert /*+ APPEND_VALUES */ into TEST_ROW_IAV values (c_rec.rn, c_rec.val);
      commit;
    end loop;
    rep_redo('After /*+ APPEND_VALUES */');
    --
    for c_rec in c_1 loop
      insert /*+ APPEND */ into TEST_ROW_IA select c_rec.rn, c_rec.val from dual;
      commit;
    end loop;
    rep_redo('After /*+ APPEND */');
    --
    for c_rec in c_1 loop
      insert into TEST_ROW_IV values (c_rec.rn, c_rec.val);
      commit;
    end loop;
    rep_redo('After insert values');
    --
    for c_rec in c_1 loop
      insert into TEST_ROW_I select c_rec.rn, c_rec.val from dual;
      commit;
    end loop;
    rep_redo('After insert select');
    --
    rep_ext('TEST_ROW_IAV');
    rep_ext('TEST_ROW_IA');
    rep_ext('TEST_ROW_IV');
    rep_ext('TEST_ROW_I');
    --
end;
/
And my results:


Start                at 14:10:59 Entries:       912 Size:     125,628
After /*+ APPEND_VAL at 14:11:02 Entries:   112,547 Size:  15,995,632
After /*+ APPEND */  at 14:11:08 Entries:   224,184 Size:  31,863,240
After insert values  at 14:11:09 Entries:   234,409 Size:  36,723,128
After insert select  at 14:11:11 Entries:   244,634 Size:  41,422,384
TEST_ROW_IAV         USERS    81   81,920.00   10,240
TEST_ROW_IA          USERS    81   81,920.00   10,240
TEST_ROW_IV          USERS     3      192.00       24
TEST_ROW_I           USERS     3      192.00       24

APPEND_VALUES (and indeed APPEND) are not intended for single row inserts, but even small arrays will demonstrate similar problems. Ideally you want each insert to leave full blocks to minimize unused (and probably unusable) space. If you target your array size to a single block, you may find that in practice you get variations between 75% of a block and 1 and a bit blocks (more empty space). However if each insert creates one hundred blocks, you don't mind if the last one is a bit empty. With this in mind you probably want to think of arrays of at least thousands of rows, and maybe in the tens or hundreds of thousands of rows depending on both row size and block size

One more script, looking at how things should be done. Firstly the classical insert with append hint from a select. Secondly, with an array of a hundred thousand rows inserted with the append_values hint. Finally, the '10g' way of a direct path insert from a PL/SQL variable, using SQL types. What you see is that, for this data set, there's not much to choose between the three.

drop table TEST_FORALL_IAV purge;
drop table TEST_IA purge;
drop table TEST_TYPE_IA purge;

drop type type_test_ia;
drop type type_tab_test_ia;

create type type_test_ia is object (id number, val varchar2(4000));
/
create type type_tab_test_ia is table of type_test_ia;
/

create table TEST_FORALL_IAV (id number, val varchar2(4000));
create table TEST_IA (id number, val varchar2(4000));
create table TEST_TYPE_IA (id number, val varchar2(4000));

clear screen
declare
  cursor c_1 is
    select rownum rn, 'b' val from dual connect by level <= 100000;
  TYPE tab_1 is table of c_1%rowtype index by pls_integer;
  t_1    tab_1;
  t_tab    type_tab_test_ia;
  --
  procedure rep_ext (p_seg in varchar2)
  is
    cursor c_e  is
      select rpad(segment_name,20) segment_name, tablespace_name,

             count(extent_id) cnt, sum(round(bytes/1024)) kb, sum(blocks) blocks
      from user_extents
      where segment_name  = p_seg
      group by segment_name, tablespace_name;
  begin
    for c_out in c_e loop
      dbms_output.put_line(to_char(c_out.segment_name)||

        ' '||c_out.tablespace_name||' '||to_char(c_out.cnt,'9990')||
        ' '||to_char(c_out.kb,'999,990.00')||

        ' '||to_char(c_out.blocks,'999,990'));
    end loop;
  end rep_ext;
  --
  procedure rep_redo (p_text in varchar2)
  is
    cursor c_r is
      select sum(case when name = 'redo entries' then value end) redo_entries,
             sum(case when name = 'redo size' then value end) redo_size
      from v$mystat s join v$statname n on n.statistic# = s.statistic#
      where name in ('redo entries','redo size');
  begin
    dbms_output.put(rpad(p_text,20)||' at '||to_char(sysdate,'hh24:mi:ss')||' ');
    for c_rec in c_r loop

        dbms_output.put_line('Entries:'||to_char(c_rec.redo_entries,'9,999,990')||
          ' Size:'||to_char(c_rec.redo_size,'999,999,990'));
    end loop;
  end rep_redo;
    -- 

begin
  commit;
  rep_redo('Start');
  insert /*+ APPEND */ into TEST_IA
  select rownum rn, 'b' val from dual connect by level <= 100000;
  rep_redo('After Append dual');
  --
  open c_1;
  fetch c_1 bulk collect into t_1;
  close c_1;
  forall i in 1..t_1.count
      insert /*+ APPEND_VALUES */ into TEST_FORALL_IAV values t_1(i);
  rep_redo('After Append values');
  --
  select cast(collect(type_test_ia(rn,val)) as type_tab_test_ia)
  into t_tab
  from (select rownum rn, 'b' val from dual connect by level <= 100000);
  --
  insert /*+ APPEND */ into    TEST_TYPE_IA
  select * from table(t_tab);
  rep_redo('After Append type');
  --
  rep_ext ('TEST_IA');
  rep_ext ('TEST_FORALL_IAV');
  rep_ext ('TEST_TYPE_IA');
end loop;
/

And again, my results

Start                at 14:33:04 Entries:       580 Size:     112,392
After Append dual    at 14:33:04 Entries:       850 Size:     153,468
After Append values  at 14:33:04 Entries:     1,116 Size:     193,836
After Append type    at 14:33:05 Entries:     1,383 Size:     234,432
TEST_IA              USERS    17    2,048.00      256
TEST_FORALL_IAV      USERS    17    2,048.00      256
TEST_TYPE_IA         USERS    17    2,048.00      256

Sunday, February 07, 2010

Exploits and revoking the risks of revoking PUBLIC

Oracle DBAs should be aware of the latest exploit published for Oracle 10g and 11g.

The advice given was to revoke some privileges from public. There has been discussion about the number of grants to public and the practicalities of revoking them (eg here).

I feel it is worth giving an overview of public grants and security.

When Oracle wants to give users functionality (eg export), they can go about implementing it in two ways. Firstly, they can build a powerful set of code that runs with SYS or SYSTEM privileges. Because of the potential for that power to be exploited by naughty code, they'd have to build in a lot of checks to block off undesirable effects. They need to try to predict the possible inputs and where they might cause an issue.

The other approach is to have the code run with the user's privileges. That way they can, in theory, miss out a large proportion of those checks. The drawback of this approach is evidenced in this exploit. The user has to be given authority on all the dependencies of the code 'entry point'. So here DBMS_JVM_EXP_PERMS is an undocumented package. As such there is no reason for a user to ever execute it directly, and the intention is that no-one should be executing it. The reason that PUBLIC has been granted execute permission on it is that it is called by another package (presumably DBMS_JAVA, but possibly others such as import) which runs with user's privileges.

You cannot assume that because you do not call something directly it would be safe to revoke PUBLIC privileges on it.

The difficulty with revoking public privileges is that it is very difficult to determine what will break. It may not be a package that breaks, but any Oracle supplied code (eg expdp, log miner, external tables, advanced queuing...). It is safe to assume that Oracle only tests its databases with all those PUBLIC permissions in place. It is also safe to assume that, if you revoke a privilege, something will break. It may well be something you don't use. Given that Java is not present in Express Edition, you'd probably be safe in assuming everything you could do in XE would still work with Java disabled.

But great care should be taken before revoking any default PUBLIC grant, and it should be accompanied by testing as much as possible, of both applications and DBA related tasks (including DR, backup/recovery, database alerts, auditing and anything else you rely on).

This is a dangerous exploit and it will be interesting to see Oracle's response (and whether it comes in the next CPU). David Litchfield is retiring from the Oracle security field which could case a shadow over his decision to release this exploit in advance of any fix.

Friday, February 05, 2010

11g Named notation in SQL for Constructor functions

One of the 11gR1 enhancements allowed the use of named notation in SQL function calls.

In 11g, you can do
  select utl_raw.cast_from_number(n=> 1) from dual;
In 10g, the same sql will error.

I prefer named notation. You can see in the statement what values are being passed where, and that makes mistakes more obvious. The lack of it for SQL calls wasn't generally a big deal though. Code is generally in packages, and you could always create a wrapper function with a different name. However there's one situation where you can't do that, and that concerns constructor functions.

When you create an SQL type, you get a default constructor where, if you pass in all the attributes in the correct order, it will return an instance of the object.
Example:
  select mdsys.sdo_point_type(1,2,3) from dual;
But sometimes you don't want to specify values for all the attributes, so you create a non-default constructor.

create type address_typ is object
  (street_number  varchar2(10),
  street_name     varchar2(50),
  locality        varchar2(50),
  postcode        varchar2(4),
  state           varchar2(3), 
  CONSTRUCTOR FUNCTION
    address_typ(pi_street_number in varchar2 default null, 

              pi_street_name in varchar2 default null,
              pi_locality in varchar2 default null, 
              pi_postcode in varchar2 default null, 
              pi_state in varchar2 default null)
  return self as result,
  CONSTRUCTOR FUNCTION address_typ(pi_wide in varchar2)
  return self as result
);
/

create type body address_typ is
  CONSTRUCTOR FUNCTION
  
address_typ(pi_street_number in varchar2 default null, 
              pi_street_name in varchar2 default null,
              pi_locality in varchar2 default null, 
              pi_postcode in varchar2 default null, 
              pi_state in varchar2 default null)
  return self as result is
  begin
    self.street_number  := pi_street_number;
    self.street_name    := pi_street_name;
    self.locality       := pi_locality;
    self.postcode       := pi_postcode;  
    self.state          := pi_state;
    return; 
  end address_typ;
  --
  CONSTRUCTOR FUNCTION address_typ(pi_wide in varchar2)
  return self as result is
  begin
    self.state := 'NSW'; --dummy line replacing complex parsing routine
    return;
  end address_typ;
end;
/

I have created a type with two constructors. The first replaces the default constructor so that I don't need to specify all the atrributes. The second is an alternative constructor (eg to parse a string into a properly formatted address).

In 10g, if I try
select address_typ('1') from dual;
I get "ORA-06553: PLS-307: too many declarations of 'ADDRESS_TYP' match this call". I can't rename either function as constructors need to have the same name as the object. In 10g, I'd be stuck. Or I'd have to create a function or packaged function to do the job, and end up with code splashed here and there.
.
In 11g, I'd still get the same error, but I have a workaround. I can use
select address_typ(pi_wide => '1') from dual;
As long as the specified parameter names point to a single function, I can use named notation to bypass the problem. Even with constructors, I've got freedom to name my parameters as I please.

Problem solved...for those on 11g at least


 

A blog with benefits

Astute observers may have noticed that my blog has moved. After blogging for over four years on igor-db.blogspot.com, I made the plunge into getting my own domain name. The blog is still a 'blogger' site, and therefore hosted by Google (the option to host blogger blogs elsewhere, through FTP, is deprecated) and apart from the name change, is still functioning as before.

The move is actually pretty simple. Through my Blog publishing settings, I chose the sydoracle.com domain name and paid the princely sum of $10 (US) for it. Then I pointed my blog at blog.sydoracle.com, and clicked the box to redirect sydoracle.com to the blog. Bingo (or perhaps Bloggo).

A bit less than an hour later, I had an email from Google Apps. This is where my added benefits come from as my new domain is linked to the Google Apps (Standard) offering. Google Apps Standard Edition is free [Any chance of that from Oracle :)]. That means I get up to 50 mail addresses under sydoracle.com. Apparently having your own domain for emails gets extra tech cred :)

I also get to link in Google Sites. I've set up a placeholder for www.sydoracle.com, and will try to consolidate some of my scripts and links there.

Google handled it all pretty smoothly, apparently mostly through the magic of 301 redirects, and my past couple of posts have fed through my twitter setup and the feed I use in Google Reader and the OraNA aggregator without interruption. I've got to send some emails to those linking to the blog itself that I'd be grateful if they updated the link. Links to individual posts can just rely on the re-directs. The Real Blogger Status has posts describing the process in detail (and how to solve practically anything that can go wrong).

As for the domain name, I'm based in Sydney (in Australia) and blog about Oracle. I also didn't want a name too similar to www.sydneyoracle.com.au, which is used by the Sydney Oracle Meetup. So it fits, and is mine for just $10 a year.