Using XSU tools with PL/SQL - e-ammar
Mr.
Ahmad,
Jordan
1159,
Amman 618
Quote {display:block;font-family: Vendra; font-size:14pt}
p1,P2,P3{font-style: normal; display:block}
P4{font-family: Comic Sans MS;font-weight:bold; font-style:italic; color:red;text-transform: uppercase; display:block; font-size:14pt}
said {display:block; text-align:center; text-decoration: underline; font-weight:bold; font-size:10pt; font-family:Vendra}
Miss
Suha,
Lebanon
2100,
Leb 118
Address {font-family: Vendra}
Row {display: block}
Title {color: red}
Name{font-family: Vendra ; color: blue}
Country {font-family: Courier New}
Code { font-style: italic}
PO {font-weight:bold}
Everybody can be great because anybody can serve,
You do not have to have a college degree to serve,
You do not have to make your subject and verb agree to serve,
You only need a heart full of grace, a soul generated by love
Martin Luther King
Using XSU tools with PL/SQL
Set serveroutput on size 4000
CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
xmlstr varchar2(32767);
line varchar2(2000);
begin
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
end;
/
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
begin
-- set up the query context...!
queryCtx := DBMS_XMLQuery.newContext('select * from emp');
-- get the result..!
result := DBMS_XMLQuery.getXML(queryCtx);
-- Now you can use the result to put it in tables/send as messages..
printClobOut(result);
DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
end;
JAVA
import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.lang.*;
import java.sql.*;
// class to test the String generation!
class testXMLSQL {
public static void main(String[] argv)
{
try{
// create the connection
Connection conn = getConnection("scott","tiger");
// Create the query class.
OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp");
// Get the XML string
String str = qry.getXMLString();
// Print the XML output
System.out.println(" The XML output is:\n"+str);
// Always close the query to get rid of any resources..
qry.close();
}catch(SQLException e){
System.out.println(e.toString());
}
}
// Get the connection given the user name and password..!
private static Connection getConnection(String username, String password)
throws SQLException
{
// register the JDBC driver..
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Create the connection using the OCI8 driver
Connection conn =
DriverManager.getConnection("jdbc:oracle:oci8:@",username,password);
return conn;
}
}
c:\oracle\ora92\jdk\bin\javac testXMLSQL.java
java testXMLSQL.java
You must have the CLASSPATH pointing to this directory for the Java executable to
find the class. Alternatively use various visual Java tools including Oracle
JDeveloper to compile and run this program. When run, this program prints out the
XML file to the screen.
Your classpath must include
Xsu12.jar C:\ORACLE\ORA92\rdbms\jlib\xsu12.jar
Xdb.jar C:\ORACLE\ORA92\rdbms\jlib\xdb.jar
Classes12.zip C:\dev9i\jdbc\lib\classes12.zip
Xmlparserv2.jar c:\oracle\ora92\lib\xmlparserv2.jar
Use windows environment to set the classpath
SQL> create table news
2 (f1 number,
3 f2 varchar2(1000));
Table created.
SQL> insert into news values (1,'Hello this is the news from Jordan');
1 row created.
SQL> insert into news values (2, 'Hello this is Jordan and Oracle');
1 row created.
SQL> commit;
Commit complete.
SQL>
1 CREATE INDEX my_index
2 ON news ( f2 )
3* INDEXTYPE IS ctxsys.context
SQL> /
SQL> l
1 SELECT SCORE(1), f2 from news
2* WHERE CONTAINS(f2, 'oracle', 1) > 0 ORDER BY SCORE(1) DESC
SQL> /
SCORE(1)
----------
F2
--------------------------------------------------------------------------------
4
Hello this is Jordan and Oracle
I am trying to store XML document into CLOB column. Basically I have one table
with the following definition:
CREATE TABLE xml_store_testing
(
xml_doc_id NUMBER,
xml_doc CLOB )
I want to store my XML document in xml_doc field.
I have written another PL/SQL procedure shown below, to read the contents of the
XML Document. The XML document is available on the file system. XML document
contains just ASCII data - no binary data.
CREATE OR REPLACE PROCEDURE FileExec
(
p_Directory IN VARCHAR2,
p_FileName IN VARCHAR2)
AS v_CLOBLocator CLOB;
v_FileLocator BFILE;
BEGIN
SELECT xml_doc
INTO v_CLOBLocator
FROM xml_store_testing
WHERE xml_doc_id = 1
FOR UPDATE;
v_FileLocator := BFILENAME(p_Directory, p_FileName);
DBMS_LOB.FILEOPEN(v_FileLocator, DBMS_LOB.FILE_READONLY);
dbms_output.put_line(to_char(DBMS_LOB.GETLENGTH(v_FileLocator)));
DBMS_LOB.LOADFROMFILE(v_CLOBLocator, v_FileLocator,
DBMS_LOB.GETLENGTH(v_FileLocator));
DBMS_LOB.FILECLOSE(v_FileLocator);
END FileExec;
create table bookcatalog
(isbn varchar2 (20),
title varchar2 (20),
author_lastname varchar2(30),
publisher varchar2 (20),
price number ( 10,3));
insert into bookcatalog
values ('&isbn','&title','&author_lastname','&publisher','&price');
SQL> /
Enter value for isbn: 11222EED
Enter value for title: XML HANDBOOK
Enter value for author_lastname: AHMAD
Enter value for publisher: OSBORNE
Enter value for price: 122.22
old 2: values ('&isbn','&title','&author_lastname','&publisher','&price')
new 2: values ('11222EED','XML HANDBOOK','AHMAD','OSBORNE','122.22')
1 row created.
SQL> /
Enter value for isbn: 553kkd
Enter value for title: The bible
Enter value for author_lastname: maurice
Enter value for publisher: dar alislam
Enter value for price: 10.22
old 2: values ('&isbn','&title','&author_lastname','&publisher','&price')
new 2: values ('553kkd','The bible','maurice','dar alislam','10.22')
1 row created.
select isbn, title, author_lastname, publisher, price from bookcatalog
catalog.xls
-
-
-
-
Scott's book catalog
-
-
ISBN
Title
Author
Publisher
Price
-
-
-
-
-
-
-
-
-
-
-
-
-
-
$
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.