Erasing Part of a Temporary LOB

Figure 11-24 Use Case Diagram: Erasing Part of a Temporary LOB

Text description of the illustration adl11t13.gif

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

Purpose

This procedure describes how to erase part of a temporary LOB.

Usage Notes

Not applicable.

• Syntax

See Chapter 3, "LOB Support in Different 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): Oracle9i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- ERASE

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

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

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

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

• Java (JDBC): See "Java (JDBC): Erasing Part of a LOB" in Chapter 10, "Internal Persistent LOBs".

Scenario

Not applicable.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): Erasing Part of a Temporary LOB

• C (OCI): Erasing Part of a Temporary LOB

• COBOL (Pro*COBOL): Erasing Part of a Temporary LOB

• C/C++ (Pro*C/C++): Erasing Part of a Temporary LOB

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

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

PL/SQL (DBMS_LOB Package): Erasing Part of a Temporary LOB

/* Note that the example procedure eraseTempLOB_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE trimTempLOB_proc IS
   Lob_loc        CLOB;
   amt            number;
   Src_loc        BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount         INTEGER := 32767;
BEGIN

   /* Create a temporary LOB: */
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);

   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);

   /* Populate the temporary LOB with some data: */
   Amount := 32767;
   DBMS_LOB.LOADFROMFILE(Lob_loc, Src_loc, Amount);
   /* Erase  the LOB data: */
   amt := 3000;
   DBMS_LOB.ERASE(Lob_loc, amt, 2);

    /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE (Lob_loc);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Erasing Part of a Temporary LOB

/* Erase 2 bytes at offset 100 in a temporary LOB: */ 
sb4 erase_temp_lobs ( OCIError      *errhp,
                      OCISvcCtx     *svchp,
                      OCIStmt       *stmthp,
                      OCIEnv        *envhp)
{
  OCILobLocator *tblob;
  OCILobLocator *bfile;
  ub4 amt = 4000;
  ub4 erase_size = 2;
  ub4 erase_offset = 100;
  sb4 return_code = 0;

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

  if(OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,
                        (ub4) OCI_DTYPE_FILE,
                        (size_t) 0, (dvoid **) 0))
  {
     printf("OCIDescriptor Alloc FAILED \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_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION))
  {
     (void) printf("FAILED: CreateTemporary() \n");
     return -1;
  } 
   
  if (OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE))
  {
    printf( "OCILobOpen FAILED for temp LOB \n");
    return_code =  -1;
  }
   
  /* Populate the temp LOB with 4000 bytes of data: */
  if(OCILobLoadFromFile(svchp,
                        errhp,
                        tblob,
                        (OCILobLocator*)bfile,
                        (ub4)amt,
                        (ub4)1,(ub4)1))
  {
    printf( "OCILobLoadFromFile FAILED\n");
    return_code = -1;

  }

  if (OCILobErase(svchp, errhp, (OCILobLocator *) tblob, &erase_size,
                  erase_offset))
  {
      printf( "OCILobErase FAILED for temp LOB \n");
      return_code = -1;
  } else 
  {
      printf( "OCILobErase succeeded for temp LOB \n");
  }

  if (OCILobClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobClose FAILED for bfile \n");
    return_code =  -1;
  }

  if (OCILobClose(svchp, errhp, (OCILobLocator *) tblob))
  {
    printf( "OCILobClose FAILED for temporary LOB \n");
    return_code =  -1;
  }
  /* free the temporary LOB now that we are done using it */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return_code = -1;
  }
  return return_code;
  }

COBOL (Pro*COBOL): Erasing Part of a Temporary LOB

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/cobol/Teresa

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

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP VALUE 10.
       01  POS            PIC S9(9) COMP VALUE 1.
       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.
       TEMP-BLOB-ERASE.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locator: 
           EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB 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-BLOB READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB
           END-EXEC.
           
      * Erase some of the LOB data: 
           EXEC SQL
                LOB ERASE :AMT FROM :TEMP-BLOB AT :POS
           END-EXEC.

      * Close the LOBs
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC.
      * Free the temporary LOB: 
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-BLOB 
           END-EXEC.

      * And free the LOB locators: 
           EXEC SQL FREE :TEMP-BLOB 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++): Erasing Part of a Temporary LOB

This script is also located at $ORACLE_HOME/rdbms/demo/lobs/proc/terase

#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 eraseTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount;
  int Position = 1024;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();

  /* Allocate and Create the Temporary LOB: */
  EXEC SQL ALLOCATE :Temp_loc;
  EXEC SQL LOB CREATE TEMPORARY :Temp_loc;

  /* 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_loc READ WRITE;

  /* Load a specified amount from the BFILE into the Temporary LOB: */
  Amount = 4096;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;

  /* Erase a specified amount from the Temporary LOB at a given position: */
  Amount = 2048;
  EXEC SQL LOB ERASE :Amount FROM :Temp_loc AT :Position;

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

  /* Free the Temporary LOB: */
  EXEC SQL LOB FREE TEMPORARY :Temp_loc;

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

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