From oracle-l-bounce@freelists.org Thu Jul 1 17:17:14 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i61MGiY09415 for ; Thu, 1 Jul 2004 17:16:54 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i61MGX609389 for ; Thu, 1 Jul 2004 17:16:44 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D91E272C7AE; Thu, 1 Jul 2004 16:58:49 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31050-45; Thu, 1 Jul 2004 16:58:49 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3666572C79B; Thu, 1 Jul 2004 16:58:49 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 01 Jul 2004 16:57:28 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EB7AD72C779 for ; Thu, 1 Jul 2004 16:57:27 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30721-34 for ; Thu, 1 Jul 2004 16:57:27 -0500 (EST) Received: from mailout1.echostar.com (mailout1.echostar.com [204.76.128.101]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8C67472C758 for ; Thu, 1 Jul 2004 16:57:27 -0500 (EST) Received: by riv-exchcon.echostar.com with Internet Mail Service (5.5.2653.19) id <3B52MLN2>; Thu, 1 Jul 2004 16:20:19 -0600 Message-ID: From: "Looney, Jason" To: "'oracle-l@freelists.org'" Subject: RE: CLOB Store procedure Date: Thu, 1 Jul 2004 16:20:15 -0600 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 4227 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jason.Looney@echostar.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hamid, My code to create the lob looks like this: IF fms_event_xml_count > 0 THEN v_log_location := 'CREATE LOB'; DBMS_LOB.createtemporary (v_xml_clob, TRUE); DBMS_LOB.OPEN (v_xml_clob, DBMS_LOB.lob_readwrite); FOR v_loop_index IN 1 .. fms_event_xml_count LOOP v_log_location := 'CREATE LOB:LOOP INDEX:' || v_loop_index; DBMS_OUTPUT.put_line (v_log_location); IF LENGTH (fms_event_xml_text (v_loop_index)) > 0 THEN DBMS_OUTPUT.put_line ('WRITEAPPEND'); DBMS_LOB.writeappend (v_xml_clob, LENGTH((fms_event_xml_text(v_loop_index)), fms_event_xml_text (v_loop_index)); END IF; END LOOP; END IF; Later when I go to store it I do this: IF pi_xml_message_text IS NOT NULL THEN g_log_location := 'Writing LOB XML_MESSAGE_TEXT to Database'; DBMS_APPLICATION_INFO.set_action (g_log_location); DBMS_LOB.OPEN (v_xml_message_text, DBMS_LOB.lob_readwrite); DBMS_LOB.COPY (v_xml_message_text, pi_xml_message_text, DBMS_LOB.getlength (pi_xml_message_text)); DBMS_LOB.CLOSE (v_xml_message_text); END IF; It might be that you never close the lob. The other issue may be that you issue a commit before you write the lob but after you have the handle. Try changing both. Also try creating the clob and then use COPY as in the example above, or you could try using WRITEAPPEND. The site that helped me most get through this is ASKTOM.ORACLE.COM. Take a look there as well. Hope that helps. Jason. -----Original Message----- From: Hamid Alavi [mailto:hamid.alavi@quovadx.com] Sent: Thursday, July 01, 2004 3:50 PM To: 'oracle-l@freelists.org' Subject: RE: CLOB Store procedure Jason, I have change it as follows, but when I run it doesn't insert the CLOB value into the table: Any Idea??? CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN VARCHAR2 , batch_file_id IN FILE_BLOB_TEMP.BATCH_FILE_ID%TYPE DEFAULT NULL) AS lob_loc CLOB; v_curr_val INTEGER; buffer VARCHAR2(32000); amount BINARY_INTEGER :=32000; position INTEGER:=1; I INTEGER; BEGIN INSERT INTO FILE_BLOB_TEMP(batch_file_id,file_content) VALUES(seq_batch_file_id.NEXTVAL,EMPTY_CLOB() ) RETURNING file_content INTO lob_loc; COMMIT; FOR I IN 1 .. 3 LOOP DBMS_LOB.WRITE (lob_loc,amount,position,buffer); this line doesn't work properly!! position := position + amount; COMMIT; END LOOP; END; / -----Original Message----- From: Looney, Jason [mailto:Jason.Looney@echostar.com] Sent: Thursday, July 01, 2004 1:27 PM To: 'oracle-l@freelists.org' Subject: RE: CLOB Store procedure Something like this eliminates the overhead of the additional queries. INSERT INTO table_1 (col1, col2, clob_col) VALUES (col1_seq.nextval, p_col2, EMPTY_CLOB ()) RETURNING col1, clob_col INTO v_col1, v_clob_col; Jason. -----Original Message----- From: Hamid Alavi [mailto:hamid.alavi@quovadx.com] Sent: Thursday, July 01, 2004 2:23 PM To: 'oracle-l@freelists.org' Subject: RE: CLOB Store procedure RETURNING ? what do you mean is it insert the clob for you? -----Original Message----- From: Looney, Jason [mailto:Jason.Looney@echostar.com] Sent: Thursday, July 01, 2004 1:10 PM To: 'oracle-l@freelists.org' Subject: RE: CLOB Store procedure You could use the RETURNING clause so you don't have to get CURRVAL or your CLOB handle. Jason. -----Original Message----- From: Hamid Alavi [mailto:hamid.alavi@quovadx.com] Sent: Thursday, July 01, 2004 1:22 PM To: 'Oracle-L (E-mail) Subject: CLOB Store procedure List, I have written this store procedure to store a CLOB Object into a table, Just want you guys look at it & if any thing wrong respond to it, appreciate yur help & time. Thanks, CREATE OR REPLACE PROCEDURE S_Blob(FILE_BLOB IN CLOB, batch_file_id IN FILE_BLOB_TEMP.BATCH_FILE_ID%TYPE DEFAULT NULL) AS lob_loc CLOB; v_curr_val INTEGER; buffer VARCHAR2(32000); amount BINARY_INTEGER :=32000; position INTEGER:=1; I INTEGER; BEGIN INSERT INTO FILE_BLOB_TEMP(batch_file_id,file_content) VALUES(seq_batch_file_id.NEXTVAL,EMPTY_CLOB()); COMMIT; SELECT seq_batch_file_id.CURRVAL INTO v_curr_val FROM dual; SELECT file_content INTO lob_loc FROM FILE_BLOB_TEMP WHERE batch_file_id = v_curr_val FOR UPDATE; FOR I IN 1 .. 3 LOOP DBMS_LOB.WRITE (lob_loc,amount,position,buffer); position := position + amount; COMMIT; END LOOP; END; / ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------