Home » Developer & Programmer » JDeveloper, Java & XML » Emergency problem of creating a view of XMLType
 Emergency problem of creating a view of XMLType [message #121555] |
Mon, 30 May 2005 10:48  |
sluo
Messages: 8
Registered: May 2005
|
Junior Member |
|
|
Hi, All:
I have an emergency problem when create a view of XMLType
using the following SQL script: (any reply also can forward to
steve_luo88@yahoo.ca, actually the original task is to store
the query result in a oracle table and later on can retrieve it
as XML element)
------------------------------
DROP TABLE employees;
CREATE TABLE employees
(empno number(4), fname varchar2(20), lname varchar2(20), hire date, salary
number(6));
INSERT INTO employees VALUES
(2100, 'John', 'Smith', Date'2000-05-24', 30000);
INSERT INTO employees VALUES
(2200, 'Mary', 'Martin', Date'1996-02-01', 30000);
CREATE OR REPLACE VIEW Emp_view OF XMLTYPE WITH OBJECT ID
(EXTRACT(sys_nc_rowinfo$,'/Emp/@empno').getnumberval())
AS SELECT XMLELEMENT("Emp", XMLAttributes(empno),
XMLForest(e.fname ||' '|| e.lname AS "name",
e.hire AS "hiredate")) AS "result"
FROM employees e
WHERE salary > 20000;
------------------------------------
Everything is okay for running the above scripts, but when
Select * from EMP_View, the wrong formatted result as:
<Emp EMPNO="2100">
<name>John Smith</name>
<hiredate>24-MAY-00</hiredate>
</
<Emp EMPNO="2200">
<name>Mary Martin</name>
<hiredate>01-FEB-96</hiredate>
<
2 rows selected.
-------------------
So what's wrong.
I aslo meet the problem when try to load the XDK into Database by running:
$ORACLE_HOME/rdbms/admin/initxml.sql
the system said that it can not open "dbmsxsu.xml" and
"xmlload.xml"
But the system said the initxml.sql procedure successfully
executed.
Any suggestions greatly appreciated!
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121559 is a reply to message #121555] |
Mon, 30 May 2005 11:19  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
What Oracle release do you use ?
I'm working in 9.2.0.4:
SQL> CREATE OR REPLACE VIEW Emp_view OF XMLTYPE WITH OBJECT ID
2 (EXTRACT(sys_nc_rowinfo$,'/Emp/@empno').getnumberval())
3 AS SELECT XMLELEMENT("Emp", XMLAttributes(empno),
4 XMLForest(e.fname ||' '|| e.lname AS "name",
5 e.hire AS "hiredate")) AS "result"
6 FROM employees e
7 WHERE salary > 20000;
View created.
SQL> select * from emp_view;
SYS_NC_ROWINFO$
-------------------------------------------------------------------------------
<Emp EMPNO="2100"><name>John Smith</name><hiredate>24/05/2000</hiredate></Emp>
<Emp EMPNO="2200"><name>Mary Martin</name><hiredate>01/02/1996</hiredate></Emp>
Rgds.
|
|
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121573 is a reply to message #121564] |
Mon, 30 May 2005 15:35 |
sluo
Messages: 8
Registered: May 2005
|
Junior Member |
|
|
DROP TABLE employees;
CREATE TABLE employees
(empno number(4), fname varchar2(20),
lname varchar2(20), hire date, salary
number(6));
INSERT INTO employees VALUES
(2100, 'John', 'Smith', Date'2000-05-24',30000);
INSERT INTO employees VALUES
(2200, 'Mary', 'Martin', Date'1996-02-01', 30000);
CREATE OR REPLACE VIEW Emp_view OF XMLTYPE
WITH OBJECT ID
(EXTRACT(sys_nc_rowinfo$,'/Emp/@empno').
getnumberval())
AS SELECT XMLELEMENT("Emp",
XMLAttributes(empno),
XMLForest(e.fname ||' '||
e.lname AS "name",
e.hire AS "hiredate"))
AS "result"
FROM employees e
WHERE salary > 20000;
---by UserName: sluo, Pwd:steveluo88,
Hi, All:
I have an emergency problem when create
a view of XMLType
using the following SQL script:
------------------------------
DROP TABLE employees;
CREATE TABLE employees
(empno number(4), fname varchar2(20),
lname varchar2(20), hire date, salary
number(6));
INSERT INTO employees VALUES
(2100, 'John', 'Smith', Date'2000-05-24', 30000);
INSERT INTO employees VALUES
(2200, 'Mary', 'Martin', Date'1996-02-01', 30000);
CREATE OR REPLACE VIEW Emp_view OF XMLTYPE WITH
OBJECT ID
(EXTRACT(sys_nc_rowinfo$,'/Emp/@empno').
getnumberval())
AS SELECT XMLELEMENT("Emp",
XMLAttributes(empno),
XMLForest(e.fname ||' '|| e.lname
AS "name",
e.hire AS "hiredate"))
AS "result"
FROM employees e
WHERE salary > 20000;
------------------------------------
Everything is okay for running the above
scripts, but when
Select * from EMP_View, the wrong formatted
result as:
<Emp EMPNO="2100">
<name>John Smith</name>
<hiredate>24-MAY-00</hiredate>
</
<Emp EMPNO="2200">
<name>Mary Martin</name>
<hiredate>01-FEB-96</hiredate>
<
2 rows selected.
-------------------
So what's wrong.
I aslo meet the problem when try to load
the XDK into Database by running:
$ORACLE_HOME/rdbms/admin/initxml.sql
the system said that it can not open "dbmsxsu.xml" and
"xmlload.xml"
But the system said the initxml.sql procedure
successfully
executed.
Any suggestions greatly appreciated!
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121574 is a reply to message #121559] |
Mon, 30 May 2005 15:43 |
sluo
Messages: 8
Registered: May 2005
|
Junior Member |
|
|
I also work on the Oracle 9.2, why
your query result looks good but mine wrong!
Do you need to set up XDK in Oracle 9.i
and see my problem of running InitXML.sql.
(do you need to set up XDK, or at the very
beginning of install Oracle, by selecting
the options of JDeveloper)
|
|
|
|
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121621 is a reply to message #121585] |
Tue, 31 May 2005 02:46 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
| sluo wrote on Tue, 31 May 2005 03:36 |
But how to extract the specific records.
see select "empno=2000"
|
SQL> select * from emp_view where SYS_NC_ROWINFO$ like '_Emp EMPNO="2100"%';
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<Emp EMPNO="2100"><name>John Smith</name><hiredate>24/05/2000</hiredate></Emp>
SQL> SELECT * FROM emp_view X
2 WHERE extractValue(value(X),'Emp/name') = 'John Smith'
3 /
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<Emp EMPNO="2100"><name>John Smith</name><hiredate>24/05/2000</hiredate></Emp>
SQL> select * from emp_view X where
2 existsNode(value(X),'/Emp[name="John Smith"]') = 1
3 /
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<Emp EMPNO="2100"><name>John Smith</name><hiredate>24/05/2000</hiredate></Emp>
SQL> select * from emp_view X
2 where extractValue(value(X),'/Emp/@EMPNO') = 2100
3 /
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<Emp EMPNO="2100"><name>John Smith</name><hiredate>24/05/2000</hiredate></Emp>
SQL> select * from emp_view X where
2 existsNode(value(X),'/Emp[@EMPNO="2100"]') = 1
3 /
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<Emp EMPNO="2100"><name>John Smith</name><hiredate>24/05/2000</hiredate></Emp>
Rgds.
|
|
|
|
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121817 is a reply to message #121697] |
Wed, 01 June 2005 03:43 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Concerning update question:
SQL> create table xml_tab of xmltype;
Table created.
SQL> insert into xml_tab select value(t) from emp_view t;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from xml_tab;
SYS_NC_ROWINFO$
--------------------------------------------------------------------------------
<Emp EMPNO="2100"><name>John Smith</name><hiredate>24.05.00</hiredate></Emp>
<Emp EMPNO="2200"><name>Mary Martin</name><hiredate>01.02.96</hiredate></Emp>
SQL> update xml_tab t set value(t) = updateXML(value(t),'/Emp/name/text()','New name')
2 where extractValue(value(t),'/Emp/@EMPNO') = 2100
3 /
1 row updated.
SQL> select value(t) from xml_tab t where extractValue(value(t),'/Emp/@EMPNO') = 2100;
VALUE(T)
--------------------------------------------------------------------------------
<Emp EMPNO="2100">
<name>New name</name>
<hiredate>24.05.00</hiredate>
</Emp>
Rgds.
|
|
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121874 is a reply to message #121868] |
Wed, 01 June 2005 09:55 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
| sluo wrote on Wed, 01 June 2005 18:34 |
(In Oracle 9i Developer's guide, there are some introductions,
but it seems too long)
|
Steve, I'm afraid no other way else to read it.
Shotly, you have to register jdbc driver, create Connection
object, create PreparedStatement and execute statement you need.
Very stupy example below.
OracleConnection cnn = null;
try
{
ResultSet rs = null;
PreparedStatement pstm = null;
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
cnn = (OracleConnection)DriverManager.getConnection("jdbc:oracle:thin:@10.232.1.94:1521:dbg9204","scott","tiger");
cnn.setAutoCommit(false);
....
pstm = cnn.prepareStatement("update emp set sal=sal");
pstm.execute();
cnn.commit();
Rgds.
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121903 is a reply to message #121874] |
Wed, 01 June 2005 12:18 |
sluo
Messages: 8
Registered: May 2005
|
Junior Member |
|
|
Thank you very much, I deeply appreciate your precious help.
I mean that can we use some statements in Java
code as the following:
create or replace view of
XMLType Emp_view OF XMLTYPE WITH OBJECT ID
(EXTRACT(sys_nc_rowinfo$,'/Emp/@empno').getnumberval())
AS SELECT XMLELEMENT("Emp", XMLAttributes(empno),
XMLForest(e.fname ||' '|| e.lname AS "name",
e.hire AS "hiredate")) AS "result"
FROM employees e
WHERE salary > 20000;
or
select * from emp_view where extractValue (value (x),
'Emp/name')='John Smith'
For prepare statement, I understand it and try to use it
in Java.
|
|
|
|
| Re: Emergency problem of creating a view of XMLType [message #121976 is a reply to message #121903] |
Thu, 02 June 2005 02:58 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
In java you can execute any DML statements like
SELECT, UPDATE (including any for XMLType data).
Say, you can call "select * from emp_view where extractValue (value (x),'Emp/name')= ?" where ? will be the parameter
of statement.
What of DDL like CREATE VIEW, you will need to use
constructions like "begin execute immediate <<something>>; end;".
PreparedStatement, Statement and CallableStatement objects in Java don't support DDL commands.
Also you can use DBMS_UTILITY.EXEC_DDL_STATEMENT procedure
and call it from Java through PreparedStatement with String
parameter.
Rgds.
|
|
|
|
Goto Forum:
Current Time: Mon Nov 25 17:15:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
