Re: Execute Immediate Not Working With WHERE CURRENT OF

[Quoted] Hi Daniel,

   Thanks for your reply. Actually the example i posted was a skimmed version of it. Before each update the value will be calculated based on some logic and then updated using the WHERE CURRENT OF. Can you post me a simple example of a procedure with to do the follow

CREATE OR REPLACE FUNCTION Udf_Test (

   strTech IN VARCHAR2
)

   RETURN PLS_INTEGER
IS

• VARIABLES TO STORE INPUT PARAMETERS in_DID VARCHAR2 (18 BYTE);
• LOCAL VARIABLES intRowCount PLS_INTEGER; intErrorCount PLS_INTEGER; strUNID VARCHAR2 (24); strTableName VARCHAR (20);

   CURSOR c1 IS

                  SELECT SUBSEC
          FROM TESTTABLE

• ASSIGNING INPUT PARAMETES TO THE LOCAL VARIABLES in_DID := UPPER (strTech); strTableName := 'TESTTABLE';
• LOCAL VARIABLE INITIALIZATION intRowCount := 0; intErrorCount := 0;

[Quoted]    FOR c1_rec IN c1 LOOP

          #### SOME LOGIC WILL MODIFY THE strTech VALUE ###### AND THEN UPDATE
          EXECUTE IMMEDIATE ' UPDATE TESTTABLE SET SUBSEC = ' || strTech || '
WHERE CURRENT OF c1';

   END LOOP;    RETURN intErrorCount;
EXCEPTION
   WHEN OTHERS
   THEN

      ROLLBACK;
      intErrorCount := intErrorCount + 1;


         DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_STACK);
         DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);


      RETURN intErrorCount;