Recuperar Informacion de BLOB

Introduction ------------ Databases have long been used to store large objects. Oracle 8 significantly enhances this feature by introducing the Large Object(LOB) datatypes. LOBs can be broadly categorized as Internal LOBs and External LOBs. Internal LOBS can store binary data (BLOBs), single-byte character data (CLOBs), or multi-byte character data (NCLOBs). Internal LOBs are stored in the database tablespaces in a way that optimizes space and provides efficient access. Internal LOBs participate in the transaction model of the server. External LOBs (henceforth referred to as BFILES), are stored in operating system files outside the database tablespaces. These LOBs do not participate in transactions. Data stored in a LOB is called the LOB's value. To the Oracle8 Server, a LOB's value is unstructured and cannot be queried against. LOBs can be stored along with other row data or separate from row data. Regardless of how the data is stored, every LOB has a locator associated with it which can be viewed as a handle or pointer to the actual location. Selecting a LOB returns the LOB locator instead of the LOB value. Two new special functions in Oracle8 SQL DML, EMPTY_BLOB() and EMPTY_CLOB(), allow initialization of NULL or non-NULL LOB columns to empty. PL/SQL provides a mechanism to manipulate these LOBs via the DBMS_LOB package. The DBMS_LOB package provides functions and procedures which allow manipulation of specific parts as well as complete internal LOBs and read-only operations on BFILEs. All DBMS_LOB routines work based on LOB locators. This bulletin assumes that readers have some experience working with LOBs, i.e. creating tables with LOB columns, initializing LOBs either EMPTY_CLOB() or EMPTY_BLOB(), and using the constructors to initialize the LOBs if the tables are defined on object types. Cautionary Note --------------- The examples presented in this note make use of DBMS_OUTPUT and/or UTL_FILE packages. Both packages have line and buffer limitations which result in an exception being raised if the size of the data output exceeds those limitations. DDL for LOBs ------------ The LOB definition can involve CREATE TYPE and CREATE TABLE statements. For example, the following statement specifies a CLOB, BLOB, and BFILE within a datatype lob_type: CREATE TYPE lob_type AS object ( lob_id NUMBER(3), video_clip BLOB, document CLOB , some_file BFILE ); This DDL creates an object table of lob_type where each row is an instance of lob_type data: CREATE TABLE lob_store OF lob_type; The following statement stores LOBs in a relational table, as opposed to an object table as in the preceding statement: CREATE TABLE lob_store ( lob_id NUMBER(3), video_clip BLOB DEFAULT empty_blob(), document CLOB DEFAULT NULL, some_file BFILE DEFAULT NULL); DML for LOBs ------------ The PL/SQL block below populates the table LOB_STORE with 10 LOB IDs which defaults to initializing the video_clip to empty, and the document and some_file to null. DECLARE loop_count INTEGER; BEGIN loop_count := 1; WHILE loop_count <= 10 LOOP INSERT INTO lob_store (lob_id) VALUES (loop_count); loop_count := loop_count + 1; END LOOP; UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('0123456789'), document = 'abcdefgh' where lob_id=2; UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('7777777'), document = 'ijklmn' where lob_id=3; UPDATE lob_store SET video_clip=empty_blob(), document = empty_clob() where lob_id=4; END; / The examples listed in this bulletin are based on the relational table LOB_STORE described in the DDL section of the bulletin and on the data inserted in the table using the PL/SQL block above. Subprograms in the DBMS_LOB Package ----------------------------------- The more commonly used procedures and functions contained in the DBMS_LOB package can be broadly categorized as follows: a) The routines that can modify BLOB, CLOB, and NCLOB values are: APPEND() - append the contents of the source LOB to the destination LOB COPY() - copy all or part of the source LOB to the destination LOB ERASE() - erase all or part of a LOB LOADFROMFILE() - load BFILE data into an internal LOB TRIM() - trim the LOB value to the specified shorter length WRITE() - write data to the LOB from a specified offset b) The routines that read or examine LOB values are: COMPARE() - comapre two entire or part of two lobs GETLENGTH() - get the length of the LOB value INSTR() - return the matching position of the nth occurrence of the pattern in the LOB READ() - read data from the LOB starting at the specified offset SUBSTR() - return part of the LOB value starting at the specified offset c) The read-only routines specific to BFILEs are: FILECLOSE() - close the file FILECLOSEALL()- close all previously opened files FILEEXISTS() - check if the file exists on the server FILEGETNAME() - get the directory alias and file name FILEISOPEN() - check if the file was opened using the input BFILE locators FILEOPEN() - open a file The remainder of this bulletin explains each of the functions/procedures in the DBMS_LOB package and contains an example demonstrating how these subprograms can be used to achieve the desired result. Manipulating Internal LOBS (BLOB, CLOB, NCLOB) with DBMS_LOB Package -------------------------------------------------------------------- I) Finding the length of the variable/column which is declared of LOB datatype. Syntax FUNCTION GETLENGTH ( lob_loc IN BLOB) RETURN INTEGER; FUNCTION GETLENGTH ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; FUNCTION GETLENGTH ( lob_loc IN BFILE) RETURN INTEGER; Example: SQL> select dbms_lob.getlength(document) from lob_store where lob_id = 1; DBMS_LOB.GETLENGTH(DOCUMENT) ---------------------------- The length is not defined because the document was initialized to null by default. The document column needs to be initialized using the built in special function EMPTY_CLOB() to empty. SQL> update lob_store set document = empty_clob() where lob_id = 1; 1 row updated. SQL> select dbms_lob.getlength(document) from lob_store where lob_id = 1; DBMS_LOB.GETLENGTH(DOCUMENT) ---------------------------- 0 II) Populating an internal LOB. The DBMS_LOB.WRITE procedure can be used to insert values into a LOB column of a table. WRITE() overwrites any data that already exists in the LOB at the offset, for the length specified. Syntax PROCEDURE WRITE ( lob_loc IN OUT BLOB, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN RAW); PROCEDURE WRITE ( lob_loc IN OUT CLOB CHARACTER SET ANY_CS, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET); Example: This PL/SQL code demonstrates how a LOB column can be populated. DECLARE lobloc CLOB; buffer VARCHAR2(32000); amount NUMBER := 20; offset NUMBER := 1; BEGIN --Initialize buffer with data to be inserted buffer := 'abcdefghijklmnopqrstuvwxyz'; amount := length(buffer); dbms_output.put_line(buffer); dbms_output.put_line(to_char(amount)); SELECT document INTO lobloc -- get LOB handle FROM lob_store WHERE lob_id = 1 FOR UPDATE; dbms_lob.write(lobloc,amount,1,buffer); COMMIT; END; / SQL> SELECT dbms_lob.getlength(document) FROM lob_store WHERE lob_id = 1; DBMS_LOB.GETLENGTH(DOCUMENT) ---------------------------- 26 III) Reading from a LOB column can be achieved by using the DBMS_LOB.READ procedure. This procedure allows piecewise retrieval as well as retrieval of the entire LOB into a buffer. Syntax PROCEDURE READ ( lob_loc IN BLOB, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW); PROCEDURE READ ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET); PROCEDURE READ ( lob_loc IN BFILE, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW); Example: This example shows how a piece wise fetch can be accomplished using DBMS_LOB.READ procedure. DECLARE lobloc CLOB; buffer VARCHAR2(32000); amount NUMBER := 10; amount_in_buffer NUMBER; offset NUMBER := 4; BEGIN --Initialize buffer with data to be inserted SELECT document INTO lobloc -- get LOB handle FROM lob_store WHERE lob_id = 2; dbms_lob.read(lobloc,amount,offset,buffer); --using length built-in function to find the length of the buffer amount_in_buffer := length(buffer); dbms_output.put_line(buffer); dbms_output.put_line(to_char(amount_in_buffer)); COMMIT; END; / defgh 5 PL/SQL procedure successfully completed. IV) The overloaded APPEND() procedure appends the complete source LOB to the destination LOB. Syntax PROCEDURE APPEND (dest_lob IN OUT BLOB, src_lob IN BLOB); PROCEDURE APPEND (dest_lob IN OUT CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET); Example: CREATE OR REPLACE PROCEDURE tst_append IS dblob BLOB; sblob BLOB; dclob CLOB; sclob CLOB; spos NUMBER; amt BINARY_INTEGER; bufb RAW(20); bufc VARCHAR2(20); BEGIN dbms_output.put_line('--------------- APPEND Begin ---------------'); /* append the BLOBs in the lob_store table */ SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2 FOR UPDATE; SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 3; dbms_lob.append(dblob, sblob); /* append the CLOBs in the lob_store table */ SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE; SELECT document INTO sclob FROM lob_store WHERE lob_id = 3; dbms_lob.append(dclob, sclob); /* end Xn */ COMMIT; /* testing APPEND operation */ amt := 20; spos := 1; SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 2; dbms_lob.read(sblob, amt, spos, bufb); dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb)); SELECT document INTO sclob FROM lob_store WHERE lob_id = 2; dbms_lob.read(sclob, amt, spos, bufc); dbms_output.put_line('Result CLOB: ' || bufc); dbms_output.put_line('--------------- APPEND End ---------------'); dbms_output.put_line(' '); END tst_append; / SQL> execute tst_append; --------------- APPEND Begin --------------- Result BLOB: 01234567897777777 Result CLOB: abcdefghijklmn --------------- APPEND End --------------- PL/SQL procedure successfully completed. V) The ERASE() procedure allows one to erase a part or the entire LOB depending on the amount and offset parameters passed to the procedure. The actual number of bytes or characters erased can differ from the number specified in the amount parameter if the end of the LOB parameter has been reached. Erased characters are replaced with zero byte filters for BLOBs and spaces for CLOBs. CREATE OR REPLACE PROCEDURE tst_erase IS dblob BLOB; dclob CLOB; amt NUMBER; pos NUMBER; bufb RAW(10); bufc VARCHAR2(10); BEGIN dbms_output.put_line('--------------- ERASE Begin ---------------'); -- Case 1: erase 5 bytes from the middle of the LOB -- amt := 5; pos := 3; -- Case 2: trim 2 bytes from the end of the LOB amt := 2; pos := 6; -- Case 3: erase 5 bytes from the beginning of the LOB -- amt := 5; pos := 1; -- Case 4: erase from an empty LOB /* test ERASE for BLOBs */ SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2 FOR UPDATE; dbms_lob.erase(dblob, amt, pos); /* test ERASE for CLOBs */ SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE; dbms_lob.erase(dclob, amt, pos); /* end the transaction */ COMMIT; /* checking the ERASE operation */ amt := 10; pos := 1; SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2; dbms_lob.read(dblob, amt, pos, bufb); dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb)); SELECT document INTO dclob FROM lob_store WHERE lob_id = 2; dbms_lob.read(dclob, amt, pos, bufc); dbms_output.put_line('Result CLOB: ' || bufc); dbms_output.put_line('--------------- ERASE End ---------------'); dbms_output.put_line(' '); end tst_erase; / SQL> execute tst_erase --------------- ERASE Begin --------------- Result BLOB: 01234 Result CLOB: abcde h --------------- ERASE End --------------- When using DBMS_LOB.ERASE on BLOBs, the second parameter to the DBMS_LOB.ERASE procedure is not taken into consideration. It from the offset to the end of the data. This is a known bug (BUG:554028). VI) The COPY procedure allows one to copy part or all of a source internal LOB into a destination internal LOB. The offsets for both the source and destination LOBs can be specified. Syntax PROCEDURE COPY ( dest_lob IN OUT BLOB, src_lob IN BLOB, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); PROCEDURE COPY ( dest_lob IN OUT CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); Example: This example demonstrates how part of the LOBs in row with lob_id 2 can be copied into row with lob_id 4; CREATE OR REPLACE PROCEDURE tst_copy IS dblob BLOB; sblob BLOB; dclob CLOB; sclob CLOB; amt NUMBER; dpos NUMBER; spos NUMBER; bufb RAW(30); bufc VARCHAR2(30); BEGIN dbms_output.put_line('--------------- COPY Begin ---------------'); dbms_output.put_line(' '); -- Case 1: copy row 3 LOBs to row 4 LOBs amt := 5; dpos := 1; spos := 3; -- test COPY for BLOBs SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 4 FOR UPDATE; SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 2; dbms_lob.copy(dblob, sblob, amt, dpos, spos); COMMIT; -- test COPY for CLOBs SELECT document INTO dclob FROM lob_store WHERE lob_id = 4 FOR UPDATE; SELECT document INTO sclob FROM lob_store WHERE lob_id = 2; dbms_lob.copy(dclob, sclob, amt, dpos, spos); COMMIT; -- check of COPY operation SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4; dbms_lob.read(sblob, amt, dpos, bufb); dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb)); SELECT document INTO sclob FROM lob_store WHERE lob_id = 4; dbms_lob.read(sclob, amt, dpos, bufc); dbms_output.put_line('Result CLOB: ' || bufc); dbms_output.put_line('--------------- COPY End ---------------'); dbms_output.put_line(' '); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('COPY: no_data_found error'); END tst_copy; / SQL> exec tst_copy --------------- COPY Begin --------------- Result BLOB: 23456 Result CLOB: cdefg --------------- COPY End --------------- PL/SQL procedure successfully completed. VII) The COMPARE() function allows comparison between two entire LOBs or parts of the LOBs. COMPARE() returns a zero if the data exactly matches over the specified range. Otherwise, it returns a non-zero value. Syntax FUNCTION COMPARE ( lob_1 IN BLOB, lob_2 IN BLOB, amount IN INTEGER := 4294967295, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; FUNCTION COMPARE ( lob_1 IN CLOB CHARACTER SET ANY_CS, lob_2 IN CLOB CHARACTER SET lob_1%CHARSET, amount IN INTEGER := 4294967295, offset_1 IN INTEGER := 1, offset_2 IN INTEGER := 1) RETURN INTEGER; Example: If this procedure tst_compare is run immediately after the tst_copy procedure, the result is similar to that shown in the output of the execution below. CREATE OR REPLACE PROCEDURE tst_compare IS dblob BLOB; sblob BLOB; dclob CLOB; sclob CLOB; amt NUMBER; dpos NUMBER; spos NUMBER; ret INTEGER; BEGIN dbms_output.put_line('--------------- COMPARE Begin ---------------'); dpos := 3; spos := 1; amt := 5; /* test COMPARE for BLOBs */ SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2; SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4; ret := -1; ret := dbms_lob.compare(dblob, sblob, amt, dpos, spos); dbms_output.put_line('Return value for BLOB: ' || ret); /* testing the validity of COMPARE for CLOBs */ SELECT document INTO dclob FROM lob_store WHERE lob_id = 2; SELECT document INTO sclob FROM lob_store WHERE lob_id = 4; ret := -1; ret := dbms_lob.compare(dclob, sclob, amt, dpos, spos); dbms_output.put_line('Return value for CLOB: ' || ret); dbms_output.put_line('--------------- COMPARE End ---------------'); dbms_output.put_line(' '); END tst_compare; SQL> exec tst_compare; --------------- COMPARE Begin --------------- Return value for BLOB: 1 Return value for CLOB: 1 --------------- COMPARE End --------------- PL/SQL procedure successfully completed. VIII) The TRIM() procedure can be used to trim the value of an internal LOB to a length specified in the newlen parameter. Syntax FUNCTION TRIM ( lob_loc IN BLOB, newlen IN INTEGER); FUNCTION TRIM ( lob_loc IN CLOB, newlen IN INTEGER); Example: The following example shows how the TRIM() procedure can be used to truncate the value stored in a CLOB to 5. CREATE OR REPLACE PROCEDURE tst_trim IS dclob CLOB; nlen NUMBER; amt BINARY_INTEGER; bufc VARCHAR2(5); BEGIN dbms_output.put_line('--------------- TRIM Begin ---------------'); nlen := 5; /* test TRIM for CLOBs */ SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE; dbms_lob.trim(dclob, nlen); COMMIT; /* check of TRIM operation */ SELECT document INTO dclob FROM lob_store WHERE lob_id = 2; bufc := ''; dbms_lob.read(dclob, nlen, 1, bufc); dbms_output.put_line('Result CLOB: ' || bufc); dbms_output.put_line('--------------- TRIM End ---------------'); dbms_output.put_line(' '); END tst_trim; / SQL> exec tst_trim --------------- TRIM Begin --------------- Result CLOB: abcde --------------- TRIM End --------------- PL/SQL procedure successfully completed. Manipulating External LOBS (BFILES) with DBMS_LOB Package --------------------------------------------------------- A BFILE column or attribute for a particular row or user-defined type stores a BFILE locator which can be considered as an opaque pointer to a file on the server's file system. The locator contains the directory alias, the filename, and some state information. A new DIRECTORY database item enables administering access and usage of BFILES. A DIRECTORY item specifies an alias for a directory on the server's file system. The schema creating a directory object needs special privileges. The SQL statement below grants to user naveen the privilege to create directory objects. grant create any directory to naveen; Creating a directory object which maps an operating system path where the flat file is stored. The directory object name is used to map an operating system file directory to a BFILE Lob Locator. create or replace directory "LOBMANIP" as '/home/usupport/npasumar/bulletin/'; Loading a lob locator of a BFILE using the DBMS_LOB.LOADFROMFILE The following four steps are involved in loading a BFILE locator to map OS file. Note that ORACLE has only read-only access to BFILEs. 1) Check if the external file was opened by using the specified FILE locator using the DBMS_LOB.FILEISOPEN() function. Syntax FUNCTION FILEISOPEN ( file_loc IN BFILE) RETURN INTEGER; 2) Open the external file using the DBMS_LOB.FILEOPEN() procedure. Syntax PROCEDURE FILEOPEN ( file_loc IN OUT BFILE, open_mode IN BINARY_INTEGER := file_readonly); 3) Copy a part or whole of the external LOB into a Internal LOB using the DBMS_LOB.LOADFROMFILE. Syntax PROCEDURE loadfromfile ( dest_lob IN OUT BLOB, src_file IN BFILE, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); 4) Close the BFILE that was opened via the DBMS_LOB.FILEOPEN() procedure. Syntax PROCEDURE FILECLOSE ( file_loc IN OUT BFILE); Example: This program reads data from an operating system file and reads it into a BLOB. It assumes that the directory object LOBMANIP has been created and the directory to which it maps contains a file 'sample10.pc'. CREATE OR REPLACE PROCEDURE load_lob IS temp_blob BLOB; file_on_os BFILE := bfilename('LOBMANIP', 'append.sql'); ignore INTEGER; BEGIN ignore := dbms_lob.fileexists(file_on_os); IF ignore=1 THEN ignore := dbms_lob.fileisopen(file_on_os); IF (ignore=1) THEN null; ELSE dbms_lob.fileopen(file_on_os, dbms_lob.file_readonly); END IF; SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 5 FOR UPDATE; dbms_output.put_line('External file size is: ' || dbms_lob.getlength(file_on_os)); dbms_lob.loadfromfile(temp_blob,file_on_os, dbms_lob.getlength(file_on_os)); dbms_lob.fileclose(file_on_os); dbms_output.put_line('Internal BLOB size is: ' || dbms_lob.getlength(temp_blob)); ELSE dbms_output.put_line('File does not exist'); END IF; COMMIT; END; SQL> exec load_lob External file size is: 1230 Internal BLOB size is: 1230 PL/SQL procedure successfully completed. The next example demonstrates how the data stored in an internal LOB can be written to a flat file using a combination of the built-in packages DBMS_LOB and UTL_FILE. CREATE OR REPLACE PROCEDURE retrieve_lob IS temp_blob BLOB; data_buffer RAW (1); temp_buffer VARCHAR2(1); amount BINARY_INTEGER := 1; position INTEGER := 1; filehandle utl_file.file_type; error_number NUMBER; error_message VARCHAR2(100); length_count INTEGER; BEGIN SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 1; length_count := dbms_lob.getlength(temp_blob); dbms_output.put_line('Internal LOB size is: ' || length_count); filehandle := utl_file.fopen('/home/usupport/npasumar/bulletin', 'lob_flat.out','W'); WHILE length_count <> 0 LOOP dbms_lob.read (temp_blob, amount, position, data_buffer); temp_buffer := utl_raw.cast_to_varchar2(data_buffer); utl_file.put (filehandle, temp_buffer); position := position + 1; length_count := length_count - 1; data_buffer := null; END LOOP; dbms_output.put_line('Exit the loop'); utl_file.fclose(filehandle); dbms_output.put_line('Close the file'); EXCEPTION WHEN OTHERS THEN BEGIN error_number := sqlcode; error_message := substr(sqlerrm ,1 ,100); dbms_output.put_line('Error #: ' || error_number); dbms_output.put_line('Error Message: ' || error_message); utl_file.fclose_all; END; END; Note: The utl_file package is only suitable for handling TEXT data. SQL> exec retrieve_lob Internal LOB size is: 1230 Exit the loop Close the file PL/SQL procedure successfully completed. Comparison Between OCI Functions and DBMS_LOB PACKAGES ------------------------------------------------------ The LOBs can also be manipulated using OCI. Here is a comparison of the DBMS_LOB package procedures and the OCI functions. OCI (ociap.h) DBMS_LOB (dbmslob.sql) N/A DBMS_LOB.COMPARE() N/A DBMS_LOB.INSTR() N/A DBMS_LOB.SUBSTR() OCILobAppend DBMS_LOB.APPEND() OCILobAssign N/A [use Pl/SQL assign operator] OCILobCharSetForm N/A OCILobCharSetId N/A OCILobCopy DBMS_LOB.COPY() OCILobDisableBuffering N/A OCILobEnableBuffering N/A OCILobErase DBMS_LOB.ERASE() OCILobFileClose DBMS_LOB.FILECLOSE() OCILobFileCloseAll DBMS_LOB.FILECLOSEALL() OCILobFileExists DBMS_LOB.FILEEXISTS() OCILobFileGetName DBMS_LOB.FILEGETNAME() OCILobFileIsOpen DBMS_LOB.FILEISOPEN() OCILobFileOpen DBMS_LOB.FILEOPEN() OCILobFileSetName N/A (use BFILENAME operator) OCILobFlushBuffer N/A OCILobGetLength DBMS_LOB.GETLENGTH() OCILobIsEqual N/A [use Pl/SQL equal operator] OCILobLoadFromFile DBMS_LOB.LOADFROMFILE() OCILobLocatorIsInit N/A [always initialize] OCILobRead DBMS_LOB.READ() OCILobTrim DBMS_LOB.TRIM() OCILobWrite DBMS_LOB.WRITE() Frequently Asked Questions -------------------------- 1. What are the differences between LONG and LOB? LONG LOB ---- --- Single column per table Multiple columns per table Up to 2 Gigabytes Up to 4 Gigabytes SELECT returns data SELECT returns locator Data stored in-line Data stored in-line or out-of-line No object type support Support object types Sequential access of data Random access of data

