RE:Longitud de un campo tipo LONG
Creo que te caería bien una leída de este documento:
http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96624/03_types.htm#10751
Además lee el código que a continuación pegué, es tomado de Metalink.oracle.com
Salu2.
Subject: Reading Contents of LONG with PL/SQL & Writing to File
Type: FAQ
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 10-MAR-1998
Last Revision Date: 30-AUG-2000
Reading the Contents of a Long
------------------------------
with PL/SQL and Writing it to a File
------------------------------------
Background
----------
The Oracle7 LONG datatype is capable of storing up to 2GB of character data
in a row. However the largest PL/SQL variable that can be declared is 32K
- either VARCHAR2, CHAR, or LONG. In the past, this has imposed a limit on
the amount of data that can be fetched.
With version 2.2 of PL/SQL however, (Oracle 7.2) it is possible to retrieve
the entire contents of a LONG column in 'chunks' at a time - similar to the
OCI call 'oflng' where an offset is used to indicate where to start the
fetch from.
Overview
--------
This article demonstrates how this can be done by fetching the entire
contents of a LONG (using the DBMS_SQL package) and writing it to a file
using the UTL_FILE package that comes with version 2.3 of PL/SQL (Oracle 7.3).
More information on these packages can be found in the "Oracle 7 Server
Application Developer's Guide", (A32536-1). Version 7.2 covers DBMS_SQL,
while 7.3 covers both DBMS_SQL and UTL_FILE.
Example Code Information
------------------------
In the example code (below), the following must exist in the init.ora:
UTL_FILE_DIR=/tmp
Note: The contents of the LONG can only be output into a file in /tmp.
If this parameter is not contained in the init.ora, add it and restart the
instance for it to take effect.
Suppose you have a table ASCII_DOCS of the following structure:
Name Null? Type
------------------------------- -------- ----
ID NUMBER
DOCUMENT LONG
ID is a primary key that is used to distinguish which document you want to
extract. The procedure below accepts two parameters - the primary key of the
document to write, and the filename to save it as.
It then constructs the SQL statement, parses and binds in the host variable,
and executes the statement. It issues a call to fetch_row once to fetch
just that row (this can be put within a loop if there are multiple
documents/rows to write).
Then column_value_long is repeatedly called with a different offset (cur_pos)
which indicates where to start the fetch of the long from. This is then
written to the file, and the process continues until no data has been returned
from a call to column_value_long (i.e. when chunk_size_returned is zero).
The file is then closed. If an unexpected exception occurs, the file is closed
and the exception is raised.
A chunk size of 254 has been selected for this example so that it may be
easily adapted to use DBMS_OUTPUT to display the contents of the LONG in
SQL*Plus. However, 2000 or even 32K can be used and is quicker.
Also notice that PUT and not PUT_LINE has been used when writing out each
chunk. This is so that no extra carriage returns are added to the output -
any that were in the original document are preserved.
Example Code
------------
CREATE OR REPLACE PROCEDURE dump_doc(docid IN NUMBER,
filename IN VARCHAR2)
IS
data_chunk VARCHAR2(254); -- these can go higher
chunk_size NUMBER:=254; -- up to 32k
chunk_size_returned NUMBER;
location VARCHAR2(20) := '/tmp';
mycursor NUMBER;
stmt VARCHAR2(1024);
cur_pos NUMBER:=0;
rows NUMBER;
dummy NUMBER;
file_handle UTL_FILE.FILE_TYPE;
add status VARCHAR2(50);
BEGIN
file_handle:=utl_file.fopen(location,filename,'w');
-- open the file for writing
stmt:='SELECT DOCUMENT FROM ASCII_DOCS WHERE ID = :doctoget';
mycursor:=dbms_sql.open_cursor;
dbms_sql.parse(mycursor, stmt, dbms_sql.v7);
dbms_sql.bind_variable(mycursor, ':doctoget', docid);
-- bind the doctoget host variable with the plsql parameter docid
-- which is passed into the function
dbms_sql.define_column_long(mycursor,1);
dummy:=dbms_sql.execute(mycursor);
rows:=dbms_sql.fetch_rows(mycursor);
-- only doing one fetch for the primary key as assuming the whole
-- document is stored in one row
loop
-- fetch 'chunks' of the long until we have got the lot
dbms_sql.column_value_long(mycursor,
1,
chunk_size,
cur_pos,
data_chunk,
chunk_size_returned);
utl_file.put(file_handle, data_chunk); -- use PUT to preserve data
cur_pos:=cur_pos + chunk_size;
exit when chunk_size_returned = 0;
end loop;
dbms_sql.close_cursor(mycursor);
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
utl_file.fclose(file_handle);
END dump_doc;
.
