Useful shortcuts for vi editor

Tuesday, 5 December 2017

ORA-00900, ORA-00911 solution

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;