missing expression error 1

[DotNetGnat]

Guys,

I have the following function in one of my packages.

FUNCTION "UPDATE_SITE_TEMPLATE"
(
INROW MYTABLE%ROWTYPE,
blah VARCHAR2
) RETURN MYTABLE%ROWTYPE AS

AROW MYTABLE%ROWTYPE;

BEGIN

AROW := GET_MYTABLE(INROW, blah);

STMT := 'UPDATE TABLE1 SET ';
IF (AROW.field1 IS NOT NULL OR AROW.field1 <> 0) THEN
STMT := STMT || 'field1 =:P1 ';
END IF;
IF (AROW.field2 IS NOT NULL OR AROW.field2 <> 0) THEN
STMT := STMT || ', field2 =:P2 ';
END IF;
IF (AROW.field3 IS NOT NULL OR field3.SIDE <> 0) THEN
STMT := STMT || ', field3 =:P3 ';
END IF;
IF (AROW.field4 IS NOT NULL OR AROW.field4 <> 0) THEN
STMT := STMT || ', field4 =:P4 ';
END IF;
STMT := STMT || ' WHERE field5 =:P5 ';

IF (SUBSTR(STMT, 19,1)=',') THEN
STMT := SUBSTR(STMT, 1, 18) || SUBSTR(STMT, 19);
END IF;

EXECUTE IMMEDIATE STMT USING AROW.field1, AROW.field2, 
			     AROW.field3, ARow.field4, 
			     Arow.field5;

RETURN AROW;

END;

what i am trying to do here is... exclude the field from the update query if it is null, but i am getting the missing expression error. What am I doing wrong here.

Any suggestions...thanks

-DNG

 

[DotNetGnat]

actually I am getting a "bind variable does not exist" error

please suggest

-DNG

 

[engineer2100]

Since you setting the columns conditionally it could be that one or two conditions might have got satisfied and you are still referring to those variables.

You should Execute Immediate based on how many variables are being set/referred.

HTH

-Engi

 

[DotNetGnat]

thanks, i fixed the problem by not using parameter method.

-DNG

 

[Dagon]

If you mean you have contatenated the variables as strings into the expression, that is a very bad idea. Because the values change every time, each update will be a separate statement, which will have to be loaded into the SGA and individually parsed. This will massively slow down the processing if you are trying to update a large number of rows. It is always better practice to use bind variables in such a situation.

The problem is, as Engineer says, that you are sometimes missing the parameters because the input variables are null. However, there is a simple solution to this. Merely rewrite the function as:

FUNCTION "UPDATE_SITE_TEMPLATE"
(
INROW MYTABLE%ROWTYPE,
blah VARCHAR2
) RETURN MYTABLE%ROWTYPE AS

AROW MYTABLE%ROWTYPE;

BEGIN

AROW := GET_MYTABLE(INROW, blah);

STMT := 'UPDATE TABLE1 
SET FIELD1=NVL(:FIELD1, FIELD1),
    FIELD2=NVL(:FIELD2, FIELD2),
    FIELD3=NVL(:FIELD3, FIELD3),
    FIELD4=NVL(:FIELD4, FIELD4),
 WHERE field5 =:P5 ';

IF (SUBSTR(STMT, 19,1)=',') THEN
STMT := SUBSTR(STMT, 1, 18) || SUBSTR(STMT, 19);
END IF;

EXECUTE IMMEDIATE STMT USING AROW.field1, AROW.field2, 
                 AROW.field3, ARow.field4, 
                 Arow.field5;

RETURN AROW;

END;

 

[DotNetGnat]

Dagon, Thanks a lot. I was just looking for that code.

I tried it and it worked like charm. Have a star, thanks.

-DNG