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.

Google Online Preview   Download