select user_name, application_name, responsibility_name,
security_group_name,
greatest(to_date(u.start_date), to_date(ur.start_date), to_date(r.start_date)) start_date,
decode(
least(nvl(u.end_date, to_date('01/01/4712','DD/MM/YYYY')),
nvl(ur.end_date, to_date('01/01/4712','DD/MM/YYYY')),
nvl(r.end_date, to_date('01/01/4712','DD/MM/YYYY'))),
to_date('01/01/4712','DD/MM/YYYY'), '',
least(nvl(u.end_date, nvl(ur.end_date, r.end_date)),
nvl(ur.end_date, nvl(u.end_date, r.end_date)),
nvl(r.end_date, nvl(u.end_date, ur.end_date)))) end_date
from fnd_user u, fnd_user_resp_groups_all ur,
fnd_responsibility_vl r, fnd_application_vl a,
fnd_security_groups_vl s
where a.application_id = r.application_id
and u.user_id = ur.user_id
and r.application_id = ur.responsibility_application_id
and r.responsibility_id = ur.responsibility_id
and ur.start_date <= sysdate
and nvl(ur.end_date, sysdate + 1) > sysdate
and u.start_date <= sysdate
and nvl(u.end_date, sysdate + 1) > sysdate
and r.start_date <= sysdate
and nvl(r.end_date, sysdate + 1) > sysdate
and ur.security_group_id = s.security_group_id
and r.version in ('4','W','M')
--and responsibility_name = 'Functional Administrator'
order by user_name, application_name, responsibility_name,
security_group_name
Friday, September 18, 2009
Thursday, September 3, 2009
Code to print webbean's child names with their ids
Enumeration enum= webBean.getChildNames();
while(enum!=null && enum.hasMoreElements())
{
String name = (String)enum.nextElement();
System.out.println(name);
}
it should print the child names, with their ids
while(enum!=null && enum.hasMoreElements())
{
String name = (String)enum.nextElement();
System.out.println(name);
}
it should print the child names, with their ids
Monday, July 27, 2009
Puzzle - $1 vanished ?
Three men go to a cheap motel, and the desk clerk charges them a sum of $30.00 for the night. The three of them split the cost ten dollars each. Later the manager comes over and tells the desk clerk that he overcharged the men, since the actual cost should have been $25.00. The manager gives the bellboy $5.00 and tells him to give it to the men. The bellboy, however, decides to cheat the men and pockets $2.00, giving each of the men only one dollar.
Now each man has paid $9.00 to stay for the night, and 3 x $9.00 = $27.00. The bellboy has pocketed $2.00. But $27.00 + $2.00 = $29.00. Where is the missing $1.00? WTF?
Solution:
Total Customer effectively paid =27
Total amnt the hotel got = 25
The difference is in bellboy's pocket = 27-25 =2
Now each man has paid $9.00 to stay for the night, and 3 x $9.00 = $27.00. The bellboy has pocketed $2.00. But $27.00 + $2.00 = $29.00. Where is the missing $1.00? WTF?
Solution:
Total Customer effectively paid =27
Total amnt the hotel got = 25
The difference is in bellboy's pocket = 27-25 =2
Puzzle -Daughter's Age Problem
A census taker came to a house where a man lived with
three daughters. "What are your daughters' ages?" he asked.
The man replied, "The product of their ages is 72, and the
sum of their ages is my house number."
"But that's not enough information," the census taker insisted.
"All right," answered the farmer, "the oldest loves chocolate.
What are the daughters' ages?
Solution :
We can look at all the possibilities this way - the oldest may be the same age as the middle, and the middle may be the same age as the youngest:
OLD MID YOUNG SUM
72 1 1 74
36 2 1 39
24 3 1 28
18 4 1 23
18 2 2 22
12 3 2 17
12 6 1 19
9 8 1 18
9 4 2 15
8 3 3 14
6 6 2 14
6 4 3 13
These are all the possibilities, since if we give the oldest a lower age, there is no way the product can equal 72.We know their ages add up to the farmer's house number. The sum column gives all possibilities for the house number. When the farmer gave the census person the information about the product of their ages being 72 and the sum of their ages being his house number the census person said this was not enough information. So there must have been at least two different possibilities that were still viable options for the census taker to choose from. This means the house number must have had two sums equal it. So the daughters are:
8 3 3 14
6 6 2 14
since they both add up to 14, the only number that appeared twice in the sum of the ages. The fact that the farmer had an oldest daughter says that the daughters must in fact be ages 8, 3, and 3.
three daughters. "What are your daughters' ages?" he asked.
The man replied, "The product of their ages is 72, and the
sum of their ages is my house number."
"But that's not enough information," the census taker insisted.
"All right," answered the farmer, "the oldest loves chocolate.
What are the daughters' ages?
Solution :
We can look at all the possibilities this way - the oldest may be the same age as the middle, and the middle may be the same age as the youngest:
OLD MID YOUNG SUM
72 1 1 74
36 2 1 39
24 3 1 28
18 4 1 23
18 2 2 22
12 3 2 17
12 6 1 19
9 8 1 18
9 4 2 15
8 3 3 14
6 6 2 14
6 4 3 13
These are all the possibilities, since if we give the oldest a lower age, there is no way the product can equal 72.We know their ages add up to the farmer's house number. The sum column gives all possibilities for the house number. When the farmer gave the census person the information about the product of their ages being 72 and the sum of their ages being his house number the census person said this was not enough information. So there must have been at least two different possibilities that were still viable options for the census taker to choose from. This means the house number must have had two sums equal it. So the daughters are:
8 3 3 14
6 6 2 14
since they both add up to 14, the only number that appeared twice in the sum of the ages. The fact that the farmer had an oldest daughter says that the daughters must in fact be ages 8, 3, and 3.
Test For Palindrome - Java
package palindrome;
import java.util.*;
public class TestPalindrome
{
public TestPalindrome()
{
}
public static boolean isPalindrome(String word) {
int left = 0; // index of leftmost unchecked char
int right = word.length() -1; // index of the rightmost
while (left < right) { // continue until they reach center
if (word.charAt(left) != word.charAt(right)) {
return false; // if chars are different, finished
}
left++; // move left index toward the center
right--; // move right index toward the center
}
return true; // if finished, all chars were same
}
public static void main(String[] args)
{
TestPalindrome testPalindrome = new TestPalindrome();
String s = "Was it a rat I saw?" ;
s = s.replaceAll("[\\s\\p{Punct}]", "").toLowerCase();
boolean flag = isPalindrome(s);
System.out.println(s + " PALINDROME ? " + flag);
}
}
import java.util.*;
public class TestPalindrome
{
public TestPalindrome()
{
}
public static boolean isPalindrome(String word) {
int left = 0; // index of leftmost unchecked char
int right = word.length() -1; // index of the rightmost
while (left < right) { // continue until they reach center
if (word.charAt(left) != word.charAt(right)) {
return false; // if chars are different, finished
}
left++; // move left index toward the center
right--; // move right index toward the center
}
return true; // if finished, all chars were same
}
public static void main(String[] args)
{
TestPalindrome testPalindrome = new TestPalindrome();
String s = "Was it a rat I saw?" ;
s = s.replaceAll("[\\s\\p{Punct}]", "").toLowerCase();
boolean flag = isPalindrome(s);
System.out.println(s + " PALINDROME ? " + flag);
}
}
Wednesday, July 1, 2009
Extract Jar from Blob Column through Java code
I used this code to extract jar file which was stored in BLOB column of the database.
/**
*
* This code extracts jar file from cz_archives table from the BLOB column.
*
*/
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ExtractBlob
{
public ExtractBlob()
{
}
final static int bBufLen = 4 * 8192;
static String query;
static String outFile;
static Connection conn;
public static void main(String[] args) throws FileNotFoundException, IOException, SQLException
{
String baseFolder = "C:/CZArchives";
String folderName = "";
String fileName = "";
String query = "SELECT 'TEST-Archive' FOLDER_NAME, "
+ "CZArch.ARCHIVE_URL DOCNAME,CZArch.ARCHIVE_BLOB "
+ "FROM CZ_ARCHIVES CZArch, CZ_RP_ENTRIES CZRp "
+ "WHERE CZRp.OBJECT_ID = CZArch.ARCHIVE_ID "
+ "AND CZArch.deleted_flag = '0' "
+ " AND CZRp.OBJECT_TYPE = 'ARC' "
+ "and CZArch.name = 'nameofyourjarfile' ";
//replace nameofyourjarfile with your jar file name
conn = getConnection();
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Blob blob = null;
while (rs.next())
{
folderName = rs.getString(1);
fileName = rs.getString(2);
// String created = rs.getString(4);
// String updated = rs.getString(5);
blob = rs.getBlob(3);
System.out.println("Got " + folderName);
System.out.println("Got " + fileName);
// System.out.println("Created " + created);
// System.out.println("Updated " + updated);
outFile = baseFolder + "/" + folderName;
outFile = baseFolder;
long wrote = 0;
File file = new File(outFile);
file.mkdir();
outFile = outFile + "/" + fileName;
OutputStream fwriter = new FileOutputStream(outFile);
wrote = readFromBlob(blob, fwriter);
fwriter.close();
System.out.println("Wrote " + wrote + " bytes to file " + outFile);
}
rs.close();
stmt.close();
conn.close();
}
public static Connection getConnection()
{
Connection con = null;
String dsn = "::";
String uname = "apps";
String passwd = "pwd";
try
{
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@" + dsn;
Class.forName(driver);
con = DriverManager.getConnection(url, uname, passwd);
}
catch (Exception ee)
{
ee.printStackTrace();
}
return con;
}
public static long readFromBlob(Blob blob, OutputStream out) throws SQLException, IOException
{
InputStream in = blob.getBinaryStream();
int length = -1;
long read = 0;
byte[] buf = new byte[bBufLen];
while ((length = in.read(buf)) != -1)
{
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}
}
/**
*
* This code extracts jar file from cz_archives table from the BLOB column.
*
*/
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ExtractBlob
{
public ExtractBlob()
{
}
final static int bBufLen = 4 * 8192;
static String query;
static String outFile;
static Connection conn;
public static void main(String[] args) throws FileNotFoundException, IOException, SQLException
{
String baseFolder = "C:/CZArchives";
String folderName = "";
String fileName = "";
String query = "SELECT 'TEST-Archive' FOLDER_NAME, "
+ "CZArch.ARCHIVE_URL DOCNAME,CZArch.ARCHIVE_BLOB "
+ "FROM CZ_ARCHIVES CZArch, CZ_RP_ENTRIES CZRp "
+ "WHERE CZRp.OBJECT_ID = CZArch.ARCHIVE_ID "
+ "AND CZArch.deleted_flag = '0' "
+ " AND CZRp.OBJECT_TYPE = 'ARC' "
+ "and CZArch.name = 'nameofyourjarfile' ";
//replace nameofyourjarfile with your jar file name
conn = getConnection();
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
Blob blob = null;
while (rs.next())
{
folderName = rs.getString(1);
fileName = rs.getString(2);
// String created = rs.getString(4);
// String updated = rs.getString(5);
blob = rs.getBlob(3);
System.out.println("Got " + folderName);
System.out.println("Got " + fileName);
// System.out.println("Created " + created);
// System.out.println("Updated " + updated);
outFile = baseFolder + "/" + folderName;
outFile = baseFolder;
long wrote = 0;
File file = new File(outFile);
file.mkdir();
outFile = outFile + "/" + fileName;
OutputStream fwriter = new FileOutputStream(outFile);
wrote = readFromBlob(blob, fwriter);
fwriter.close();
System.out.println("Wrote " + wrote + " bytes to file " + outFile);
}
rs.close();
stmt.close();
conn.close();
}
public static Connection getConnection()
{
Connection con = null;
String dsn = "
String uname = "apps";
String passwd = "pwd";
try
{
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@" + dsn;
Class.forName(driver);
con = DriverManager.getConnection(url, uname, passwd);
}
catch (Exception ee)
{
ee.printStackTrace();
}
return con;
}
public static long readFromBlob(Blob blob, OutputStream out) throws SQLException, IOException
{
InputStream in = blob.getBinaryStream();
int length = -1;
long read = 0;
byte[] buf = new byte[bBufLen];
while ((length = in.read(buf)) != -1)
{
out.write(buf, 0, length);
read += length;
}
in.close();
return read;
}
}
Thursday, June 25, 2009
How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x
Identify the OA Framework version in your instance by activating diagnostics and click the "About This Page" from any OAF page. Click the "Technology Components" tab. The OA Framework version in the top row of the table can then be matched to the JDeveloper Patch.
Release 11i
OA Framework 5.10 patch JDeveloper 9i Patch
ATG.PF.H(patch 3438354 or OApps 11.5.10) Patch 4045639 9IJDEVELOPER WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (patch 4017300) Patch 4141787 9IJDEVELOPER WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (patch 4125550) Patch 4573517 Oracle9i JDeveloper with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (patch 4334965) Patch 4725670 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (patch 4676589) Patch 5455514 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (patch 5473858) Patch 6012619 9IJDeveloper With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (patch 5903765) Patch 6739235 9IJDeveloper With OA Extension ARU FOR 11i10 RUP6
Release 12.0
ATG Release 12 Version JDeveloper 10g Patch
12.0.0 Patch 5856648 10g Jdev with OA Extension
12.0.1(patch 5907545) Patch 5856648 10g Jdev with OA Extension
12.0.2(patch 5484000 or 5917344) Patch 6491398 10g Jdev with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3(patch 6141000 or 6077669) Patch 6509325 10g Jdev with OA Extension ARU for R12 RUP3
12.0.4(patch 6435000 or 6272680) Patch 6908968 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12 RUP4
12.0.5(No new ATG code released) No new JDev patch required
12.0.6(patch 6728000 or patch 7237006) Patch 7523554 10G Jdeveloper With OA Extension ARU for R12 RUP6
refer to metalink, doc id : 416708.1
Release 11i
OA Framework 5.10 patch JDeveloper 9i Patch
ATG.PF.H(patch 3438354 or OApps 11.5.10) Patch 4045639 9IJDEVELOPER WITH OA EXTENSION ARU FOR FWK.H
ATG PF CU1 (patch 4017300) Patch 4141787 9IJDEVELOPER WITH OA EXTENSION ARU FOR CU1
ATG PF CU2 (patch 4125550) Patch 4573517 Oracle9i JDeveloper with OA Extension for 11.5.10 CU2
11i.ATG_PF.H RUP3 (patch 4334965) Patch 4725670 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP3
11i.ATG_PF.H RUP4 (patch 4676589) Patch 5455514 9IJDEVELOPER WITH OA EXTENSION ARU FOR 11i10 RUP4
11i.ATG_PF.H RUP5 (patch 5473858) Patch 6012619 9IJDeveloper With OA Extension ARU FOR 11i10 RUP5
11i.ATG_PF.H.RUP6 (patch 5903765) Patch 6739235 9IJDeveloper With OA Extension ARU FOR 11i10 RUP6
Release 12.0
ATG Release 12 Version JDeveloper 10g Patch
12.0.0 Patch 5856648 10g Jdev with OA Extension
12.0.1(patch 5907545) Patch 5856648 10g Jdev with OA Extension
12.0.2(patch 5484000 or 5917344) Patch 6491398 10g Jdev with OA Extension ARU for R12 RUP2 (replaces 6197418)
12.0.3(patch 6141000 or 6077669) Patch 6509325 10g Jdev with OA Extension ARU for R12 RUP3
12.0.4(patch 6435000 or 6272680) Patch 6908968 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12 RUP4
12.0.5(No new ATG code released) No new JDev patch required
12.0.6(patch 6728000 or patch 7237006) Patch 7523554 10G Jdeveloper With OA Extension ARU for R12 RUP6
refer to metalink, doc id : 416708.1
Tuesday, May 26, 2009
Monday, May 18, 2009
OAF - Write Diagnostic statements for debug Purposes:
From your contorller :
import oracle.apps.fnd.framework.OAFwkConstants;
String testmsg = "This is a test diagnostic message";
pageContext.writeDiagnostics(this,"TESTDIAG: = " + testmsg ,OAFwkConstants.STATEMENT);
Then set profile option at user level
FND: Diagnostics - set to Yes
FND: Debug Log Enabled - set to Yes --default to NO
FND: Debug Log Level - set to Statement
FND: Debug Log Filename for Middle-Tier - give a path to a log file on the server. (test.log)
FND: Debug Log Level ---%
run the application and search for your diagnostic message in test.log file.
import oracle.apps.fnd.framework.OAFwkConstants;
String testmsg = "This is a test diagnostic message";
pageContext.writeDiagnostics(this,"TESTDIAG: = " + testmsg ,OAFwkConstants.STATEMENT);
Then set profile option at user level
FND: Diagnostics - set to Yes
FND: Debug Log Enabled - set to Yes --default to NO
FND: Debug Log Level - set to Statement
FND: Debug Log Filename for Middle-Tier - give a path to a log file on the server. (test.log)
FND: Debug Log Level ---%
run the application and search for your diagnostic message in test.log file.
Thursday, May 14, 2009
Thursday, May 7, 2009
OAF - Cant find server.xml ?
How to create server.xml if its not there in your $JAVA_TOP/...server/ folder
Many a times server.xml is not packaged with other objects in server folder for may packages. If you are lucky you may find it, but if you dont, then you will have to create it or ask oracle support to provide one.
I find the approach of recreating it much easier .
Approach1 : (Easy)
Create the business component package for java in Jdev mentioning the path till the server folder. It will create the server.xml file.
Now you have to add all your VO,EO and AM's in this package by selecting the package in jdev and click on the file menu > open button. Select all the files. Now all your VO,EO and AM's will be added to this server.xml
Pros: Simple and faster
Cons: leaves the VO/EO/AM in edit mode at users mercy to do something dumb and modify them.
Approach2 : (little hassle)
Take server.xml from some other folder from your/myprojects/..../server folder and add each and every entry for VO, VL, LOV, AM, EO manually. Agrred its a lot of pain , specially if you have to add 25 objects so
forexample : code from server.xml
SeparateXMLFiles="true"
PackageName="oracle.apps.imc.i.am.creating.this.server" >
FullName="oracle.apps.imc.i.am.creating.this.server.putthenameofAM"
ObjectType="AppModule" >
FullName="oracle.apps.imc.i.am.creating.this.server.putthenameofVO"
ObjectType="ViewObject" >
Now add this server.xml file to your project.jpr, you can see all the objects imported correctly
Pros: when you click on any VO, notice all the options are greyed out, so you cant accidentally modify them.
cons: its hard to manually edit the server.xml and add entries of each object.
But you know what , both approaches work and you can choose whatever works for you.
Many a times server.xml is not packaged with other objects in server folder for may packages. If you are lucky you may find it, but if you dont, then you will have to create it or ask oracle support to provide one.
I find the approach of recreating it much easier .
Approach1 : (Easy)
Create the business component package for java in Jdev mentioning the path till the server folder. It will create the server.xml file.
Now you have to add all your VO,EO and AM's in this package by selecting the package in jdev and click on the file menu > open button. Select all the files. Now all your VO,EO and AM's will be added to this server.xml
Pros: Simple and faster
Cons: leaves the VO/EO/AM in edit mode at users mercy to do something dumb and modify them.
Approach2 : (little hassle)
Take server.xml from some other folder from your
forexample : code from server.xml
SeparateXMLFiles="true"
PackageName="oracle.apps.imc.i.am.creating.this.server" >
FullName="oracle.apps.imc.i.am.creating.this.server.putthenameofAM"
ObjectType="AppModule" >
FullName="oracle.apps.imc.i.am.creating.this.server.putthenameofVO"
ObjectType="ViewObject" >
Now add this server.xml file to your project.jpr, you can see all the objects imported correctly
Pros: when you click on any VO, notice all the options are greyed out, so you cant accidentally modify them.
cons: its hard to manually edit the server.xml and add entries of each object.
But you know what , both approaches work and you can choose whatever works for you.
System.out.println in production code
This is the most commonly asked question in web applications development in jdeveloper including OA Framework, ADF, ejb, web services etc.
Can I leave System.out.println or System.err.println in the production code?
System.out.println comes handy when debugging the application because you can see the messages pretty much easily in the jdeveloper console. And it takes relatively less time than the debugging option in Jdeveloper. So even experienced developers use it for debugging. You may think, "Anyway this doesnot any affect in the production system". But actually System.out.println statements will have serious performance issue in the production system if you fail to remove them in the final code.
The reason is that the application server will have only one output stream and one error stream in the JVM. Hence multiple threads calling the System.out.println() have to be synchronized.
Considering the production system with thousands of users, back end programs, webservices, scheduled process running parralley in the machine. Calling system.out.println() will potentially block the performance of whole system.
Also the application server doesn't redirect the std out to a file, and is lost.
Hence always remove the System.out.println() /System.err.println() from the production code.
Ref : http://prasanna-adf.blogspot.com/2009/04/systemoutprintln.html
Can I leave System.out.println or System.err.println in the production code?
System.out.println comes handy when debugging the application because you can see the messages pretty much easily in the jdeveloper console. And it takes relatively less time than the debugging option in Jdeveloper. So even experienced developers use it for debugging. You may think, "Anyway this doesnot any affect in the production system". But actually System.out.println statements will have serious performance issue in the production system if you fail to remove them in the final code.
The reason is that the application server will have only one output stream and one error stream in the JVM. Hence multiple threads calling the System.out.println() have to be synchronized.
Considering the production system with thousands of users, back end programs, webservices, scheduled process running parralley in the machine. Calling system.out.println() will potentially block the performance of whole system.
Also the application server doesn't redirect the std out to a file, and is lost.
Hence always remove the System.out.println() /System.err.println() from the production code.
Ref : http://prasanna-adf.blogspot.com/2009/04/systemoutprintln.html
debug bc4j objects
How to debug the VO, LOV, poplist queries?
You can see the executed queries and its bind values in the jdeveloper console or messages window. By default, this option is not available in jdeveloper.
To enable the debug console option in Jdev 9i
1. Right click on project and select project properties
2. Navigate Configurations -> Development -> runner
3. Add the following parameter in the java options
-Djbo.debugoutput=console
4. Select OK
5. Rebuild the project and run again to see the debug information in jdeveloper log window
This option would be very helpful in finding out what going on in the bc4j objects. Including LOV queries, poplist queries, VOs etc. Whenever the query is executed through the AM or procedure is called, the bind variable values and executed sql will be displayed in the jdeveloper message window.
You can see the executed queries and its bind values in the jdeveloper console or messages window. By default, this option is not available in jdeveloper.
To enable the debug console option in Jdev 9i
1. Right click on project and select project properties
2. Navigate Configurations -> Development -> runner
3. Add the following parameter in the java options
-Djbo.debugoutput=console
4. Select OK
5. Rebuild the project and run again to see the debug information in jdeveloper log window
This option would be very helpful in finding out what going on in the bc4j objects. Including LOV queries, poplist queries, VOs etc. Whenever the query is executed through the AM or procedure is called, the bind variable values and executed sql will be displayed in the jdeveloper message window.
Friday, April 17, 2009
Using OA Extensions to extend existing Apps 11i OA Framework pages (step by step)
--Got this from Metalink : Found it useful.
PURPOSE
-------
This document are some personal thoughts to describe the steps to add an Extension to an Apps 11i page using the OA Extensions released with 11.5.10
It is not meant to be a commercial strength application, but simply an simple exercise to go through the steps to demonstrate some kind of addition to an existing Apps 11i OA Framework page.
SCOPE & APPLICATION
-------------------
Consultants or Customers who need a guided practise to extend an existing Apps 11i OAF page
Using OA Extensions to extend existing Apps 11i OA Framework pages (step by step)
--------------------------------------------------------------------------------
I decided to do a simple customization to the "Customer Search" page in the " iReceivables Internal, Vision Operations (USA)" responsibility
Internal system is a 11.5.10 CU2 VISION installation, with Windows 2000 RDBMS and middle tier, and also load balanced Linux Middle tier (so I need to deploy Java code onto both middle tier servers)
STEP BY STEP
------------
Create customization in existing package
-----------------------------------------
1) Create new project
In my simple test, I am creating a new OA Project called "mzExtend"I am using application shortname "MZ" and responsibility key "MZCUSTOMSSWA" for my project, as this is already setup on my environment (see Note 216589.1)
In project settings, change the Runner options in configuration section to add the entry "-Djbo.project=mzExtend" to the end of the line.
Also add "F:\oracle\viscomn\java" to the "Additional Classpath" (this is a shared network drive to my OA_JAVA directory) You would have to copy the server.xml to your local PC first, if the Apps 11i instance was on a Unix box.
2) Add existing BC4J Objects to your project
Refer to chapter 9 on the Developers Guide "Extending OA Framework Applications"
The "server.xml" files are shipped with Apps 11i, so you add the server.xml file to your custom project in order to access the original BC4J objects. You need access to the original objects in order to select them in the "extends" field when creating your new object.
Add the file "F:\oracle\viscomn\java\oracle\apps\ar\irec\common\server\server.xml" to the project. This will create a BC4J Package in your custom project.
For my custom page, I need to create a custom VO and AM
oracle.apps.ar.irec.common.server.InternalCustomerSearchByCustomerIdVO
oracle.apps.ar.irec.common.server.CustomerSearchAM
Create a new VO, extending the original
oracle.apps.ar.irec.common.server.mzInternalCustomerSearchByCustomerIdVO
Manually copy the SQL from the original VO, my customization requires me to remove the WHERE clause from the original SQL
Create a new AM, extending the original
oracle.apps.ar.irec.common.server.mzCustomerSearchAM
In the view objects selection, add "mzInternalCustomerSearchByCustomerIdVO"
I am modifying these java files, to enable a default selection to show when the custom page is launched
mzCustomerSearchAMImpl.java
mzInternalCustomerSearchByCustomerIdVOImpl.java
3) Create a new OA Web page called "mzSearchPG.xml"
Simple page, with 4 fields from "mzInternalCustomerSearchByCustomerIdVO"
Add a Page Controller "mzSearchPGCO.java" to send a hard coded customerID to the AM to show customer data on the page when the page is launched (I told you this was a simple example :-) ) See java code in Appendix A below.
Run this page through JDeveloper and check it displays the page, with some data on it..
4) Now I want to deploy this to my Apps 11i instance, so I can run through Apps itself
a) First copy over the compiled objects, if using your custom scheme, this could be easiest achieved by copying over the whole of the D:\Jdev_510\jdevhome\jdev\myclasses\oracle\apps\mz directory to the $OA_JAVA\oracle\apps\mz directory, although you can be more selective if required
NOTE - it is the "myclasses" files you need to copy (the .class not the .java files!)
In this case, I have added classes to the existing ar directory structure, so need to copy the 4 class files from "D:\Jdev_510\jdevhome\jdev\myclasses\oracle\apps\ar\irec\common\server" to the $OA_JAVA/oracle\apps\ar\irec\common\server directory.
b) I have not yet used the "substitution" mechanism in JDev to use custom objects rather than the original ones, but had I done so, I would need to load the.jpx file into the MDS. Follow the instructions in the Developers Guide, chapter 9 "Deploying Customer Extentions" to do this, but you can use a batch file like below:-
REM
REM Batch file to set environment to upload JPX file into Apps database
REM Version : 1.0
REM Author : Mike Shaw
REM Updated : 16th Sept 2004
REM
REM This section is for the PC specific variables
set JDEV_BIN_HOME=D:\Jdev_510\jdevbin
set JDEV_USER_HOME=D:\Jdev_510\jdevhome\jdev
set DB_CONNECTION="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(host=hostname.domain.com)(port=1521))(CONNECT_DATA=(SID=VIS)))"
set JPX2UPLOAD=mzExtend
set APPS_PASSWD=apps
REM End of PC specific variables
REM Set PATH to ensure we are using the right Java.exe
set PATH=%JDEV_BIN_HOME%\jdk\bin;%JDEV_BIN_HOME%\jdev\bin;%PATH%
REM This is what we actually want to run...
call jpximport.bat %JDEV_USER_HOME%\myprojects\%JPX2UPLOAD%.jpx -username apps -password %APPS_PASSWD% -dbconnection %DB_CONNECTION%
pause
REM End of process
This loads the JPX into the MDS, for example to the location
/oracle/apps/ar/irec/common/server/customizations/site/0/mzInternalCustomerSearchByCustomerIdVO
To remove the substitution, you would need to use JDR_UTILS, for example
exec jdr_utils.deleteDocument('/oracle/apps/ar/irec/common/server/customizations/site/0/mzInternalCustomerSearchByCustomerIdVO');
c) Deploy the XML pages into MDS from local PC. Can use following wrapper script
REM
REM Batch file to set environment to upload XML Page files into Apps database
REM Version : 1.0
REM Author : Mike Shaw
REM Updated : 27th Jan 2005
REM
REM This section is for the PC specific variables
set JDEV_BIN_HOME=D:\Jdev_510\jdevbin
set JDEV_USER_HOME=D:\Jdev_510\jdevhome\jdev
set JDEV_PROJECT_HOME=%JDEV_USER_HOME%\myprojects
set DB_CONNECTION="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(host=hostname.domain.com)(port=1521))(CONNECT_DATA=(SID=VIS)))"
REM example of PAGE2UPLOAD mz\comments\bc4jcomponents\CreateCommentPG
REM can also just specify directory to load all pages in that directory
set PAGE2UPLOAD=ar\irec\common\server
set APPS_PASSWD=apps
REM End of PC specific variables
REM Set PATH to ensure we are using the right Java.exe
set PATH=%JDEV_BIN_HOME%\jdk\bin;%JDEV_BIN_HOME%\jdev\bin;%PATH%
REM This is what we actually want to run...
call import.bat %JDEV_PROJECT_HOME%\oracle\apps\%PAGE2UPLOAD% -rootdir %JDEV_PROJECT_HOME% -mmddir %JDEV_BIN_HOME%\jdev\lib\ext\jrad\config\mmd -username apps -password %APPS_PASSWD% -dbconnection %DB_CONNECTION% -jdk13 -validate
pause
REM End of process
Check they are uploaded OK, using the SQL
REM START OF SQL
set serveroutput on
set pagesize 132
exec JDR_UTILS.listContents('/oracle/apps/ar/irec/common/server', true);
REM END OF SQL
The following is a general script to look for all pages in your custom schema
REM START OF SQL
set serveroutput on
set pagesize 132
exec JDR_UTILS.listContents('/oracle/apps/mz', true);
REM END OF SQL
d) Create new Function to call the page, for example:
Function Name = MZ_CREATE_COMMENT
User Function Name = mz Create Comment
Type = SSWA JSP Function (JSP)
HTML Call = OA.jsp?page=/oracle/apps/mz/comments/bc4jcomponents/CreateCommentPG
In my case, I am adding the following:
Function Name = MZ_CUSTOMER_SEARCH
User Function Name = mz Customer Search
Type = SSWA JSP Function (JSP)
HTML Call = OA.jsp?page=/oracle/apps/ar/irec/common/server/mzSearchPG
e) Add function to menu
MZ_CUSTOM_SSWA menu for example.
f) Bounce Apache
g) Test page
Login as user with the responsibility to see the menu
Select the menu function to launch the page and check the results are the same as from JDeveloper
Create similar customization, in custom schema
----------------------------------------------
I really should have created the above customization in my custom schema, so will do so now.
Create a new empty BC4J package called
oracle.apps.mz.sanity.server
For my custom page, I need to create a custom VO and AM
Create a new VO, extending the original
oracle.apps.mz.sanity.server.mzSearchVO
Manually copy the SQL from the original VO, my customization requires me to remove the WHERE clause from the original SQL
Create a new AM, extending the original
oracle.apps.mz.sanity.server.mzSearchAM
In the view objects selection, add "mzSearchVO"
I am modifying these java files, to enable a default selection to show when the custom page is launched
mzSearchAMImpl.java
mzSearchVOImpl.java
3) Create a new OA Web page called "mzNewSearchPG.xml"
Simple page, with 4 fields from "mzSearchVO"
Add a Page Controller "mzNewSearchCO.java" to send a hard coded customerID to the AM to show customer data on the page when the page is launched.
Run this page through JDeveloper and check it displays the page, with some data on it..
4) Deploy the page and java objects to the Apps 11i instance and re-test
5) Once checked that it is working, I am now going to personalize this new screen.
I did not include all the items from the VO in the customization, so will add a new field to the page directly.
Do a Site level personalization, then add an item.
Type = Stylised Text
ID = ConcatenatedAddress
prompt = Address
VO Attribute = ConcatenatedAddress
VO Instance = mzSearchVO1
On saving this personalization and returning to the page, you will see the Address text on the page as well
APPENDIX A
--
-- java code for mzSearchPGCO page controller
-- (comments and spacing stripped out)
--
package oracle.apps.ar.irec.common.server.webui;
import java.io.Serializable;
import oracle.apps.fnd.common.MessageToken;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OADialogPage;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.jbo.domain.Number;
public class mzSearchPGCO extends OAControllerImpl
{
public static final String RCS_ID="$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
-- NOTE this is hard coded value for quickness and simplicity.
String customerId = "8070";
OAApplicationModule am = pageContext.getApplicationModule(webBean);
Serializable[] params = { customerId };
am.invokeMethod("initDetails", params);
}
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
}
} // end of class
IMPORTANT NOTE
--------------
Ensure you are using the correct version of JDeveloper. Review Note 416708.1 to confirm the correct patch number
RELATED DOCUMENTS
-----------------
Note 330236.1 "Configuring JDeveloper For Use With Oracle Applications 11i"
Note 357218.1 "Troubleshooting JDeveloper setup for Oracle Applications"
Note 416708.1 "How to found right version of JDeveloper for eBusiness Suite 11i and 12"
PURPOSE
-------
This document are some personal thoughts to describe the steps to add an Extension to an Apps 11i page using the OA Extensions released with 11.5.10
It is not meant to be a commercial strength application, but simply an simple exercise to go through the steps to demonstrate some kind of addition to an existing Apps 11i OA Framework page.
SCOPE & APPLICATION
-------------------
Consultants or Customers who need a guided practise to extend an existing Apps 11i OAF page
Using OA Extensions to extend existing Apps 11i OA Framework pages (step by step)
--------------------------------------------------------------------------------
I decided to do a simple customization to the "Customer Search" page in the " iReceivables Internal, Vision Operations (USA)" responsibility
Internal system is a 11.5.10 CU2 VISION installation, with Windows 2000 RDBMS and middle tier, and also load balanced Linux Middle tier (so I need to deploy Java code onto both middle tier servers)
STEP BY STEP
------------
Create customization in existing package
-----------------------------------------
1) Create new project
In my simple test, I am creating a new OA Project called "mzExtend"I am using application shortname "MZ" and responsibility key "MZCUSTOMSSWA" for my project, as this is already setup on my environment (see Note 216589.1)
In project settings, change the Runner options in configuration section to add the entry "-Djbo.project=mzExtend" to the end of the line.
Also add "F:\oracle\viscomn\java" to the "Additional Classpath" (this is a shared network drive to my OA_JAVA directory) You would have to copy the server.xml to your local PC first, if the Apps 11i instance was on a Unix box.
2) Add existing BC4J Objects to your project
Refer to chapter 9 on the Developers Guide "Extending OA Framework Applications"
The "server.xml" files are shipped with Apps 11i, so you add the server.xml file to your custom project in order to access the original BC4J objects. You need access to the original objects in order to select them in the "extends" field when creating your new object.
Add the file "F:\oracle\viscomn\java\oracle\apps\ar\irec\common\server\server.xml" to the project. This will create a BC4J Package in your custom project.
For my custom page, I need to create a custom VO and AM
oracle.apps.ar.irec.common.server.InternalCustomerSearchByCustomerIdVO
oracle.apps.ar.irec.common.server.CustomerSearchAM
Create a new VO, extending the original
oracle.apps.ar.irec.common.server.mzInternalCustomerSearchByCustomerIdVO
Manually copy the SQL from the original VO, my customization requires me to remove the WHERE clause from the original SQL
Create a new AM, extending the original
oracle.apps.ar.irec.common.server.mzCustomerSearchAM
In the view objects selection, add "mzInternalCustomerSearchByCustomerIdVO"
I am modifying these java files, to enable a default selection to show when the custom page is launched
mzCustomerSearchAMImpl.java
mzInternalCustomerSearchByCustomerIdVOImpl.java
3) Create a new OA Web page called "mzSearchPG.xml"
Simple page, with 4 fields from "mzInternalCustomerSearchByCustomerIdVO"
Add a Page Controller "mzSearchPGCO.java" to send a hard coded customerID to the AM to show customer data on the page when the page is launched (I told you this was a simple example :-) ) See java code in Appendix A below.
Run this page through JDeveloper and check it displays the page, with some data on it..
4) Now I want to deploy this to my Apps 11i instance, so I can run through Apps itself
a) First copy over the compiled objects, if using your custom scheme, this could be easiest achieved by copying over the whole of the D:\Jdev_510\jdevhome\jdev\myclasses\oracle\apps\mz directory to the $OA_JAVA\oracle\apps\mz directory, although you can be more selective if required
NOTE - it is the "myclasses" files you need to copy (the .class not the .java files!)
In this case, I have added classes to the existing ar directory structure, so need to copy the 4 class files from "D:\Jdev_510\jdevhome\jdev\myclasses\oracle\apps\ar\irec\common\server" to the $OA_JAVA/oracle\apps\ar\irec\common\server directory.
b) I have not yet used the "substitution" mechanism in JDev to use custom objects rather than the original ones, but had I done so, I would need to load the
REM
REM Batch file to set environment to upload JPX file into Apps database
REM Version : 1.0
REM Author : Mike Shaw
REM Updated : 16th Sept 2004
REM
REM This section is for the PC specific variables
set JDEV_BIN_HOME=D:\Jdev_510\jdevbin
set JDEV_USER_HOME=D:\Jdev_510\jdevhome\jdev
set DB_CONNECTION="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(host=hostname.domain.com)(port=1521))(CONNECT_DATA=(SID=VIS)))"
set JPX2UPLOAD=mzExtend
set APPS_PASSWD=apps
REM End of PC specific variables
REM Set PATH to ensure we are using the right Java.exe
set PATH=%JDEV_BIN_HOME%\jdk\bin;%JDEV_BIN_HOME%\jdev\bin;%PATH%
REM This is what we actually want to run...
call jpximport.bat %JDEV_USER_HOME%\myprojects\%JPX2UPLOAD%.jpx -username apps -password %APPS_PASSWD% -dbconnection %DB_CONNECTION%
pause
REM End of process
This loads the JPX into the MDS, for example to the location
/oracle/apps/ar/irec/common/server/customizations/site/0/mzInternalCustomerSearchByCustomerIdVO
To remove the substitution, you would need to use JDR_UTILS, for example
exec jdr_utils.deleteDocument('/oracle/apps/ar/irec/common/server/customizations/site/0/mzInternalCustomerSearchByCustomerIdVO');
c) Deploy the XML pages into MDS from local PC. Can use following wrapper script
REM
REM Batch file to set environment to upload XML Page files into Apps database
REM Version : 1.0
REM Author : Mike Shaw
REM Updated : 27th Jan 2005
REM
REM This section is for the PC specific variables
set JDEV_BIN_HOME=D:\Jdev_510\jdevbin
set JDEV_USER_HOME=D:\Jdev_510\jdevhome\jdev
set JDEV_PROJECT_HOME=%JDEV_USER_HOME%\myprojects
set DB_CONNECTION="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(host=hostname.domain.com)(port=1521))(CONNECT_DATA=(SID=VIS)))"
REM example of PAGE2UPLOAD mz\comments\bc4jcomponents\CreateCommentPG
REM can also just specify directory to load all pages in that directory
set PAGE2UPLOAD=ar\irec\common\server
set APPS_PASSWD=apps
REM End of PC specific variables
REM Set PATH to ensure we are using the right Java.exe
set PATH=%JDEV_BIN_HOME%\jdk\bin;%JDEV_BIN_HOME%\jdev\bin;%PATH%
REM This is what we actually want to run...
call import.bat %JDEV_PROJECT_HOME%\oracle\apps\%PAGE2UPLOAD% -rootdir %JDEV_PROJECT_HOME% -mmddir %JDEV_BIN_HOME%\jdev\lib\ext\jrad\config\mmd -username apps -password %APPS_PASSWD% -dbconnection %DB_CONNECTION% -jdk13 -validate
pause
REM End of process
Check they are uploaded OK, using the SQL
REM START OF SQL
set serveroutput on
set pagesize 132
exec JDR_UTILS.listContents('/oracle/apps/ar/irec/common/server', true);
REM END OF SQL
The following is a general script to look for all pages in your custom schema
REM START OF SQL
set serveroutput on
set pagesize 132
exec JDR_UTILS.listContents('/oracle/apps/mz', true);
REM END OF SQL
d) Create new Function to call the page, for example:
Function Name = MZ_CREATE_COMMENT
User Function Name = mz Create Comment
Type = SSWA JSP Function (JSP)
HTML Call = OA.jsp?page=/oracle/apps/mz/comments/bc4jcomponents/CreateCommentPG
In my case, I am adding the following:
Function Name = MZ_CUSTOMER_SEARCH
User Function Name = mz Customer Search
Type = SSWA JSP Function (JSP)
HTML Call = OA.jsp?page=/oracle/apps/ar/irec/common/server/mzSearchPG
e) Add function to menu
MZ_CUSTOM_SSWA menu for example.
f) Bounce Apache
g) Test page
Login as user with the responsibility to see the menu
Select the menu function to launch the page and check the results are the same as from JDeveloper
Create similar customization, in custom schema
----------------------------------------------
I really should have created the above customization in my custom schema, so will do so now.
Create a new empty BC4J package called
oracle.apps.mz.sanity.server
For my custom page, I need to create a custom VO and AM
Create a new VO, extending the original
oracle.apps.mz.sanity.server.mzSearchVO
Manually copy the SQL from the original VO, my customization requires me to remove the WHERE clause from the original SQL
Create a new AM, extending the original
oracle.apps.mz.sanity.server.mzSearchAM
In the view objects selection, add "mzSearchVO"
I am modifying these java files, to enable a default selection to show when the custom page is launched
mzSearchAMImpl.java
mzSearchVOImpl.java
3) Create a new OA Web page called "mzNewSearchPG.xml"
Simple page, with 4 fields from "mzSearchVO"
Add a Page Controller "mzNewSearchCO.java" to send a hard coded customerID to the AM to show customer data on the page when the page is launched.
Run this page through JDeveloper and check it displays the page, with some data on it..
4) Deploy the page and java objects to the Apps 11i instance and re-test
5) Once checked that it is working, I am now going to personalize this new screen.
I did not include all the items from the VO in the customization, so will add a new field to the page directly.
Do a Site level personalization, then add an item.
Type = Stylised Text
ID = ConcatenatedAddress
prompt = Address
VO Attribute = ConcatenatedAddress
VO Instance = mzSearchVO1
On saving this personalization and returning to the page, you will see the Address text on the page as well
APPENDIX A
--
-- java code for mzSearchPGCO page controller
-- (comments and spacing stripped out)
--
package oracle.apps.ar.irec.common.server.webui;
import java.io.Serializable;
import oracle.apps.fnd.common.MessageToken;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.fnd.framework.OAApplicationModule;
import oracle.apps.fnd.framework.OAException;
import oracle.apps.fnd.framework.OAViewObject;
import oracle.apps.fnd.framework.webui.OAControllerImpl;
import oracle.apps.fnd.framework.webui.OADialogPage;
import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.jbo.domain.Number;
public class mzSearchPGCO extends OAControllerImpl
{
public static final String RCS_ID="$Header$";
public static final boolean RCS_ID_RECORDED =
VersionInfo.recordClassVersion(RCS_ID, "%packagename%");
public void processRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processRequest(pageContext, webBean);
-- NOTE this is hard coded value for quickness and simplicity.
String customerId = "8070";
OAApplicationModule am = pageContext.getApplicationModule(webBean);
Serializable[] params = { customerId };
am.invokeMethod("initDetails", params);
}
public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
{
super.processFormRequest(pageContext, webBean);
}
} // end of class
IMPORTANT NOTE
--------------
Ensure you are using the correct version of JDeveloper. Review Note 416708.1 to confirm the correct patch number
RELATED DOCUMENTS
-----------------
Note 330236.1 "Configuring JDeveloper For Use With Oracle Applications 11i"
Note 357218.1 "Troubleshooting JDeveloper setup for Oracle Applications"
Note 416708.1 "How to found right version of JDeveloper for eBusiness Suite 11i and 12"
Thursday, January 29, 2009
How to Compare Text Files Using diff
If you need to compare two text files in Unix, you're mostly likely to use the diff command.
Today I'll talk about the simplest scenario: you want to compare two files and understand if there are any differences.
Suppose you have two files in /tmp directory:
/tmp/1.txt:
aaa
bbb
ccc
ddd
eee
fff
ggg
and /tmp/2.txt:
bbb
c c
ddd
eee
fff
ggg
hhh
I have deliberately created them so short and simple - this way it's easier to explain how the comparison works. If there are no differences between the files, you will see no output, but if two text files are indeed different, all the text mismatches will be highlighted using the standard diff output:
$ diff /tmp/1.txt /tmp/2.txt
1d0
< aaa
3c2
< ccc
---
> c c
7a7
> hhh
Lines like "1d0" and "3c2" are the coordinates and types of the differences between the two compared files, while lines like "< aaa" and "> hhh" are the differences themselves.
Diff change notation includes 2 numbers and a character between them. Characters tell you what kind of change was discovered:
d - a line was deleted
c - a line was changed
a - a line was added
Number to the left of the character gives you the line number in the original (first) file, and the number to the right of the character tells you the line number in the second file used in comparison.
So, looking at the two text files and the diff output above, you can see what happened:
This means that 1 line was deleted. < aaa suggests that the aaa line is present only in the original file:
1d0
< aaa
And this means that the line number 3 has changed. You can see how this confirms that in the first file the line was "ccc", and in the second it now is "c c".
3c2
< ccc
---
> c c
Finally, this confirms that one new line appeared in the second file, it's "hhh" in the line number 7:
7a7
> hhh
That's all you need to know to start playing with text comparisons
Today I'll talk about the simplest scenario: you want to compare two files and understand if there are any differences.
Suppose you have two files in /tmp directory:
/tmp/1.txt:
aaa
bbb
ccc
ddd
eee
fff
ggg
and /tmp/2.txt:
bbb
c c
ddd
eee
fff
ggg
hhh
I have deliberately created them so short and simple - this way it's easier to explain how the comparison works. If there are no differences between the files, you will see no output, but if two text files are indeed different, all the text mismatches will be highlighted using the standard diff output:
$ diff /tmp/1.txt /tmp/2.txt
1d0
< aaa
3c2
< ccc
---
> c c
7a7
> hhh
Lines like "1d0" and "3c2" are the coordinates and types of the differences between the two compared files, while lines like "< aaa" and "> hhh" are the differences themselves.
Diff change notation includes 2 numbers and a character between them. Characters tell you what kind of change was discovered:
d - a line was deleted
c - a line was changed
a - a line was added
Number to the left of the character gives you the line number in the original (first) file, and the number to the right of the character tells you the line number in the second file used in comparison.
So, looking at the two text files and the diff output above, you can see what happened:
This means that 1 line was deleted. < aaa suggests that the aaa line is present only in the original file:
1d0
< aaa
And this means that the line number 3 has changed. You can see how this confirms that in the first file the line was "ccc", and in the second it now is "c c".
3c2
< ccc
---
> c c
Finally, this confirms that one new line appeared in the second file, it's "hhh" in the line number 7:
7a7
> hhh
That's all you need to know to start playing with text comparisons
The dots in Gmail Id ??
Is this a bug or a feature???? So, basically it doesn’t reeeaaallly matter if I have dots in my userid or not??? Ok. Suppose my mail id really does contain dots, then what happens? (Stupid question???)
Gmail doesn’t recognize dots (.) as characters within a username. This way, you can add and remove dots to your username for desired address variations. messages sent to your.username@gmail.com and y.o.u.r.u.s.e.r.n.a.m.e@gmail.com are delivered to the same inbox, since the characters in the username are the same.
Keep in mind that hyphens (-) and underscores (_) can’t be used in a Gmail address. Also, usernames are case insensitive, so it doesn’t matter if you enter upper case or lower case letters.
If you created your account with a dot in your username and you wish you hadn’t, you can change your ‘Reply-to address.’ To change your reply-to address:
Click ‘Settings’ at the top of any Gmail page.
Enter your username@gmail.com without a dot in the ‘Reply-to address’ field.
Click ‘Save Changes.’
When you log in to Gmail, you need to enter any dots that were originally defined as part of your username. Note: Google Apps for Your Domain does recognize dots. If you’d like to receive mail with a dot (.) in your username, please ask your domain administrator to add the desired username as a nickname in your user account.
Gmail doesn’t recognize dots (.) as characters within a username. This way, you can add and remove dots to your username for desired address variations. messages sent to your.username@gmail.com and y.o.u.r.u.s.e.r.n.a.m.e@gmail.com are delivered to the same inbox, since the characters in the username are the same.
Keep in mind that hyphens (-) and underscores (_) can’t be used in a Gmail address. Also, usernames are case insensitive, so it doesn’t matter if you enter upper case or lower case letters.
If you created your account with a dot in your username and you wish you hadn’t, you can change your ‘Reply-to address.’ To change your reply-to address:
Click ‘Settings’ at the top of any Gmail page.
Enter your username@gmail.com without a dot in the ‘Reply-to address’ field.
Click ‘Save Changes.’
When you log in to Gmail, you need to enter any dots that were originally defined as part of your username. Note: Google Apps for Your Domain does recognize dots. If you’d like to receive mail with a dot (.) in your username, please ask your domain administrator to add the desired username as a nickname in your user account.
Tuesday, January 20, 2009
SQL - How does one implement IF-THEN-ELSE logic in a SELECT statement?
create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
--dont forget to create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
Dont forget to Drop Table YOUR_TABLE after you are done doing this example.
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
--dont forget to create table Emp (Empno Number,Ename varchar2(50),Job varchar2(30),Mgr varchar2(50),Hiredate date,Deptno number);
select * from emp
insert into emp values(1,'name1','job1','Manager1','17-Dec-2008',101 );
insert into emp values(2,'name2','job2','Manager2','14-Dec-2008',102 )
insert into emp values(3,'name3','job3','Manager3','1-Dec-2008',102 );
insert into emp values(4,'name4','job4','Manager4','2-Dec-2008',102 );
insert into emp values(5,'name5','job5','Manager5','3-Dec-2008',102 );
create table test (A number,B number, C number) ;
insert into test values(1,2,3);
insert into test values(3,2,3);
insert into test values(2,3,1);
insert into test values(2,1,3);
insert into test values(2,2,1);
How does one implement IF-THEN-ELSE logic in a SELECT statement?
CASE EXPRESSIONS
SELECT ename,deptno,
CASE WHEN deptno = 101 THEN 'Minimum wage'
WHEN deptno > 102 THEN 'Over paid'
ELSE 'Under paid'
END AS "Salary Status"
FROM emp;
Decode() function
select job,deptno,decode(deptno, 101, 'supervisor', 102, 'HR', 'Unknown')
from emp;
Greatest() or least() functions
select a,b,decode( GREATEST(A,B), A, 'A is greater OR EQUAL than B',
'B is greater than A') from test
NVL and NVL2 can be used to test for NULL values.
NVL(a,b) == if 'a' is null then return 'b'.
SELECT ename,nvl(ename, 'No Name') FROM emp;
NVL2(a,b,c) == if 'a' is not null then return 'b' else return 'c'.
SELECT nvl2(ename, 'Do have a name', 'No Name') FROM emp;
COALESCE() Function
COALESCE() returns the first expression that is not null. Example:
SELECT 'Dear '|| COALESCE(ename, 'Sir or Madam') FROM emp;
NULLIF() Function
NULLIF() returns a NULL value if both parameters are equal in value. The following query would return NULL:
SELECT NULLIF(ename, ename) FROM emp;
Dont forget to Drop Table YOUR_TABLE after you are done doing this example.
SQL Tips2
1. To find The Nth Maximum Salary.
SELECT DISTINCT SAL
FROM EMP A
WHERE &N=(SELECT COUNT (DISTINCT B.SAL) FROM EMP B WHERE A.SAL<=B.SAL);
2. To use Exists Clause. Correlated subquery
SELECT DNAME, DEPTNO
FROM DEPT
WHERE EXISTS (SELECT * FROM EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO)
3. To Find The Not Null Column Alone In A Table.
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE NULLABLE = 'N'
AND TABLE_NAME = 'COUNTRY'
4.To delete The Duplicate Rows Alone in A Table. (frequently asked question)
DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM DEPT GROUP BY DEPTNO HAVING COUNT (*) >=1)
5.To find The Max Salary without MAX Function.
1. SELECT DISTINCT SAL
FROM EMP1 WHERE SAL NOT IN
(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))
2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)
6. To find the database name
select name from v$database;
7. To convert the given number to word
SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;
8. What is the difference between unique and distinct keyword in Oracle.
To summarise:
True:
* Only one primary key is allowed per table (any number of unique keys are allowed).
* Primary key columns automatically become NOT NULL (unique keys can contain nulls).
* UNIQUE and DISTINCT are synonymous in a SELECT list.
Not true:
* A unique key cannot be referenced in a foreign key definition (it can)
* A primary key will have a unique index (it might not)
* DISTINCT and UNIQUE handle LOBs differently (they do not)
* UNIQUE is Oracle syntax while DISTINCT is ANSI (both appear to be ANSI-compliant according to the SQL*Plus flagger option, although I couldn't find this standard documented. Oracle did not support SELECT UNIQUE until 8.1.5.)
9. What is the difference between union and union all
UNION
1. returns all distinct rows selected by either query
2. does the multi level sorting on data starting from first column
3. due to sorting query performance goes down dramatically.
UNION ALL
1. returns all the rows selected by either query including all duplicates.
2. No sorting is applied on the data.
3. No performance issue in this set operation. And it is advisable to use union all instead of union where ever applicable.
SELECT DISTINCT SAL
FROM EMP A
WHERE &N=(SELECT COUNT (DISTINCT B.SAL) FROM EMP B WHERE A.SAL<=B.SAL);
2. To use Exists Clause. Correlated subquery
SELECT DNAME, DEPTNO
FROM DEPT
WHERE EXISTS (SELECT * FROM EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO)
3. To Find The Not Null Column Alone In A Table.
SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE NULLABLE = 'N'
AND TABLE_NAME = 'COUNTRY'
4.To delete The Duplicate Rows Alone in A Table. (frequently asked question)
DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM DEPT GROUP BY DEPTNO HAVING COUNT (*) >=1)
5.To find The Max Salary without MAX Function.
1. SELECT DISTINCT SAL
FROM EMP1 WHERE SAL NOT IN
(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))
2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)
6. To find the database name
select name from v$database;
7. To convert the given number to word
SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;
8. What is the difference between unique and distinct keyword in Oracle.
To summarise:
True:
* Only one primary key is allowed per table (any number of unique keys are allowed).
* Primary key columns automatically become NOT NULL (unique keys can contain nulls).
* UNIQUE and DISTINCT are synonymous in a SELECT list.
Not true:
* A unique key cannot be referenced in a foreign key definition (it can)
* A primary key will have a unique index (it might not)
* DISTINCT and UNIQUE handle LOBs differently (they do not)
* UNIQUE is Oracle syntax while DISTINCT is ANSI (both appear to be ANSI-compliant according to the SQL*Plus flagger option, although I couldn't find this standard documented. Oracle did not support SELECT UNIQUE until 8.1.5.)
9. What is the difference between union and union all
UNION
1. returns all distinct rows selected by either query
2. does the multi level sorting on data starting from first column
3. due to sorting query performance goes down dramatically.
UNION ALL
1. returns all the rows selected by either query including all duplicates.
2. No sorting is applied on the data.
3. No performance issue in this set operation. And it is advisable to use union all instead of union where ever applicable.
SQL Tips
1. Multiple row generation from DUAL
select rownum i from dual connect by level <= 16
Above query works only in 10g
select rownum from dual a, dual b connect by level <>This query works on both 9i and 10g
2. Lock Query
SELECT s.username dbuser,
owner || '.' || object_name "Object",
s.osuser,
s.PROGRAM,
lk.sid,
lk.type lock_type,
decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode
FROM v$lock lk,
v$session s,
dba_objects ao
WHERE lk.lmode > 1
AND object_name LIKE 'JAI%'
AND s.username IS NOT NULL
AND lk.sid = s.sid
AND ao.object_id(+) = lk.id1
To find and kill the locking program..
select session_id from dba_dml_locks where name = 'JAI_RCV_HEADERS' ;
select sid, serial# from v$session where sid in ( );
alter system kill session ',';
repeat alter stmt for all sids obt. in 2nd query
3. Desc query
In oracle, you would have used desc to describe the table. Now let us see, how to generate DESC output through sql query.
SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
FROM all_tab_columns
WHERE TABLE_NAME = 'PO_VENDORS';
4. How to disable a not null constraint.
In case you have created a database column as 'not null'. And you don't want it to be 'Not null' then how will you change it?
eg:
create table temp_temp ( a number not null, b number not null)
And your want to remove "Not null" constraint for columns A.
Ans:
sql> desc temp_temp:
Name Null? Type
------------------------------- -------- ----
A NOT NULL NUMBER
B NOT NULL NUMBER
Get the Not null Constraint name from the database.
SQL> select constraint_name, search_condition from all_constraints where table_name='TEMP_TEMP';
CONSTRAINT_NAME SEARCH_CONDITION
----------------- ---------------------
SYS_C00258673 "A" IS NOT NULL
SYS_C00258674 "B" IS NOT NULL
In the results look for the row with name of the column(like "A" is not null) in the search condition. Take the corresponding constraint_name
SQL> alter table temp_temp drop constraint SYS_C00258673;
Table altered.
SQL> desc temp_temp
Name Null? Type
------------------------------- -------- ----
A NUMBER
B NOT NULL NUMBER
Not null constraint is dropped from the Table.
5. How to convert row values into columns. or how to perform multi dimensional query?
Run the script for this example:
--drop table temp_temp ; --if there is any already present
create table temp_temp ( dept varchar2(100), year number ,amount number)
insert into temp_temp values('A',2005,100);
insert into temp_temp values('B',2005,500);
insert into temp_temp values('C',2005,344);
insert into temp_temp values('A',2006,400);
insert into temp_temp values('B',2006,122);
select * from temp_temp;
For Eg Dept Year Amount
A 2005 100
B 2005 500
C 2005 344
A 2006 400
B 2006 122
Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.
Dept 2005 2006
A 100 400
B 500 122
C 344 0
The challenge is to bring the row values into columns. And here is the solution.
SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
sum(decode(year,'2006',amount,0)) "2006"
from temp_temp
group by dept;
6. How to give if condition within the column.
This is so simple problem, you can use Case when clause to do that.
select ename, eid,case when salary <=1000 then 'A'
when salary <=2000 then 'B'
when salary <=3000 then 'C'
when salary <=4000 then 'D'
when salary <=5000 then 'E'
else 'F'
end from emp;
But how will you do switch-case without use when clause?
Here is the query to do the same stuff.
select ename,
eid,
decode(greatest(salary,0),least(salary,1000),'A',decode(greatest(salary,1001)
,least(salary,2000),'B',decode(greatest(salary,2001),least(salary,3000),'C',decode(greatest (salary,3001),least(salary,4000),'D',decode(greatest(salary,4001),least(salary,5000),'E','F')))))
from emp order by 3;
7. How would you select the range of rows from the table?
Say for example, you wanted to select the rows from 2 to 5.
Solution:
select dept, no
from (select dept, rownum no from temp_temp)
where no between 2 and 5;
8. How can one dump/ examine the exact content of a database column?
SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;
DUMP (COL1)
----------------
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
select rownum i from dual connect by level <= 16
Above query works only in 10g
select rownum from dual a, dual b connect by level <>This query works on both 9i and 10g
2. Lock Query
SELECT s.username dbuser,
owner || '.' || object_name "Object",
s.osuser,
s.PROGRAM,
lk.sid,
lk.type lock_type,
decode(lk.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.lmode)) lockmode,
decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', to_char(lk.request)) requestmode
FROM v$lock lk,
v$session s,
dba_objects ao
WHERE lk.lmode > 1
AND object_name LIKE 'JAI%'
AND s.username IS NOT NULL
AND lk.sid = s.sid
AND ao.object_id(+) = lk.id1
To find and kill the locking program..
select session_id from dba_dml_locks where name = 'JAI_RCV_HEADERS' ;
select sid, serial# from v$session where sid in ( );
alter system kill session ',';
repeat alter stmt for all sids obt. in 2nd query
3. Desc query
In oracle, you would have used desc to describe the table. Now let us see, how to generate DESC output through sql query.
SELECT column_name || ' ' || decode(nullable, 'N', 'not null', ' ') || ' ' || data_type ||
decode(data_type, 'NUMBER', decode('' || nvl(data_precision, -1), '-1', '', '(' || data_precision || ',' || data_scale || ')'),
'(' || data_length || ')') fields
FROM all_tab_columns
WHERE TABLE_NAME = 'PO_VENDORS';
4. How to disable a not null constraint.
In case you have created a database column as 'not null'. And you don't want it to be 'Not null' then how will you change it?
eg:
create table temp_temp ( a number not null, b number not null)
And your want to remove "Not null" constraint for columns A.
Ans:
sql> desc temp_temp:
Name Null? Type
------------------------------- -------- ----
A NOT NULL NUMBER
B NOT NULL NUMBER
Get the Not null Constraint name from the database.
SQL> select constraint_name, search_condition from all_constraints where table_name='TEMP_TEMP';
CONSTRAINT_NAME SEARCH_CONDITION
----------------- ---------------------
SYS_C00258673 "A" IS NOT NULL
SYS_C00258674 "B" IS NOT NULL
In the results look for the row with name of the column(like "A" is not null) in the search condition. Take the corresponding constraint_name
SQL> alter table temp_temp drop constraint SYS_C00258673;
Table altered.
SQL> desc temp_temp
Name Null? Type
------------------------------- -------- ----
A NUMBER
B NOT NULL NUMBER
Not null constraint is dropped from the Table.
5. How to convert row values into columns. or how to perform multi dimensional query?
Run the script for this example:
--drop table temp_temp ; --if there is any already present
create table temp_temp ( dept varchar2(100), year number ,amount number)
insert into temp_temp values('A',2005,100);
insert into temp_temp values('B',2005,500);
insert into temp_temp values('C',2005,344);
insert into temp_temp values('A',2006,400);
insert into temp_temp values('B',2006,122);
select * from temp_temp;
For Eg Dept Year Amount
A 2005 100
B 2005 500
C 2005 344
A 2006 400
B 2006 122
Here you need to display the report in following format. That is comparative sales analysis of each department between 2005 and 2006.
Dept 2005 2006
A 100 400
B 500 122
C 344 0
The challenge is to bring the row values into columns. And here is the solution.
SELECT dept, sum(decode(year,'2005',amount,0)) "2005",
sum(decode(year,'2006',amount,0)) "2006"
from temp_temp
group by dept;
6. How to give if condition within the column.
This is so simple problem, you can use Case when clause to do that.
select ename, eid,case when salary <=1000 then 'A'
when salary <=2000 then 'B'
when salary <=3000 then 'C'
when salary <=4000 then 'D'
when salary <=5000 then 'E'
else 'F'
end from emp;
But how will you do switch-case without use when clause?
Here is the query to do the same stuff.
select ename,
eid,
decode(greatest(salary,0),least(salary,1000),'A',decode(greatest(salary,1001)
,least(salary,2000),'B',decode(greatest(salary,2001),least(salary,3000),'C',decode(greatest (salary,3001),least(salary,4000),'D',decode(greatest(salary,4001),least(salary,5000),'E','F')))))
from emp order by 3;
7. How would you select the range of rows from the table?
Say for example, you wanted to select the rows from 2 to 5.
Solution:
select dept, no
from (select dept, rownum no from temp_temp)
where no between 2 and 5;
8. How can one dump/ examine the exact content of a database column?
SELECT DUMP (col1) FROM tab1 WHERE cond1 = val1;
DUMP (COL1)
----------------
Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.
Find the OA Application/IDs/ShortNames
FND_APPLICATION_VL is a view that joins the translated, language
specific values from FND_APPLICATION_TL with the non-displayed
data from FND_APPLICATION to form complete application
entities. Oracle Application Object Library uses this view
to display information in languages installed at your
site
select * from FND_APPLICATION_VL where application_id=702 (your app id)
specific values from FND_APPLICATION_TL with the non-displayed
data from FND_APPLICATION to form complete application
entities. Oracle Application Object Library uses this view
to display information in languages installed at your
site
select * from FND_APPLICATION_VL where application_id=702 (your app id)
Monday, January 19, 2009
User_source view for source code
How to view source code of a Function ?
How to view source code of a Prodcedure ?
How to view source code of a Package ?
How to view source code of a Trigger ?
you can view the source code of a function or a procedure by using data dictionary views. All the PL SQL objects like types, procedure, functions, Packages code can be viewed from a view called “USER_SOURCE“.
User_source data dictionary view provides the source code of all the PL SQL objects.
Each line of code represents a single row in this dictionary view.
If you want to view the source of PL SQL OBJECTS created by you then use dictionary view “USER_SOURCE“.
If you want to view the source of PL SQL OBJECTS created by others(other users) but have an execute permisssion on the OBJECTS, then use dictionary view “ALL_SOURCE“.
IF you have ‘SELECT ANY DICTIONARY’ Previliege, then you can view the source of all the pl/sql objects from the dictionary view DBA_SOURCE.
want to view the code of a FUNCTION in oracle ?
select * from user_source where type='FUNCTION' AND NAME='FUNCTION_NAME' ORDER BY LINE;
want to view the code of a PROCEDURE in oracle ?
select * from USER_SOURCE where type='PROCEDURE' AND NAME='PROCEDURE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE SPECIFICATION in oracle ?
select * from USER_SOURCE where type='PACKAGE' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE BODY in oracle ?
select * from USER_SOURCE where type='PACKAGE BODY' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a TRIGGER in oracle ?
select * from USER_SOURCE where type='TRIGGER' AND NAME='TRIGGER_NAME' ORDER BY LINE;
want to view source code of a procedure created by another user?
select * from user_source where type='PROCEDURE' and NAME='PROCEDURE_NAME' and owner='USERS_NAME' order by line;
want to view the source code of a Function created by another user in oracle ?
select * from user_source where type='FUNCTION' AND OWNER='USERS_NAME' AND NAME='FUNCTION_NAME' ORDER BY LINE;
How to view source code of a Prodcedure ?
How to view source code of a Package ?
How to view source code of a Trigger ?
you can view the source code of a function or a procedure by using data dictionary views. All the PL SQL objects like types, procedure, functions, Packages code can be viewed from a view called “USER_SOURCE“.
User_source data dictionary view provides the source code of all the PL SQL objects.
Each line of code represents a single row in this dictionary view.
If you want to view the source of PL SQL OBJECTS created by you then use dictionary view “USER_SOURCE“.
If you want to view the source of PL SQL OBJECTS created by others(other users) but have an execute permisssion on the OBJECTS, then use dictionary view “ALL_SOURCE“.
IF you have ‘SELECT ANY DICTIONARY’ Previliege, then you can view the source of all the pl/sql objects from the dictionary view DBA_SOURCE.
want to view the code of a FUNCTION in oracle ?
select * from user_source where type='FUNCTION' AND NAME='FUNCTION_NAME' ORDER BY LINE;
want to view the code of a PROCEDURE in oracle ?
select * from USER_SOURCE where type='PROCEDURE' AND NAME='PROCEDURE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE SPECIFICATION in oracle ?
select * from USER_SOURCE where type='PACKAGE' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a PACKAGE BODY in oracle ?
select * from USER_SOURCE where type='PACKAGE BODY' AND NAME='PACKAGE_NAME' ORDER BY LINE;
want to view the code of a TRIGGER in oracle ?
select * from USER_SOURCE where type='TRIGGER' AND NAME='TRIGGER_NAME' ORDER BY LINE;
want to view source code of a procedure created by another user?
select * from user_source where type='PROCEDURE' and NAME='PROCEDURE_NAME' and owner='USERS_NAME' order by line;
want to view the source code of a Function created by another user in oracle ?
select * from user_source where type='FUNCTION' AND OWNER='USERS_NAME' AND NAME='FUNCTION_NAME' ORDER BY LINE;
Tuesday, January 13, 2009
CZ - --Query to find the difference b/w 2 configurations :
--Query to find the difference b/w 2 configurations :
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :1
and CFG.config_rev_nbr = :2
MINUS
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :3
and CFG.config_rev_nbr = :4
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :1
and CFG.config_rev_nbr = :2
MINUS
select
CFG.ps_node_name Name,
TXT.TEXT_STR Description,
CFG.item_num_val Quantity
from
CZ_CONFIG_ITEMS CFG,
CZ_INTL_TEXTS TXT,
CZ_PS_NODES ND
where CFG.inventory_item_id is not null
and ND.intl_text_id=TXT.intl_text_id
and ND.ps_node_id=CFG.ps_node_id
and CFG.config_hdr_id = :3
and CFG.config_rev_nbr = :4
CZ - List published models in Configurator Repository
select (Select vl.application_name
from fnd_application_vl vl
where vl.application_id = ma.fnd_application_id) app_name,
ma.fnd_application_id,
ma.publication_id,
mp.remote_publication_id,
p.Name Model_Name,
p.Desc_text,
ma.publication_mode,
ma.product_key,
(Select mtl.segment1
from mtl_system_items mtl where mtl.inventory_item_id =
ma.inventory_item_id and mtl.organization_id = ma.bom_explosion_org_id) ITEM,
ma.start_date,
ma.disable_date,
ma.server_id,
ma.ui_def_id,
you.Name Usage
--, ma.*
from
cz_model_applicabilities_v ma,
cz_model_publications mp,
cz_model_usages you,
cz_devl_projects p
where
you.model_usage_id = ma.usage_id
and ma.publication_id = mp.publication_id
and p.devl_project_id = ma.model_id
and mp.deleted_flag = 0
and mp.disabled_flag = 0
and ma.inventory_item_id is not null
and fnd_application_id = 880
and language = 'US'
and mp.source_target_flag = 'T'
and Decode (mp.UI_STYLE, 0, 'DHTML', 3, 'APPLET', 7, 'JRAD', 'NA') = 'JRAD'
and upper(mp.publication_mode) =
(
select upper(
COALESCE
(
(select v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = FND_GLOBAL.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id)
)
) PUB_MODE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_MODE'
)
and you.name =
(
select
COALESCE
(
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = fnd_global.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id
)
) USAGE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_USAGE'
)
Order by model_name
-----------------------------------------
--debugging scripts :
SELECT * FROM cz.cz_db_logs WHERE message_id='353320' --publication id
SELECT model_id,publication_id from cz_model_publications where export_status = 'ERR'
SELECT intl_text_id, persistent_intl_text_id,text_str,ui_def_id FROM cz_intl_texts where persistent_intl_Text_id = 33125520; --persistent id
SELECT model_id,ui_def_id,intl_text_id,creation_date from cz_intl_texts where persistent_intl_text_id = 33125520 --persistent id
SELECT intl_text_id, text_str, persistent_intl_text_id, seeded_flag, ui_def_id, model_id FROM cz_intl_texts where ui_def_id = 23080 AND deleted_flag = 0;
SELECT * from cz_model_publications WHERE object_type = 'UIT' ;
select ui_def_id from cz_ui_defs c
where c.DEVL_PROJECT_ID=(select max(DEVL_PROJECT_ID)
from cz_devl_projects dp , mtl_system_items_b msi
where dp.inventory_item_id=msi.INVENTORY_ITEM_ID
and segment1= 'AT2200-10H')
from fnd_application_vl vl
where vl.application_id = ma.fnd_application_id) app_name,
ma.fnd_application_id,
ma.publication_id,
mp.remote_publication_id,
p.Name Model_Name,
p.Desc_text,
ma.publication_mode,
ma.product_key,
(Select mtl.segment1
from mtl_system_items mtl where mtl.inventory_item_id =
ma.inventory_item_id and mtl.organization_id = ma.bom_explosion_org_id) ITEM,
ma.start_date,
ma.disable_date,
ma.server_id,
ma.ui_def_id,
you.Name Usage
--, ma.*
from
cz_model_applicabilities_v ma,
cz_model_publications mp,
cz_model_usages you,
cz_devl_projects p
where
you.model_usage_id = ma.usage_id
and ma.publication_id = mp.publication_id
and p.devl_project_id = ma.model_id
and mp.deleted_flag = 0
and mp.disabled_flag = 0
and ma.inventory_item_id is not null
and fnd_application_id = 880
and language = 'US'
and mp.source_target_flag = 'T'
and Decode (mp.UI_STYLE, 0, 'DHTML', 3, 'APPLET', 7, 'JRAD', 'NA') = 'JRAD'
and upper(mp.publication_mode) =
(
select upper(
COALESCE
(
(select v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = FND_GLOBAL.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id)
)
) PUB_MODE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_MODE'
)
and you.name =
(
select
COALESCE
(
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_user you
where
v.level_id = 10004
and you.user_name = fnd_global.user_name
and v.level_value = you.user_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_responsibility_vl are
where
v.level_id = 10003
and are.responsibility_name = fnd_global.resp_name
and v.level_value = are.responsibility_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v,
fnd_application_vl a
where
v.level_id = 10002
and a.application_name = fnd_global.application_short_name
and v.level_value = a.application_id
and v.profile_option_id = o.profile_option_id),
(select
v.profile_option_value
from
fnd_profile_option_values v
where
v.level_id = 10001
and v.profile_option_id = o.profile_option_id
)
) USAGE
from
fnd_profile_options o
where
o.profile_option_name = 'CZ_PUBLICATION_USAGE'
)
Order by model_name
-----------------------------------------
--debugging scripts :
SELECT * FROM cz.cz_db_logs WHERE message_id='353320' --publication id
SELECT model_id,publication_id from cz_model_publications where export_status = 'ERR'
SELECT intl_text_id, persistent_intl_text_id,text_str,ui_def_id FROM cz_intl_texts where persistent_intl_Text_id = 33125520; --persistent id
SELECT model_id,ui_def_id,intl_text_id,creation_date from cz_intl_texts where persistent_intl_text_id = 33125520 --persistent id
SELECT intl_text_id, text_str, persistent_intl_text_id, seeded_flag, ui_def_id, model_id FROM cz_intl_texts where ui_def_id = 23080 AND deleted_flag = 0;
SELECT * from cz_model_publications WHERE object_type = 'UIT' ;
select ui_def_id from cz_ui_defs c
where c.DEVL_PROJECT_ID=(select max(DEVL_PROJECT_ID)
from cz_devl_projects dp , mtl_system_items_b msi
where dp.inventory_item_id=msi.INVENTORY_ITEM_ID
and segment1= 'AT2200-10H')
Customization of Reports in Oracle Apps
Oracle Reports will become a thing of past in Fusion, however it will still demand resources for the next 5yrs or so.
Question: I have been asked to customize Invoice Print program which happens to be an Oracle Report. What will be the steps, that I must follow.
Answer : Follow the steps below.
1. You will be told the name of the existing report that must be customized. Note down the exact name and query that name in Concurrent Program screen. Click on “Copy Program button” selecting checkbox option “Copy Parameters”. This will help you copy the current program definition to custom version of the program.
Also note down the name of the executable as it appears in concurrent program definition screen.
2. In same responsibility i.e. Application Developer, navigate to screen concurrent executable and query on the field labeled "Executable Short Name".
Note down the application within which it is registered. If the application is Oracle Receivables, then you must go to the database server and get hold the file named RAXINV.rdf in $AR_TOP/reports/US.
3. Copy that file to your custom AR Top directory. Basically that is the directory where custom reports for AR will be deployed..
cd $XXAR_TOP/reports/us
cp $AR_TOP/reports/us/RAXINV.rdf $XXAR_TOP/reports/us
Effectively you have now done the following:-
1. Made the custom version of report registered with XXAR application. If you worked for say company named EA, then this might have been $EAAR_TOP/reports/US
2. When you run that report, Oracle concurrent manager will search for that report in $XXAR_TOP/reports/US
The report will be found there, and executed.
Note: We haven’t made any changes as yet. Also, you need to include the new concurrent program name in the relevant request group.
Now you can ftp that report to your pc, make modifications for necessary customizations, and then ftp that piece of rdf back to the server. Run it again, to see it working.
Some important tips:-
1. Avoid writing SQL in format trigger, although in rare cases it becomes necessary to do so.
2. Learn from Oracle's Report, by reverse engineering them.
3. Do not write a formula column for something which can be achieved by amending the query in data group itself.
4. Do not hardcode things like Currency Formatting. Have a look at Oracle's Amount fields, and use the same user exit.
5. srw2.message can be used for minor debugging, as those messages will appear in the log file of the concurrent program.
6. You can set the trace checkbox against the concurrent program definition, to generate SQL Trace. This trace will not produce bind variable values though.
7. Join between two queries in data group will always be outerjoined, by default.
8. Avoid filters on Data Group queries. Try to implement that logic within the query itself.
Reference :http://oracle.anilpassi.com/customization-of-reports-in-oracle-apps.html
Question: I have been asked to customize Invoice Print program which happens to be an Oracle Report. What will be the steps, that I must follow.
Answer : Follow the steps below.
1. You will be told the name of the existing report that must be customized. Note down the exact name and query that name in Concurrent Program screen. Click on “Copy Program button” selecting checkbox option “Copy Parameters”. This will help you copy the current program definition to custom version of the program.
Also note down the name of the executable as it appears in concurrent program definition screen.
2. In same responsibility i.e. Application Developer, navigate to screen concurrent executable and query on the field labeled "Executable Short Name".
Note down the application within which it is registered. If the application is Oracle Receivables, then you must go to the database server and get hold the file named RAXINV.rdf in $AR_TOP/reports/US.
3. Copy that file to your custom AR Top directory. Basically that is the directory where custom reports for AR will be deployed..
cd $XXAR_TOP/reports/us
cp $AR_TOP/reports/us/RAXINV.rdf $XXAR_TOP/reports/us
Effectively you have now done the following:-
1. Made the custom version of report registered with XXAR application. If you worked for say company named EA, then this might have been $EAAR_TOP/reports/US
2. When you run that report, Oracle concurrent manager will search for that report in $XXAR_TOP/reports/US
The report will be found there, and executed.
Note: We haven’t made any changes as yet. Also, you need to include the new concurrent program name in the relevant request group.
Now you can ftp that report to your pc, make modifications for necessary customizations, and then ftp that piece of rdf back to the server. Run it again, to see it working.
Some important tips:-
1. Avoid writing SQL in format trigger, although in rare cases it becomes necessary to do so.
2. Learn from Oracle's Report, by reverse engineering them.
3. Do not write a formula column for something which can be achieved by amending the query in data group itself.
4. Do not hardcode things like Currency Formatting. Have a look at Oracle's Amount fields, and use the same user exit.
5. srw2.message can be used for minor debugging, as those messages will appear in the log file of the concurrent program.
6. You can set the trace checkbox against the concurrent program definition, to generate SQL Trace. This trace will not produce bind variable values though.
7. Join between two queries in data group will always be outerjoined, by default.
8. Avoid filters on Data Group queries. Try to implement that logic within the query itself.
Reference :http://oracle.anilpassi.com/customization-of-reports-in-oracle-apps.html
Subscribe to:
Posts (Atom)