将数据插入Oracle

发布于 2024-10-19 22:35:01 字数 143 浏览 5 评论 0原文

我正在尝试将数据库从 Informix 迁移到 Oracle。Informix 有一个选项,例如在插入表时,如果值的大小超过列长度,则 Informix 会自动修剪数据。但是 Oracle 不支持此操作,并且总是引发异常.有没有办法阻止和允许修剪,或者我们必须虔诚地尊重?

I am trying to migrate a DB from Informix to Oracle.Informix had an option like while inserting into a table if the size of the value exceeds the column length then Informix automatically trims the data.But Oracle does not support this and always throws an exception.Is there a way to prevent and allow trim or we have to respect religiously?

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

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

发布评论

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

评论(3

别低头,皇冠会掉 2024-10-26 22:35:01

Oracle中没有自动修剪数据的功能,您必须自己明确地修剪它,例如

insert into mytable (id, text) values (123, substr(var,1,4000));

There is no automatic trimming of data in Oracle, you have to trim it explicitly yourself e.g.

insert into mytable (id, text) values (123, substr(var,1,4000));
划一舟意中人 2024-10-26 22:35:01

Oracle确实支持各种修剪变量的SQL函数。我怀疑你想要的是“SUBSTR()”。问题是您需要明确指定所需的长度。在此示例中,T23.WHATEVER 被假定为 VARCHAR2(30),而 T24.TOO_LONG_COLUMN 更长:

insert into t23
  (id
    , whatever)
select pk_col
       , substr(too_long_col, 1, 30)
from t42
/

Oracle does support a variety of SQL functions which trim variables. I suspect the one you'll want is 'SUBSTR()'. The problem is that you will need to specify the desired length explicitly. In this example T23.WHATEVER is presumed to be VARCHAR2(30) and T24.TOO_LONG_COLUMN is, er, longer:

insert into t23
  (id
    , whatever)
select pk_col
       , substr(too_long_col, 1, 30)
from t42
/
花之痕靓丽 2024-10-26 22:35:01

除了托尼的建议之外,您还可以使用 CAST

select cast ('1234' as varchar2(3)) a
from dual

如果您正在进行数据迁移,请查看 DML 错误记录

将所有不合格数据与失败原因放入相应的表中,这绝对是梦想。

As well as Tony's suggestion, you can use a CAST

select cast ('1234' as varchar2(3)) a
from dual

If you are doing data migration, look into DML Error Logging

Having all your non-conformant data put into a corresponding table with the failure reason is positively dreamy.

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