Re: Execute Immediate with variable qty bind params

On 14 May 2006 06:43:11 -0700, frank.sconzo_at_gmail.com wrote:

>Greetings,
>
>I have written an oracle procedure named create_story to update an
>existing row in a table, or first create one, then update it. The
>parameters passed into the procedure dictate how the update statement
>is dynamically constructed. My problem is how to make use of the
>"USING" clause of execute immediate when my SQL update statement has a
>variable number of columns being updated. Consider the simplified
>example below. I've included a create table statement that would go
>along with the procedure if you want to try it out. i also
>significantly simplified the procedure and table for the purposes of
>discussion. I'm using Oracle 10gR2.
>
>I understand very well that my execute statement should be formed as
>such:
>
>EXECUTE IMMEDATE updatestmt USING val1, val2, ... etc.
>
>This would eliminate my need for enclosing the bind parameters with
>apostropes (eg: '''').
>But, I am trying to make this procedure only update columns in the
>table when their associated parameters are not null. As a result, I
>cannot supply a fixed bind parameter list to the execute immediate
>statement.
>
>The procedure itself compiles fine and executes properly now, until one
>of my parameters contains an apostrophe itself.
>
>Here is an example block that calls the procedure successfully:
>DECLARE ID NUMBER;
>BEGIN
> CREATE_STORY(
> PSTO_AUTHOR => 'KyteT',
> PSTO_BYLINE => 'by Tom Kyte',
> PSTO_HEADLINE => 'A Grand View',
> PSTO_NAME => 'BUSH135576',
> PSTO_WORDCOUNT => 1,
> PSTO_ID => ID );
> DBMS_OUTPUT.PUT_LINE('Assigned ID='||ID);
>END;
>
>But if a VARCHAR contains an apostphe, as HEADLINE does in this case,
>the resulting dynamic update statement in the procedure ends up with a
>stray apostrophe, causing ORA-00933: SQL command not properly ended.
>I could escape all apostrophes in all VARCHARs within the stored
>procedure or supply them doubly-escaped on the client end, but that
>feels very wrong.
>
>DECLARE ID NUMBER;
>BEGIN
> CREATE_STORY(
> PSTO_AUTHOR => 'KyteT',
> PSTO_BYLINE => 'by Tom Kyte',
> PSTO_HEADLINE => 'A Bird''s-Eye View',
> PSTO_NAME => 'BUSH135576',
> PSTO_WORDCOUNT => 1,
> PSTO_ID => ID );
> DBMS_OUTPUT.PUT_LINE('Assigned ID='||ID);
>END;
>
>Any way to do this better? Perhaps using DBMS_SQL? I took a look at
>that package but didn't see anything immediately that might help.
>
>CREATE OR REPLACE PROCEDURE CREATE_STORY(
> pSTO_HEADLINE IN VARCHAR2 DEFAULT NULL,
> pSTO_BYLINE IN VARCHAR2 DEFAULT NULL,
> pSTO_AUTHOR IN VARCHAR2 DEFAULT NULL,
> pSTO_WORDCOUNT IN NUMBER DEFAULT NULL,
> pSTO_NAME IN VARCHAR2,
> pSTO_ID OUT NUMBER
>) IS
>lId NUMBER;
>lCount NUMBER;
>setexpr VARCHAR2(1024);
>updatestmt VARCHAR2(1024);
>UK_CONSTRAINT_VIOLATION EXCEPTION;
>PRAGMA EXCEPTION_INIT (UK_CONSTRAINT_VIOLATION, -00001);
>BEGIN
>
> -- see if the story already exists
> SELECT COUNT(*) INTO lCount FROM STORY WHERE STO_NAME = pSTO_NAME AND
>ROWNUM = 1;
>
> IF (lCount = 1) THEN
> -- obtain the id
> SELECT STO_ID into lId FROM STORY WHERE STO_NAME = pSTO_NAME;
> ELSE
> -- create the row
> INSERT INTO STORY (STO_ID, STO_NAME) VALUES (SEQ_STORY.NEXTVAL,
>pSTO_NAME);
> SELECT SEQ_STORY.CURRVAL INTO lId FROM DUAL;
> END IF;
>
> -- now lId will contain either a new or an existing story id.
> -- next, update the story attributes, using the id as the key.
> IF (pSTO_WORDCOUNT IS NOT NULL) THEN
> IF (LENGTH(setexpr) > 0) THEN
> setexpr := setexpr || ', STO_WORDCOUNT = ' || pSTO_WORDCOUNT;
> ELSE
> setexpr := ' STO_WORDCOUNT = ' || pSTO_WORDCOUNT ;
> END IF;
> END IF;
>
> IF pSTO_BYLINE IS NOT NULL THEN
> IF LENGTH(setexpr) > 0 THEN
> setexpr := setexpr || ', STO_BYLINE = ''' || pSTO_BYLINE || '''';
> ELSE
> setexpr := ' STO_BYLINE = ''' || pSTO_BYLINE || '''';
> END IF;
> END IF;
>
> IF pSTO_HEADLINE IS NOT NULL THEN
> IF LENGTH(setexpr) > 0 THEN
> setexpr := setexpr || ', STO_HEADLINE = ''' || pSTO_HEADLINE ||
>'''';
> ELSE
> setexpr := ' STO_HEADLINE = ''' || pSTO_HEADLINE || '''';
> END IF;
> END IF;
>
> IF pSTO_AUTHOR IS NOT NULL THEN
> IF LENGTH(setexpr) > 0 THEN
> setexpr := setexpr || ', STO_AUTHORD = ''' || pSTO_AUTHOR || '''';
> ELSE
> setexpr := ' STO_AUTHOR = ''' || pSTO_AUTHOR || '''';
> END IF;
> END IF;
>
> -- update the attributes of the story
> IF LENGTH(setexpr) > 0 THEN
> updatestmt := 'UPDATE STORY SET ' || setexpr || ' WHERE STO_ID = ' ||
>lId;
> EXECUTE IMMEDIATE updatestmt;
> END IF;
>
> -- return the unique id referring to this story
> pSTO_ID := lId;
>
> EXCEPTION
> WHEN UK_CONSTRAINT_VIOLATION THEN
> DEBUGLOG('Concurrent process inserted into STORY first. Call
>create_story again.');
> WHEN NO_DATA_FOUND THEN
> DEBUGLOG('CREATE_STORY: Exception: no data found');
> WHEN OTHERS THEN
> DEBUGLOG('CREATE_STORY: Other Exception - re-raising');
> RAISE;
>
>END CREATE_STORY;
>/
>
>CREATE TABLE STORY (
> STO_HEADLINE VARCHAR2(50) ,
> STO_BYLINE VARCHAR2(50) ,
> STO_AUTHOR VARCHAR2(50) ,
> STO_WORDCOUNT NUMBER ,
> STO_NAME VARCHAR2(50) NOT NULL,
> STO_ID NUMBER(10) NOT NULL
>);
>CREATE SEQUENCE SEQ_STORY START WITH 1 INCREMENT BY 1
>MINVALUE 0 NOCACHE NOCYCLE NOORDER;
>CREATE INDEX IDX_STO_ID ON STORY (STO_ID);
>CREATE INDEX IDX_STO_NAME ON STORY (STO_NAME);
>ALTER TABLE STORY ADD ( CONSTRAINT PK_STO_ID PRIMARY KEY (STO_ID) );
>ALTER TABLE STORY ADD ( CONSTRAINT UK_STORY_NAME UNIQUE (STO_NAME) );
>
>
>Thanks in advance for your time and any thoughts or suggestions you may
>have.
>
>Cheers,
>Frank

Your attempt in trying to be 'smart' has resulted in a procedure which is essentially unscalable, and you would better completely revise it.

First of all, there is already a DUP_VAL_ON_INDEX exception, which is bound to ora-0001. There is no need to associate another exception to it, especially with such a confusing name. Secondly, what you do now is
check whether the record exists.
If it doesn't exist, insert the surrogate key only, and retrieve it using a redundant select of the current value of the sequence.

The first select is inefficient, as it is guaranteed to return only one record (as there is an unique key on sto_name), so the rownum=1 is redundant, and as count(*) will perform a redundant sort operation, the select is inefficient too.
Obviously, you should have selected the sto_id, and if that statement raised a NO_DATA_FOUND condition, you would have known the record didn't exist. If the record did exist, you would already have had the sto_id column, without a extra select.
Thirdly, the select from dual is redundant, as the INSERT statement has a RETURNING clause, which can return the STO_ID value into a variable.
Fourthly, I don't know why you code an INSERT followed by an UPDATE. This combination is going to be a source of 'buffer busy waits' so of contention.
Finally: I don't know what you expect to gain by leaving out the columns you don't update. Personally I don't think you only will *loose* as you replace *static* sql by *dynamic* sql, which is going to be parsed everytime.
Update those NULL columns into NULL and it will work. What you have now is a lamentable kludge, which will start your way to disaster.

--
Sybrand Bakker, Senior Oracle DBA