Oracle Questions

1.                  From an Employee table, how will you display the record which has a maximum salary?

Ans:- select * from emp where salary = (select max(salary) from emp)

2.                  What is the difference between the Primary and Foreign key?

 Ans:- 1)Primary Key  is unique and not null column .Foreign key is define relation b/w two        tables it is primary key In child tables.

           2)we can have only one Pk for a table but we canhave multiple FK.

           3)By default clustered index will be created for PK where as FK doesn't.       

3.                  How will you delete a particular row from a Table?  (Using where condition)

4.                  How will you select unique values from a list of records?(distinct)

5.                  What is meant by Join? What are the different types of Joins available? Explain.

            A JOIN is used to match/equate different fields from 2 or more tables using primary/foreign keys. Output is based on type of Join and what is to be queries i.e. common data between 2 tables, unique data, total data, or mutually exclusive data.

 Simle join ,inner /equi/natural join,outer join(left,right),self join

  

6.                  overloading of stored procedure is possible in oracle? 

             No Only Package procedure/Functions can be Overloaded.

7.                  how to create table with in the procedure or function?(Dynamic Sql i.e execute immediate)

create or replace procedure Mypro()
is
v_sql varchar2(100);
begin

v_sql := 'create table mytab(mynum number(10),myname varchar(100))';
execute immediate v_sql ;

end;
8.               what is overloading procedure or overloading function ?
Ans:-  

It is the idea that the functionality of a PL/SQL stored procedure of function can be changed based on the input datatype.

For a simple example of overloading, you can write a PL/SQL function that does one thing when a numeric argument is passed to the procedure and another thing when a character string is passed as an argument.

PL/SQL lets you overload packaged (but not standalone) functions: You can use the same name for different functions if their formal parameters differ in number, order, or datatype family.

However, a RESTRICT_REFERENCES pragma can apply to only one function declaration. Therefore, a pragma that references the name of overloaded functions always applies to the nearest preceding function declaration.

In this example, the pragma applies to the second declaration of valid:

CREATE PACKAGE Tests AS 
    FUNCTION Valid (x NUMBER) RETURN CHAR; 
    FUNCTION Valid (x DATE) RETURN CHAR; 
    PRAGMA RESTRICT_REFERENCES (valid, WNDS); 
 END;

http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_pc.htm

Defining Program Incompatibility Rules

INCOMPATIBLE means: Two incompatible reports or request set are not allowed to run in parallel. Incompatible reports /program can only run sequentially, the first one must terminate before the other is allowed to start.

So, it can happen that a report is pending for a long time, when it is waiting for the incompatible report to finish.

In Oracle Context you have to know that INCOMPATIBLE type may be global or domain . If you choose Domain, the incompatibility is resolved at a domain-specific level. If you choose Global, then this concurrent program will be considered globally incompatible with your concurrent program, regardless of which domain it is running in.Make sense.. Lets take a look how documentation define:

Example of Program Incompatibilities

You can understand like this: Oracle General Ledger's Posting program,which is used to post journal entries.

If the Posting program's incompatibility with other concurrent programs were not enforced, other financial reports running simultaneously with the Posting program could contain incorrect account balance information.

Definition of incompatible type "Domain" VS "Global"

If you choose Domain, the incompatibility is resolved at a domain-specific level.

If you choose Global(Fyi..The concept of "Global" incompatibilities was introduced with Patch 2364876), then this concurrent program will be considered globally incompatible with your concurrent program, regardless of which domain it is running in.

You can define a concurrent program to be globally incompatible with another program that is, the two programs cannot be run simultaneously at all; or you can define a concurrent program to be incompatible with another program in a Conflict Domain. Conflict domains are abstract representations of groups of data.

They can correspond to other group identifiers, such as sets of books, or they can be arbitrary.

Concurrent Conflict Domains Concept

As per Oracle system admin user guide, If two programs are defined as incompatible with one another, the data these programs cannot access simultaneously must also be identified.

In other words, to prevent two programs from concurrently accessing or updating the same data, you have to know where, in terms of data, they are incompatible. A Conflict Domain identifies the data where two incompatible programs cannot run simultaneously.

In Oracle Applications, data is stored in database tables that belong to a particular application. Each table may also contain information used to determine what conditions need to be met to access the individual records. These conditions may consist of one or more of the following data groupings:

• Set of books : This is based out of underline profile option i.e..GL_SET_OF_BOOKS
• Multiple Operating units :This is based out of underline profile option MO_OPERATING_UNIT)
• Multiple Orgs :This is based out of underline profile option ie. INV_ORGANIZATION_ID Manufacturing Applications
• HR may use business group as a conflict domain
• Fixed asset may use Fixed asset dep. book(FA)

A conflict domain is an abstract representation of the groupings used to partition your data. There is no limit to the number of domains that can be defined, but excessive domains may hurt performance.

More on Oracle Conflict Domains

