Toad World® Forums

ORA-00900: invalid SQL statement

Hello,
Is it possible anonymous block?

DECLARE
CURSOR CUR IS
SELECT FIRST_NAME || JOB_ID || HIRE_DATE AS RS FROM HR.EMPLOYEES;
BEGIN
FOR I IN CUR LOOP
EXECUTE IMMEDIATE I.RS;
END LOOP;
END;

Thanks for your advice

If you're asking if Toad/Oracle can execute anonymous blocks, then answer is yes.

If you're asking advice on coding, this is the wrong forum. That said, I don't think you're code above will work, for a number of reasons, even though I'm not a PL/SQL expert.

Here's a link with examples to get you started... there's tons of other stuff on the net you can dig up:
https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/dynamic.htm

Hope this helps.

The reason that your pl/sql block fails is because your cursor loop is selecting some values out of the HR.EMPLOYEES table, and then trying to run those values as a sql statement.

I'm not really sure what you intended for that code to do, but either of these runs without error:

DECLARE
  CURSOR CUR IS
    SELECT FIRST_NAME || JOB_ID || HIRE_DATE AS RS FROM HR.EMPLOYEES;
BEGIN
  FOR I IN CUR LOOP
    dbms_output.put_line(I.RS);
  END LOOP;
END;


BEGIN
  EXECUTE IMMEDIATE 'SELECT FIRST_NAME || JOB_ID || HIRE_DATE AS RS FROM HR.EMPLOYEES';
END;
1 Like

execute immediate will execute any valid statement (SQL or PL/SQL).
Error message is your best friend, in term of what might go wrong.
Try to run the output of the cursor in any application (Toad, SQL-Developer, sqlplus...) and the error message will be the same.
The statement, that the code is running, is something like "SMITH 11 17-DEC-80" which is not a SQL statement. 00900 is the only response expected.