| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CLOB Store procedure
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,
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));
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_at_quovadx.com]
Sent: Thursday, July 01, 2004 3:50 PM
To: 'oracle-l_at_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_at_echostar.com]
Sent: Thursday, July 01, 2004 1:27 PM
To: 'oracle-l_at_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_at_quovadx.com]
Sent: Thursday, July 01, 2004 2:23 PM
To: 'oracle-l_at_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_at_echostar.com]
Sent: Thursday, July 01, 2004 1:10 PM
To: 'oracle-l_at_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_at_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;
/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |