Tuesday, July 24, 2007

ADF:Invoking AM methods from the Managed Bean

Pure JSF way:
-------------

FacesContext facesContext=FacesContext.getCurrentInstance();
ExpressionFactory exp=facesContext.getApplication().getExpressionFactory();
MethodExpression getDeptNames = exp.createMethodExpression(facesContext.getELContext(),
"#{bindings.getAllDepts.execute}", null,new Class[]{});
HashMap map=(HashMap)getDeptNames.invoke(facesContext.getELContext(),null);



ADF way:
---------

Packages to be imported

import javax.el.ExpressionFactory;

import javax.faces.application.Application;
import javax.faces.context.FacesContext;

import oracle.adf.model.OperationBinding;
import oracle.adf.model.binding.DCBindingContainer;
import oracle.adf.model.binding.DCIteratorBinding;

Sample Code Snippet:
-------------------
FacesContext fctx = FacesContext.getCurrentInstance();
Application app = fctx.getApplication();
ExpressionFactory exp=fctx.getApplication().getExpressionFactory();

DCBindingContainer bc;
bc = (DCBindingContainer)exp.createValueExpression(fctx.getELContext(),
"#{bindings}",DCBindingContainer.class).
getValue(fctx.getELContext());

//Get Current Row Handle
DCIteratorBinding iter = bc.findIteratorBinding("Emp2Iterator");
Row row = iter.getCurrentRow();
Number empNo = (Number)row.getAttribute("Empno");

//get Access to Method Binding

OperationBinding operation = (OperationBinding)bc.get("printANumber");
operation.getParamsMap().put("data", empNo);
operation.execute();

Thursday, July 19, 2007

PLSQL:Compile invalid DB objects


SET SERVEROUTPUT ON SIZE 1000000
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSE
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/

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;
/

Wednesday, April 04, 2007

HTML:Monospace Characters in textArea

To use monospaced text throughout a form,
1)-enclose the entire form between PRE tags.
2)-
<textarea style="font-family: monospace;font-size: 10pt" rows="9"  cols="80">

Monday, January 08, 2007

BC4J:Get top level master EO in an association.

Here is a code snippet to get the top level parent EO instance in a multi-level association hierarchy.
  
   /*Recursive function to get the top level entity given a child level entity.
*/
public EntityImpl getTopLevelEntityName(EntityImpl entity) {
AttributeDef[] attrList = entity.getStructureDef().getAttributeDefs();

EntityImpl parentEntity = entity;
for(AttributeDef attr : attrList){
if(AttributeDef.ATTR_ASSOCIATED_ROW == attr.getAttributeKind()){
Object oParentEntity = entity.getAttribute(attr.getName());
if(oParentEntity instanceof EntityImpl){
parentEntity = getTopLevelEntityName((EntityImpl)oParentEntity);
}
}
}
return parentEntity;
}

Sunday, January 07, 2007

Bc4J:Clone a VO to another(with resultset)

public static void cloneTheVO(ViewObjectImpl vo){
String sViewDefname = vo.getDefFullName();

while(true){
ViewObjectImpl tempVO= (ViewObjectImpl)am.findViewObject("NewVOName");
if(tempVO == null) break;
else tempVO.remove();
}

ViewObjectImpl newVO = (ViewObjectImpl)am.createViewObject("NewVOName" , sViewDefname);
int iOrigRangeSize = vo.getRangeSize();
vo.setRangeSize(-1);
Row[] rows = vo.getAllRowsInRange();
vo.setRangeSize(iOrigRangeSize);

for(Row row : rows){//JDK5.0 feature..change it as required..
newVO.insertRow(row);
}

long lRows = newVO.getEstimatedRowCount();
System.out.println("lRows="+lRows);
}

Friday, January 05, 2007

APPs:Get the Profile Option Values

Following sql script can be used as the apps user to query the profile option values.


SELECT p.profile_option_name short_name,
n.user_profile_option_name name,
decode(v.level_id, 10001, 'Site', 10002,
'Application', 10003,
'Responsibility', 10004,
'User', 10005,
'Server', 'UnDef') level_set,

decode(to_char(v.level_id), '10001', '', '10002',
app.application_short_name, '10003',
rsp.responsibility_key, '10005',
svr.node_name, '10006',
org.name, '10004',
usr.user_name, 'UnDef') "CONTEXT",

v.profile_option_value VALUE
FROM fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
WHERE p.profile_option_id = v.profile_option_id(+)
AND p.profile_option_name = n.profile_option_name
AND usr.user_id(+) = v.level_value
AND rsp.application_id(+) = v.level_value_application_id
AND rsp.responsibility_id(+) = v.level_value
AND app.application_id(+) = v.level_value
AND svr.node_id(+) = v.level_value
AND org.organization_id(+) = v.level_value
AND v.profile_option_value LIKE '%[INSERT YOUR PROFILE VALUE HERE]%'
ORDER BY short_name,
level_set;