Temporary LOBs, 17 of 29

Copying a LOB Locator for a Temporary LOB

Figure 11-15 Use Case Diagram: Copying a LOB Locator for a Temporary LOB

Text description of adl11tm3.gif follows

Text description of the illustration adl11tm3.gif

See:

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

Purpose

This procedure describes how to copy a LOB locator for a temporary LOB.

Usage Notes

Not applicable.

Syntax

Use the following syntax references for each programmatic environment:

PL/SQL (DBMS_LOB): Oracle9i Supplied PL/SQL Packages Reference "DBMS_LOB" -- CREATETEMPORARY, LOADFROMFILE, FREETEMPORARY
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobLocatorIsInit
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide: Appendix F, "Embedded SQL and Precompiler Directives" -- LOB ASSIGN
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB ASSIGN
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): See Chapter 10, "Internal Persistent LOBs", "Java (JDBC): Copying a LOB Locator".

Scenario

This generic operation copies one temporary LOB locator to another.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Copying a LOB Locator for a Temporary LOB
C (OCI): Copying a LOB Locator for a Temporary LOB
COBOL (Pro*COBOL): Copying a LOB Locator for a Temporary LOB
C/C++ (Pro*C/C++): Copying a LOB Locator for 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): Copying a LOB Locator for a Temporary LOB

Note:

Assigning one LOB to another using PL/SQL entails using the "=" sign. This is discussed in more detail with regard to "Read Consistent Locators" in Chapter 5, "Large Objects: Advanced Topics".

/* Note that the example procedure copyTempLOBLocator_proc is not part of the 
   DBMS_LOB package. */

CREATE OR REPLACE PROCEDURE copyTempLOBLocator_proc(
   Lob_loc1 IN OUT CLOB, Lob_loc2 IN OUT CLOB) IS

   bufp     VARCHAR2(4);
   Amount   NUMBER  := 32767;
   Src_loc  BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
BEGIN
    DBMS_LOB.CREATETEMPORARY(Lob_loc1,TRUE);
    DBMS_LOB.CREATETEMPORARY(Lob_loc2,TRUE);
    /* Populate the first temporary LOB with some data. */
    /* Opening file is mandatory: */
    DBMS_LOB.OPEN(Src_loc,DBMS_LOB.LOB_READONLY);
    /* Opening LOB is optional: */
    DBMS_LOB.OPEN(Lob_loc1,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(Lob_loc2,DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.LOADFROMFILE(Lob_loc1,Src_loc,Amount);

   /* Assign Lob_loc1 to Lob_loc2 thereby creating  a copy of the value of
      the temporary LOB referenced by Lob_loc1 at this point in time: */
   Lob_loc2 := Lob_loc1;

   /* When you write some data to the LOB through Lob_loc1, Lob_loc2
      will not see the newly written data whereas Lob_loc1 will see
      the new data: */
   /*Closing LOBs is mandatory if they were opened: */
    DBMS_LOB.CLOSE (Src_loc);
    DBMS_LOB.CLOSE (Lob_loc1);
    DBMS_LOB.CLOSE (Lob_loc2);
    DBMS_LOB.FREETEMPORARY(Lob_loc1);
    DBMS_LOB.FREETEMPORARY(Lob_loc2);
END;

C (OCI): Copying a LOB Locator for a Temporary LOB

* This function creates two temporary lobs. It populates one and 
   then copies the locator of that one to the other temporary 
   LOB locator: */ 

sb4 copy_locators( OCIError    *errhp,
                   OCISvcCtx   *svchp, 
                   OCIEnv      *envhp)
{
  sb4 return_code = 0;
  OCILobLocator *tblob;
  OCILobLocator *tblob2;
  OCILobLocator *bfile;
  ub4 amount = 4000;

  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob,                                                                   
                                      (ub4) OCI_DTYPE_LOB, 
                                      (size_t) 0, (dvoid **) 0)); 

  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &tblob2,                                                             
                                      (ub4) OCI_DTYPE_LOB, 
                                      (size_t) 0, (dvoid **) 0)); 

  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &bfile,                                                 
                                      (ub4) OCI_DTYPE_FILE, 
                                      (size_t) 0, (dvoid **) 0)); 

  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED in load_temp\n");
    return -1;
  }

  if (OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_FILE_READONLY))
  {
    printf( "OCILobFileOpen FAILED for the bfile load_temp \n");
    return -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(OCILobCreateTemporary(svchp,errhp, tblob2,(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 -1;
  }

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

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

  if(OCILobLocatorAssign(svchp,errhp, (CONST OCILobLocator *)tblob,&tblob2))
  {

    printf("OCILobLocatorAssign failed \n");
    return_code = -1;
  }

  /* Close the lobs */
  if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile))
  {
    printf( "OCILobClose FAILED for bfile \n");
    return -1;
  }

  checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob)));
  checkerr(errhp,(OCILobClose(svchp, errhp, (OCILobLocator *) tblob2)));
  

  /* Free the temporary lobs now that we are done using it */
  if(OCILobFreeTemporary(svchp, errhp, tblob))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }

  if(OCILobFreeTemporary(svchp, errhp, tblob2))
  {
    printf("OCILobFreeTemporary FAILED \n");
    return -1;
  }
}

COBOL (Pro*COBOL): Copying a LOB Locator for a Temporary LOB

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

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

       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
        
       01  TEMP-DEST      SQL-BLOB.
       01  TEMP-SRC       SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) VARYING.
       01  AMT            PIC S9(9) COMP.

       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-COPY-LOCATOR.

           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-DEST END-EXEC.
           EXEC SQL ALLOCATE :TEMP-SRC END-EXEC.
           EXEC SQL ALLOCATE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-DEST
           END-EXEC. 
           EXEC SQL 
                LOB CREATE TEMPORARY :TEMP-SRC
           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-SRC READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :TEMP-DEST READ WRITE END-EXEC.
           EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC.

      * MOVE the desired amount to copy to AMT: 
           MOVE 5 TO AMT.
           EXEC SQL
                LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-SRC
           END-EXEC.
           
      * Assign source BLOB locator to destination BLOB locator: 
           EXEC SQL 
                LOB ASSIGN :TEMP-SRC TO :TEMP-DEST
           END-EXEC.

           EXEC SQL LOB CLOSE :TEMP-SRC END-EXEC.
           EXEC SQL LOB CLOSE :TEMP-DEST END-EXEC.
           EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-SRC
           END-EXEC.
           EXEC SQL 
                LOB FREE TEMPORARY :TEMP-DEST
           END-EXEC.
           EXEC SQL FREE :TEMP-SRC END-EXEC.
           EXEC SQL FREE :TEMP-DEST 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++): Copying a LOB Locator for a Temporary LOB

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

#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 copyTempLobLocator_proc()
{
  OCIBlobLocator *Temp_loc1, *Temp_loc2;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount = 4096;

  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 Temporary LOB: */
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc1;
  /* Assign Temp_loc1 to Temp_loc2 thereby creating a copy of the value of
     the Temporary LOB referenced by Temp_loc1 at this point in time: */
  EXEC SQL LOB ASSIGN :Temp_loc1 TO :Temp_loc2;

  /* 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;
  copyTempLobLocator_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}