Append One (Temporary) LOB to Another

Figure 10-20 Use Case Diagram: Append One (Temporary) LOB to Another

See: "Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs.

Purpose

This procedure describes how to append one (temporary) LOB to another.

Usage Notes

Not applicable.

Syntax

See Chapter 3, "LOB Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB): Oracle8i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- APPEND

• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobAppend.

• COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide: Appendix F, "Embedded SQL and Precompiler Directives" -- LOB APPEND

• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB APPEND

• Visual Basic (OO4O): There is no applicable syntax reference for this use case.

• Java (JDBC): There is no applicable syntax reference for this use case.

Scenario

These examples deal with the task of appending one segment of sound to another. Use sound-specific editing tools to match the wave-forms.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Append One (Temporary) LOB to Another

• C (OCI): Append One (Temporary) LOB to Another

• COBOL (Pro*COBOL): Append One (Temporary) LOB to Another

• C/C++ (Pro*C/C++): Append One (Temporary) LOB to Another

• Visual Basic (OO4O): No example is provided with this release.

• Java (JDBC): No example is provided with this release.

PL/SQL (DBMS_LOB Package): Append One (Temporary) LOB to Another

/* Note that the example procedure appendTempLOB_proc is not part of the 
   DBMS_LOB package. */
CREATE OR REPLACE PROCEDURE appendTempLOB_proc IS
   Dest_loc2 CLOB;
   Dest_loc  CLOB;
   Amount    NUMBER;
   Src_loc   BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE);
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Dest_loc2,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Src_loc,DBMS_LOB.LOB_READWRITE);
   Amount := 32767;
   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
   DBMS_LOB.LOADFROMFILE(Dest_loc2, Src_loc, Amount);
   DBMS_LOB.APPEND(Dest_loc, Dest_loc2);
   /* Close the temporary lobs and then free them: */
   DBMS_LOB.CLOSE(Dest_loc);
   DBMS_LOB.CLOSE(Dest_loc2);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.FREETEMPORARY(Dest_loc);
   DBMS_LOB.FREETEMPORARY(Dest_loc2);
END;

C (OCI): Append One (Temporary) LOB to Another

/* This function takes two temporary LOB locators and appends the second LOB to 
   the first one. It returns 0 if it completes successfully, and 
   -1, otherwise.*/

