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, 15 of 29
|
See:
"Use Case Model: Internal Temporary LOBs", for all basic operations of Internal Temporary LOBs. |
This procedure describes how to get the length of a temporary LOB.
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:
The following examples get the length of interview to see if it will run over the 4 gigabyte limit.
Examples are provided in the following programmatic environments:
/* Note that the example procedure getLengthTempCLOB_proc is not part of the DBMS_LOB package. */ CREATE OR REPLACE PROCEDURE getLengthTempCLOB_proc IS Length INTEGER; tlob CLOB; bufc VARCHAR2(8); Amount NUMBER; pos NUMBER; Src_loc BFILE := BFILENAME('AUDIO_DIR', 'Washington_audio'); BEGIN DBMS_LOB.CREATETEMPORARY(tlob,TRUE); /* Opening the LOB is optional: */ DBMS_LOB.OPEN(tlob,DBMS_LOB.LOB_READWRITE); /* Opening the file is mandatory: */ DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY); Amount := 32767; DBMS_LOB.LOADFROMFILE(tlob, Src_loc, Amount); /* Get the length of the LOB: */ length := DBMS_LOB.GETLENGTH(tlob); IF length = 0 THEN DBMS_OUTPUT.PUT_LINE('LOB is empty.'); ELSE DBMS_OUTPUT.PUT_LINE('The length is ' || length); END IF; /* Must close any lobs that were opened: */ DBMS_LOB.CLOSE(tlob); DBMS_LOB.CLOSE(Src_loc); /* Free the temporary LOB now that we are done with it: */ DBMS_LOB.FREETEMPORARY(tlob); END;
/* This function takes a temporary LOB locator as an amount as argument and prints out the length of the corresponding LOB. The function returns 0 if it completes successfully, and -1 if it fails.*/ sb4 print_length( OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp) { ub4 length=0; ub4 amount = 4; ub4 pos = 1; OCILobLocator *bfile; OCILobLocator *tblob; sb4 return_code = 0; printf("in print_length\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; } 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; } checkerr(errhp,(OCILobFileOpen(svchp, errhp, (OCILobLocator *) bfile, OCI_LOB_READONLY))); /* Create a temporary BLOB: */ if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT, OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION)) { (void) printf("FAILED: CreateTemporary() \n"); return_code = -1 ; } if(OCILobOpen(svchp, errhp, (OCILobLocator *) tblob, OCI_LOB_READWRITE)) { (void) printf("FAILED: Open Temporary \n"); return_code = -1; } if(OCILobLoadFromFile(svchp, errhp, tblob,(OCILobLocator*)bfile, (ub4)amount, (ub4)1,(ub4)1)) { (void) printf("FAILED: Open Temporary \n"); return_code = -1; } if (OCILobGetLength(svchp, errhp, tblob,&length)) { printf ("FAILED: OCILobGetLength in print_length\n"); return_code = -1; } /* Close the bfile and the temp LOB */ checkerr(errhp,OCILobFileClose(svchp, errhp, (OCILobLocator *) bfile)); checkerr(errhp,OCILobClose(svchp, errhp, (OCILobLocator *) tblob)); /* Free the temporary LOB now that we are done using it: */ if(OCILobFreeTemporary(svchp, errhp, tblob)) { printf("OCILobFreeTemporary FAILED \n"); return_code = -1; } fprintf(stderr,"Length of LOB is %d\n",length); return return_code; }
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/cobol/tlength
IDENTIFICATION DIVISION.PROGRAM-ID. TEMP-LOB-LENGTH. 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 LEN PIC S9(9) COMP. 01 LEN-D PIC 9(4). 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-LOB-LENGTH. 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. * Get the length of the temporary LOB: EXEC SQL LOB DESCRIBE :TEMP-BLOB GET LENGTH INTO :LEN END-EXEC. MOVE LEN TO LEN-D. DISPLAY "Length of TEMPORARY LOB is ", LEN-D. * 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.
This script is also located at $ORACLE_HOME/rdbms/demo/lobs/proc/tlength
#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 getLengthTempLOB_proc() { OCIBlobLocator *Temp_loc; OCIBFileLocator *Lob_loc; char *Dir = "AUDIO_DIR", *Name = "Washington_audio"; int Length, Amount; 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; /* Get the length of the Temporary LOB: */ EXEC SQL LOB DESCRIBE :Temp_loc GET LENGTH INTO :Length; /* Note that in this example, Length == Amount == 4096: */ printf("Length is %d bytes\n", Length); /* 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; getLengthTempLOB_proc(); EXEC SQL ROLLBACK WORK RELEASE; }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|