Oracle added a "procedural" component, otherwise know as PL/SQL, to the Oracle Relational Database Management System a long time ago. With a programming language in the database engine, application developers can work with database objects in their native form with significant improvements in efficiency and scalability.

I can not claim to know everything there is to know about PL/SQL and significant changes happen from version to version that make it even more difficult to keep up. This web log is my attempt to document some of the things I have learned over the years and to report on "new" (at least to me) discoveries as I come across them.

Feedback and suggestions are always appreciated. E-mail me at JohnJeunnette at PrairieSystemsGroup dot com.

John.

 
7/31/2012
UTL_FILE and BLOB data
Keywords: BLOB , UTL_FILE
I have been moving documents out of the database with a Java procedure for quite awhile but discovered today that the Oracle 11 Express Edition does not include a JVM (Java Virtual Machine). So, after a bit of research I discovered that the UTL_FILE package can handle binary data. (Actually, the feature has been around since version 9i...). Some example code: DECLARE -- this_file utl_file.file_type; l_buffer raw(32767); l_amount binary_integer := 32767; l_pos number := 1; l_blob blob; l_blob_len number; out_path (set the path to a directory in the utl_file_dir initialization parameter) out_name varchar2(200) := null; -- BEGIN -- for document_record in ( select blob_content from ) loop out_name := (build a meaningful file name) -- l_blob_len := dbms_lob.getlength(document_record.blob_content); -- this_file := utl_file.fopen( out_path, out_name,'wb',32767); -- while l_pos < l_blob_len loop dbms_lob.read(document_record.blob_content, l_amount, l_pos, l_buffer); utl_file.put_raw(l_file, l_buffer, true); l_pos := l_pos + l_amount; end loop; -- utl_file.fclose(l_file); -- end loop; -- END;
 

Comments:

Add a Comment:
Author:
Text:
  So we think you are a human,
please enter the sum of 6 and 9:
 
 
 

Other Entries:
March 2011
   3/14/2011:   ODTUG Tool Survey
March 2010
   3/15/2010:   The PL/SQL Web Toolkit

 
Prairie Systems Group, Limited | Denver, Colorado | www.PrairieSystemsGroup.com
PLSQL_JOURNAL
Copyright 2009, Prairie Systems Group, Limited