tp's blog | 一路走来

You wish to update rows in one table using values from another. For example, you have a table called NEW_SAL, which holds the new salaries for certain employees. The contents of table NEW_SAL are:

select *

from new_sal

DEPTNO        SAL

—— ———-

10       4000

Column DEPTNO is the primary key of table NEW_SAL. You want to update the salaries and commission of certain employees in table EMP using values table NEW_SAL if there is a match between EMP.DEPTNO and NEW_SAL.DEPTNO, update EMP.SAL to NEW_SAL.SAL, and update EMP.COMM to 50% of NEW_SAL.SAL. The rows in EMP are as follows:

select deptno,ename,sal,comm

from emp

order by 1

DEPTNO ENAME             SAL       COMM

—— ———- ———- ———-

10 CLARK           2450

10 KING            5000

10 MILLER          1300

20 SMITH            800

20 ADAMS           1100

20 FORD            3000

20 SCOTT           3000

20 JONES           2975

30 ALLEN           1600         300

30 BLAKE           2850

30 MARTIN          1250        1400

30 JAMES            950

30 TURNER          1500           0

30 WARD            1250         500

Solution

Use a join between NEW_SAL and EMP to find and return the new COMM values to the UPDATE statement. It is quite common for updates such as this one to be performed via correlated subquery. Another technique involves creating a view (traditional or inline, depending on what your database supports), then updating that view.

DB2 and MySQL

Use a correlated subquery to set new SAL and COMM values in EMP. Also use a correlated subquery to identify which rows from EMP should be updated:

1 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2

2                                      from new_sal ns

3                                     where ns.deptno=e.deptno)

4  where exists ( select null

5                   from new_sal ns

6
where ns.deptno = e.deptno )

Oracle

The method for the DB2 solution will certainly work for Oracle, but as an alternative, you can update an inline view:

1 update (

2  select e.sal as emp_sal, e.comm as emp_comm,

3         ns.sal as ns_sal, ns.sal/2 as ns_comm

4    from emp e, new_sal ns

5   where e.deptno = ns.deptno

6 ) set emp_sal = ns_sal, emp_comm = ns_comm

PostgreSQL

The method used for the DB2 solution will work for PostgreSQL, but as an alternative you can (quite conveniently) join directly in the UPDATE statement:

1 update emp

2    set sal = ns.sal,

3        comm = ns.sal/2

4   from new_sal ns

5  where ns.deptno = emp.deptno

SQL Server

The method used for the DB2 solution will work for SQL Server, but as an alternative you can (similarly to the PostgreSQL solution) join directly in the UPDATE statement:

1 update e

2    set e.sal  = ns.sal,

3        e.comm = ns.sal/2

4   from emp e,

5        new_sal ns

6  where ns.deptno = e.deptno

Discussion

Before discussing the different solutions, I’d like to mention something important regarding updates that use queries to supply new values. A WHERE clause in the subquery of a correlated update is not the same as the WHERE clause of the table being updated. If you look at the UPDATE statement in the “Problem” section, the join on DEPTNO between EMP and NEW_SAL is done and returns rows to the SET clause of the UPDATE statement. For employees in DEPTNO 10, valid values are returned because there is a match DEPTNO in table NEW_SAL. But what about employees in the other departments? NEW_SAL does not have any other departments, so the SAL and COMM for employees in DEPTNOs 20 and 30 are set to NULL. Unless you are doing so via LIMIT or TOP or whatever mechanism your vendor supplies for limiting the number of rows returned in a result set, the only way to restrict rows from a table in SQL is to use a WHERE clause. To correctly perform this UPDATE, use a WHERE clause on the table being updated along with a WHERE clause in the correlated subquery.

DB2 and MySQL

To ensure you do not update every row in table EMP, remember to include a correlated subquery in the WHERE clause of the UPDATE. Performing the join (the correlated subquery) in the SET clause is not enough. By using a WHERE clause in the UPDATE, you ensure that only rows in EMP that match on DEPTNO to table NEW_SAL are updated. This holds true for all RDBMSs.

Oracle

In the Oracle solution using the update join view, you are using equi-joins to determine which rows will be updated. You can confirm which rows are being updated by executing the query independently. To be able to successfully use this type of UPDATE, you must first understand the concept of key-preservation. The DEPTNO column of the table NEW_SAL is the primary key of that table, thus its values are unique within the table. When joining between EMP and NEW_SAL, however, NEW_SAL.DEPTNO is not unique in the result set, as can be seen below:

select e.empno, e.deptno e_dept, ns.sal, ns.deptno ns_deptno

from emp e, new_sal ns

