7 Replies Latest reply: Jul 16, 2012 9:42 AM by user4485803 RSS

    Oracle 11g procedures clarification

    user4485803
      I m bit worried , i am following proper pl/sql standard and exception handling. Please advise.

      main purpose of this procedure is  to insert the chunks of file into temp table.


      PROCEDURE file_temp_sp
      (
      in_clob_Src IN BLOB,
      in_file_name IN VARCHAR2,
      in_app_module_id IN VARCHAR2,
      in_app_module_subid IN VARCHAR2,
      out_error_no OUT NUMBER
      )
      IS

      /*****************************************************************************/
      -- Description : This procedure is used to insert the chunks of document in temp table

      /*****************************************************************************/

      v_newId INTEGER;
      l_key_subkey_id VARCHAR2(500);

      BEGIN

      out_error_no := 0;

      l_key_subkey_id := in_app_module_id || in_app_module_subid;


      --Fetch Max ChunkPart
      SELECT MAX(temp.filechunkpart)
      INTO v_newId
      FROM upload temp
      WHERE temp.filename = in_file_name
      AND temp.key_subkey_id = l_key_subkey_id ; ------- in_app_module_id || in_app_module_subid;

      IF v_newId IS NULL THEN
      v_newId := 0;
      END IF;

      -- inserting the document in temporary table
      INSERT
      INTO upload
      (filedata,
      filename,
      key_subkey_id
      )
      VALUES
      (in_clob_Src,
      v_newId + 1,
      l_key_subkey_id);

      END file_temp_sp;
        • 1. Re: Oracle 11g procedures clarification
          rp0428
          >
          Please advise
          >
          My advise would be to actually ask a question.
          • 2. Re: Oracle 11g procedures clarification
            sb92075
            user4485803 wrote:
            I m bit worried , i am following proper pl/sql standard and exception handling. Please advise.

            main purpose of this procedure is  to insert the chunks of file into temp table.
            Rarely is TEMP table needed within any Oracle application.

            row by row processing is slow by slow.
            NEVER do in PL/SQL what can be done in plain SQL.
            • 3. Re: Oracle 11g procedures clarification
              user4485803
              Can i rewrite the same code with merge statement ,


              find the max value of filechunk , if it matches with existing row key value increment filechunk file value 1 and insert
              if not insert as a fresh record with filechunk = 0

              Does merge supports matched and non matched for insert statement?

              Here is the merge code:


              MERGE INTO upload m
              USING (
              SELECT MAX(filechunkpart) as c_part,filename,uploadedby
              FROM upload
              WHERE filename = in_file_name
              AND uploadedby = in_user_id
              ) c
              ON (m.file_name = c.uploadedby
              AND m.uploadedby = c.in_user_id)
              WHEN MATCHED THEN
              (filedata,
              chunksize,
              filename,
              filechunkpart,
              filestartoffset,
              fileendoffset,
              uniqueid,
              uploadedby)
              VALUES
              (M.in_clob_Src,
              in_chunk_size,
              in_file_name,
              M.c_part + 1,
              in_file_start_offset,
              in_file_end_offset,
              in_unique_id,
              in_user_id)
              WHEN NOT MATCHED THEN
              (filedata,
              chunksize,
              filename,
              filechunkpart,
              filestartoffset,
              fileendoffset,
              uniqueid,
              uploadedby)
              VALUES
              (M.in_clob_Src,
              in_chunk_size,
              in_file_name,
              0,
              in_file_start_offset,
              in_file_end_offset,
              in_unique_id,
              in_user_id);
              • 4. Re: Oracle 11g procedures clarification
                Billy~Verreynne
                I do not see any use for out_error_no.

                It is wrong to override exceptions and return exceptions as output parameters. That violates the code contract between that code and its caller, wrt error handling as implemented by the programming language's run-time environment.

                And not only does the code define out_error_no, it does not actually return an error code either.

                As for the code's design - I do not see why one would want to treat a single file's content as chunks using multiple BLOBs stored as individual rows. What for??

                A single BLOB is capable of holding between 8TB and 128TB data (depending on db block size). Yet, instead of using a single BLOB for the file, this code creates multiple BLOBs containing different chunks for the same file. How is this approach robust or logical?
                • 5. Re: Oracle 11g procedures clarification
                  user4485803
                  thanks a lot for the information.

                  We cant upload big file into one shot so we would be getting chunk 1,2,3 etc.
                  while processing these chunk file we create lob to handle this.

                  Here is the questions is

                  if chunk file exists , find the existing chunk file max value and insert into table with max value +1
                  if chunk file doesn't exists then insert chunk file start with 0.

                  The below code is wrong fine but not the oracle standard , i mean proper exception handling
                  Please help me to implement proper pl/sql standard.
                  thanks in advance.


                  PROCEDURE file_temp_sp
                  (
                  in_clob_Src IN BLOB,
                  in_file_name IN VARCHAR2,
                  in_app_module_id IN VARCHAR2,
                  in_app_module_subid IN VARCHAR2,
                  out_error_no OUT NUMBER
                  )
                  IS

                  /*****************************************************************************/
                  -- Description : This procedure is used to insert the chunks of document in temp table
                  /*****************************************************************************/
                  v_newId INTEGER;
                  l_key_subkey_id VARCHAR2(500);

                  BEGIN
                  out_error_no := 0;

                  l_key_subkey_id := in_app_module_id || in_app_module_subid;

                  --Fetch Max ChunkPart

                  SELECT MAX(temp.filechunkpart)
                  INTO v_newId
                  FROM upload temp
                  WHERE temp.filename = in_file_name
                  AND temp.key_subkey_id = l_key_subkey_id ;



                  IF v_newId IS NULL THEN
                  v_newId := 0;
                  END IF;

                  -- inserting the document in temporary table
                  INSERT
                  INTO upload
                  (filedata,
                  filename,
                  key_subkey_id
                  )
                  VALUES
                  (in_clob_Src,
                  v_newId + 1,
                  l_key_subkey_id);

                  END file_temp_sp;
                  • 6. Re: Oracle 11g procedures clarification
                    Billy~Verreynne
                    Why not create a single row for the file - and as each successive chunk is supplied, use DBMS_LOB.WriteAppend() to add that to that row's BLOB?

                    Deal with the file as a single BLOB. Not mutiple BLOBs. Because if the file is divided into pieces, whoever wants to use that file needs to put these pieces back together again. So why then store it as pieces in the first place?
                    • 7. Re: Oracle 11g procedures clarification
                      user4485803
                      sometime if the chunk file is not properly uploaded we will overwrite (re upload) those files.
                      it is a already existing business flow, i would not able to change the logic.

                      wanted to correct the code

                      PROCEDURE file_temp_sp
                      (
                      in_clob_Src IN BLOB,
                      in_file_name IN VARCHAR2,
                      in_app_module_id IN VARCHAR2,
                      in_app_module_subid IN VARCHAR2,
                      out_error_no OUT NUMBER
                      )
                      IS

                      /*****************************************************************************/
                      -- Description : This procedure is used to insert the chunks of document in temp table
                      /*****************************************************************************/
                      v_newId INTEGER;
                      l_key_subkey_id VARCHAR2(500);

                      BEGIN
                      out_error_no := 0;

                      l_key_subkey_id := in_app_module_id || in_app_module_subid;

                      --Fetch Max ChunkPart

                      SELECT MAX(temp.filechunkpart)
                      INTO v_newId
                      FROM upload temp
                      WHERE temp.filename = in_file_name
                      AND temp.key_subkey_id = l_key_subkey_id ;

                      IF v_newId IS NULL THEN
                      v_newId := 0;
                      END IF;

                      -- inserting the document in temporary table
                      INSERT
                      INTO upload
                      (filedata,
                      filename,
                      key_subkey_id
                      )
                      VALUES
                      (in_clob_Src,
                      v_newId + 1,
                      l_key_subkey_id);

                      EXCEPTION
                      WHEN OTHERS THEN
                      out_error_no := 3000;
                      error_handler.log_and_quit( SQLCODE, SQLERRM || ' -- Upload a chunk file', TRUE );

                      END file_temp_sp;