Execute Immediate with variable qty bind params

From: <frank.sconzo_at_gmail.com>
Date: 14 May 2006 06:43:11 -0700
Message-ID: <1147614191.807950.126520@u72g2000cwu.googlegroups.com>

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 Received on Sun May 14 2006 - 08:43:11 CDT