Thursday, January 08, 2015

AVOID the VOID

I've recently been involved with extending a number of systems that have pre-built data models. I'm generally unhappy with these data models for a variety of reasons. There are many great academic texts on data modeling. I will try to put together a bibliography in an upcoming post. For now, I'll start by discussing the "never delete data" trend. It is generally coupled with the use of a column to indicate that the data should have been deleted (typically a column named VOID) but was instead allowed, indeed required, to linger forever in the table.

There are typically two arguments in favor of the "never delete, add a VOID column" data model: I want to know what happened from a traceability perspective, and, I want to be able to do incremental extracts to populate some other system and need to know if I need to VOID the row in the other system.

Example without VOID

It's easiest to deal with a concrete example, so let's make one. Assume we have an employee table that stores data about employees. For the purposes of my argument (and because it probably makes sense) let us assume we require a unique SSN for each employee. Typically this table would look like this:


CREATE table "EMPLOYEE" (
    "ID"         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    "SSN"        VARCHAR2(32) NOT NULL,
    "LAST_NAME"  VARCHAR2(128) NOT NULL,
    "FIRST_NAME" VARCHAR2(128),

    "SALARY"     NUMBER,
    "OTHER_INFO" VARCHAR2(256),
    "LAST_UPDATED" DATE NOT NULL,
    constraint  "EMPLOYEE_PK" primary key ("ID")
)
/

alter table "EMPLOYEE" add
constraint "EMPLOYEE_SSN_UK"
unique ("SSN")
/
  

Because we want to be able to do incremental updates, we need the LAST_UPDATED column to be not null and we need to ensure it is always set correctly. There are many reasons to avoid triggers; just do an internet search for "Tom Kyte triggers" to see a number of valid arguments. For this purpose, though, I will add a trigger:

create or replace trigger "EMPLOYEE_BRIUT"
BEFORE
insert or update on "EMPLOYEE"
for each row
begin

  :new.last_updated := sysdate;

end;


Note that I have told the database that SSN will be unique by adding EMPLOYEE_SSN_UK.
The database will automatically also create an unique index of the same name.

Let's explore what happens if two users attempt to insert employees with the same SSN.

User A (note lack of commit):
insert into employee (ssn, last_name)
  values ('123456789', 'Smith');


 1 rows inserted.


 User B:
 insert into employee (ssn, last_name)
  values ('123456789', 'Smith');


 ....waiting....

User A:

commit;

committed.

 User B:

Error starting at line : 1 in command -
insert into employee (ssn, last_name)
  values ('123456789', 'Smith')
