Friday, 22 August 2008

Unable to see WYSIWYG in JSP Visual Editor of JDeveloper

When we design an ADF page in Jdeveloper, the visual editor usually displays WYSIWYG of the page structure. But based under some circustances, we may not be getting the WYSIWYG. But only boxes of the tags in the editor.

Check this metalink note for the full detail of the issue and the resolution.

Cheers,
Ganesh

Thursday, 21 August 2008

Auditing in Oracle Applications

Auditing database tables is a mandatory requirement for Enterprise Level applications.
Oracle Applications provides the Auditing capability built-in.

For HRMS there is are some additional steps required, that is in the next post.

The following are the steps to be followed:

1.Choose the System Administrator Responsibility and navigate to Security->AuditTrail->Install and make sure that Audit Enabled checkbox is set for the schema in which the table is present.

2.Goto Security->AuditTrail->Tables and select the table by clicking the Torch icon in the top.Now choose the columns you want to audit by adding the columns one by one.

3.Navigate to Security->AuditTrail->Groups
Either define a new Group and add the table you want to or add it to any existing group for the application of your table.

Steps 2 and 3 can be done in any order.

Now we have to run "AuditTrail Update Tables" concurrent program and Auditing is enabled for the table!

Cheers,
Ganesh

Interesting SQL

I got the following mail from one of my classmates:

Hey Ganesh,

I need some help.
I need a SQL query to perform the following:

Following is the requirement. (Database – Oracle9i , Language - Java)

1. Consider there is a table Table1.

2. Records are to be obtained from table based on three columns. Let us call the columns as Column1, Column2 and Column3. Values for these will be provided by the user.

3. Following is the Rule
Records to be retrieved with the following criteria
1. Rule1 - Records where values given by user
Exactly match Column1 + Exactly match Column2 + Column3 contains the specified string.
2. Rule2 - Records where values given by user
Exactly match Column1 + Exactly match Column2
3. Rule3 - Records where values given by user
Exactly match Column1 + Column3 contains the specified string.
4. Rule4 - Records where values given by user
Column3 contains the specified string.

Records need to be ordered as above list, i.e records for point 1 should come first, for point 2 should come next and so on. And there shouldn’t be any duplicates.


I have a sample query but this will have duplicates (Empno’s selected in query 1 will be repeated in query 2 & 3, empno’s from 2 will be repeated in 3). Assume empno is key

select EMPNO, ename, 11 as colorder from emp where (sal = 100 and deptno = 10 and job = 'CLERK')
union
select EMPNO, ename, 22 as colorder from emp where (sal = 100 and deptno = 10)
union
select EMPNO, ename, 33 as colorder from emp where sal = 100
order by 3


I think the result from the above query can be easily manipulated by a program to remove duplicates. But I wanted try this in SQL itself and as efficiently as possible.
Let me know if you know a way to do this. Thanks.


And I mailed the following query for the same.. Not sure if there is a more sophisticated solution..

select empno,ename, case
when sal >2000 and deptno = 10 and job = 'CLERK' THEN
11
when sal>2000 and deptno = 10 then
22
when sal>2000 then
33
ELSE
0
end dummy
from emp


Cheers
Ganesh