July 26, 2004 at 3:36 am
Just discovered something strange. If you write a stored proc in Enterprise Manager to create a primary key, click Check Syntax and then OK, the PK is created!
In other words, "Create Procedure" seems to be running it as well with no rollback. This is a bit of a pain if you're into the old iterative build-check-test routine.
I know DDL in a stored proc is uncommon, but couldn't avoid it here. My table is for a data load from an Excel file. In-house rules require a PK, but the load may contain null rows. I wanted to disable the PK (can do this in Oracle), so tried ALTER TABLE ALTER COLUMN... NO CHECK but I suppose the NOT NULL and PPK constraints have lives of their own. Hence went for drop / recreate as the next easiest option and discovered the oddity above. Can anyone tell me why this isn't a bug?
July 26, 2004 at 4:07 am
2 of the great rules of IT have just kicked in:
1) As soon as you give up on a problem, the solution wanders casually into view
2) As soon as you post to a worldwide forum, someone local has an answer
My stored proc had GO statements in it. This caused the immediate execution. Lesson to self: don't use GO because it seems like a good idea: just batch when necessary.
Bill.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply