Re: Execute Immediate with variable qty bind params

Sybrand,

Thanks for all the suggstions. They are much appreciated.

When I started, I didn't find the exception named DUP_VAL_ON_INDEX. I suppose the list I have is incomplete. Thanks for pointing it out - I'll try to find the full list on tahiti.oracle.com - (perhaps you know of a direct link?).

You're right about the first select always returning only one row - I was a bit off-base there, reusing code I had from another procedure where there was no unique constraint.

I read somewhere that one shouldn't use exceptions to control the logic of a body of code. That's why I didn't do a select for the sto_id and handle the insertion when a NO_DATA_FOUND exception is raised. Would you suggest putting the NO_DATA_FOUND exception handler near the select for sto_id, and if the record doesn't exist, insert the new record as part of the exception handler? Then, after the exception handler is done, flow control will resume in the code body prior to the exception?

I understand. Use the returning clause to obtain the new sto_id instead of selecting seq.currval from dual.

I code an insert followed by an update so that I avoid duplication of code. My plan was to obtain a story id, regardless of whether it was from an insert or update, then update the attributes of the story. Building the insert or update statement requires the same amount of data validation (not see in my simplified example I posted) so I tried to consolidate the code by always doing an update of all the additional columns. Perhaps the name of my procedure is misleading too - it should really be "CREATE_OR_MODIFY_STORY" instead of "CREATE_STORY".

Leaving out the columns I don't update will guarantee that if another update was done at some prior time, I don't mess with the data already there. Perhaps another transaction already inserted a story and set the headline. In a new transaction, the headline isn't passed in as a parameter, so I want to leave that column in the tuple alone. If I set it to NULL because it is passed into the procedure as NULL, I'll lose data already in the table. I thought about somehow setting STO_HEADLINE = STO_HEADLINE when pSTO_HEADLINE is null in the EXEC-IMM statement. But I'm not specifying a bind variable there and something feels wrong about re-setting a column value with the same value if it is not necessary.

I'll re-think my approach. Thanks for your time and suggestions.

--Frank Received on Sun May 14 2006 - 12:54:03 CDT