Re: Execute Immediate with variable qty bind params

Frank,

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, frank.sconzo_at_gmail.com wrote:



>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?).


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

>necessary.

>



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.

>

>--Frank