7 Replies Latest reply on Aug 30, 2011 8:44 PM by Edward.Mx-Oracle

    Storing BLOB data with my own procedure

    Edward.Mx-Oracle
      Hi guys,
      I'm kind of new on APEX, already search through the forum and did not find something about my issue.
      I have a procedure, that inserts a new record on a table, this new record has a BLOB column. Also the procedure does some parallel validations and insertions to other tables (totally unrelated but it depends on the unique ID generated for the row where the BLOB is).

      I want to know if I use my own procedure for inserting a new BLOB will it populate the mimetype, filename, last_update and character_set columns? or, that can only be done by using the predetermined Form Wizard that APEX offers, with the Automatic Row Processing (DML).

      I really need to use my procedure cause I have some extra validations and I depend of the unique ID that is generated in Real Time, and I dont think there is a way to modify an Automatic Row Processing.

      And if I create a parallel process, how can I get the same ID APEX just used for the row with the Blob file, to update with other information I need to store after validating it?

      I thought on retrieving the last ID generated to go back and insert what my procedure does but this can be risky as other user can be inserting a new blob record at the same moment.
        • 1. Re: Storing BLOB data with my own procedure
          Tyson Jouglet
          Numbers,

          As you start getting into more complex transactions and larger applications I find it is a lot easier to maintain when most, if not all, transactions are handled by database packages. So to answer your question, Yes you can use your own procedure to insert a blob. If you have an "Automatic Row Processing(DML)" (ARP) on your page there is a property called "Return Key Into Item" put the name of the item which holds the primary key in that field. Also make sure your custom procedure runs after the ARP and referenced the item you specified in the "Return Key Into Item" field.

          I am pretty sure this utilizes the "RETURNING" clause for the inserts/updates for the ARP.

          Cheers,
          Tyson Jouglet
          1 person found this helpful
          • 2. Re: Storing BLOB data with my own procedure
            fac586
            >

            Welcome to the forum: please read the FAQ and forum sticky threads (if you haven't done so already), and ensure you have updated with your profile with a real handle instead of "876236".

            You'll get a faster, more effective response to your questions by including as much relevant information as possible upfront. This should include:

            <li>Full APEX version
            <li>Full DB version and edition
            <li>Web server architecture (EPG, OHS or APEX listener)
            <li>Browser(s) and version(s) used
            <li>Theme
            <li>Template(s)
            <li>Region type(s)
            I have a procedure, that inserts a new record on a table, this new record has a BLOB column. Also the procedure does some parallel validations and insertions to other tables (totally unrelated but it depends on the unique ID generated for the row where the BLOB is).
            How can it be "totally unrelated" if it "depends on the unique ID generated for the row where the BLOB is"?
            I want to know if I use my own procedure for inserting a new BLOB will it populate the mimetype, filename, last_update and character_set columns?
            Magically? No. You would have to use the Table WWV_FLOW_FILES Storage Type for the file browse item, transferring the BLOB from the <tt>apex_application_files</tt> view (the preferred way to access <tt>wwv_flow_files</tt>). This also contains columns for the file metadata:
            select blob_content, filename, mime_type, last_updated
            into   ... /* procedure variables */
            from   apex_application_files
            where  name = :p1_blob_file /* BLOB file browse item */
            Character set information has to be handled using a separate item as in declarative BLOB support.
            • 3. Re: Storing BLOB data with my own procedure
              Edward.Mx-Oracle
              Hi,
              Thanks both of you,
              I will try the second solution then the first one. I want to be able to manipulate the processing of the page and ARP does not let me do that.

              Im using apex.oraclecorp.com which is 4.1
              Theme: Crimson


              One question Fac586:

              Im creating the procedure like this:

              create or replace procedure "SAVE_SCRIPT"(
              P5_SCRIPT IN BLOB default NULL
              )

              Here I receive the BLOB file from the browser... Then I insert it later in the body to the proper table


              EXECUTE IMMEDIATE 'INSERT INTO "script" (filename,mimetype,blob_script,last_update_date) VALUES(:1,:2,:3,:4)'
              USING ?,?,P5_SCRIPT,SYSDATE;
              COMMIT;

              How do I assign the filename and mimetype values using WWV_FLOW_FILES as you stated?
              • 4. Re: Storing BLOB data with my own procedure
                fac586
                Edward_MX wrote:

                Im creating the procedure like this:

                create or replace procedure "SAVE_SCRIPT"(
                P5_SCRIPT IN BLOB default NULL
                )

                Here I receive the BLOB file from the browser... Then I insert it later in the body to the proper table

                EXECUTE IMMEDIATE 'INSERT INTO "script" (filename,mimetype,blob_script,last_update_date) VALUES(:1,:2,:3,:4)'
                USING ?,?,P5_SCRIPT,SYSDATE;
                COMMIT;
                <li>It's strongly recommended to create packages rather than standalone procedures and functions.
                <li>DO NOT use quoted identifiers where it is not necessary to do so. Drop the <tt>"script"</tt> table and any other objects you have created using explicit lowercase or Oracle reserved word identifiers that require the use of quoted identifiers and recreate them using basic, valid, nonquoted names.
                <li>DO NOT use dynamic SQL when static code can be used.
                <li>DO NOT commit unnecessarily. When commit is executed?: this is usually more than enough.

                Where is "the unique ID generated for the row where the BLOB is"?
                How do I assign the filename and mimetype values using WWV_FLOW_FILES as you stated?
                create or replace procedure save_script (p_script in varchar2)
                is
                begin
                  &#8942;
                  insert into script s
                    (s.filename, s.mimetype, s.blob_script, s.last_update_date)
                  select f.filename, f.mime_type, f.blob_content, f.last_updated
                  from   apex_application_files f
                  where  f.name = save_script.p_script
                  &#8942;
                end save_script;
                All this of "script" makes me somewhat suspicious. What exactly is the nature of these BLOBs?
                • 5. Re: Storing BLOB data with my own procedure
                  Edward.Mx-Oracle
                  Thanks again Mr. MacMillan

                  That is exactly what I was looking for...
                  Great advices you got there regarding the use of my "conventions"... I was surprise that just because I created the table in lowercase I needed to use quotes, I did not know that before.. Im new to plsql too. In any case, by "dynamic SQL" you mean to the fact that I'm calling a procedure?
                  About the suspicious "scripts", well, Im creating an internal script repository, (will be called ScriptHUB) and its purpose is to storage files, mainly sql scripts. The user (Developer) will upload the file and other users can see it and put their comments and rate it. These BLOBs are meant to be sql files. Or TXT for that matter, any kind of basic unicode text file.

                  Ok. so, assuming that my "file browse" item is called "p_script".
                  The user will upload the file, will hit the "submit" button and it will call the procedure and inserting into the table using the references to apex_application_files.


                  FILE BROWSE ----> HIT SUBMIT ----> call SAVESCRIPT procedure (ask for the mimetype,filename,lastupdate from apex_application_files and save them into my scripts table)


                  Thanks so much. That answers exactly what I was asking for. You even gave me more with those advices. I appreciate your time.

                  Best regards

                  Edited by: Edward_MX on 30-ago-2011 7:58
                  • 6. Re: Storing BLOB data with my own procedure
                    fac586
                    Edward_MX wrote:
                    Ok. so, assuming that my "file browse" item is called "p_script".
                    No, pass the value of the file browse item to the procedure:
                    save_script(:p5_script);
                    • 7. Re: Storing BLOB data with my own procedure
                      Edward.Mx-Oracle
                      Great!

                      It worked! I would post the script but is kind of large. The main issue is to get the information from apex application files... by doing a DESC APEX_APPLICATION_FILES it returns all the values that stores. great method thans Mr. Paul