抑制 ddl 创建脚本中的 ORA-00942 错误

发布于 2024-09-25 18:39:02 字数 304 浏览 8 评论 0原文

假设您生成 ddl 以通过 Hibernate SchemaExport 等创建所有数据库表等。您得到的是一个开头以 drop 语句开头的脚本。没问题,因为我想要这个。但运行此脚本会在 Oracle 数据库上运行时产生大量 ORA-00942 错误。

由于如果表尚不存在,它们并不是真正的错误,因此我希望我的创建脚本在执行时没有错误,这样很容易确定失败的内容(如果有)。

我有什么选择?我确实希望生成 drop 语句,因为表可能存在也可能不存在,但我不希望一百万个 ORA 返回给我,我必须检查(以确定它们是否是实际错误)只是因为它不能'不要掉落一张全新的桌子。

Say you generate ddl to create all your db tables etc via Hibernate SchemaExport etc. What you get is a script which starts with drop statements at the beginning. Not a problem, as I want this. But running this script produces a crapload of ORA-00942 errors running on an Oracle db.

Since they're not really errors if the tables just didn't exist yet, I'd like my create script to be error free when it executes so it's easy to determine what (if any) failed.

What are my options? I DO want drop statements generated since tables may or may not exist yet, but I don't want a million ORA-s coming back at me that I have to check (to determine if they're actual errors) just because it couldn't drop a table that's brand new.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

终遇你 2024-10-02 18:39:02

“假设您生成 ddl 来创建所有
通过 Hibernate 的数据库表等
SchemaExport 等。您得到的是
以 drop 开头的脚本
开头的陈述。不是一个
问题,正如我想要的。但跑步
这个脚本产生了一堆垃圾
在 Oracle 上运行时出现 ORA-00942 错误
分贝。”

我们应该使用源代码控制和配置管理最佳实践来正确维护我们的模式。在这种情况下,我们事先知道我们运行脚本所针对的模式是否包含这些表。我们不会收到错误,因为我们不尝试 然而,并不

总是可以做到这一点,第一个脚本只有 DROP TABLE 语句,以友好的开头。

PROMPT  It is safe to ignore any ORA-00942 errors in the following statements

第二个脚本包含所有 CREATE TABLE。语句并导致

PROMPT  All the statements in this script should succeed.  So investigate any errors

另一个选项是使用数据字典:

begin
    for r in ( select table_name from user_tables )
    loop
        execute immediate 'drop table '||r.table_name
                    ||' cascade constraints';
    end loop;
end;

小心这个选项,它会删除模式中的每个表。

"Say you generate ddl to create all
your db tables etc via Hibernate
SchemaExport etc. What you get is a
script which starts with drop
statements at the beginning. Not a
problem, as I want this. But running
this script produces a crapload of
ORA-00942 errors running on an Oracle
db."

Ideally we should maintain our schema properly, using source control and configuration management best practices. In this scenario we know beforehand whether the schema we run our scripts against contains those tables. We don't get errors because we don't attempt to drop tables which don't exist.

However it is not always possible to do this. One alternate approach is to have two scripts. The first script just has the DROP TABLE statements, prefaced with a friendly

PROMPT  It is safe to ignore any ORA-00942 errors in the following statements

The second script has all the CREATE TABLE statements and leads off with

PROMPT  All the statements in this script should succeed.  So investigate any errors

Another option is to use the data dictionary:

begin
    for r in ( select table_name from user_tables )
    loop
        execute immediate 'drop table '||r.table_name
                    ||' cascade constraints';
    end loop;
end;

Be careful with this one. It is the nuclear option and will drop every table in your schema.

初吻给了烟 2024-10-02 18:39:02

如果您获得了 drop 语句的脚本,而 Hibernate 不会为您执行此操作,则将 DROP TABLE 语句包装在 IF 中,以在删除该表之前测试该表是否存在:

IF EXISTS(SELECT NULL 
            FROM TABLE_XYZ) THEN
  DROP TABLE TABLE_XYZ;
END IF;

If you get a script of drop statements, and Hibernate won't do it for you then wrap the DROP TABLE statements in an IF to test if the table exists before dropping it:

IF EXISTS(SELECT NULL 
            FROM TABLE_XYZ) THEN
  DROP TABLE TABLE_XYZ;
END IF;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文