CODEWITHSUNDEEP
c#oracle-databasenhibernatefluent-nhibernate
Dinny
Dinny

Reputation: 475

Inserting byte[0] into a NOT NULLABLE column in Oracle DB Table

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)
)

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).

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

Working in Pl/Sql

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

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

Not working in 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 ?

Upvotes: 1

Views: 637

Answers (1)

Amit Joshi
Amit Joshi

Reputation: 16387

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();
}

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.

Upvotes: 1

Related Questions