 xml Sequence displaying errors. [message #176118] |
Wed, 07 June 2006 01:27  |
ksrk
Messages: 6
Registered: June 2006
|
Junior Member |
|
|
Hello Techies,
I am using oracle 8i in my machine. I am trying to run stored procedure.
Here is my stored procedure.
create or replace procedure X
as
xmlstr clob := "null";
xmlstr_xml XMLTYPE;
fileHandle utl_file.file_type;
charString varchar2(80);
begin
--for getting the output on the screen
dbms_output.enable(1000000);
-- open the XML document in read only mode
fileHandle:=utl_file.fopen('C:\Documents and Settings\user\Desktop\','final-1.XML','r');
loop
begin
utl_file.get_line(fileHandle,charString);
exception
when no_data_found then
utl_file.fclose(fileHandle);
exit;
end;
xmlstr:= xmlstr||charString;
end loop;
-- Convert CLOB data into XMLTYPE instance
xmlstr_xml := XMLTYPE(xmlstr);
-- Extract all the rows and loop for each row
for i in ( select EXTRACTVALUE(VALUE(t),'/APPLICATIONS/APPLICATION_ID') AID,
EXTRACTVALUE(VALUE(t), '/APPLICATIONS/APPLICATION_NAME')ANAME,
EXTRACTVALUE(VALUE(t), '/APPLICATIONS/DESCRIPTION')DESCRI
FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/KRISHNA//APPLICATIONS'))) t )
loop
insert into ESP_SHARED_APPLICATIONS values(i.AID,i.ANAME,i.DESCRI);
end loop;
exception
when no_data_found then
dbms_output.put_line('no data found');
end;
when i run this procedure , I am getting the following errors.
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE X:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/18 PL/SQL: Item ignored
3/26 PLS-00201: identifier 'null' must be declared
4/24 PLS-00201: identifier 'XMLTYPE' must be declared
4/24 PL/SQL: Item ignored
21/14 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
21/14 PL/SQL: Statement ignored
24/14 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
LINE/COL ERROR
-------- -----------------------------------------------------------------
24/14 PL/SQL: Statement ignored
26/23 PL/SQL: SQL Statement ignored
29/40 PLS-00201: identifier 'XMLSEQUENCE' must be declared
31/21 PL/SQL: SQL Statement ignored
31/65 PLS-00364: loop index variable 'I' use is invalid
SQL>
Can u guys help me in fixing these errors.
regards,
Krish
|
|
|
|
|
|
|
|
| Re: xml Sequence displaying errors. [message #216341 is a reply to message #176121] |
Fri, 26 January 2007 11:30  |
kiran_oracle
Messages: 1
Registered: January 2007
|
Junior Member |
|
|
Actually Oracle XML database(XDB) is introduced in 9i .
In 8i you can use java XSU .
In 8.1.7.4 you can either use the 2.0.2.9 XDK/XSU or install
9.2.0.6 XDK/XSU into 8i oracle database
Note.171658.1 Ext/Pub How To Install and Uninstall the XML Developers Toolkit (XDK for PL/SQL which includes XDK for Java)
Note.277152.1 Ext/R.R How to install XDK 9.2.0.x in an 8.1.7 Database Cleanly
Inorder to insert XML tag data into corresponding tables using pl/sql you need to DBMS_XMLSAVE.
Inorder to generate XML document correspoding to table data use
DBMS_XMLQUERY
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
