Index: openacs-4/packages/acs-content-repository/sql/oracle/content-util.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/oracle/content-util.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/acs-content-repository/sql/oracle/content-util.sql	24 Mar 2001 22:00:48 -0000	1.1
+++ openacs-4/packages/acs-content-repository/sql/oracle/content-util.sql	17 Sep 2007 12:58:30 -0000	1.2
@@ -60,69 +60,141 @@
 /
 show errors
 
-create or replace procedure clob_to_blob(
-  clob_loc clob, blob_loc blob) 
-as language 
-  java 
-name 
-  'com.arsdigita.content.Util.clobToBlob(
-    oracle.sql.CLOB, oracle.sql.BLOB
-   )';
-/
-show errors
 
-create or replace procedure blob_to_clob(
-  blob_loc blob, clob_loc clob) 
+create or replace procedure string_to_blob_size(
+  s varchar2, blob_loc blob, blob_size number) 
 as language 
   java 
 name 
-  'com.arsdigita.content.Util.blobToClob(
-    oracle.sql.BLOB, oracle.sql.CLOB
+  'com.arsdigita.content.Util.stringToBlob(
+    java.lang.String, oracle.sql.BLOB, int
    )';
 /
 show errors
 
+create or replace function blob_to_string (blob_loc IN BLOB)
+RETURN VARCHAR2
+IS
+v_blength number;
+v_ret varchar(32767);
+v_amount binary_integer:=10000;
+v_offset integer:=1;
+v_buffer raw(20000);
+BEGIN
+  v_blength:=dbms_lob.getlength(blob_loc);
+  IF v_blength=0 THEN
+   return v_ret;
+  ELSE
+    if v_blength<v_amount then
+      v_amount:=v_blength;
+    end if;
 
-create or replace procedure string_to_blob(
-  s varchar2, blob_loc blob) 
-as language 
-  java 
-name 
-  'com.arsdigita.content.Util.stringToBlob(
-    java.lang.String, oracle.sql.BLOB
-   )';
+    FOR i IN 1..CEIL(v_blength/v_amount) LOOP
+      dbms_lob.read(blob_loc,v_amount,v_offset,v_buffer);
+      v_ret:=v_ret||utl_raw.cast_to_varchar2(v_buffer);
+      v_offset:=v_offset+v_amount;
+    END LOOP;
+
+    return v_ret;
+  END IF;
+END;
 /
-show errors
 
-create or replace procedure string_to_blob_size(
-  s varchar2, blob_loc blob, blob_size number) 
-as language 
-  java 
-name 
-  'com.arsdigita.content.Util.stringToBlob(
-    java.lang.String, oracle.sql.BLOB, int
-   )';
+create or replace procedure string_to_blob (STRING IN VARCHAR2, BLOB_LOC IN OUT BLOB)
+IS
+v_slength number;
+v_ret blob;
+v_amount binary_integer:=10000;
+v_offset integer:=1;
+v_buffer raw(20000);
+BEGIN
+  v_slength:=length(string);
+  IF v_slength = 0 THEN
+    blob_loc:=v_ret;
+  ELSE
+    v_buffer:=utl_raw.cast_to_raw(string);
+    v_slength:=utl_raw.length(v_buffer);
+    
+    IF v_slength<v_amount THEN
+      v_amount:=v_slength;
+    END IF;
+
+    FOR i IN 1..CEIL(v_slength/v_amount) LOOP
+      dbms_lob.write(v_ret,v_amount,v_offset,v_buffer);
+      v_offset:=v_offset+v_amount;
+    END LOOP;
+    blob_loc:=v_ret;
+  END IF;
+END;
 /
-show errors
 
-create or replace function blob_to_string(
-  blob_loc blob) return varchar2
-as language 
-  java 
-name 
-  'com.arsdigita.content.Util.blobToString(
-    oracle.sql.BLOB
-   ) return java.lang.String';
+create or replace procedure clob_to_blob (CLOB_LOC IN CLOB, BLOB_LOC IN OUT BLOB)
+IS
+v_clength number;
+v_ret BLOB;
+v_amount binary_integer:=10000;
+v_offset integer:=1;
+v_lang_context number:=DBMS_LOB.DEFAULT_LANG_CTX;
+v_warning NUMBER;
+BEGIN
+  v_clength:=dbms_lob.getlength(CLOB_LOC);
+  if v_clength<v_amount then
+    v_amount:=v_clength;
+  end if;
+  FOR i IN 1..CEIL(v_clength/v_amount) LOOP
+    dbms_lob.converttoblob(v_ret,clob_loc,v_amount,v_offset,v_offset,0,v_lang_context,v_warning);
+    v_offset:=v_offset+v_amount;  
+  END LOOP;
+  blob_loc:=v_ret;
+END;
 /
-show errors
 
-create or replace procedure blob_to_file(
-s varchar2, blob_loc blob)
-as language
-  java
-name
-  'com.arsdigita.content.Util.blobToFile(
-  java.lang.String, oracle.sql.BLOB
-  )';
+create or replace procedure blob_to_clob (BLOB_LOC IN BLOB, CLOB_LOC IN OUT CLOB)
+IS
+v_blength number;
+v_ret CLOB;
+v_amount binary_integer:=10000;
+v_offset integer:=1;
+v_lang_context number:=DBMS_LOB.DEFAULT_LANG_CTX;
+v_warning NUMBER;
+BEGIN
+  if v_blength<v_amount then
+    v_amount:=v_blength;
+  end if;
+  FOR i IN 1..CEIL(v_blength/v_amount) LOOP
+    dbms_lob.converttoclob(v_ret,blob_loc,v_amount,v_offset,v_offset,0,v_lang_context,v_warning);
+    v_offset:=v_offset+v_amount;  
+  END LOOP;
+  clob_loc:=v_ret;
+END;
 /
-show errors
+
+
+
+create or replace procedure blob_to_file (path IN VARCHAR2, BLOB_LOC BLOB)
+IS
+v_dir varchar2(128);
+v_file varchar2(32);
+v_blength number;
+outfile utl_file.file_type;
+v_amount binary_integer:=10000;
+v_offset integer:=1;
+v_buffer raw(20000);
+BEGIN
+  v_dir:=substr(path,1,instr(path,'/',-1)-1);
+  v_file:=substr(path,instr(path,'/',-1)+1);
+  v_blength := dbms_lob.getlength(blob_loc);
+  outfile := utl_file.fopen(v_dir, v_file, 'WB');
+  if v_blength<v_amount then
+    v_amount:=v_blength;
+  end if;
+
+  FOR i IN 1..CEIL(v_blength/v_amount) LOOP
+    dbms_lob.read(blob_loc,v_amount,v_offset,v_buffer);
+    utl_file.put_raw(outfile,v_buffer);
+    v_offset:=v_offset+v_amount;
+  END LOOP;
+
+  utl_file.fclose(outfile);
+END;
+/
\ No newline at end of file