Error report -
SQL Error: ORA-00001: unique constraint (ANTON.EMPLOYEE_SSN_UK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.



The database recognized that the SSN was a duplicate and did not allow User B to insert. Had User A issued a rollback, User B could have continued, but once User A had committed the record, User B received an error.

This functionality was completed with a single line of code:

alter table "EMPLOYEE" add constraint "EMPLOYEE_SSN_UK" unique ("SSN")

If I were coding an API and wanted to capture the error, it would require one additional line of code (assuming you already have the keyword EXCEPTION in your API):

exception
  when DUP_VAL_ON_INDEX then ... do something

Later you realize that this person NEVER should have been entered into the employee table. This was not an employee, it was a customer. You issue the following command:

delete from employee
  where ssn = '123456789';
 
commit;


Later still, this customer becomes an employee. You issue the following:

insert into employee (ssn, last_name)
  values ('123456789', 'Smith');


commit;

This works just fine and no additional code is required.

Example With VOID

Let us assume that someone convinces you to disallow any deletes. Instead you are asked to add a VOID column. The VOID column will contain a V if the record is "void," else it will be null.

 CREATE table "EMPLOYEE_NO_DELETE" (
    "ID"         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    "SSN"        VARCHAR2(32) NOT NULL,
    "LAST_NAME"  VARCHAR2(128) NOT NULL,
    "FIRST_NAME" VARCHAR2(128),

    "SALARY"     NUMBER,
    "OTHER_INFO" VARCHAR2(256),
    "VOID"       VARCHAR2(1),

    "LAST_UPDATED" DATE NOT NULL,
    constraint  "EMPLOYEE_ND_PK" primary key ("ID")
)
/


create or replace trigger "EMPLOYEE_ND_BRIUT"
BEFORE
insert or update on "EMPLOYEE_NO_DELETE"
for each row
begin

  :new.last_updated := sysdate;

end;
 
/

Given the scenario listed above, we won't be able to add the unique constraint on SSN. If we were to do so, we would not be able to add them employee the second time as there would already be an employee record with that same SSN. Perhaps we could get away with making SSN + VOID unique.

alter table "EMPLOYEE_NO_DELETE" add constraint "EMPLOYEE_ND_SSN_V_UK" unique ("SSN", VOID)
/

That seems to do the trick.

insert into employee_no_delete (ssn, last_name)
  values ('123456789', 'Smith');


1 rows inserted.
 
update employee_no_delete
  set void = 'V'
  where ssn = '123456789';
 

1 rows updated.
 
insert into employee_no_delete (ssn, last_name)
  values ('123456789', 'Smith');
 

1 rows inserted.
 
commit;


We still have all of the great features around row locking on uniqueness provided by the database.

Of course, if your users are anything like mine, you will find that Mr. Smith has once again been added as employee but he is really a customer. So...

update employee_no_delete
  set void = 'V'
  where ssn = '123456789';


Ah, but here we get

Error starting at line : 1 in command -
update employee_no_delete
  set void = 'V'
  where ssn = '123456789'
Error report -
SQL Error: ORA-00001: unique constraint (ANTON.EMPLOYEE_ND_SSN_V_UK) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.


We already have a row with this combination of SSN and VOID. There is no choice but to remove the constraint, and, along with it, all of the multi-user concurrency features provided by the database.

alter table "EMPLOYEE_NO_DELETE" drop constraint
"EMPLOYEE_ND_SSN_V_UK"
/   


By dropping the constraint we lose more than we can possibly recover by adding our own code. Nevertheless, let's try.

Now we have to add code to ensure that two users (in two separate sessions) never insert or update rows to cause a duplicate SSN. That means we must insist that all updates happen through an API. You might argue that only updates that include the SSN must go through the API. There are edge cases where this could cause deadlocks--and more importantly, it would be difficult to allow updates to everything except SSN. Hence, we have a new rule: All updates must go through our API. This rule also means that, unless we code special additional APIs, all processing is row by row (AKA slow by slow). Want to give everyone a 10% raise? That means updating each row rather than issuing a single update. Our API is also somewhat complicated. We must ensure that there is only one insert or update that involves SSN at a time--across sessions. As we don't have much insight into the happenings of another session, we'll need some way to track this. In order to serialize the inserts and any updates that might change the SSN, we must lock the EMPLOYEE_NO_DELETE table--the whole table. This means before each insert or update we must issue

lock table employee_no_delete in share mode nowait;

We might consider using WAIT instead of NOWAIT, especially as we assume that there will be a lot of table locks.

lock table employee_no_delete in share mode wait 10;

Alternative Method

I've seen this implemented manually by creating another table that tracks table names--and then the API must lock the appropriate row.

CREATE table TABLE_WITH_VOID_COLUMN (
    TABLE_NAME VARCHAR2(256),
    constraint  "TABLE_NAME_PK" primary key ("TABLE_NAME")
)
/


insert into table_with_void_column (table_name)
  values ('EMPLOYEE_NO_DELETE');

commit;

We would never actually update this row, but would lock it in order to interact between sessions.

That method involves creating a new table and code to accomplish something Oracle already provides. Obviously, that is something we are already attempting by adding the VOID column and coding around it, so I'm not surprised to see custom table locking implemented by the same folks who implement custom delete handling.

Back to the API

In order to ensure that a row is never deleted and that we never have a duplicate SSN, we need an API such as the one below.

create or replace package employee_ND_api is

procedure ins ( p_emp_rec    in employee_no_delete%rowtype) ;

end employee_ND_api;  

/


create or replace package body employee_ND_api is

procedure ins ( p_emp_rec    in employee_no_delete%rowtype) is

duplicate_ssn   exception;
l_count         number;

begin

  lock table employee_no_delete in share mode wait 10;
  begin

    select 1 into l_count
      from dual
      where exists (select 1 from employee_no_delete e
        where e.ssn = p_emp_rec.ssn);

    -- oops we found a row already there
    raise duplicate_ssn;

  exception when no_data_found then null; -- ok to continue
    insert into employee_no_delete values p_emp_rec;
  end;

-- note: We can not commit. There should be a full transaction,
--       actions before and after this action, that need to be
--       committed together. Hence, we lock the row in
--       table_with_void_column until the whole transaction
--       completes.
end ins;

end employee_ND_api; 

/

The update routine would be slightly more complicated as we must also lock the row we intend to update, but the INS routine above points to some issues already. We have now locked the entire EMPLOYEE_NO_DELETE table. As noted, we can't commit the newly inserted record as there may be other DML that needs to occur--inserts or updates to other rows or data in other tables. Hence, the entire table remains locked until the final commit or rollback. No other session can insert or update any row of EMPLOYEE_NO_DELETE until we complete.

Moreover, there may be many tables--perhaps all tables--in our system with this same requirement. Hence, when we attempt to update data in another table in our unified transaction, we will need to take the same approach--lock the entire table. Unless every transaction in our system always follows the same order, we will certainly run in to deadlocks on a frequent basis: one session will lock EMPLOYEE_NO_DELETE, another will lock DEPT_NO_DELETE, the first will attempt to lock DEPT_NO_DELETE but be blocked. Then the second will attempt to lock EMPLOYEE_NO_DELETE and the database will detect a deadlock--forcing a rollback of one of the sessions. There is no way to avoid this.

The Incremental Update Requirement

Clearly EMPLOYEE_NO_DELETE, with its void column, has problems. The requirement to do incremental updates of another system, though, remains. If we return to the EMPLOYEE table and allow the row to be deleted using the EMPLOYEE table rather than setting VOID = 'V' in the EMPLOYEE_NO_DELETE table, how does the incremental update routine know to remove (or void) the row?

This is trivial with the use of a trigger on the EMPLOYEE table. Whenever a delete occurs, write a row to another table to indicate the delete. I'll mention Tom Kyte's dislike of triggers here again. I generally agree with Tom on this point. I don't even like the trigger I used above to populate the last_updated column. In the case of audit tables, though, I think a trigger is absolutely warranted. We are not changing any data in the base table and there are no foreign keys or even constraints on the audit table. Users would only ever be granted SELECT on that table. This is the place for a trigger. Depending upon our audit requirements, we might just indicated who took the action, when and whether it was an insert, update or delete. If we really need traceability, though, it's easy to capture the whole image of the row. I'll do that for this example:


-- create the audit table 

CREATE table "EMPLOYEE_AUDIT" (
    "ID"         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    "SSN"        VARCHAR2(32) NOT NULL,
    "LAST_NAME"  VARCHAR2(128) NOT NULL,
    "FIRST_NAME" VARCHAR2(128),

    "SALARY"     NUMBER,
    "OTHER_INFO" VARCHAR2(256),
    "LAST_UPDATED" DATE NOT NULL,
    "ROW_ACTION"     VARCHAR2(32) 
)
/


-- create the trigger

create or replace trigger "EMPLOYEE_ARIUT"
AFTER
insert or update or delete on "EMPLOYEE"
for each row

declare
l_action  varchar2(32);
begin


  if inserting then l_action := 'INSERT';
  elsif updating then l_action := 'UPDATE';
  else l_action := delete;
 
  insert into employee_audit (id, ssn, last_name, first_name

    , salary, other_info, last_updated, row_action)
    values (:new.id, :new.ssn, :new.last_name, :new.first_name
    , :new.salary, :new.other_info, :new.last_updated, l_action);

end;

/

The incremental routine can simply query the deleted row to gather the data. With sufficient data in the audit table, we can create a view that looks exactly like EMPLOYEE_NO_DELETE, but without its inherent shortcomings (nay, fatal flaws).

--create a view that has the deleted row
create or replace view employee_with_void
select id, ssn, last_name, first_name, salary
    , other_info, last_updated, null void
  from employee
union
select id, ssn, last_name, first_name, salary
    , other_info, last_updated, 'V' void
  from employee_audit
  where row_action = 'DELETE' 
/

The audit table can provide much better information if we need it. With just a LAST_UPDATED column (and no audit table), the incremental routine would never know about multiple changes that occur between the incremental runs. It may not need to, but if it does, the audit table provides that ability.

In fact (not supposition, but absolute fact) the right method is to allow the row to be deleted. This provides true data protection, performs better and requires far less code which is also far less complicated.

Still Unconvinced?

OK, maybe you don't have the requirement for any unique constraints. First, I don't buy that argument for well over 90% of tables. If you are doing incremental updates in another system, you need some way to identify where to apply the incremental update--that would be the logical unique key. But, for the sake of argument, we will assume that you don't have any unique keys. That means that you don't need to be concerned about multi-user concurrency issues as much. What about that VOID column, though? Will you allow a row to be "unvoided?" Will you allow any updates to a voided record? If not, you would definitely need an API to keep that from happening. Given every possible argument and every leniency of requirement, it will still be less complicated for developers and users of the data to AVOID the VOID. For every query implemented by every user or developer save the incremental update routine the query must contain

where void is null

Indexes may need to include the VOID column--you'll need to do the analysis on a table by table basis.

You definitely need an API and likely slow by slow processing everywhere.

And here is the worst part, even the incremental load process is more complicated--if you want to get it right. If you just have two columns for audit/delete purposes, LAST_UPDATED and VOID, you lack the fidelity of knowing a row ever existed or what its incremental states were. Take this scenario, for example:

midnight: incremental process runs
9:00 am  User adds employee Smith, SSN = 123456789
9:15 am  User updates Smith to Jones, SSN = 123459999
10:00 am Payroll runs and pays Jones $500
noon  User updates Jones to Smith, SSN = 123456789
1:00 pm User voids the record
midnight: incremental process runs

All that the last incremental process sees is that there was a row for Smith, SSN = 123456789 and that it was voided. What is the incremental process expected to do with that information?

Need I mention foreign keys? If you have either a parent or a child record, how do you handle the relationship? Obviously a parent record can't be deleted; it would have to be voided. All child records would have to be voided as well. The cascading all has to be coded for--not to mention the locking of all of the cascading. At this point I have to ask: why did your company spend so much money on an Oracle database? I doubt it was to hire developers to code the same features again--with less functionality.

Adding a table is easy. Creating a view is easy. All subsequent code benefits from these constructs. Why does the VOID persist? If anyone has a reason for a VOID column, let me know in the comments. Until then, please join with me in this movement--AVOID the VOID.
 

 

Monday, October 06, 2014

Boston APEX Meetup

It's been a long time since I posted, but this seems like as good a reason as any to start up again.  C2 is sponsoring an APEX meetup in the Boston area.  You can find the info here:
http://www.meetup.com/Boston-Apex-Meetup/events/206851272/

I'm looking forward to catching up with other members of the Oracle and APEX communities.  Stay tuned for more blog posts as well!

Anton

Monday, October 03, 2011

SOAPEX at OOW 2011

Recently, I've been doing a lot with Oracle Application Express (APEX) and web services.  At Oracle OpenWorld I came across a presentation on just this topic.  Douwe Pieter van den Bos, an Oracle Ace, presented on using APEX with the Oracle SOA Suite.  I have used SOA Suite in the past, and quite like it, but recently the web services I've been using are SOAP based services that I have very little influence on.  They are not built with or deployed on the Oracle SOA Suite.

The SOAPEX presentation gave a nice overview of how to set up APEX to use web services and made the smart recommendation to build your web service references in a single application (SOAPEX) and then use the inherit/subscription model of APEX to keep things up to date.

My specific challenges have been a little more difficult, specifically in the need to consume very large and complicated web services--so complex (or possibly overly complicated) that APEX is unable to parse the wsdl.  More on this later...

Oracle 12c Database

It's definitely about the cloud at Oracle OpenWorld 2011.  In many ways Oracle has always promoted it's database in the private cloud--long before the term cloud (private or public) became popular.  At previous OpenWorlds, Larry Ellison poked fun at the cloud, noting that the notion isn't new.  Well, even Oracle must bend to the popularity of the term cloud.  I haven't yet heard an official name for the next database release, but I'm betting on Oracle 12c.

Wednesday, March 30, 2011

It's Been a Long Time

My last blog post was a tribute to Scott Spadafore.  A year and a half before, I also offered tribute to another close friend and APEX guru, Carl Backstrom.  I have had a hard time getting past the loss of these two friends.  Such a hard time that this is my first post in over a year--my first since Scott's haiku.  I have started to write many posts, but none seemed worthy of moving Scott's haiku down the page.  Scott was a pillar of the Oracle community--not just Application Express, though he certainly dominated that arena.

The passing of these two friends was a loss to many others as well.  Several months ago John Scott had a grand idea.  He gathered together over a dozen people who had benefited from the work of Carl and Scott, who had enjoyed success because of their efforts, and had become friends with Carl, Scott and each other through the APEX community.  John suggested that we jointly write a book, in Carl and Scott's memory, and donate the author royalties to the funds established for Carl's and Scott's children.  I was honored to be included in this group.

John's blog provides the full list of authors and more info about the book, Expert Oracle Application Express.  I offer my thanks to John for coordinating this project, and to all of the contributors.

Monday, March 22, 2010

Haiku Two

In November 2008 I offered a haiku in Carl Backstrom's memory.  That post referenced texting a haiku.  That text originated with Scott Spadafore.  It is with great sadness that I offer haiku two in Scott's memory.

spring leaps forth
though warmth, shining sun
brilliance lost

Tuesday, March 09, 2010

Thank You Granny!

My wife recently took a look at this blog and told me that it would be better with pictures.  She also suggested that not every post had to start with: If you are installing/configuring/coding with the Oracle product abc and you get error ORA-nnnnn . . . With that in mind, I share the following.

I used to commute to work by bike most days, but I was out of commission for about a year.  Today was a beautiful morning, just right to get back on the bike for a ride to work.  After a year off it was a bit of a shakey start.  Just figuring out where my gear was.  Pairing down my now typical road warrior gear to the bare essentials (for example, a 4 port switch instead of the 10 port).  Stuffing everything into a new bag.  Hoping my lunch would not spill out into traffic.

It used to take me 26 minutes door to door.  This morning was a little slower.  Which brings me to the title of this post.  Below is a shot of my Campy Chorus Racing Triple.

A Campy Racing Triple is for people who want to think they are still fast, but realize their lifestyle will include occasionally towing a trailer with the road bike, possibly a child seat.  That small ring is known as the Granny Gear.  It's for those times, for people like me.  It's not for a commute to work, mind you.  It's for carrying heavy loads, for extreme circumstances.  Note that the photo does not show me using the granny.  That is, there is no actual evidence that I actually resorted to it today.  The shot below is merely circumstantial.





That says 41minutes, 51 seconds.  Granted, that included finding my lock in my stuffed bag and locking up, but with all the excuses I can muster, it is still sloooow.  I think of it this way, though . . . By car it takes 28 minutes.  That means I spent about 14 minutes this morning just doing something I enjoy.  Everyone should get 14 minutes a day to do something they enjoy.  And, I get another 14 minutes this evening on my way home.  I wish you the same.

Thursday, February 25, 2010

APEX Refresh Classic Report Region AJAX style

We often have the need to refresh a classic report region, AJAX style. It is straightforward to get a refresh link on the page. I used to just build a link using $a_report (the APEX built-in for doing partial page refresh on classic reports). But I have found it is better to create a javascript function in the region header or footer. This has the advantage that you can call it from a button on the page, or from any location on the page, not just from within the header or footer itself. If you put the following

<script type="text/javascript">
function c2RefreshTasks(){ 
  pId = '#REGION_ID#';   // report region id
  $a_report(pId.substring(1),'1','15');  // APEX built-in
  }
</script>

This allows me to put a link anywhere on the page

<a href="javascript:c2RefreshTasks();">refresh tasks</a>

I can also create a standard button anywhere on the page that calls this javascript.

I recently also had the need to pop a new window (child), add a task in that child window, close the child and then refresh the task region in parent window. It turned out to be easy...

Just create the APEX child window that does the insert/update.&nbsp; Have it branch to a page (e.g. P99). On P99, put the following in the HTML header

<script type="text/javascript">
 // important to have the try because the parent window might have changed...
{ try {
  window.opener.c2RefreshTasks();
  }
  catch(err) {
  window.close(); 
  }
}
  window.close();
</script>

Saturday, December 19, 2009

Oracle Application Express 4.0 (APEX 4.0) Early Adopter

Just about everyone has already blogged about it, but APEX 4.0 EA is available now.
http://tryapexnow.com/
What else is there to say that has not already been said?  Not much probably, but I'll point out one new feature: APEX 4.0 is not available in Internet Explorer.  I have it on good authority that this feature will only be in the beta and will not make it into the production release--we can hope, though.  (Thanks to Neelesh Shah for pointing out this new feature.)

Also, check out the new SQL Developer features:
http://krisrice.blogspot.com/2009/12/application-express-and-anonymous.html

And, of course, Patrick's sample plugin:
http://www.inside-oracle-apex.com/test-drive-oracle-apex-4-0-early-adopter/

Thursday, December 03, 2009

ODTUG Kaleidoscope 2010: APEX Plugin Showcase

One of the best new features of APEX 4.0 is the extensible plugin architecture.  You will be able to create your own item and region types as well as custom dynamic actions (javascript enabled actions on the browser).  You'll be able to add these plugins right in to the builder so they are available to all of your developers.  You will be able to share (or even sell) these with the APEX community.  This is big.

To get you started the fine folks at ODTUG are going to build five fantastic plugins and give them away to anyone who attends Kaleidoscope 2010.  You can check out the details here.

The trick to writing a great plugin is to have a great idea.  I'll have some input into the five plugins.  If there is an APEX item type, region type or dynamic action that you think should be there but isn't, please leave me a comment and let me know. 

Monday, November 30, 2009

Interesting APEX with dblink issue

We recently ran in to a problem when connecting across a database link to a Postgres database.  The query looked like this
select "column1", "column2"
  from "my_schema"."my_long_named_view"@my_db_link
It works fine from sql*plus and sql Developer, but when running the same query in Application Express (in an app or in the SQL Workshop) we got this error:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC]DRV_QspecDescribe: DB_ODBC_RECORD (189): ; [OpenLink][ODBC][PostgreSQL Server] current transaction is aborted, commands ignored until end of transaction block (SQL State: S1000; SQL Code: 1) ORA-02063: preceding 2 lines from MY_DB_LINK
Dave Rydzewski came up with the solution.  Shorten the name of the view in Postgres and change the query to look like this (note two fewer double quotes "):
select "column1", "column2"
  from "my_schema.short_v"@my_db_link
I'm still not sure what APEX does to make it blow up.  I wonder how many people use APEX with a db link to Postgres.

Tuesday, November 24, 2009

VMware pc2mac

I thought tonight was going to be the night.  I purchased a macbook pro about 6 weeks ago.  I've slowly been getting familiar with the mac, and realizing that I'll probably still need a PC image at least occasionally.  Last night I installed VMware Fusion for the mac (nice that it's called Fusion, that way it almost has something to do with this Oracle blog).  Tonight I was to create the image from my work laptop and test it out on the macbook.  Alas, try as I might, I still don't have an image.  This is my story.

I read the VMware readme (yep, I do that kind of thing).  I learned about pc2mac, the utility that lets you stream the image right off your running pc and onto an image on your mac.  I read the guides.  I installed the pc2mac utility on my PC, restarted my PC and the VMware Fusion PC Migration Agent screen opened right up.  I ran VMware on the mac and followed the instructions, typed in the four-digit passcode, the administrator password, clicked continue, and got an error:

Converter failed to connect to remote machine.
An error occured while transferring data
I tried a few times, but did not get any further.  I decided to come back later, but first, I returned to the PC to close out the migration agent.  Noticing the checkbox "Run the VMware Fusion PC Migration Agent Every time I start my PC", I realized that I probably don't want this to every time.  I unchecked the box.

Returning later to try again, I could not find a way to start up that migration assistant.  I googled.  I rebooted, re-installed pc2mac, rebooted, uninstalled, rebooted, reinstalled, rebooted.  Mind you, this was the PC I was rebooting--not a quick affair, much like this blog post.  I'll cut to the chase, even an uninstall and reinstall would not bring up that screen again.  I finally found the trick though.  Edit the registry:

HKEY_CURRENT_USER > Software > VMware, Inc. > PC Migration Agent
Edit RunAtStartup and change from 0 to 1.

Of course, the change from 0 to 1 was just a guess, but it worked.

Now, at 11:06pm, I'm back to having a pc2mac utility that will run the first screen, but it still does not work.  Now that's progress.  Oh, and my wife just said, "How do I find your blog?"  Good night everybody.

[Update: 25 Nov 2009, 4:53pm]  I also had an issue logging an issue with VMware.  The first problem was that I could not register the product.  The VMware registration website kept giving an error that it was not a valid code despite the fact that it was in the email and the product installation accepted it.  I was able to log a customer service issue about not being able to register, but I was not able to log a technical issue without first registering.

I called and spoke with an extremely helpful support rep.  She told me they were having issues with some registration numbers and offered to create the technical ticket for me.  She was so helpful that I went back to the VMware site to drop a note to anyone that would accept it.  This is the note I typed:

I recently purchased VMware Fusion.  I generally know my way around technology, but just registering my VMware software on the website was impossible (truly, as there is currently a bug in your system).  After quite a bit of frustration, though, I finally called and spoke with a support representative.  I'm sorry that did not get her full name, but she created a new SR for me, 1459858086, and it has been assigned to SGARDNER.

This support rep was very helpful, polite and overall reflected very well on VMware.  Though my issue has not been resolved, I had a very positive experience speaking with this rep.  If you have the ability to commend her I hope you will.
That's not bad, right?  Unfortunately, though, it never went anywhere.  The only page I could find a place type the note was here: https://www.vmware.com/contact/contact_sales.html
The page never allowed me to pick a Country or State.  It just kept showing "loading..." without ever showing a country.  Of course, those fields are mandatory.

Sorry friendly support rep, I'm afraid that note won't make it anywhere.

Wednesday, November 04, 2009

Looking for APEX Developers

I have a client in the northeast of the US. If you are really good with the Oracle DB, pl/sql and APEX, and live or want to move to the US Northeast, send me your info and I'll pass it along. Please send an email with your resume to me (anton) at work (concept2completion.net).

Thanks,

Anton

Wednesday, September 30, 2009

Cloning an Oracle Schema

When I started this blog I decided I would only blog about things you could not find reasonably easily with a simple google search. This post violates that rule. Cloning a schema is something that I do fairly frequently but not frequently enough that I remember the exact syntax. First I googled it every time, later I created a little text file with the commands. Having already gone to the trouble to write a text file, I might as well just paste it in here. I use datapump. In the example below I want to export the SCOTT schema which has all of its data in the USERS tablespace. I want to import it into a database (either the same or another Oracle database) as the user SCOTT2 in the tablespace USERS2. That means I need to change both the database schema and tablespace. Here are the commands (replace values as appropriate for your env):
export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev1
cd $ORACLE_HOME/bin
./expdp system/ dumpfile=scott.dmp schemas=scott
Note: this will create the file scott.dmp in the location $ORACLE_HOME/admin/dpdump. You can create a different directory for it, but for my purposes this is sufficient.

That is all it takes to do the export.
If you plan to import into the same database it can stay in the same location.

To do the import into a different database you will need to copy the scott.dmp file to the right location for the other database.
cp $ORACLE_HOME/admin/c2dev1/dpdump/scott.dmp /mynas/scott.dmp
Then I ssh to the target machine and copy to the new target location
cp /mynas/scott.dmp $ORACLE_HOME/admin/c2dev2/dpdump/scott.dmp

export ORACLE_HOME=/opt/oracle/product/oracle10g
export ORACLE_SID=c2dev2
cd $ORACLE_HOME/bin
./impdp system/ dumpfile=scott.dmp remap_schema=scott:scott2 remap_tablespace=USERS:USERS2
That should do it. This should make it easy for me to do in the future and hopefully help someone else along the way.

Tuesday, September 29, 2009

APEXposed 2009 - A request for input

I will be speaking at APEXposed 2009 in Atlanta, GA on 10 & 11 November. Some of you have certainly seen the How to Hack an APEX Application presentation. I will be giving a revised version of that. It is difficult to find interesting things because the APEX developers keep adding features to make it harder for developers to get into trouble, but I'll have a few items of interest, plus the old standby's.

My second talk is APEX and the Oracle Database.
The power of APEX is partly the immense scope of capabilities present in the Oracle database. In this presentation I am going to show how to use many of these capabilities within APEX. Below are a few topics I have in mind.
  • Oracle Text (Intermedia)
  • Spacial
  • SQL Analytics
  • File Compression
  • owa routines
  • utl_inaddr
  • External Tables
  • Virtual Private Database
I'd love to get additional ideas--if you have any favorites, please let me know!

Wednesday, June 10, 2009

Migrating Portal Repository with change in DN

Another in a long line of very esoteric issues...

If you are migrating a portal repository, possibly from Production back into Dev or vice versa you may run into the following error when running ptlconfig

STEP 1 : Populating Portal seed in OID
Connected.
Creating Lightweight User Accounts and Groups in OID
Portal schema version is: 10.1.2.0.2
Error code : -6502
Error message: ORA-06502: PL/SQL: numeric or value error
ERROR: creating lightweight users and groups in OID ... exiting

PL/SQL procedure successfully completed.

This happens if you have changed the base dn of your OID. For example, you might have had a dn of dc=concept2completion,dc=net
and then decided to change to dot com
dc=concept2completion,dc=com

or possibly you removed a sub-domain
dc=chicago,dc=concept2completion,dc=net
changed to
dc=concept2completion,dc=net

In either case, you will get the error listed above. The problem is that the script secoidd.sql relies on the dn stored in the table wwsub_model$. Below is an extract of secoidd.sql

if l_subscriber_dn is null then
-- The control should never reach here as the subscriber DN
-- should be available in the wwsub_model$ table.
So, if you get into this position, you will need to update the value in the dn column of wwsub_model$.

Seems like such a simple solution. So simple that I have now gone to the process of figuring it out three times.

Monday, June 08, 2009

SQL for Date Spanned Data

I was recently presented with a sql challenge. Although I came up with something that works, I'm not convinced it is the best solution. This is a very long post that is mostly question, but possibly a helpful example. The details are below.

I was given a table with the following columns
ID -- Primary Key
DIVISION -- Identifier of a Division
START_DATE -- Date when the Division starts
END_DATE -- Date when the Division ends

If you want to try this yourself, I added all the create statements and sample data creation to the end of this note.

The data has an implicit concept of a "span." A Division may have many spans. The start date of a new span is typically one day after the end date of the previous span. In this case a Division has a contiguous span. Spans never overlap; that is, a single division never has a row with a start date between the start and end date of another row of the same division (and never has an end date between the start and end of another row of the same division).

Below is some sample data:

Id, Division, Start_date, End_date

10 1 09-JUN-2009 10-JUN-2009
11 1 11-JUN-2009 12-JUN-2009
12 1 13-JUN-2009 14-JUN-2009 -- Note the following row is not contiguous
14 1 17-JUN-2009 18-JUN-2009
15 1 19-JUN-2009 20-JUN-2009


If this were the total data set then Division 1 would have 5 spans, but only two contiguous spans (09 - 14 Jun and 17 - 20 Jun).

The challenge was to create a sql statement to only return contiguous spans by division. I immediately thought of a CONNECT BY but the challenge was to determine the START WITH condition.

[Update: In response to this post Alex Nuijten came up with a better method than any described below. Take a look here:
http://nuijten.blogspot.com/2009/06/analytic-function-finding-gaps.html ]
I came up with two ways to do this. I'm still not certain if there is a better solution. The first is far better and uses some SQL analytics to determine the START WITH condition. If you were to use a WHERE clause, it should go in the WITH section as identified. My example is below.

with span_info as (select s.division, s.start_date, s.end_date
, (s.start_date -lag(end_date, 1) over (partition by s.division order by start_date)) new_span
-- null or > 1 indicates a new contiguous span
, (lead(start_date, 1) over (partition by s.division order by start_date) - s.end_date) end_span
-- the end_span is not needed, just left for example
-- null or > 1 indicates the end of a contiguous span
from spantest s
-- a WHERE clause would go HERE, not in the main SELECT below
order by 1, 2 )
select division
, connect_by_root start_date span_start -- start of a contiguous span
-- , start_date -- start of the last span of a contiguous span
, end_date -- end of the contiguous span
-- , new_span, end_span, connect_by_isleaf
from span_info
where connect_by_isleaf = 1
-- gets only "leaf" recodes, i.e. the end of a contiguous span
start with nvl(new_span,1000) > 1
-- gets beginning of a contiguous span
connect by
prior end_date + 1 = start_date
-- connects spans, the 1 here and in the "start with" can be changed together
and prior division = division
-- ensures spans are for the same division
order by 1, 2

My second idea is to use an exists command to determine if the the first there exists a row with an end_date equal to start_date - 1 for the division. I can write the statement, but I'm not sure how to get the combination of the DIVISION and the START_DATE in to the START WITH. I could do some concatenation, but I don't like that at all. The code is below, but DO NOT RUN THE QUERY; it is very slow.

-- ************ DO NOT RUN THIS QUERY
select division
, connect_by_root start_date span_start -- start of a contiguous span
-- , start_date -- start of the last span of a contiguous span
, end_date -- end of the contiguous span
-- , new_span, end_span, connect_by_isleaf
from spantest
where connect_by_isleaf = 1
-- gets only "leaf" recodes, i.e. the end of a contiguous span
start with to_char(start_date,'yyyymmdd') ||'~' || division in
(select to_char(st2.start_date,'yyyymmdd') ||'~' || st2.division
from spantest st2
where not exists
(select 1 from spantest st3
where st3.division = st2.division
and st3.end_date = st2.start_date + 1
and st3.id != st2.id)
)
-- gets beginning of a contiguous span
connect by
prior end_date + 1 = start_date
-- connects spans, the 1 here and in the "start with" can be changed together
and prior division = division
-- ensures spans are for the same division
order by 1, 2


This is a lot of code and no-one other than me may ever read it. If you do, though, and have other ideas, I would love to hear them.

-- ********* Create Statements and sample data

CREATE TABLE "SPANTEST"
( "ID" NUMBER NOT NULL ENABLE,
"DIVISION" NUMBER NOT NULL ENABLE,
"START_DATE" DATE NOT NULL ENABLE,
"END_DATE" DATE NOT NULL ENABLE,
CONSTRAINT "SPANTEST_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE INDEX "SPANTEST_IDX3" ON "SPANTEST" ("END_DATE")
/
CREATE INDEX "SPANTEST_IDX2" ON "SPANTEST" ("START_DATE")
/
CREATE INDEX "SPANTEST_IDX1" ON "SPANTEST" ("DIVISION")
/

CREATE SEQUENCE "SPANTEST_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1441 CACHE 20 NOORDER NOCYCLE
/

CREATE OR REPLACE TRIGGER "BI_SPANTEST"
before insert on "SPANTEST"
for each row
begin
if :NEW."ID" is null then
select "SPANTEST_SEQ".nextval into :NEW."ID" from dual;
end if;
end;

/
ALTER TRIGGER "BI_SPANTEST" ENABLE
/


-- create sample data for 10 Divisions
begin
for i in 1..20 loop
insert into spantest (division, start_date, end_date)
select 1, trunc(sysdate + (rownum*2 - 1)),trunc(sysdate + (rownum*2)) from all_tables;
end loop;
end;
/

-- create span gaps on the 15th of each month
delete from spantest where
to_char(start_date,'dd') = '15'
/

Wednesday, May 06, 2009

Locating a Corrupt PDF (or many types of corrupt files) in a BLOB

I recently had a client pose the following question to me.
We have a system that generates pdf's based upon data from our Oracle database. The pdf's are then stored in a BLOB column in a table. We recently realized that at least one of those pdf's was corrupt. Is there a way within Oracle to determine if others are corrupt?

I initially thought about trying to write a script that would pull each blob, run it through some kind of pdf validator and write a log entry if it were bad. Hmmm, use plsql and call out to a validator on the O/S? Write a java routine to do the same thing? What validator would I use?

Then I had an idea--Oracle Text (formerly Intermedia and Context). I could create a context index on the blob column and see if there are any errors.

CREATE INDEX myIndex ON my_table(my_blob) INDEXTYPE IS ctxsys.context;

If the Oracle Text engine is unable to index the blob it writes a log entry to the view ctx_user_index_errors including the rowid. Depending on the database version you might have issues with very recent versions of the pdf, but that was not an issue in our case as every pdf was created by the same pdf generator.

I think Oracle Text is underused. This is just one more example of how to make it work for you.

Saturday, May 02, 2009

Oracle SQL Delimiter

It's been a while since I made a real post, one that has some technical merit. This one just barely qualifies.

There is a nice classic report type "Function Returning a SQL Query" which allows you to write a block of code that returns a select statement. That select statement is the basis for the report. I often use it to simplify building a where clause. It might look something like this:

declare
l_q varchar2(32767); -- This is the return variable. It will hold a select statement

begin
l_q := 'select d.name department, e.name employee
from dept d, emp e
where d.deptno = e.deptno ';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;


With Interactive Reports this has become less likely, but it still happens.

Sometimes, though, the query is a bit more complicated, something like this:

begin
l_q := 'select d.name department, e.name employee,
to_date(to_char(e.hiredate,''yyyymm'' ||''01''),''yyyymmdd'') first_day_of_month
from dept d, emp e
where d.deptno = e.deptno
and e.status in (''NEW'', ''PENDING'', ''FOO'')
and d.status = ''NEW'' ';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;


You get the idea, a lot of strings and a lot of double ticks (''). There is an easier way, though: a custom SQL Delimiter. Usually a single tick (') indicates the start of a string. If you use a tick within it, you need two ticks ('') to escape it (see code above). But you can avoid that with a custom delimiter. You turn it on with q', that's all, just the letter q followed by a tick. The trick is that the very next character is the delimiter, and a few are special. You end the statement with followed by a tick. Examples are the easiest, so here are a few:

1. l_q := q'! I don't need to escape the ' in the word don't !';
In this case the ! is the special character. q'! starts the string and !' ends it.

2. l_q = q'{ I don't need to escape the ' in the word don't }';
This is a little different. If your special character is { then you use }' to turn it off. It is pretty obvious which are special: [], {}, <>.

So, the code above becomes:

begin
l_q := q'{ select d.name department, e.name employee,
to_date(to_char(e.hiredate,'yyyymm' ||'01'),'yyyymmdd') first_day_of_month
from dept d, emp e
where d.deptno = e.deptno
and e.status in ('NEW', 'PENDING', 'FOO')
and d.status = 'NEW' }';

if :P1_DEPTNO is not null then
l_q := l_q || ' and d.deptno = :P1_DEPTNO ';
end if;

-- insert more if statements like above

return l_q;
end;


You can use this just about anywhere in sql or pl/sql where you want a string.

Friday, April 10, 2009

What is this?




First, I apologize, this is my third post in a row that is not technical. I promise I will get back to the real purpose of this blog in my next post.

Now, on to the question. Does anyone recognize this device? I found it in my closet the other morning. I suspect I probably took it out of a bag after a trip, or picked it up instead of stepping on it in one of my kids' rooms. I probably knew what it was at the time, probably thought I would stick it there for a few days and deal with it later. Now it is later, though, and I have no idea what it is or where it came from.

It takes 3 AAA batteries. It has no identifying markings of any kind. Inside the battery compartment there is no writing. When I turn it on it makes a very high pitched, very low volume whining noise. There is nothing on the sides that are not shown in the two images above. You now know as much as I do about this thing.

If you know what this is, really know what it is, please post a comment. Thanks.

Update: We have a winner! I won't publish the answer here, in case you want to guess. Check out the comments for the answer.

Thursday, April 09, 2009

Going Viral


This is a chart of traffic on http://www.crapmanagement.com
As you can see, yesterday crapmanagement "went viral," more than doubling its former high (which was mostly me testing the web logging capabilities).