Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Execute Immediate with variable qty bind params

Re: Execute Immediate with variable qty bind params

From: Sybrand Bakker <>
Date: Sun, 14 May 2006 22:16:16 +0200
Message-ID: <>


see comments embedded below

(Yes, I know this is a toppost)

Hope this helps,

Sybrand Bakker, Senior Oracle DBA

On 14 May 2006 10:54:03 -0700, wrote:

>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 - (perhaps you know
>of a direct link?).
Ought to be in the pl./sql reference manual
>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 can imagine two more or less drastic approaches. The first only avoids the count. The tric is begin select 'x' from dual where exists (select 'x' from <table> where <indexed column> = <your value> exception when no_data_found then <raise flag> end; if <flag> then insert .... etc (However the *full* insert, so *without* subsequent update) The second approach is rather more drastic begin insert the record exception when dup_val_on_index then -- update it I would avoid the count at all costs. I would also put this select in it's own begin end block and cater for both the no_data_found exception *and* the too_many_rows exception (ora-1002)
>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
One tric that does work is update emp set ename =nvl(:b1, ename), department_id = nvl(:b2, department_id) etc. surely this does force Oracle to update columns in itself, and you get some ugly code, but it does avoid dynamic sql.
>I'll re-think my approach. Thanks for your time and suggestions.
Received on Sun May 14 2006 - 15:16:16 CDT

Original text of this message