Best way to pass a huge amount of text to a stored procedure?

[nafej]

I need to pass the contents of an XML file (> than 32,767 bytes - the limit of varchar) to an Oracle stored procedure to be parsed and inserted into tables. It appears that ADO is incapable of passing a large amount of data like this. Has anyone done this?? One method I thought of is breaking the XML into multiple blocks, each of which could fit into a varchar, and concatenating them back together into a CLOB within the SP. But, I can't seem to assign text or varchar values to a CLOB variable.

Any suggestions would be greatly appreciated!

Thanks, Jeff

 

[mark06]

Here we go ...

1. Create a logical directory (connected as SYSTEM) in Oracle using ...
CREATE DIRECTORY <DIRNAME> AS '<OSPATH>';
(don't include a '/' or a '\' at the end of the OS path)

2. Grant read access to the directory to the 'load user' ...
GRANT READ ON DIRECTORY <DIRNAME> TO <LOADUSER>;

3. Put the file in this directory. It can now be read into PL/SQL as a BFILE, and loaded into a database CLOB column. In PL/SQL ...
(a) Create a procedure with one BFILE and one CLOB variable.
(b) Use DBMS_LOB.CREATETEMPORARY to create a temporary LOB.
(c) Assign the BFILE variable using the BFILENAME function.
(d) Open the BFILE.
(e) Use DBMS_LOB.LOADFROMFILE to copy the BFILE to the temporary LOB.
(f) Use SQL to pass the temporary LOB to the CLOB column in the database.

Should be ok.

Good luck!
Mark.

 

[nafej]

Thanks a lot for the response, it got me on the right track!