Re: Execute Immediate Not Working With WHERE CURRENT OF

rhythmram_at_gmail.com wrote:
> 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
> FOR UPDATE OF SUBSEC;
>
>
> BEGIN
> -- ASSIGNING INPUT PARAMETES TO THE LOCAL VARIABLES
> in_DID := UPPER (strTech);
> strTableName := 'TESTTABLE';
>
>
> -- LOCAL VARIABLE INITIALIZATION
> intRowCount := 0;
> intErrorCount := 0;
>
>
> 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;
> END;
>
> Thanks

To do the following WHAT?

For what you've posted:

1. You don't need FOR UPDATE
2. You don't need a CURSOR loop
3. You don't need Native Dynamic SQL

A simple UPDATE statement would suffice.

If you have some real need for any of them then post an example. Otherwise:

CREATE OR REPLACE PROCEDURE myproc (c VARCHAR2) IS BEGIN
   UPDATE TESTTABLE SET SUBSEC = c;
   COMMIT;
END;
/

will suffice. Seriously ... the loop is doing nothing except wasting CPU.

You indicate that there is logic that alters the value of strTech but without seeing it no help is possible. And even if this is assumed a cursor loop is still a bad solution unless you are in 8i or earlier.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)