ORA-00900: invalid SQL statement - Call procedure inside another

Question

I create procedure which call procedure inside another. I have an error ORA-00900: invalid SQL statement.

CREATE OR REPLACE PROCEDURE AP_MOVE_OUT
is
BEGIN 
  EXECUTE IMMEDIATE 'AP_MOVEOUT_COUNT(''GNW-M2'',to_date(''2020-11-02'',''yyyy-MM-dd''),to_date(''2020-11-06'',''yyyy-MM-dd''),''ast'');';
END;
/

Procedure compiled without any error, but when I run it:

BEGIN 
  AP_MOVE_OUT;
END;
/

I've got an error: ORA-00900: invalid SQL statement ORA-06512: at "DEVUSER.AP_MOVE", line 4 ORA-06512: at line 2 00900. 00000 - "invalid SQL statement"

When I execute procedure AP_MOVEOUT_COUNT outside procedure AP_MOVE_OUT it works correctly, so I can't find the reason of these error. Here is the example that works for me outside procedure AP_MOVE_OUT:

EXEC AT_MOVEOUT_COUNT('GNW-M2',to_date('2020-11-02','yyyy-MM-dd'),to_date('2020-11-06','yyyy-MM-dd'),'ast');

Answer 1

Expression called by execute immediate should be a valid sql or pl/sql statement. In your case the statement is not complete. Transforming it to a complete anonymous pl/sql block should solve the problem.

create or replace procedure AP_MOVE_OUT
is
BEGIN 
  execute immediate 'begin AP_MOVEOUT_COUNT(''GNW-M2'',to_date(''2020-11-02'',''yyyy-MM-dd''),to_date(''2020-11-06'',''yyyy-MM-dd''),''ast''); end;';
END;

See also simple example on fiddle

Also consider using bind variables instead of concatenating values into the code.

Answer 2

You don't need to use EXECUTE IMMEDIATE to call a stored procedure from another. You also don't use EXEC or any other keyword.

You've said that

exec AT_MOVEOUT_COUNT('GNW-M2',to_date('2020-11-02','yyyy-MM-dd'),to_date('2020-11-06','yyyy-MM-dd'),'ast');

works, so I would expect the following procedure to work too:

create or replace procedure AP_MOVE_OUT
is
BEGIN
  AT_MOVEOUT_COUNT('GNW-M2',to_date('2020-11-02','yyyy-MM-dd'),to_date('2020-11-06','yyyy-MM-dd'),'ast');
END;