| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Reading the Contents of a LONG with PLSQL
n the following code it is assumed the following appears in the init.ora:
UTL_FILE_DIR=/tmp
(ie the contents of the LONG can only be output into a file in /tmp. If
this parameter was not in the init.ora at all, add it and re-start the
instance
so that it takes effect).
Suppose we have a table ASCII_DOCS of the following structure :
Name Null? Type ------------------------------- -------- ---- ID NUMBER DOCUMENT LONG
ID is a primary key that we use to distinguish which document we want to
extract. The function below accepts 2 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 then 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
out to the file, and the process continues until no data has been returned
from
a call to column_value_long (ie when chunk_size_returned is zero). The
file is
then closed and a success flag is returned. If an unexpected exception
occurs,
the file is closed and a failure flag is returned.
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 will be 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.
CREATE OR REPLACE FUNCTION dump_doc(docid IN NUMBER,
filename IN VARCHAR2)
RETURN VARCHAR2
IS
data_chunk VARCHAR2(254);
chunk_size NUMBER:=254;
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;
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);
cur_pos:=cur_pos + chunk_size;
exit when chunk_size_returned = 0;
end loop;
dbms_sql.close_cursor(mycursor);
utl_file.fclose(file_handle);
return('Success');
EXCEPTION
WHEN OTHERS THEN
utl_file.fclose(file_handle);
return ('Failure');
"The Views expressed here are my own and not necessarily those of Oracle Corporation"
Venkatesh Kumar <vkumar_at_fundu.com> wrote in message
news:379A951D.ABDA4C11_at_fundu.com...
> Hi guys,
> We are trying to figure out a way to populate the Long columns in a
table
> using PL/Sql. We have a Web application with a Text area field. The data
in the
> Text area field is to be stored in a long column. However PL/Sl puts limit
on
> the size of a variable ( I think 2000K). I would like to know if there any
other
> solutions to this issue or if any any one has tried other methods using
Pl/Sql.
>
 |
 |