where e.deptno = ns.deptno

EMPNO     E_DEPT        SAL  NS_DEPTNO

—– ———- ———- ———-

7782         10       4000         10

7839         10       4000         10

7934         10       4000         10

To enable Oracle to update this join, one of the tables must be key-preserved, meaning that if its values are not unique in the r
esult set, it should at least be unique in the table it comes from. In this case NEW_SAL has a primary key on DEPTNO, which makes it unique in the table. Because it is unique in its table, it may appear multiple times in the result set and will still be considered key-preserved, thus allowing the update to complete successfully.

PostgreSQL and SQL Server

The syntax is a bit different between these two platforms, but the technique is the same. Being able to join directly in the UPDATE statement is extremely convenient. Since you specify which table to update (the table listed after the UPDATE keyword) there’s no confusion as to which table’s rows are modified. Additionally, because you are using joins in the update (since there is an explicit WHERE clause), you can avoid some of the pitfalls when coding correlated subquery updates; in particular, if you missed a join here, it would be very obvious you’d have a problem.

Copy from: http://www.orafaq.com/wiki/Inline_view

An inline view is a SELECT statement in the FROM-clause of another SELECT statement. In-line views are commonly used simplify complex queries by removing join operations and condensing several separate queries into a single query. This feature was introduced in Oracle 7.2.

This feature is commonly referred to in the MSSQL community as a derived table, and in the Postgres community simply refers to it as a subselect (subselects are inline views + subqueries in Oracle nomenclature).

Examples

Example inline view:

SELECT *

FROM (select deptno, count(*) emp_count

from emp

group by deptno) emp,

dept

WHERE dept.deptno = emp.depto;

Another good example of an inline view is:

SELECT a.last_name, a.salary, a.department_id, b.maxsal

  FROM employees a,

       (SELECT department_id, max(salary)maxsal

          FROM employees

      GROUP BY department_id) b

 WHERE a.department_id = b.department_id

   AND a.salary < b.maxsal;

The above query display the employees who earn the highest salary in each department.

Copy from http://apps2fusion.com/apps/apps/oracle-apps-interview-questions/28-technical-interview-questions-in-oracle-apps

Please find a list of possible technical interview questions in Oracle Apps. All the contents on this website are Copyright protected.

I will try to keep this upto date with any new questions that I come accross. New apps questions will be added to the top of this post.

Most of the Oracle apps interview questions listed here are technical in natue. These interview questions span various Oracle Apps modules plus FND & OA Framework. For Oracle HRMS and Payroll interview questions visit HRMS Interview Questions

For Oracle iProcurement interview questions, kindly visit iProc Interview Questions .

Question: How will you migrate Oracle General Ledger Currencies and Sets of Books Definitions fromone environment to another without reKeying? Will you use FNDLOAD?
Answer: FNDLOAD can not be used in the scenario. You can use migrator available in “Oracle iSetup” Responsibility

Question: This is a very tough one, almost impossible to answer, but yet I will ask. Which Form in Oracle Applications has most number of Form Functions?
Answer: “Run Reports”. And why not, the Form Function for this screen has a parameter to which we pass name of the “Request Group”, hence securing the list of Concurrent Programs that are visible in “Run Request” Form. Just so that you know, there are over 600 form functions for “Run Reports”

Question: Which responsibility do you need to extract Self Service Personalizations?
Answer:Functional Administrator

Question: Can you list any one single limitation of Forms Personalization feature that was delivered with 11.5.10
Answer:You can not implement interactive messages, i.e. a message will give multiple options for Response. The best you can get from Forms Personalization to do is popup up Message with OK option.

Question: You have just created two concurrent programs namely “XX PO Prog1″ & “XX PO Prog2″. Now you wish to create a menu for Concurrent Request submission such that only these two Concurrent Programs are visible from that Run Request menu. Please explain the steps to implement this?
Answer:
a) Define a request group, lets say with name “XX_PO_PROGS”
b) Add these two concurrent programs to the request group “XX_PO_PROGS”
c) Define a new Form Function that is attached to Form “Run Reports”
d) In the parameter field of Form Function screen, enter
REQUEST_GROUP_CODE=”XX_PO_PROGS” REQUEST_GROUP_APPL_SHORT_NAME=”XXPO” TITLE=”XXPO:XX_PO_PROGS”
e) Attach this form function to the desired menu.

Question: Does Oracle 10g support rule based optimization?
Answer: The official stance is that RBO is no longer supported by 10g.