A conflict domain is a set of related data stored in one or more ORACLE schemas and linked by grants and synonyms. Do not confuse logical databases with your ORACLE database. The ORACLE database contains all your Oracle Applications data, with each application's data usually residing in one ORACLE schema. You can think of a logical Defining database as a line drawn around a set of related data for which you wish to define concurrent program incompatibilities. In other words, logical databases determine which concurrent programs cannot run at the same time.Make sense:)

Profile Options as part of standard setup

• Concurrent:Conflicts Domain :This option identifies the domain within which all the incompatibilities between programs has to be resolved.
  The profile can be set at Site, Application, Responsibility and User levels.This can be an operating unit name, a legal entity name, or a set of books name as the domain name. You are allowed to define as many as domains as you need. Since you cannot delete conflicts domains, you should keep the domains to a necessary minimum.

DEFINING CONFLICTS DOMAINS

Navigate to Concurrent > Conflicts Domains

How its Conflict Domains Works with conflict resolution manager

All programs are assigned a conflict domain when they are submitted. If a domain is defined as part of a parameter the concurrent manager will use it to resolve incompatibilities. If the domain is not defined by a parameter the concurrent manager uses the value defined for the profile option Concurrent:Conflicts Domain.

Lastly, if the domain is not provided by a program parameter and the Concurrent:Conflicts Domain profile option has not been defined the 'Standard' domain is used. The Standard domain is the default for all requests.

All programs use the Standard conflict domain unless a value is defined for the profile option Concurrent:Conflicts Domain or a conflict domain is defined through a program parameter.

Each request submitted uses parameters which identify the records that it will access.

For programs that are defined with incompatibility rules an additional parameter (conflict domain parameter) is used. The conflict domain may be set automatically based on such variables as a login ID, set of books, or the organization the user is working in. The conflict domain parameter may in some cases be selected in the parameters field of the Submit Requests form. Once the parameter is determined the Conflict Resolution Manager (CRM) uses the domain to ensure that incompatible programs do not run simultaneously in the same domain.

Example to make a report set incompatible with itself 11i

This came from Oracle Support and works for 11i.

1. Go to system administration responsibility and navigate to Concurrent > Set
2. Query on desired Request Set. For Example: Test_Report_set
3. Check the "Allow Incompatibility" check box in the Run Options and then save this record.
   • This step will create a new concurrent program, the naming convention will be of the form "Request Set Test_Report_set "
4. Navigate to Concurrent > Program > Define.
5. Query on new concurrent program "Request Set Test_Report_set “, remembering the concurrent program name begins with "Request Set.".
6. Click on 'Incompatibilities' button located at the bottom of the form
7. In the Incompatible Programs form specify the name of the concurrent program,"Request Set Test_Report_set", in the Name column and the value in the Scope column should be ' Set ' and save this record.
8. Test ..test ..test

1->When a concurrent program is incompatible with another program, the two programs cannot access or update the same data simultaneously.

2->When you define a concurrent program, you can list those programs you want it to be incompatible with. You can also list the program as incompatible with itself, which means that two instances of the program cannot run simultaneously.

3->You can also make a program incompatible with all other concurrent programs by defining the program to be run-al

4->You define a concurrent program to be run-alone or to be incompatible with specific concurrent programs by editing the concurrent program's definition using the Concurrent Programs window. 

5->Program incompatibility and run-alone program definitions are enforced using Conflict Domains.

Concurrent Conflicts Domains

1->If two programs are defined as incompatible with one another, the data these programs cannot access simultaneously must also be identified.

2->In other words, to prevent two programs from concurrently accessing or updating the same data, you have to know where, in terms of data, they are incompatible. A Conflict Domain identifies the data where two incompatible programs cannot run simultaneously.

Conflict Domains

In Oracle Applications, data is stored in database tables that belong to a particular application. Each table may also contain information used to determine what conditions need to be met to access the individual records. These conditions may consist of one or more of the following data groupings:

• SOB - based on the profile option GL_SET_OF_BOOKS

• Multiple installations (referred to as MSOB)

• Multiple Operating units (determined by profile option MO_OPERATING_UNIT) (referred as MULTIORG).

• Multiple Orgs (determined by profile option INV_ORGANIZATION_ID, Used by Manufacturing Applications)

• HR may use business group as a conflict resolution domain

• FA may use FA book

• etc...

How to track Current Apps Versions

1)select product_version,patch_level from fnd_product_installations
Get current version ana Patch level information.

2)select * FROM V$VERSION
Database Version infomation.

3)select * from v$instance
Instance details

4)select WF_EVENT_XML.XMLVersion() XML_VERSION from sys.dual;
Current XML Parser Version info.

5)select TEXT from WF_RESOURCES where TYPE = 'WFTKN' and NAME = 'WF_VERSION'
Workflow version Number.

6)select home_url from icx_parameters
Oracle applications front end URL

7)SELECT VALUE FROM V$PARAMETER WHERE NAME=’USER_DUMP_DEST’
Get the Trace file location.

8) XML Publisher Vesion info.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.