 Newbie! Please Help - XML to Relational Table (I have searched) [message #420999] |
Thu, 03 September 2009 11:03  |
p-illist
Messages: 3
Registered: September 2009
|
Junior Member |
|
|
Hello All,
I'll get right to the point...I have a PL/SQL procedure that I believe should work to get an xml file into a relational table, but it keeps giving me compile errors. When I fix them, other compile errors arise. Can somebody look at my procedure and tell me if it is something that should be able to work.
I ran this first
******************************
CREATE TABLE XML_IMPORT_STORE (import_date date, file_data XMLTYPE);
CREATE DIRECTORY XML_FILE_DIR AS '/pkg/misc/samaplx';
BEGIN
XMLLOAD (ach_dir => 'XML_FILE_DIR',
ach_file_name => 'ach_test.xml',
ach_table_name => 'XML_IMPORT_STORE');
END;
******************************************
This is the procedure:
CREATE OR REPLACE PROCEDURE XMLLOAD (ach_dir IN VARCHAR2,
ach_file_name IN VARCHAR2,
ach_table_name IN VARCHAR2) AS
l_bfile BFILE := BFILENAME(ach_dir,ach_file_name);
l_clob CLOB;
BEGIN
XMLLOAD (ach_dir => 'XML_FILE_DIR',
ach_file_name => 'ach_test.xml',
ach_table_name => 'XML_IMPORT_STORE');
DBMS_LOB.createtemporary (l_clob, TRUE);
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
DBMS_LOB.fileclose(l_bfile);
EXECUTE IMMEDIATE 'INSERT INTO ' || ach_table_name || ' VALUES (XMLTYPE.createXML(:l_clob))' USING l_clob;
DBMS_LOB.freetemporary (l_clob);
COMMIT;
FOR i in ( select EXTRACTVALUE(VALUE(t), '/VENDOR/ID') vendor_id,
EXTRACTVALUE(VALUE(t), '/VENDOR/ACCOUNT') vendor_account,
EXTRACTVALUE(VALUE(t), '/VENDOR/DELIVERY') vendor_delivery
FROM TABLE(xmlSEQUENCE(EXTRACT(SELECT file_data FROM XML_IMPORT_STORE, '/CUSTOMERS/VENDOR'))) t )
LOOP
UPDATE VENDOR_ACH SET vendor_ach_account = i.vendor_account
AND vendor_delivery_account = i.vendor_delivery
WHERE vendor_ach_id = i.vendor_id;
COMMIT;
IF SQL%NOTFOUND THEN
INSERT INTO VENDOR_ACH VALUES (i.vendor_id,i.vendor_account,i.vendor_delivery);
END IF;
END LOOP;
END;
|
|
|
|
|
|
| Re: Newbie! Please Help - XML to Relational Table (I have searched) [message #421006 is a reply to message #421002] |
Thu, 03 September 2009 11:23  |
p-illist
Messages: 3
Registered: September 2009
|
Junior Member |
|
|
Ok, I understand if it doesn't compile then it won't work (i'm not that dense lol) but I just wanted to know if the procedure I have setup is something that could work with some tweaks.
Oracle RDBMS:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for HPUX: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
|
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