Question: Does oracle support partitioning of tables in Oracle Apps?
Answer: Yes, Oracle does support partitioning of tables in Oracle Applications. There are several implementations that partition on GL_BALANCES. However your client must buy licenses to if they desire to partition tables. To avoid the cost of licensing you may suggest the clients may decide to permanently close their older GL Periods, such that historical records can be archived.
Note: Before running the archival process the second time, you must clear down the archive table GL_ARCHIVE_BALANCES (don’t forget to export archive data to a tape).

Question: What will be your partitioning strategy on GL_BALANCES? Your views please?
Answer: This really depends upon how many periods are regularly reported upon, how many periods are left open etc. You can then decide to partition on period_name, or period ranges, or on the status of the GL Period.

Question: Does Oracle support running of gather stats on SYS schema in Oracle Apps?
Answer: If your Oracle Applications instance is on 10g, then you can decide to run stats for SYS schema. This can be done by exec dbms_stats.gather_schema_stats(‘SYS’);
Alternately using command dbms_stats.gather_schema_stats(‘SYS’,cascade=>TRUE,degree=>20);
I will prefer the former with default values.
If you wish to delete the stats for SYS use exec dbms_stats.delete_schema_stats(‘SYS’);
You can schedule a dbms_job for running stats for SYS schema.

Question: Can you use concurrent program “Gather Schema Statistics” to gather stats on sys schema in oracle apps?
Answer: No, “Gather Schema Statistics” has no parameters for SYS schema. Please use dbms_job.

Question: Which table is used to provide drill down from Oracle GL into sub-ledger?
Answer: GL_IMPORT_REFERENCES

Question: What is the significance of profile option “Node Trust Level” in Oracle Apps.
Answer: If this profile option is set to a value of external against a server, then it signifies that the specific mid-tier is External i.e. it will be exposed to the www. In other words this server is not within the firewall of your client. The idea behind this profile option is to flag such middle-tier so that special restrictions can be applied against its security, which means a very restricted set of responsibilities will be available from such Middle-Tier.

Question: What is the significance of profile option “Responsibility Trust Level”.
Answer: In order to make a responsibility accessible from an external web tier, you must set profile option “Responsibility Trust Level” at responsibility level to “External”. Only those responsibilities that have this profile option against them will be accessible from External Middle tiers.

Question: What else can you suggest to restrict the access to screens from external web tiers?
Answer: You may use URL filtering within Apache.

Question: What is the role of Document Manager in Oracle Purchasing?
Answer: POXCON is an immediate concurrent program. It receives pipe signal from the application when a request is made for approval/reservations/receipts.

Question: How to debug a document manager in Oracle Apps?
Answer: Document manger runs within the concurrent manager in Oracle Applications. When an application uses a Document Manager, it sends a pipe signal which is picked up by the document manager.
There are two mechanisms by which to trace the document manager
1. Set the debugging on by using profile option
STEP 1. Set profile option “Concurrent:Debug Flags” to TCTM1
This profile should only generate debugs when set at Site level(I think, as I have only tried site), because Document Manager runs in a different session.
STEP 2. Bounce the Document Managers
STEP 3. Retry the Workflow to generate debugs.
STEP 4. Reset profile option “Concurrent:Debug Flags” to blank
STEP 5. have a look at debug information in table fnd_concurrent_debug_info

2. Enable tracing for the document managers
This can be done by setting profile option “Initialization SQL Statement — Custom” against your username before reproducing the issue. The value of this profile will be set so as to enable trace using event 10046, level 12.

Question: You have written a Java Concurrent Program in Oracle Apps. You want to modify the CLASSPATH such that new class CLASSPATH is effective just for this program.
Answer: In the options field of the concurrent program you can enter something similar to below.
-cp :/home/xxvisiondev/XXDEVDB/comn/java/appsborg.zip:/home/xxvisiondev/XXDEVDB/comn/java

Question: How will you open a bc4j package in jdeveloper?
Answer: Oracle ships a file named server.xml with each bc4j package. You will need to ftp that file alongside other bc4j objects(VO’s, EO’s, AM, Classes etc).
Opening the server.xml will load the complete package starting from AM(application module). This is a mandatory step when building Extensions to framework.

Question: In OA Framework Self-Service screen, you wish to disable a tab. How will you do it?
Answer: Generally speaking, the tabs on a OA Framework page are nothing but the SubMenus. By entering menu exclusion against the responsibility, you can remove the tab from self service page.

Question: In self service, you wish to change the background color and the foreground text of the OA Framework screens to meet your corporate standards. How will you do it?
Answer: You will need to do the below steps
a….Go to Mid Tier, and open $OA_HTML/cabo/styles/custom.xss
b…Enter below text( change colours as needed)