SQL
DECLARE
sql_text VARCHAR (1000);
BEGIN
sql_text := 'exec P_COMPILE_INVALID_OBJECTS()';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
Error
ORA-00900: invalid SQL statement
ORA-06512: at line 8
Solution
Use 'CALL' instead of 'EXEC' keyword.
DECLARE
sql_text VARCHAR (100);
BEGIN
sql_text := 'call P_COMPILE_INVALID_OBJECTS()';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
SQL
DECLARE
sql_text VARCHAR (1000);
BEGIN
sql_text := 'select sysdate from dual;';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;
Error
ORA-00911: invalid character
ORA-06512: at line 8
Solution
Do not put comma (;) at the end of the sql statement.
DECLARE
sql_text VARCHAR (100);
BEGIN
sql_text := 'select sysdate from dual';
DBMS_OUTPUT.PUT_LINE (sql_text);
EXECUTE IMMEDIATE sql_text;
END;