asked on

loading XML into the CLOB using Oracle Forms

I have a problem loading xml data into the CLOB when it's done in Oracle Form
I'm reading XML file as a ASCII data and trying to concatinated into the CLOB variable for inserting into the Oracle table.

v_clob clob;
v_file varchar2(200);
v_file_row varchar2(32000);
in_file Text_IO.File_Type;
end_of_file boolean := FALSE;

and read .xml file

in_file := Text_IO.Fopen(v_file,'r');
WHILE NOT end_of_file
LOOP
v_clob := v_clob||v_file_row; <--- compile error wrong number of types of arguments in call to '||'
END LOOP;
insert into xml_table (doc_clob) values (v_lob);

If I declare
v_clob long;

it work fine, but raises error if lenght exceeds 32K.

Can Anyone provide an example for solving this issue?

slightwv (䄆 Netminder)

I'm not a forms person but consider myself pretty strong with CLOBS so together I'm confident we can get it working.

First I'd like to know your Oracle version (include all 4 numbers. ie./ 10.2.0.3).

While I wait for that.
Check out the DBMS_LOB package. There is a WRITE_APPEND procedure that will allow you append to a CLOB.

http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999705

Before you go rewriting the procedure to use WRITE_APPEND, check out DBMS_LOB.LOADCLOBFOMRFILE as an alternative to the Text_IO piece. You should be able to read the entire CLOB at 1 time.

Here's a procedure I use for loading text files into a CLOB table:
--------------------------------------------------------------
drop directory xml_dir;
create directory xml_dir as 'C:\';

CREATE OR REPLACE procedure load_xml
(
      v_file_name            IN      varchar2
)

IS

BEGIN

DECLARE

      v_bfile                   bfile := BFILENAME( 'XML_DIR' , v_file_name);
      v_lob                   clob;

src_offset number := 1;
dst_offset number := 1;
cs_id number := NLS_CHARSET_ID('UTF8'); /* 998 */
lang_ctx number := dbms_lob.default_lang_ctx;
warning number;

      BEGIN

      insert into bob(xml_clob )
            values(empty_clob()) returning xml_clob into v_lob;

      DBMS_LOB.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
      dbms_lob.LOADCLOBFROMFILE(v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile), dst_offset, src_offset, cs_id, lang_ctx,warning);

      DBMS_LOB.CLOSE(v_bfile);

      commit;
      END;

END;
/

show errors

tribuna33

ASKER

Our Oracle version is 9.2.0.5.0

I have no issues append character if I use PL/SQL where variery of options and packages exist.
My issues is to load data into the CLOB using Oracle Form.
For instance,
declaration for variables such as
v_clob clob
v_char varchar2(32000)

v_clob := v_clob||v_char

works in pl/sql, but doesn;t in Oracle Form

slightwv (䄆 Netminder)

If you have it working fine from PL/SQL, since I'm not a forms person, I'm not sure how I can help. Sorry.

ASKER CERTIFIED SOLUTION

tribuna33

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

Great. With your permission, I'll refund your points and accept your last post as the answer.

tribuna33

ASKER

that is fine