将byte [0]插入Oracle DB表的NOT NULLABLE列中 [英] Inserting byte[0] into a NOT NULLABLE column in Oracle DB Table

问题描述

我在Oracle DB中有一个表DOCUMENTS，具有以下描述

I have a table DOCUMENTS in Oracle DB with the below description

create table DOCUMENTS
(
   DOC_ID   NUMBER not null,
   DOCUMENT BLOB not null,
   DOC_URL VARCHAR2(4000)
)

这是一个简单的表，用于存储来自多个来源的图像.从其中一个来源开始，要求是仅插入图像的HTTP路径而不添加DOCUMENT(Document为NULL).

This is a simple table which is used to store images from multiple sources. From one of the source, the requirement is to just insert the HTTP Path of the image without the DOCUMENT (Document would be NULL).

由于该列在表中被定义为NOT NULL，因此我试图通过在表中插入一个空的Blob来克服约束.我在INSERT语句中使用empty_blob()函数在PlSql语句中工作，如下所示

As the column is defined as NOT NULL in the table, I was trying to overcome the constraint by inserting an empty blob into the table. I got it working in a PlSql statement using the empty_blob() function in the INSERT statement as shown below

使用Pl/Sql

     insert into DOCUMENTS (doc_id,document,doc_url) values SEQ_DOCUMENT_ID.nextval, empty_blob(), 'http://path_to_image');
     commit;

但是当我尝试从NHibernate持久性插入它时，我收到一条错误消息，提示" ORA-01400:无法插入NULL "

But when I try to insert it from my NHibernate persistence I am getting an error saying "ORA-01400: cannot insert NULL"

在Nhibernate中不可用

using (var session = SessionFactory.OpenSession())
{
    Document documentDomain = new Document();
    documentDomain.doc_url = "http://path_to_the_document";
    documentDomain.document = new byte[0];
    session.Save(documentDomain);
}

这里有什么我想念的吗?

Is there something I am missing here ?

推荐答案

如果原始SQL适合您(绕过Document域模型)，则NHibernate允许您执行原始SQL查询.

If raw SQL suits you (bypassing Document domain model), NHibernate allows you to execute raw SQL queries.

var sql = @"<Your Sql Here>";
int count = session.CreateSQLQuery(sql)
    .ExecuteUpdate();

因此，以下类似内容应为您工作:

So something like below should work for you:

using (var session = SessionFactory.OpenSession())
{
    session.CreateSQLQuery("insert into DOCUMENTS (doc_id,document,doc_url) values SEQ_DOCUMENT_ID.nextval, empty_blob(), 'http://path_to_image');")
            .ExecuteUpdate();
}

通过执行原始SQL，您的事务仍将得到兑现.唯一的区别是会话级缓存.通过原始SQL执行的内容不会反映会话级缓存.如果您使用的是using块，那么从Session的有限范围来看，这应该不是什么大问题.

By executing raw SQL, your transaction will still be honored. The only difference will be Session Level Cache. The stuff executed through raw SQL will not reflect Session Level Cache. If you are using using block, this should not be a big issue looking at limited scope of Session.

这篇关于将byte [0]插入Oracle DB表的NOT NULLABLE列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！