I am trying to EXECUTE IMMEDIATE an entire FOR LOOP, but it is not working. It is possible to do that?
BEGIN
FOR V_ROW IN (SELECT ROWNUM AS RN,ID AS ID FROM (SELECT ID FROM T_OPDM_PLANDEACCION WHERE IDOPORTUNIDAD=2 ORDER BY ORDEN)) LOOP UPDATE T_OPDM_PLANDEACCION SET ORDEN=V_ROW.RN WHERE ID=V_ROW.ID;END LOOP;
EXECUTE IMMEDIATE 'FOR V_ROW IN (SELECT ROWNUM AS RN,ID AS ID FROM (SELECT ID FROM T_OPDM_PLANDEACCION WHERE IDOPORTUNIDAD=2 ORDER BY ORDEN)) LOOP UPDATE T_OPDM_PLANDEACCION SET ORDEN=V_ROW.RN WHERE ID=V_ROW.ID;END LOOP';
END;
/
The second line works very well, but the 3rd line (EXECUTE IMMEDIATE 'FOR V_ROW ...') not works. String inside EXECUTE IMMEDIATE is exactly the same as the 2nd line.
I need to execute a FOR LOOP for a parametric SELECT.
A for loop is PL/SQL. Dynamic SQL is not. If you want to run PL/SQL code dynamically then you need to have it in a PL/SQL block within the dynamic statement:
BEGIN
EXECUTE IMMEDIATE 'BEGIN FOR V_ROW IN (SELECT ROWNUM AS RN,ID AS ID FROM (SELECT ID FROM T_OPDM_PLANDEACCION WHERE IDOPORTUNIDAD=2 ORDER BY ORDEN)) LOOP UPDATE T_OPDM_PLANDEACCION SET ORDEN=V_ROW.RN WHERE ID=V_ROW.ID;END LOOP;END;';
END;
That is, with BEGIN and END; (and a missing semicolon) added around what you had.
As @APC hinted, you can split your statement into multiple lines to improve readability, e.g.:
BEGIN
EXECUTE IMMEDIATE '
BEGIN
FOR V_ROW IN (
SELECT ROWNUM AS RN,ID AS ID
FROM (
SELECT ID
FROM T_OPDM_PLANDEACCION
WHERE IDOPORTUNIDAD=2
ORDER BY ORDEN
)
)
LOOP
UPDATE T_OPDM_PLANDEACCION
SET ORDEN=V_ROW.RN
WHERE ID=V_ROW.ID;
END LOOP;
END;
';
END;
It isn't obvious why you would want to do that with this example. If your going to use a 'parametric select' then you might be planning to inject that into the dynamic statement; but even that might not be necessary, depending on exactly what you mean and how you get the query. It isn't even clear why you would do this in a loop, or with PL/SQL, at all.