如何在Oracle SQL Developer中执行plpgsql匿名块? [英] How to execute plpgsql anonymous block in Oracle SQL Developer?

问题描述

如果我在工作表中执行单个SQL语句(例如CREATE ROLE my_user LOGIN PASSWORD 'my_pwd' VALID UNTIL 'infinity';)，则可以正常工作.像这样将其包装在匿名块中:

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

但是，当我在psql shell中执行该脚本时，该脚本可以正常工作.我缺少明显的东西吗?

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

使用:
PostgreSQL 9.4版本
Postgres JDBC驱动程序postgresql-9.3-1102.jdbc41
SQL Developer版本4.0(jdk 1.7.0_71)

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

推荐答案

错误的根源是 JDBC无法正确处理美元报价(尚未).相关答案:

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

• 在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';

这篇关于如何在Oracle SQL Developer中执行plpgsql匿名块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！