ODP.NET

Hello all, I'm hoping someone can help. My requirement is to extract blobs in a table to a file. I have an 11g database with about 80,000 files attached (PDF) on one table via oracle forms6i using the OLE attachment. I have tried using oracle forms OLE built-in packages but got nowhere, i was able to automate docs and excel files but not PDFs. So, as an alternative solution i have wondered if i could stream the blob to a file minus the OLE wrapper using c# in a windows form application. It's a long shot but i don't seem to have any other solution.

My environment

Windows 7

Visual Studio 2015

Oracle 11g

ODP.Net 12.2

Oracle 6i forms code I have tried (for reference only)

PROCEDURE extract_ole (filename varchar2, obj varchar2) IS

handle ole2.obj_type;

args ole2.list_type;

application OLE2.OBJ_TYPE;

errmsg VARCHAR2(2000);

BEGIN

forms_ole.ACTIVATE_SERVER(obj);

handle := forms_ole.get_interface_pointer(obj);

-- Save a document to disk

Args :=OLE2.CREATE_ARGLIST;

OLE2.ADD_ARG(Args, :control.path||filename); -- file name

OLE2.INVOKE(handle, 'SaveAs', Args);

OLE2.DESTROY_ARGLIST(Args);

ole2.release_obj(handle);

forms_ole.CLOSE_SERVER(obj);

END;

I have tried Froms 12c but that limits me even further.

I am thinking of doing something like this:

using System.Windows.Forms;
using Oracle.DataAccess.Client;
namespace OLEExtract
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string connstring =
                "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1527))" +
                "(CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;";
            using (OracleConnection conn = new OracleConnection(connstring))
            {
                conn.Open();
                string sql = "select document from safe_documents";
                using (OracleCommand comm = new OracleCommand(sql, conn))
                {
                    using (OracleDataReader rdr = comm.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            //stream blob to file here
                        }
                    }
                }
            }
        }
    }
}

At the point where I will extract the blob I was going to try and strip the OLE container away and hopefully leave just the file.

Once i have the file i can assign the correct extension using some kind of mime detector.

I realise at this point i don't know the number of bytes to take away, or where the OLE Container ends and the file starts.

Does anyone have any experience of this? Is it a nonstarter?

Thank you,