sb4 append_temp_lobs (OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{
  OCILobLocator *tblob;
  OCILobLocator *tblob2;
  OCILobLocator *bfile;
  ub4 amt = 4000;
  sb4 return_code = 0;

  printf("in append \n");
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }
  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob2,
                        (ub4) OCI_DTYPE_LOB,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,
                        (ub4) OCI_DTYPE_FILE,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED in print_length\n");
     return -1;
  }

  /* Set the BFILE to point to the Washington_audio file */ 
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED\n");
    return -1;
  }
   
  if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED  for the bfile\n");
    return_code = -1;
  }
 
  if(OCILobCreateTemporary(svchp,errhp,tblob,(ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return_code = -1;
  } 

  if(OCILobCreateTemporary(svchp,errhp,tblob2,(ub2)0, SQLCS_IMPLICIT,
                           OCI_TEMP_CLOB, OCI_ATTR_NOCACHE, 
                           OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return_code = -1;
  } 

  /* Open the lobs: */
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB tblob \n");
    return_code =  -1;
  }

  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob2, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB, tblob2 \n");
    return_code =  -1;
  }

  /* Populate the source temporary LOB with some data: */

  if(OCILobLoadFromFile(svchp, errhp, tblob,(OCILobLocator*)bfile,
                       (ub4)amt, (ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return_code = -1;
  }

  /* Append the source LOB to the dest temp LOB: */
  if (OCILobAppend(svchp, errhp,tblob2,tblob))
  {
    printf ("FAILED: OCILobAppend in append_temp_lobs\n");
    return_code = -1;
  }else 
  {
    printf("Append succeeded\n");
  }

  if(OCILobFreeTemporary(svchp,errhp,tblob))
  {
        printf("FAILED: OCILobFreeTemporary \n");
        return_code = -1;
  } 
 if(OCILobFreeTemporary(svchp,errhp,tblob2))
 {
      printf("FAILED: OCIlobFreeTemporary\n");
      return_code = -1;
 }
 return return_code;
 }

COBOL (Pro*COBOL): Append One (Temporary) LOB to Another

       IDENTIFICATION DIVISION.
       PROGRAM-ID. APPEND-TEMP-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

      * Define the username and password: 
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".

      * Define the temporary LOBs and the source BFILE: 
       01  TEMP-BLOB1     SQL-BLOB.
       01  TEMP-BLOB2     SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  AMT            PIC S9(9) COMP.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) VARYING.

      * Define the source position in BFILE: 
       01  SRC-POS        PIC S9(9) COMP.
 
      * Define the line number in case of error: 
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.
       PROCEDURE DIVISION.
       APPEND-TEMP-BLOB.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :TEMP-BLOB1 END-EXEC.
           EXEC SQL ALLOCATE :TEMP-BLOB2 END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB1
           END-EXEC. 
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-BLOB2
           END-EXEC. 

      * Set up the directory and file information: 
           MOVE "AUDIO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "Washington_audio" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

      * Open source BFILE and destination temporary BLOB: 
           EXEC SQL LOB OPEN :TEMP-BLOB2 READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :TEMP-BLOB1 READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
           DISPLAY "LOBs opened.".

      * Move the desired amount to copy to AMT: 
           MOVE 5 TO AMT.
           MOVE 1 TO SRC-POS.
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE 
                AT :SRC-POS INTO :TEMP-BLOB1
           END-EXEC.
           
           ADD 1 TO AMT GIVING SRC-POS.
           EXEC SQL 
                LOB LOAD :AMT FROM FILE :SRC-BFILE
                AT :SRC-POS INTO :TEMP-BLOB2 
           END-EXEC.
           DISPLAY "Temporary LOBs loaded".
           
           EXEC SQL
                LOB APPEND :TEMP-BLOB2 TO :TEMP-BLOB1
           END-EXEC.
           DISPLAY "LOB APPEND complete.".

           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB1
           END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB2
           END-EXEC.
           EXEC SQL FREE :TEMP-BLOB1 END-EXEC.
           EXEC SQL FREE :TEMP-BLOB2 END-EXEC.
           EXEC SQL FREE :SRC-BFILE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL
               WHENEVER SQLERROR CONTINUE
           END-EXEC.
           MOVE ORASLNR TO ORASLNRD
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Append One (Temporary) LOB to Another

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

void appendTempLOB_proc()
{
  OCIBlobLocator *Temp_loc1, *Temp_loc2;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount = 2048;
  int Position = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Create the Temporary LOBs: */
  EXEC SQL ALLOCATE :Temp_loc1;
  EXEC SQL ALLOCATE :Temp_loc2;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc1;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc2;
  /* Allocate and Initialize the BFILE Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;

  /* Opening the LOBs is Optional: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  EXEC SQL LOB OPEN :Temp_loc1 READ WRITE;
  EXEC SQL LOB OPEN :Temp_loc2 READ WRITE;

  /* Load a specified amount from the BFILE into the first Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc AT :Position INTO :Temp_loc1;

  /* Set the Position for the next load from the same BFILE: */
  Position = Amount + 1;

  /* Load a second amount from the BFILE into the second Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc AT :Position INTO :Temp_loc2;

  /* Append the second Temporary LOB to the end of the first one: */
  EXEC SQL LOB APPEND :Temp_loc2 TO :Temp_loc1;

  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL LOB CLOSE :Temp_loc1;
  EXEC SQL LOB CLOSE :Temp_loc2;

  /* Free the Temporary LOBs: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc1;
  EXEC SQL LOB FREE TEMPORARY :Temp_loc2;

  /* Release resources held by the Locators: */
  EXEC SQL FREE :Lob_loc;
  EXEC SQL FREE :Temp_loc1;
  EXEC SQL FREE :Temp_loc2;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  appendTempLOB_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}