Enviame un mail para que te mande la nota, dificilmente entiendas algo de lo que pegue anteriormente.
[email protected]

Muchas Gracias Por el articulo
Encontre la parte que necisitaba ycreel el procedimiento para crear el archivo, pero al ejecutar el procedimiento me manda los sigueinte errores
ORA-06510: PL/SQL: unhandled user-defined exception
ora-06512: at "SYS.UTL_FILE" line "99"
ora-06512: at "SYS.UTL_FILE" line "192"
ora-06512: at "PRUEBA.RECUPERAR_LOB" line "18"
ora-06512: at line "2"
el error ocurre con la siguiente instruccion
filehandle := utl_file.fopen('MY_FILES','lob_flat.out','W');
o con
filehandle := utl_file.fopen('C:\tmp','lob_flat.out','W');
MY_FILES es un directorio creado
crei que era que estaba dandole mal las rutas y nombre de archivo pero no creo,
El oerr me da lo siguiente
peprslunun06> oerr ora 06512
06512, 00000, "at %sline %s"
// *Cause: Backtrace message as the stack is unwound by unhandled
// exceptions.
// *Action: Fix the problem causing the exception or write an exception
// handler for this condition. Or you may need to contact your
// application administrator or DBA.
que crees tu que podria ser???
UTILIZO NT
P.D.
intente corres otros ejemplo que utilizan el utl_file.fopen y me dio el mismo error