pl/sql code to upload document

I have C# code that does this, but it is basically just wrapping IFS-native API methods in Oracle PL/SQL. Here it is, don’t glaze over, I will summarize further down:

if (createDocument)
{
    // We need to create an IFS document from the file.
    // We start by writing a DocTitle object, as that sets up the framework (title and Issue) for document storage in IFS.
    docTitle.doc_no = null;     // Leave null, IFS will fill in.
    docTitle.doc_class = docClass;
    docTitle.title = title;
    docTitle.dt_cre = DateTime.Now;
    success = docTitle.Write(out _);
    if (success)
    {
        // DocTitle was written successfully, which also writes other data, such as Doc_Issue. Grab the key fields defining the document.
        docNo = docTitle.doc_no;
        docSheet = docTitle.first_sheet_no;
        docRev = docTitle.first_revision;
        // We can now store the actual document (file references and contents) to the database. 
        // Create the record structure to house the file reference. By creating an "ORIGINAL" EDF_File object, it automatically
        // creates an ORIGINAL and a VIEW record. These are the "Document Revisions" in IFS (both part of revision A1).
        // *** NOTE *** If check-in and check-out is not recorded properly, the document could make it into IFS but not be viewable.
        // The strange method call below ("Check_In_View_Type_Orig_File_") appears to create EDM_File records correctly in this regard.
        parms.Clear();
        parms.Add(new OdbcParameter("Doc_Class_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_Class_", DataRowVersion.Current, docClass));
        parms.Add(new OdbcParameter("Doc_No_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_No_", DataRowVersion.Current, docNo));
        parms.Add(new OdbcParameter("Doc_Sheet_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_Sheet_", DataRowVersion.Current, docSheet));
        parms.Add(new OdbcParameter("Doc_Rev_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_Rev_", DataRowVersion.Current, docRev));
        parms.Add(new OdbcParameter("File_Name_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "File_Name_", DataRowVersion.Current, justFileName));
        parms.Add(new OdbcParameter("File_No_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "File_No_", DataRowVersion.Current, "1"));
        success = OracleAPIFuncs.CustomMethodCall("EDM_File", "Check_In_View_Type_Orig_File_", null, parms);
        if (success)
        {
            // We also need an object added to EDM_File_Storage which houses the actual BLOB containing binary document content.
            parms.Clear();
            parms.Add(new OdbcParameter("Doc_Class_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_Class_", DataRowVersion.Current, docClass));
            parms.Add(new OdbcParameter("Doc_No_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_No_", DataRowVersion.Current, docNo));
            parms.Add(new OdbcParameter("Doc_Sheet_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_Sheet_", DataRowVersion.Current, docSheet));
            parms.Add(new OdbcParameter("Doc_Rev_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_Rev_", DataRowVersion.Current, docRev));
            parms.Add(new OdbcParameter("Doc_Type_", OdbcType.VarChar, 2000, ParameterDirection.Input, true, 0, 0, "Doc_Type_", DataRowVersion.Current, "VIEW"));
            parms.Add(new OdbcParameter("File_No_", OdbcType.Numeric, 10, ParameterDirection.Input, true, 10, 10, "File_No_", DataRowVersion.Current, 1));
            success = OracleAPIFuncs.CustomMethodCall("EDM_File_Storage", "Create_New_File", null, parms);
            if (success)
            {
                // At this point, all records/objects are in place. All that is left is to load the BLOB with the document content.
                // It looks as if ODBC has a 32K limitation when trying to bind/pass a BLOB parameter. Since document file sizes will regularly exceed
                // that threshold, we need a better way of calling the package method that fills the document BLOB.
                // *** Use Oracle's .NET ManagedAccess client/layer. ***
                // This code needs to be wrapped in a specific error handler because the MDAC Oracle client has not been wrapped in
                // a global framework that handles all of that for us (like the ODBC client does).
                try
                {
                    OracleCommand writeBlobData = (OracleCommand)OracleAPIFuncs.OracleMdacUtil.Command;
                    writeBlobData.CommandText = "EDM_File_Storage_API.Write_Blob_Data";
                    writeBlobData.CommandType = CommandType.StoredProcedure;
                    writeBlobData.Parameters.Clear();
                    writeBlobData.Parameters.Add("Doc_Class_", OracleDbType.Varchar2, ParameterDirection.Input).Value = docClass;
                    writeBlobData.Parameters.Add("Doc_No_", OracleDbType.Varchar2, ParameterDirection.Input).Value = docNo;
                    writeBlobData.Parameters.Add("Doc_Sheet_", OracleDbType.Varchar2, ParameterDirection.Input).Value = docSheet;
                    writeBlobData.Parameters.Add("Doc_Rev_", OracleDbType.Varchar2, ParameterDirection.Input).Value = docRev;
                    writeBlobData.Parameters.Add("Doc_Type_", OracleDbType.Varchar2, ParameterDirection.Input).Value = "VIEW";
                    writeBlobData.Parameters.Add("File_No_", OracleDbType.Int32, ParameterDirection.Input).Value = 1;
                    writeBlobData.Parameters.Add("File_Data_", OracleDbType.Blob, ParameterDirection.Input).Value = File.ReadAllBytes(file);
                    writeBlobData.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    LastError = "Error writing document BLOB to EdmFileStorage object: " + e.Message;
                    success = false;
                }
            }
            else
            {
                LastError = "Error creating EdmFileStorage object:" + OracleAPIFuncs.LastErrorShort;
            }
        }
        else
        {
            LastError = "Error creating EdmFile object:" + OracleAPIFuncs.LastErrorShort;
        }
    }
    else
    {
        LastError = "Error adding DocTitle object: " + docTitle.LastError;
    }
}

Looks complicated, and some of the classes/types are use are custom-programmed elements. But much of the complexity involves setting up parms to call API package methods. The overall process looks like this:

1. Create a Doc_Title data object, however you want to do that in your PL/SQL code (probably using the Doc_Title_API.New__() method).
2. Call the Check_In_View_Type_Orig_File_() method of the EDM_File_API package. Looks like it takes six parameters to define the document.
3. Call the Create_New_File() method of the EDM_File_Storage_API package. Again, six parameters to define the document.
4. The prior steps are all setup. Now it is time to actually save the BLOB data to the database by calling the Write_Blob_Data() method of the EDM_File_Storage_API package. Seven parameters this time, the same six we have been passing plus the binary stream representing the file LOB. I need to use Oracle MDAC at this point because the Oracle Instant Client/ODBC provider does not do well with parameters larger than 32K in size -- need MDAC for LOBs.

After all that, if you want to link the document to an LU you will need to create a new Doc_Reference_Object data object. That is where you link the stored document with a set of keys that match an LU record of your choice.

If nothing else, you can reference the methods listed above to see how documents are stored. Some of the operations show up in the IEE Debugger while others show up less. You can also try to use the RESTful API to upload documents, but I never got that to work. The only tricky part that I don’t know how to do in PL/SQL is loading the LOB data from a file. I imagine that would involve referencing a Oracle Directory to find a file that is external to the database, and that will need to me made accessible to the Oracle server (if you code this server-side -- the above example is a client-side operation from .NET/C#, but you mentioned PL/SQL).

I will see if I can dig up any other threads about document storage now that we know the names of some of the package methods in play.

Good luck!

Joe Kaufman