How to execute plpgsql anonymous block in Oracle SQL Developer?

Question

If I execute single SQL statements in worksheet (eg CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';) then is works correctly. Wrapping it in anonymous block like this:

DO
$$
BEGIN 
  CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';
END
$$;

and I get following error message:

Error starting at line : 3 in command -
BEGIN 
  CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';
END
$$;
Error report -
ERROR: syntax error at or near "CREATE"
  Position: 10

However, the same script works fine when I execute it in psql shell. Am I missing something obvious?

Using:
PostgreSQL version 9.4
Postgres JDBC driver postgresql-9.3-1102.jdbc41
SQL Developer version 4.0 (jdk 1.7.0_71)

Answer 1

The source of the error is JDBC's inability to deal with dollar-quoting correctly (yet). Related answer:

• Exceptions when creating a trigger in PostgreSQL 9.1

You might be able to circumvent the problem in this case with:

DO
'
BEGIN 
  CREATE ROLE my_user LOGIN PASSWORD ''my_pwd'' VALID UNTIL ''infinity'';
END
';

If that doesn't do the trick, try to set a different query terminator, like advised in the linked answer.

You seem to be aware that you do not need a DO statement for the example code at all. Just:

CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';