dcsimg
Retrieving data from Oracle
2 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Sathya_Sekar
Posted On:   Tuesday, December 4, 2001 04:28 AM

Background: We have a 3-tier architecture-a client, a Java server and 8i Oracle db.To retrieve data from the db,we use a PL/SQL table lie a buffer and invoke a procedure to buffer data from stored procedures into this PL/SQL table.The server then retrieves the data from the buffer and returns it to the client. Issue : This is obvously a very laborious process.I must also mention that every buffer invoke from a stred procedure is a step towards construct the response XML document. Thus, if I say fillBuffer('abc') - the fillBuffer procedure translates this as abc . Thus a complete XML document has been created in the database and saved in the PL/SQL table.To retrieve this, the server has an Array as an output parameter which loops as ma   More>>

Background: We have a 3-tier architecture-a client, a Java server and 8i Oracle db.To retrieve data from the db,we use a PL/SQL table lie a buffer and invoke a procedure to buffer data from stored procedures into this PL/SQL table.The server then retrieves the data from the buffer and returns it to the client.


Issue : This is obvously a very laborious process.I must also mention that every buffer invoke from a stred procedure is a step towards construct the response XML document. Thus, if I say fillBuffer('abc') - the fillBuffer procedure translates this as abc . Thus a complete XML document has been created in the database and saved in the PL/SQL table.To retrieve this, the server has an Array as an output parameter which loops as many times as there are rows in the PL/SQL table. This is taking a lot of time if the no of rows retrieved is above 2000 or 3000.


RESOLUTION ?: The length of the XML being constructed is indefinite. This is a major restriction. Is there any way through which we can avoid the looping? Would using Java stored procedures help? I have also looked around for some easily-understandable material on the web on how to exactly use the Java stored procedures (like can they be invoked from the Java server through a JDBC call and can we use objects like vectors to transfer data ?). Any info would be most welcome.





Thanks    <<Less

Re: Retrieving data from Oracle

Posted By:   Bernie_Acs  
Posted On:   Tuesday, December 4, 2001 09:57 AM

PS: I ran several other benchmarks on a table with millions of records to measure time of execution for processing the XML doc.

The sampled table looked like:

ID NOT NULL NUMBER(10)

LAST_NAME VARCHAR2(50)

FIRST_NAME VARCHAR2(50)

MIDDLE_NAME VARCHAR2(50)

INITIALS VARCHAR2(10)

SUFFIX VARCHAR2(10)

COLLECTIVE_NAME VARCHAR2(300)

POSITION NOT NULL NUMBER(5)

PID NUMBER

CERTAINTY NUMBER(2)



My database is located 1000s mile away over the internet so there is some variability in benchmarks due to traffic and load.

These time skipped the print output step but did preform the XML processing..


Outputs in miliseconds:



Processed 101 Elapsed: 15473

Processed 501 Elapsed: 15753

Processed 1001 Elapsed: 18587

Processed 2001 Elapsed: 33638

Processed 3001 Elapsed: 45966

Processed 4001 Elapsed: 82478

Processed 5001 Elapsed: 68779

Processed 6001 Elapsed: 86304

Processed 7001 Elapsed: 84842



Benchmark Test Code mods:



int howMany = 7001;
rset = stmt.executeQuery ("select * from sample_table where rownum < " + howMany );
long tm = System.currentTimeMillis();
OracleXMLQuery xq = new OracleXMLQuery(conn, rset);
String output = xq.getXMLString();
System.out.println("Processed "+ howMany + " Elapsed: " + ( System.currentTimeMillis() - tm ) );

Re: Retrieving data from Oracle

Posted By:   Bernie_Acs  
Posted On:   Tuesday, December 4, 2001 08:48 AM

There are some really nice and verbose facilities prefabricated to accomidate Query to XML output. The method you have choosen is certainly one way to achieve your goal, but I would recommend you examine and consider the content of the following link for your purposes,
Oracle XML Docs ( if you are not already a registered Oracle TechNet User you will have to register before proceding use the following link to get there OTN Home Page ). As an example of how these functions work I have prepared an example of what some code might look like to produce the end product you are you to create assuming that I understand your goals. [ Disclaimer : This example is a derived from one the samples packaged with the Oracle/Jserver demo packages designed to show JSP/XML function, this sample will require that the xsul12.jar be included in the CLASSPATH if you want to experiment with it. ]. In the following example the DOM API has been used if your datasets are very large you may want to use SAX API which is discussed in the documentation link above. ( There are Java, C, and PL/SQL implimentations available to you )



package package6;

import java.sql.*;
import oracle.xml.sql.query.OracleXMLQuery;
import org.w3.dom.*;

/**
* A Class: XMLQuery an example.
*


* @Me
*/
public class XMLQuery extends Object {

/**
* Constructor
*/
public XMLQuery() {
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
String dbURL = "jdbc:oracle:oci8:@dbaba";

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(dbURL, "scott", "tiger");
stmt = conn.createStatement ();
rset = stmt.executeQuery ("SELECT ename, sal " +
"FROM scott.emp ORDER BY ename");
//
// Here is the magic call. Easy!!
//
OracleXMLQuery xq = new OracleXMLQuery(conn, rset);
//
// Just dump the XML Documnet for your viewing pleasure
//
System.out.println( xq.getXMLString() );
//
stmt.close();
rset.close();
conn.close();
} catch (java.sql.SQLException e) {
System.out.println(" SQL error: "+ e +"
" );
e.printStackTrace();
}

}

/**
* main
* @param args
*/
public static void main(String[] args) {
XMLQuery xMLQuery = new XMLQuery();
}
}





Output Produced by this code






ADAMS
1100


ALLEN
1600


BLAKE
2850


CLARK
2450


FORD
3000


JAMES
950


JONES
2975


KING
5000


MARTIN
1250


MILLER
1300


SCOTT
3000


SMITH
800


TURNER
1500


WARD
1250


About | Sitemap | Contact