Wednesday, May 23, 2007

JAVA:Clob Operation through JDBC

In latest JDBC(use ojdbc14.jar), the ResultSet.getString() method is enhanced to read a string with length > 32765 bytes.
So we can simply use like


.....

// Create a PreparedStatement object
PreparedStatement pstmt = null;

// Create a ResultSet to hold the records retrieved.
ResultSet rset = null;
.......


// Create SQL query statement to retrieve records having CLOB data from
// the database.
String sqlCall = "SELECT clob_col FROM clob_tab";
pstmt= conn.prepareStatement(sqlCall);

// Execute the PrepareStatement
rset = pstmt.executeQuery();

String clobVal = null;

// Get the CLOB value larger than 32765 bytes from the resultset
while (rset.next()) {
clobVal = rset.getString(1);
System.out.println("CLOB length: "+clobVal.length());
}


However, its better to use OracleResultSet by casting the Java ResultSet and use the getClob() method.
Example usage

Another note for developers using Oracle 10g JDBC driver, for passing a clob value previously we used to stream and pass.
In oracle 10g JDBC driver, we have sthg called OraclePreparedStatement.setStringForClob(1,str);

BC4J:View object in-memory sorting

Sometimes, we end up having requirements on sorting a VO resultset in the middle tier itself. This is how we can use in-memory sorting to sort the VO rows in middle tier.


voImpl.setSortBy("SequenceNum");
voImpl.setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
voImp.executeQuery();


This exists from JDev 10.1.3.
View Object In-memory sorting

Oracle:Global Temporary Table and ORA-14450

"Distributed transactions are not supported for temporary tables".

if some DML operations are performed on a global temporary table and then if we try and read the temporary table from some procedure in an autonomous transaction, then we encounter the error since table is locked by the main transaction.

This error you get while there is no COMMIT/ROLLBACK yet done and if you are trying to perform some DDL(Like Alter or Drop) on the GT table.

Note:
In cases where the GT table is created with option "on commit preserve rows", the table remains locked even after COMMIT.

Monday, May 21, 2007

Java:Example code for a WebCrawler


import java.io.BufferedReader;
import java.io.InputStreamReader;

import java.net.HttpURLConnection;
import java.net.URL;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class WebCrawler {

public static void main(String[] args) {
String sProxy = args[0];
String sHost = args[1];
String sFilePath = args[3];
// timeout connection after 500 miliseconds, optional ofcourse
//System.setProperty("sun.net.client.defaultConnectTimeout", "500");
//System.setProperty("sun.net.client.defaultReadTimeout", "1000");

System.setProperty("http.proxySet", "true");
System.setProperty("http.proxyHost", sProxy);
System.setProperty("http.proxyPort", sHost);

// existence symbol table of examined web pages
HashSet st = new HashSet();
ArrayList crawlerLinksList = getHTMLLines(sFilePath);
for (String link : crawlerLinksList){
st.add(link);
}

// breadth first search crawl of web
for (String v : crawlerLinksList) {
System.out.println(v);
String input = getHTML(v);

/*************************************************************
* Find links of the form: http://xxx.yyy.zzz
* \\w+ for one or more alpha-numeric characters
* \\. for dot
* could take first two statements out of loop
*************************************************************/
//String regexp = "http://(\\w+\\.)*(\\w+)";
Pattern pattern = Pattern.compile(regexp);
Matcher matcher = pattern.matcher(input);

// find and print all matches
while (matcher.find()) {
String w = matcher.group();
w = matcher.group(2);
System.out.println("Matched String="+w);
if (!st.contains(w)) {
st.add(w);
}
}

}
}

public static String getHTML(String urlToRead) {
URL url; // The URL to read
HttpURLConnection conn; // The actual connection to the web page
BufferedReader rd; // Used to read results from the web page
String line; // An individual line of the web page HTML
String result = ""; // A long string containing all the HTML
try {
url = new URL(urlToRead);
conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod("GET");
rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
while ((line = rd.readLine()) != null) {
result += line;
}
rd.close();
} catch (Exception e) {
//e.printStackTrace();
}
return result;
}

public static ArrayList getHTMLLines(String urlToRead) {
URL url; // The URL to read
HttpURLConnection conn; // The actual connection to the web page
BufferedReader rd; // Used to read results from the web page
String line; // An individual line of the web page HTML
ArrayList result = new ArrayList(); // A long string containing all the HTML
try {
url = new URL(urlToRead);
conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod("GET");
rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
while ((line = rd.readLine()) != null) {
result.add(line);
}
rd.close();
} catch (Exception e) {
//e.printStackTrace();
}
return result;
}

}

Sunday, May 20, 2007

PLSQL:Mimicing some of the Java String functions in PLSQL

Previously in PLSQL(upto 10g), we used to do complex operations for Strings like String startsWith, string EndsWith etc functions.
In Java we can achieve by simple usage of String APIs like startsWith(), endsWith() etc.

From Oracle 10g, it supports Regular Expression packages in both SQL and PLSQL engine.
So we can use this powerful functionalities like.

Java startsWith() -> PLSQL REGEXP_INSTR('AR_AGING_BUCKETS_B', '^AR')
Java endsWith() -> PLSQL REGEXP_INSTR('AR_AGING_BUCKETS_B', '_B$')
Java replace() ->
PLSQL REGEXP_REPLACE('AR_AGING_BUCKETS_B', 'ABC')

Thursday, May 17, 2007

PLSQL:Insert a PLSQL record into a Table

I want to insert a pl/sql record into a table. Here is the code.


create table T1 (a number);
/
declare
T_rec T1%ROWTYPE;
begin
T_rec.a := 1;
/*Note:No parantheses after values keyword.*/
insert into T1 values T_rec;
end;
/