Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)

Part Number A88879-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

Temporary LOBs, 16 of 29


Copying All or Part of One (Temporary) LOB to Another

Figure 11-14 Use Case Diagram: Copying All or Part of One (Temporary) LOB to Another


Text description of adl11tm2.gif follows This link takes you back to the Internal Temporary LOBs main model diagram.
Text description of the illustration adl11tm2.gif

See:

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

Purpose

This procedure describes how to copy all or part of one temporary LOB to another.

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:

Scenario

Assume the following table:

CREATE TABLE VoiceoverLib_tab of VOICED_TYP;

VoiceoverLib_tab is the same type as the Voiceover_tab referenced by the Voiced_ref column of table Multimedia_tab.

INSERT INTO Voiceover_tab 
    (SELECT * FROM VoiceoverLib_tab Vtab1
       WHERE T2.Take = 101);

This creates a new LOB locator in table Voiceover_tab, and copies LOB data from Vtab1 to the location pointed to by a new LOB locator inserted into table Voiceover_tab.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Copying All or Part of One (Temporary) LOB to Another

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

CREATE OR REPLACE PROCEDURE copyTempLOB_proc IS
   Dest_pos     NUMBER;
   Src_pos      NUMBER;
   Dest_loc     BLOB;
   Dest_loc2    BLOB;
   Src_loc      BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount       INTEGER := 32767;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Dest_loc2,TRUE);
   DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE);
   /* Opening the FILE  is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Opening the temporary  LOBs is optional: */
   DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE);
   DBMS_LOB.OPEN(Dest_loc2,DBMS_LOB.LOB_READWRITE);

   DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, Amount);
      /* Set Dest_pos to the position at which we should start writing in the
          target temp LOB */
      /* Copies the LOB from the source position to the destination
          position:*/
      /* Set amount to the amount you want copied */
          Amount := 328;
          Dest_pos := 1000;
          Src_pos := 1000;
      /* Set Src_pos to the position from which we should start copying data
          from  tclob_src: */
          DBMS_LOB.COPY(Dest_loc2,Dest_loc, Amount, Dest_pos, Src_pos);
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
      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): Copying All or Part of One (Temporary) LOB to Another

/* This function copies 4000 bytes from one temporary LOB to another. It reads 
the source LOB starting at offset 1, and writes to the destination at offset 2. 
The function returns 0 if it completes successfully, and -1 otherwise. */
sb4 copy_temp_lobs (OCIError      *errhp,
                    OCISvcCtx     *svchp,
                    OCIStmt       *stmthp,
                    OCIEnv        *envhp)
{
  OCIDefine *defnp1;
  OCILobLocator *tblob;
  OCILobLocator *tblob2;
  OCILobLocator *bfile;
  int rowind =1;
  ub4 amount=4000;
  ub4 src_offset=1;
  ub4 dest_offset=2;
  sb4 return_code = 0;

  printf("in copy_temp_lobs \n");

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

  if(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&bfile,
                        (ub4)OCI_DTYPE_FILE, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in copy_temp_lobs\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(OCIDescriptorAlloc((dvoid*)envhp, (dvoid **)&tblob2,
                        (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid**)0))
  {
    printf("OCIDescriptorAlloc failed in copy_temp_lobs\n");
    return_code =  -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_code = -1;
  } 
 
  if(OCILobFileSetName(envhp, errhp, &bfile, (text *)"AUDIO_DIR",
                       (ub2)strlen("AUDIO_DIR"),
                       (text *)"Washington_audio",
                       (ub2)strlen("Washington_audio")))
  {
    printf("OCILobFileSetName FAILED\n");
    return_code = -1;
  }
   
  if(OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))
  {
    printf( "OCILobFileOpen FAILED  for the bfile\n");
    return_code = -1;
  }

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

 if (OCILobCopy(svchp, errhp, tblob2, tblob,  amount, dest_offset,
                src_offset))
  {
    printf ("FAILED: OCILobCopy in copy_temp_lobs\n");
    return -1;
  }
  /* Close LOBs here */

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

COBOL (Pro*COBOL): Copying All or Part of One (Temporary) LOB to Another

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

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TEMP-BLOB-COPY.
       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.

      * Define the source and destination position and location: 
       01  SRC-POS        PIC S9(9) COMP VALUE 1.
       01  DEST-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-COPY.
           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.
           
      * Copy data from BFILE to temporary LOB: 
           EXEC SQL 
                LOB COPY :AMT FROM :TEMP-SRC AT :SRC-POS
                TO :TEMP-DEST AT :DEST-POS
           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 All or Part of One (Temporary) LOB to Another

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

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

  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 one of the
     Temporary LOBs: */
  Amount = 4096;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc1;
  /* Copy a specified amount from one Temporary LOB to another: */
  EXEC SQL LOB COPY :Amount FROM :Temp_loc1 TO :Temp_loc2;
  /* Closing the LOBs is Mandatory if they have been Opened: */
  EXEC SQL LOB CLOSE :Temp_loc1;
  EXEC SQL LOB CLOSE :Temp_loc2;
  EXEC SQL LOB CLOSE :Lob_loc;
  /* 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 :Temp_loc1;
  EXEC SQL FREE :Temp_loc2;
  EXEC SQL FREE :Lob_loc;
}

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


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback