{"action":"created","issue":{"url":"https://api.github.com/repos/rana/ora/issues/55","labels_url":"https://api.github.com/repos/rana/ora/issues/55/labels{/name}","comments_url":"https://api.github.com/repos/rana/ora/issues/55/comments","events_url":"https://api.github.com/repos/rana/ora/issues/55/events","html_url":"https://github.com/rana/ora/issues/55","id":122687836,"number":55,"title":"Failing to write large blob","user":{"login":"C-Pro","id":1990084,"avatar_url":"https://avatars.githubusercontent.com/u/1990084?v=3","gravatar_id":"","url":"https://api.github.com/users/C-Pro","html_url":"https://github.com/C-Pro","followers_url":"https://api.github.com/users/C-Pro/followers","following_url":"https://api.github.com/users/C-Pro/following{/other_user}","gists_url":"https://api.github.com/users/C-Pro/gists{/gist_id}","starred_url":"https://api.github.com/users/C-Pro/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/C-Pro/subscriptions","organizations_url":"https://api.github.com/users/C-Pro/orgs","repos_url":"https://api.github.com/users/C-Pro/repos","events_url":"https://api.github.com/users/C-Pro/events{/privacy}","received_events_url":"https://api.github.com/users/C-Pro/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-12-17T08:40:39Z","updated_at":"2015-12-18T05:41:13Z","closed_at":null,"body":"I need to write jpeg files to blob field through stored procedure.\r\nWhen I try to insert more then 32767 bytes I get \"DrvStmt.Exec Stmt.exe Env.ociError ORA-01460: unimplemented or unreasonable conversion requested\" as if my []byte is converted into varchar2 somewhere.\r\n\r\nSchema and plsql function:\r\n\r\n    create table \"INTEGR\".\"PHOTO\"\r\n    (\r\n              \"PHOTO_ID\" number(11,0) not null enable,\r\n              \"VEHICLE_UUID\" NVARCHAR2(50) not null enable,\r\n              \"PHOTO\" BLOB,\r\n              \"DHMS_MESSAGE\" date not null enable,\r\n              \"TO_TREAT\"     number(2,0) default 1 not null enable,\r\n              constraint \"PHOTO_ID_PK\" primary key (\"PHOTO_ID\")\r\n    );\r\n\r\n    create sequence \"INTEGR\".\"PHOTO_SEQ\";\r\n\r\n    create or replace function integr.add_photo(p_vehicle_uuid photo.vehicle_uuid%type,\r\n                                                p_photo         photo.photo%type,\r\n                                                p_dhms_message  photo.dhms_message%type,\r\n                                                p_to_treat      photo.to_treat%type)\r\n    return photo.photo_id%type as\r\n      v_id   photo.photo_id%type;\r\n    begin\r\n        if nvl(dbms_lob.getlength(p_photo),0) = 0 then\r\n            return null;\r\n        end if;\r\n        insert into integr.photo(photo_id,\r\n                                 vehicle_uuid,\r\n                                 photo,\r\n                                 dhms_message,\r\n                                 to_treat)\r\n        values(0,\r\n               p_vehicle_uuid,\r\n               p_photo,\r\n               p_dhms_message,\r\n               p_to_treat)\r\n        returning photo_id into v_id;\r\n        return v_id;\r\n    end;\r\n\r\nCode:\r\n\r\n    package main\r\n\r\n    import(\r\n        \"fmt\"\r\n        \"database/sql\"\r\n        \"gopkg.in/rana/ora.v3\"\r\n    )\r\n\r\n    func main(){\r\n        ora.Register(nil);\r\n        db, err := sql.Open(\"ora\", \"integr/integr@localhost:49161/xe\")\r\n        if err != nil {\r\n           fmt.Println(err)\r\n        }\r\n        \r\n        a := make([]byte,32767+1)\r\n        \r\n        //This inserts succsessfuly:\r\n        //sql := \"insert into photo values(photo_seq.nextval,'asdas',:1,sysdate,1)\"\r\n\r\n        //And this fails with \"DrvStmt.Exec Stmt.exe Env.ociError ORA-01460: unimplemented or unreasonable conversion requested\"\r\n        sql := \"declare v integer; begin  v:=integr.add_photo('asdas',:1,sysdate,1); end;\"\r\n\r\n        r, err := db.Exec(sql, a)\r\n        if err != nil {\r\n           fmt.Println(err)\r\n        }\r\n    }"},"comment":{"url":"https://api.github.com/repos/rana/ora/issues/comments/165678856","html_url":"https://github.com/rana/ora/issues/55#issuecomment-165678856","issue_url":"https://api.github.com/repos/rana/ora/issues/55","id":165678856,"user":{"login":"C-Pro","id":1990084,"avatar_url":"https://avatars.githubusercontent.com/u/1990084?v=3","gravatar_id":"","url":"https://api.github.com/users/C-Pro","html_url":"https://github.com/C-Pro","followers_url":"https://api.github.com/users/C-Pro/followers","following_url":"https://api.github.com/users/C-Pro/following{/other_user}","gists_url":"https://api.github.com/users/C-Pro/gists{/gist_id}","starred_url":"https://api.github.com/users/C-Pro/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/C-Pro/subscriptions","organizations_url":"https://api.github.com/users/C-Pro/orgs","repos_url":"https://api.github.com/users/C-Pro/repos","events_url":"https://api.github.com/users/C-Pro/events{/privacy}","received_events_url":"https://api.github.com/users/C-Pro/received_events","type":"User","site_admin":false},"created_at":"2015-12-18T05:41:13Z","updated_at":"2015-12-18T05:41:13Z","body":"Sorry, I could not find any examples writing blobs with this library. Only PHP Oci8 examples here: http://php.net/manual/ru/function.oci-new-descriptor.php\r\nI tried like this, but failed miserably. And I suspect that it will not work anyway, because of the way database/sql treats out parameters (it does not return them).\r\nI tried interface{}, byte[], bytes.Buffer, *bytes.Buffer as third parameter to\r\n`tx.Exec(\"insert into photo values(photo_seq.nextval,'asdas',empty_blob(),sysdate,1) returning photo_id into :1\", b, HERE)` but it all failed. Each time I have empty blob in database.\r\n\r\n    package main\r\n\r\n    import(\r\n        \"fmt\"\r\n        \"database/sql\"\r\n        \"gopkg.in/rana/ora.v3\"\r\n        \"io\"\r\n        \"bytes\"\r\n    )\r\n\r\n    func main(){\r\n        ora.Register(nil);\r\n        db, err := sql.Open(\"ora\", \"integr/integr@localhost:49161/xe\")\r\n        if err != nil {\r\n           fmt.Println(err)\r\n        }\r\n\r\n        lob_len := 32767+1\r\n        a := make([]byte, lob_len)\r\n\r\n        var buf bytes.Buffer\r\n        var b int64\r\n        //var i interface{}\r\n        tx, _ := db.Begin()\r\n\r\n        m, err := buf.Write(a)\r\n        if err!= nil || m < lob_len {\r\n            fmt.Errorf(\"Failed to write buf: %v\\n\", err)\r\n            return\r\n        }\r\n\r\n        sql := \"insert into photo values(photo_seq.nextval,'asdas',empty_blob(),sysdate,1) returning photo_id into :1\"\r\n\r\n        r, err := tx.Exec(sql, b)\r\n        if err != nil {\r\n           fmt.Println(err)\r\n        }\r\n        b, err = r.LastInsertId()\r\n        fmt.Println(b)\r\n\r\n        sql = \"update photo set photo=empty_blob() where photo_id = :1 returning photo into :2\"\r\n\r\n        r, err = tx.Exec(sql, b, &buf)\r\n        if err != nil {\r\n           fmt.Println(err)\r\n        }\r\n        n, err := io.Copy(&buf,bytes.NewReader(a))\r\n        if err != nil {\r\n\t\t\t    fmt.Errorf(\"Write err: %v\", err)\r\n\t    }\r\n\t    fmt.Printf(\"n=%d\\n\", n)\r\n        tx.Commit()\r\n    }\r\n"}}