1

I am trying to run the below Pl/SQL statements on my Oracle DB 11g on Linux box, but getting the error "missing keyword". Please let me know if I miss anything.

BEGIN

FOR X in (select * from all_tables where owner in ('owner1', 'owner2')) LOOP

EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| 'to myuser';

END LOOP;

end;
/


Error starting at line : 1 in command -
BEGIN
FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL')) LOOP
EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| 'to ARCTOOLS212';
END LOOP;

end;

Error report

ORA-00905: missing keyword ORA-06512: at line 3

  1. 00000 - "missing keyword"

*Cause:

*Action:

5

there's a space missing before to in 'to myuser'.

BEGIN
FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL'))
LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON '
        ||X.owner||'.'||X.table_name||
        ' to TESTUSER';
END LOOP;
END;
/
  • Still same error: Error starting at line : 8 in command - BEGIN FOR X in (select * from all_tables where owner in ('TESTDTA', 'TESTCTL')) LOOP EXECUTE IMMEDIATE 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' ||X.owner||'.'||X.table_name|| 'to ARCTOOLS212'; END LOOP; END; Error report - ORA-00905: missing keyword ORA-06512: at line 3 00905. 00000 - "missing keyword" *Cause: *Action: PL/SQL procedure successfully completed. – Naveen Jul 6 '16 at 5:49
  • i don't see a space between ' and to. – Hanspeter Oberlin Jul 6 '16 at 8:11
  • @HanspeterOberlin: You are right and he is missing the space before the TO keyword thus making my answer inappropriate. – JSapkota Jul 6 '16 at 8:33
  • 1
    @Naveen - When you get an issue like this with dynamic sql try creating a VARCHAR2 variable, create your SQL into the variable and then use DBMS_OUTPUT to show what it is actually trying to execute. Problems usually show up pretty quickly. – BriteSponge Jul 6 '16 at 8:37
  • 1
    @Naveen - from what you pasted in your comment above I can see that you still have no space before the 'to' in 'to ARCTOOLS212'. Fix that and you will be good. – BriteSponge Jul 6 '16 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.