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, 23 of 29


Write-Appending to a Temporary LOB

Figure 11-21 Use Case Diagram: Write-Appending to a Temporary LOB


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

See:

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

Purpose

This procedure describes how to write append to 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:

Scenario

These examples read in 32767 bytes of data from the Washington_audio file and append it to a temporary LOB.

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Writing-Appending to a Temporary LOB

/* Note that the example procedure writeAppendTempLOB_proc is not part of the 
   DBMS_LOB package. This example procedure will read in 32767 bytes of  data
   from the Washington_audio file and append it to a temporary LOB. */

CREATE OR REPLACE PROCEDURE writeAppendTempLOB_proc IS
   Lob_loc    BLOB;
   Buffer     RAW(32767);
   Src_loc    BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio');
   Amount     Binary_integer := 32767;
   Position   Binary_integer := 128;
BEGIN
   DBMS_LOB.CREATETEMPORARY(Lob_loc,TRUE);
   /* Opening the temporary LOB is optional: */
   DBMS_LOB.OPEN(Lob_loc,DBMS_LOB.LOB_READWRITE);
   /* Opening the FILE is mandatory: */
   DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
   /* Fill the buffer with data: */
   DBMS_LOB.LOADFROMFILE (Lob_loc,Src_loc, Amount);

   /* Append the data from the buffer to the end of the LOB: */
   DBMS_LOB.WRITEAPPEND(Lob_loc, Amount, Buffer);
   DBMS_LOB.CLOSE(Src_loc);
   DBMS_LOB.CLOSE(Lob_loc);
   DBMS_LOB.FREETEMPORARY(Lob_loc);
END;

C (OCI): Writing-Appending to a Temporary LOB

#define MAXBUFLEN 32767 
sb4 write_append_temp_lobs (OCIError      *errhp,
                            OCISvcCtx     *svchp,
                            OCIStmt       *stmthp,
                            OCIEnv        *envhp)
{
  OCIClobLocator *tclob;
  unsigned int Total = 40000;
  unsigned int amtp;
  unsigned int  nbytes;
  ub1 bufp[MAXBUFLEN];

  /* Allocate the locators desriptors: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &tclob ,
                            (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

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

  /* Open the CLOB */
  printf("calling open \n");
  checkerr (errhp, (OCILobOpen(svchp, errhp, tclob, OCI_LOB_READWRITE)));

  nbytes = MAXBUFLEN;   /* We will use Streaming via Standard Polling */
  
  /* Fill the Buffer with nbytes worth of Data */
  memset(bufp,'a',32767);
  
  amtp = sizeof(bufp);
  /* Setting Amount to 0 streams the data until use specifies OCI_LAST_PIECE */
  
  printf("calling write append \n");
  checkerr (errhp, OCILobWriteAppend (svchp, errhp, tclob, &amtp, 
                                      bufp, nbytes, OCI_ONE_PIECE, (dvoid *)0, 
                                      (sb4 (*)(dvoid*,dvoid*,ub4*,ub1 *))0,
                                       0, SQLCS_IMPLICIT));
  
  printf("calling close \n");
  /* Closing the LOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, tclob));

  /* Free the temporary LOB: */
  printf("calling free\n");
  checkerr(errhp,OCILobFreeTemporary(svchp,errhp,tclob));

  /* Free resources held by the locators: */
  (void) OCIDescriptorFree((dvoid *) tclob, (ub4) OCI_DTYPE_LOB);
 }

COBOL (Pro*COBOL): Write-Appending to a Temporary LOB

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

       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-APPEND-TEMP.
       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  BUFFER         PIC X(2048).
       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.
           EXEC SQL VAR BUFFER IS RAW(2048) END-EXEC.
       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.
       WRITE-APPEND-TEMP.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BFILE and BLOB locators: 
           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.
           
        MOVE "262626" TO BUFFER.
        MOVE 3 TO AMT. 
      * Append the data in BUFFER to TEMP-BLOB: 
           EXEC SQL 
                LOB WRITE APPEND :AMT FROM :BUFFER INTO :TEMP-BLOB      
           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++): Write-Appending to a Temporary LOB

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

#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);
}

#define BufferLength 256

void writeAppendTempLOB_proc()
{
  OCIBlobLocator *Temp_loc;
  OCIBFileLocator *Lob_loc;
  char *Dir = "AUDIO_DIR", *Name = "Washington_audio";
  int Amount;
  struct {
    unsigned short Length;
    char Data[BufferLength];
  } Buffer;
  EXEC SQL VAR Buffer IS VARRAW(BufferLength);
  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 = 2048;
  EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc INTO :Temp_loc;
  strcpy((char *)Buffer.Data, "afafafafafaf");
  Buffer.Length = 6;

  /* Write the contents of the Buffer to the end of the Temporary LOB: */
  Amount = Buffer.Length;
  EXEC SQL LOB WRITE APPEND :Amount FROM :Buffer INTO :Temp_loc;

  /* 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;
  writeAppendTempLOB_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