Calling stored procedure in for-loop

Question

I have created and stored a procedure in SQL Developer. I can call it and it is working as expected.

However if I try to use it in a for-loop over a set of tables I get the error:

ORA-06512: in line 10

900. 00000 - "invalid SQL statement"

This is the procedure:

create or replace PROCEDURE ADD_PARAMETERS 
(
  TBL_NAME IN VARCHAR2 
) AS 
BEGIN
  
  EXECUTE IMMEDIATE 'ALTER TABLE '||TBL_NAME||' ADD(FB_AREA_HA Float)';
  
  EXECUTE IMMEDIATE 'UPDATE '||TBL_NAME||' SET FB_AREA_HA = FB_AREA/10000';
END ADD_PARAMETERS;

And here the for-loop with the procedure:

SET SERVEROUT ON
DECLARE

sql_stmnt VARCHAR2(400);

BEGIN
    FOR x in (SELECT * FROM all_tables WHERE table_name LIKE 'BBX_%')
    LOOP
        sql_stmnt := 'EXECUTE ADD_PARAMETERS('''||x.TABLE_NAME||''')';
        DBMS_OUTPUT.PUT_LINE(sql_stmnt||';');
        EXECUTE IMMEDIATE sql_stmnt;
    END LOOP;
END;

What is the cause of this error? I'm pretty certain it is not line 10 though.

Answer 1

EXECUTE is not a SQL command. It's a SQL*Plus (and others) client shorthand for an anonymous block. The dynamic statement is executed in a SQL context, not in the client, so it isn't recognised - hence the error (which is indeed coming from line 10 of your block as that is where the EXECUTE IMMEDIATE happens).

You could do the equivalent of expanding that shorthand:

sql_stmnt := 'BEGIN ADD_PARAMETERS('''||x.TABLE_NAME||'''); END;';

But you don't need dynamic SQL for the procedure call, you can just do:

BEGIN
    FOR x in (SELECT * FROM all_tables WHERE table_name LIKE 'BBX_%')
    LOOP
        ADD_PARAMETERS(x.TABLE_NAME);
    END LOOP;
END;
/