如何在sqlplus（Oracle）中完成CREATE TYPE的输入？

发布于 2024-11-02 23:14:08 字数 203 浏览 3 评论 0原文

我正在尝试在终端中输入 CREATE TYPE，但我不知道如何完成它。这是我的具体查询：

CREATE TYPE testtype AS OBJECT (
    id int
);
/

然后它拒绝执行任何操作，而是等待更多输入。我可以毫无障碍地输入几乎所有其他查询类型。到底是怎么回事？我需要执行一些最终步骤才能完成它吗？

原文

I'm trying to input a CREATE TYPE in my terminal, what I can't figure out how to finish it. Here is my specific query:

CREATE TYPE testtype AS OBJECT (
    id int
);
/

It then refuses to perform any operation, but instead sits waiting for more input. I can input just about every other query type without a hitch. What is going on? Is there some finalizing step I need to do to make it finish?

分享到QQ

分享到微博

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

发布评论

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

哽咽笑 2024-11-09 23:14:08

当您说它“而是等待更多输入”时，您的意思是 SQL*Plus 正在要求另一行输入，还是数据库显然没有响应？

换句话说，您是否得到了这样的信息：（

SQL> create type testtype (
  2     id int
  3  );
  4  /
  5

5 表明 SQL*Plus 认为还有更多内容即将到来），或者您是否得到了类似的信息：（

SQL> create type testtype (
  2     id int
  3  );
  4  /


^Ccreate type testtype (
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation

在它运行了几秒钟后我中断了它没有成功完成。）

对于这个答案的其余部分，我将假设后者。老实说，我无法相信 SQL*Plus 无法识别您输入的结尾。单独一行上的单个 /（即使其一侧或两侧有空格）被解释为输入的明确结束，即使输入不是有效的 SQL。

请注意，如果您使用的是 bash 等 shell，即使您正在运行的程序当前没有请求用户输入，您也可以输入文本。例如：

SQL> create type testtype as object (
  2     id int
  3  );
  4  /

hello
is anybody there?
^Ccreate type testtype as object (
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation

在本例中，hello 和 is 有人吗？ 行被 SQL*Plus 忽略，因为当我输入它们时它正忙。（它可能会选择稍后读入它们 - 它们将可以从标准输入中读取 - 但在本例中它选择不这样做。）

为了让 create type 语句运行几秒钟而不是完成，我创建了以下（适当命名的）触发器：

CREATE OR REPLACE TRIGGER utterly_stupid
  BEFORE CREATE ON SCHEMA
BEGIN
  IF UPPER(ora_dict_obj_name) = 'TESTTYPE' THEN
    LOOP
      NULL;
    END LOOP;
  END IF;
END;
/

如果您尝试创建名为“TESTTYPE”的对象，则此触发器会导致无限循环。

我并不是说您的数据库中有一个像这个一样愚蠢的触发器，但您可能想查看一下以下任何 BEFORE CREATE 或 AFTER CREATE 触发器你的数据库。

另一种可能性可能是 AFTER SERVERERROR ON DATABASE 触发器。也许您的 create type 语句触发了错误，并导致这些触发器之一触发。您的数据库中有这些吗？

看似挂起的会话通常是由锁定引起的。如果另一个用户对您尝试更新的行或表有锁定，您的会话将阻塞，直到他们释放锁定。但很难看出什么可能会单独阻止 create type 语句，这就是我建议使用触发器的原因。您还可以尝试阅读这篇有关 Oracle 中的锁定和阻塞的文章。

我不敢相信问题是由于类型已经存在或者您没有创建类型的权限造成的。在这两种情况下，您都应该立即收到错误消息。

When you say that it 'instead sits waiting for more input', do you mean that SQL*Plus is asking for another line of input, or that the database apparently isn't responding?

In other words, are you getting this:

SQL> create type testtype (
  2     id int
  3  );
  4  /
  5

(the 5 indicating that SQL*Plus thinks there's more to come), or are you getting something like this:

SQL> create type testtype (
  2     id int
  3  );
  4  /


^Ccreate type testtype (
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation

(I interrupted this after it ran for a few seconds without successfully completing.)

For the rest of this answer, I'll assume the latter. I can't honestly believe that SQL*Plus would fail to recognise the end of your input. A single / on a line on its own (even with spaces on either or both sides of it) is interpreted as a definite end of input, even if the input isn't valid SQL.

Note that if you're using a shell such as bash, you can type in text even if the program you're running isn't currently requesting user input. For example:

SQL> create type testtype as object (
  2     id int
  3  );
  4  /

hello
is anybody there?
^Ccreate type testtype as object (
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation

In this case, the lines hello and is anybody there? were ignored by SQL*Plus since it was busy when I typed them in. (It may choose to later read them in - they will be available to read from its standard input - but in this case it chose not to.)

To get the create type statement to run for a few seconds and not complete, I created the following (appropriately-named) trigger:

CREATE OR REPLACE TRIGGER utterly_stupid
  BEFORE CREATE ON SCHEMA
BEGIN
  IF UPPER(ora_dict_obj_name) = 'TESTTYPE' THEN
    LOOP
      NULL;
    END LOOP;
  END IF;
END;
/

This trigger causes an infinite loop if you attempt to create an object called 'TESTTYPE'.

I'm not saying that you have a trigger that's as stupid as this in your database, but you may want to take a look at any BEFORE CREATE or AFTER CREATE triggers in your database.

Another possibility may be an AFTER SERVERERROR ON DATABASE trigger. Perhaps your create type statement is triggering an error, and causing one of these triggers to fire. Are there any of these in your database?

A session that appears to hang is usually caused by locking. If another user has a lock on a row or a table that you're trying to update, your session will block until they release their lock. But it's difficult to see what could be blocking a create type statement on its own, which is why I suggested triggers. You can also try reading this article on locking and blocking in Oracle.

I can't believe the problem is due to the type already existing or you not having permission to create a type. In both cases you should get an error straight away.

回复收藏 0 原文

绳情 2024-11-09 23:14:08

一定有什么事情你没有告诉我们：

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> CREATE TYPE testtype AS OBJECT (
  2      id int
  3  );
  4  /

Type created.

SQL>

There must be something you are not telling us:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> CREATE TYPE testtype AS OBJECT (
  2      id int
  3  );
  4  /

Type created.

SQL>

回复收藏 0 原文