Release 1 (9.0.1)
Part Number A88879-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
Temporary LOBs, 10 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to display temporary LOB data.
Not applicable.
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:
As an instance of displaying a LOB, our example stream-reads the image Drawing from the column object Map_obj onto the client-side in order to view the data.
Examples are provided in the following programmatic environments:
/* The following function acceses the Washington_audio file, creates a temporary LOB, loads some data from the file, and then reads it back and displays it. */ DECLARE Dest_loc BLOB; Src_loc BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio'); Amount INTEGER := 128; Bbuf RAW(128); Position INTEGER :=1; BEGIN DBMS_LOB.CREATETEMPORARY(Dest_loc,TRUE); /* Opening the FILE is mandatory: */ DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); /* Opening the LOB is optional: */ DBMS_LOB.OPEN(Dest_loc,DBMS_LOB.LOB_READWRITE); DBMS_LOB.LOADFROMFILE(Dest_loc,Src_loc,Amount); LOOP DBMS_LOB.READ (Dest_loc, Amount, Position, Bbuf); /* Display the buffer contents: */ DBMS_OUTPUT.PUT_LINE('Result :'|| utl_raw.cast_to_varchar2(Bbuf)); Position := Position + Amount; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('End of data loaded into temp LOB'); DBMS_LOB.CLOSE(Dest_loc); DBMS_LOB.FREETEMPORARY(Dest_loc); /* Closing the file is mandatory unless you close the files later: */ DBMS_LOB.CLOSE(Src_loc); END;
/* The following function acceses the Washington_audio file, creates a temporary LOB, loads some data from the file, and then reads it back and displays it. The reading is done in a streaming fashion. This function assumes that the file specified is kept in the directory known by the directory alias "AUDIO_DIR". It also assumes that the file is at least 14000 bytes long, which is the amount to be read and loaded. These amounts are arbitrary for this example. This function uses fprintf() to display the contents of the file. This works well for text data, but you may wish to change the method for binary data. For audio data, you could, for instance, call an audio function. The function returns 0 if it completes successfully, and -1 if it fails. */ #define MAXBUFLEN 32767 sb4 display_file_to_lob( OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp) { int rowind; char *binfile; OCILobLocator *tblob; OCILobLocator *bfile; ub4 amount = 14000; ub4 offset = 0; ub4 loblen = 0; ub4 amtp = 0; sword retval; ub4 piece = 1; ub4 remainder= 0; ub1 bufp[MAXBUFLEN]; sb4 return_code = 0; (void) printf("\n===> Testing loading files into lobs and displaying them\n\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 **) &bfile, (ub4) OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0)) { printf("OCIDescriptor Alloc FAILED in print_length\n"); return -1; } /* Create a temporary LOB: */ 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(OCILobFileSetName(envhp, errhp, &bfile, (text*)"AUDIO_DIR", (ub2)strlen("AUDIO_DIR"),(text*)"Washington_audio", (ub2)strlen("Wasthington_audio"))) { printf("OCILobFileSetName FAILED\n"); return_code = -1; } /* Open the BFILE: */ if(OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_FILE_READONLY)) { printf( "OCILobFileOpen FAILED \n"); return_code = -1; } if(OCILobLoadFromFile(svchp,errhp,tblob,(OCILobLocator*)bfile,(ub4)amount, (ub4)1,(ub4)1)) { printf( "OCILobLoadFromFile FAILED\n"); return_code = -1; } offset = 1; memset(bufp, '\0', MAXBUFLEN); retval = OCILobRead(svchp, errhp, tblob, &amtp, offset, (dvoid *) bufp, (amount < MAXBUFLEN ? amount : MAXBUFLEN), (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); printf("1st piece read from file is %s\n",bufp); switch (retval) { case OCI_SUCCESS: /* Only one piece */ (void) printf("stream read piece # %d \n", ++piece); (void)printf("piece read was %s\n",bufp); break; case OCI_ERROR: /* report_error(); function not shown here */ break; case OCI_NEED_DATA: /* There are 2 or more pieces */ remainder = amount; printf("remainder is %d \n",remainder); do { memset(bufp, '\0', MAXBUFLEN); amtp = 0; remainder -= MAXBUFLEN; printf("remainder is %d \n",remainder); retval = OCILobRead(svchp, errhp, tblob, &amtp, offset, (dvoid *) bufp, (ub4) MAXBUFLEN, (dvoid *)0, (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT); /* The amount read returned is undefined for FIRST, NEXT pieces: */ (void)fprintf(stderr,"stream read %d th piece, amtp = %d\n", ++piece, amtp); (void)fprintf(stderr,"piece of length read was %d\n", strlen((const char*)bufp)); (void)fprintf(stderr,"piece read was %s\n",bufp); } while (retval == OCI_NEED_DATA); break; default: (void) printf("Unexpected ERROR: OCILobRead() LOB.\n"); break; } /* Close the audio file: */ if (OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile)) { printf( "OCILobFileClose FAILED\n"); return_code = -1; } /* clean up the temp LOB now that we are done with it */ if(check_and_free_temp(tblob, errhp, svchp,stmthp, envhp)) { printf("check and free failed in load test\n"); return_code = -1; } return return_code; }
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/cobol/tdisplay
IDENTIFICATION DIVISION. PROGRAM-ID. ONE-READ-BLOB. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERID PIC X(9) 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 BUFFER2 PIC X(32767) VARYING. 01 AMT PIC S9(9) COMP. 01 OFFSET PIC S9(9) COMP VALUE 1. 01 ORASLNRD PIC 9(4). 01 ISTEMP PIC S9(9) COMP. EXEC SQL INCLUDE SQLCA END-EXEC. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL INCLUDE ORACA END-EXEC. EXEC SQL VAR BUFFER2 IS LONG RAW(32767) END-EXEC. PROCEDURE DIVISION. ONE-READ-BLOB. 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 :SRC-BFILE END-EXEC. EXEC SQL ALLOCATE :TEMP-BLOB END-EXEC. EXEC SQL LOB CREATE TEMPORARY :TEMP-BLOB END-EXEC. EXEC SQL WHENEVER NOT FOUND GOTO END-OF-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. EXEC SQL LOB DESCRIBE :SRC-BFILE GET LENGTH INTO :AMT END-EXEC. * Open source BFILE and destination temporary BLOB: EXEC SQL LOB OPEN :SRC-BFILE READ ONLY END-EXEC. EXEC SQL LOB OPEN :TEMP-BLOB READ WRITE END-EXEC. EXEC SQL LOB LOAD :AMT FROM FILE :SRC-BFILE INTO :TEMP-BLOB END-EXEC. * Perform a single read: EXEC SQL LOB READ :AMT FROM :TEMP-BLOB INTO :BUFFER2 END-EXEC. DISPLAY "Read ", BUFFER2, " from TEMP-BLOB". END-OF-BLOB. EXEC SQL LOB CLOSE :TEMP-BLOB END-EXEC. EXEC SQL LOB CLOSE :SRC-BFILE END-EXEC. EXEC SQL LOB FREE TEMPORARY :TEMP-BLOB END-EXEC. 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.
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/proc/tdisplay
#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 1024 void displayTempLOB_proc() { OCIBlobLocator *Temp_loc; OCIBFileLocator *Lob_loc; char *Dir = "PHOTO_DIR", *Name = "Lincoln_photo"; int Amount; struct { unsigned short Length; char Data[BufferLength]; } Buffer; int Position = 1; /* Datatype Equivalencing is Mandatory for this Datatype */ EXEC SQL VAR Buffer IS VARRAW(BufferLength); EXEC SQL WHENEVER SQLERROR DO Sample_Error(); /* Allocate and Initialize the LOB Locators */ EXEC SQL ALLOCATE :Lob_loc; EXEC SQL ALLOCATE :Temp_loc; EXEC SQL LOB CREATE TEMPORARY :Temp_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 */ EXEC SQL LOB DESCRIBE :Lob_loc GET LENGTH INTO :Amount; EXEC SQL LOB LOAD :Amount FROM FILE :Lob_loc AT :Position INTO :Temp_loc; /* Setting Amount = 0 will initiate the polling method */ Amount = 0; /* Set the maximum size of the Buffer */ Buffer.Length = BufferLength; EXEC SQL WHENEVER NOT FOUND DO break; while (TRUE) { /* Read a piece of the BLOB into the Buffer */ EXEC SQL LOB READ :Amount FROM :Temp_loc INTO :Buffer; printf("Display %d bytes\n", Buffer.Length); } printf("Display %d bytes\n", Amount); /* Closing the LOBs is mandatory if you have opened them */ 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 Locator */ EXEC SQL FREE :Temp_loc; } void main() { char *samp = "samp/samp"; EXEC SQL CONNECT :samp; displayTempLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE;}
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|