Weblabor

create table cikk
   (azon		NUMBER primary key,
    cim		VARCHAR2(100),
    szerzo		VARCHAR2(100),
    szoveg		CLOB,
    datum		DATE, 
    cikk_xml	XMLTYPE);


CREATE OR REPLACE Procedure generate_xml(p_azon number)
 IS
	l_cikk_xml	CLOB;
BEGIN
SELECT ( XMLElement ( "cikk" , XMLAttributes( azon as "azon")
		 	, XMLElement( "cim", cim)
		 	, XMLElement( "szerzo", szerzo)
 	, XMLElement( "datum", to_char(datum, 'YYYY.MM.DD'))
		 	, XMLElement( "szoveg", szoveg))
  ).getClobVal()
	  INTO l_cikk_xml
	  FROM cikkek
	 WHERE azon = p_azon;

	UPDATE cikkek set cikk_xml = XMLType(l_cikk_xml)
	 WHERE azon = p_azon;

	commit;
END;

CREATE OR REPLACE DIRECTORY ARTICLE_DIR AS 'e:\';
declare
	f bfile;
    l_textclob		clob;
    ln_warning		number;

    ln_destoffs		number :=1;
    ln_srcoffs		number :=1;
	ln_langctxt		number :=0;
	ls_filename		varchar2(240) := 'e:\x.xml';

    amt			number;
begin
	f:= BFILENAME('ARTICLE_DIR', 'x.xml');
	dbms_output.put_line('Filename: ' || ls_filename);
   	dbms_lob.fileopen(f, dbms_lob.file_readonly);
	amt := dbms_lob.getlength(f);

	IF amt != 0 then

			dbms_output.put_line('Loading ' || amt || ' bytes...');

			dbms_lob.createtemporary(l_textclob, TRUE);

			DBMS_LOB.LOADCLOBFROMFILE (
					dest_lob 		=> l_textclob,
					src_bfile		=> f,
					amount 			=> amt,
					dest_offset 	=> ln_destoffs,
					src_offset  	=> ln_srcoffs,
					bfile_csid		=> 0,
					lang_context 	=> ln_langctxt,
					warning 		=> ln_warning);

		dbms_output.put_line(dbms_lob.getlength(l_textclob) || ' bytes loaded.');

		insert into cikk (azon, cikk_xml)
		values ('6', XMLType(l_textclob));
	END IF;
	dbms_lob.freetemporary(l_textclob);
  	dbms_lob.fileclose(f);
exception
	when others then
		dbms_lob.freetemporary(l_textclob);
		dbms_lob.fileclose(f);
		raise;
end;