SQL Questions:
- What is Where clause?
A. Where clause is used to compare the record values. It is used to filter the record in SQL statement for the specific condition
- What is Group by Clause?
- Group by clause is used to devide the rows into similar groups in a table.
- What is having clause?
A. Having clause is used to display the specific group of records. This is used to filter the groups of records.
- What is set operators?
A. Set operators are Union, Unionall, Intersect and Minus.
- What are special operators?
- Special operators are IS, IN, NOT IN, LIKE, NOTLIKE, BETWEEN, NOT BETWEEN, EXISTS & SOUNDEX.
- What are the constraints?
- Constraints are there types Entity, Domain and Referential Constraints.
Domain: Not Null and Check Constraints.
Entity: Primary Key and Unique.
Referential: Foreign Key and Reference Key.
- What is Sub query and Co-related sub query?
A. Sub query: The Main query will depend the sub query results. The sub query will execute first then the main query will execute.
Co-related sub query: The sub query will depend the main query results. The main query will execute first then the sub query will execute.
- What is joins? What are they?
A. 1. Self join: We will compare the records with in single table.
2. Eque join: We can compare the records with equal condition only.
3. Non eque join: We can compare the records with out equal condition.
4. Inner join: Eque join and Non eques come under the Inner join.
5. Outer join: We will make the condition with (+) operator after the column name before equal or after equal. This join will use only equal join only. Ex: Suppose I have two tables T1 & T2. In T1 having 8 records and T2 having 5 records. These two tables’ column values are matching only 3 records. I want to display that all records with the 3 records in table T1. At that time we will put the Outer join condition Ex: where T1.a = T2.a(+)
9. What is a view? What are they?
A. A view is a logical window it is logically related to database tables, Synonyms and Views There are three types.
1. Simple view
2. Complex view
3. Materialized view
1. Simple view: We can create a view by using single table is called simple view it can’t hold the data. When we are not using primary key columns in the view we can’t do DML operations other wise we can do any DML operations by using simple view.
2. Complex view: We can create a view from more then one table or more then views is called complex view. It can’t hold the data. We can’t do any DML operations when we will use all primary key columns in our view we will do DML operations. Other wise we can’t do any DML operations. By using Instead of trigger we can do any DML operations from these views.
3. Materialized view: We can create a view from one or more database or more then one table. It can hold the data. It is like a snap short. We can’t do any DML & DDL except Truncate and Drop comment. By specifying refresh statement in this view we can refresh data from data base.
10. What is Force view?
A. We can create view with out existing columns or tables by using force.
11. What is Index?
A. Index is a database object. It is used to get the data from the database fastly. It is used to improve the performance of the getting the data from the database. Index used the binary search to get data from database. They are mainly two types that are: Column level Index and Table level index. Column level index are when we are creating table at the time we will specify or when we are specified primary key or unique is called column level index. Table level index are we are create the index externally mean after creating the table we will create the index.
1. Simple Index: It is called a primary key. We will create index on primary key column.
2. Composite Index: We can create index on multiple columns.
3. Unique Index: It is called a unique key. We will create index on unique key column.
4. Function based index: We will create index on function based column like UPPER (column name).
5. Bitmap Index: We will create the index on bitmap image column.
6. Force Index: We will create index temporarily for the sql statement only.
Ex: select /*+ index <table_name>(column_name)*/ remaining columns and from statement.
12. What is performance tuning?
A. Performance tuning is used to improve the performance of the statement or object. Mainly we will use to get the execution plan of the statement or object. There are mainly two types:
1. Explain Plan: It gives the execution plan of the single sql statement.
Step1: EXPLAIN PLAN SET STATEMENT_ID = ‘<id name>’
INTO PLAN_TABLE FOR <sql statement>.
Step2: The explain plan statement will be generated into plan_table if plan_table is not available in your system we can run the UTLXPLAN.sql file the plan_table will create.
Step3: SELECT <column names> FROM PLAN_TABLE WHERE STATEMENT_ID = ‘<statement_id>’
It will show the explain of our sql query.
2. Sql Trace: It gives the execution plan of the multiple statements. We can get a trace file from sql trace, report level and form level. In sql level
Step1: ALTER SESSION SET SQL_TRACE = ‘TRUE’;
Step2: We will execute our queries.
Step3: ALTER SESSION SET SQL_TRACE = ‘FALSE’;
Step4: Internally the trace file is generated but it is not a readable mode. We will convert the trace file into readable mode by using TKPROF. We will find our trace file.
Step5: SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘user_dump_dest’; it will show the path of the trace file.
Step6: We will go to the path and find out our trace file according to the date and time.
Step7: Go to DOS mode and go to that specified path
TKPROF <tkprof file name> <destination file name>
Step8: It will convert trace file to readable mode.
13. What are Pseudo columns? What are they?
A. Pseudo columns behave like a table columns but we are not create this columns they are:
1. Rowid: It wills genarete when we are inserting the record into table.
2. Rownum: It will populate when we are executing the select statement.
3. Next_Val: It will use when we are calling the next value from the sequence.
4. Curr_val: It will use when we are calling the current value from the sequence.
5. Level:
14. What is sequence? Explain?
A. Sequence is used to generate primary key values. Syntax: CREATE SEQUENCE <sequence name> START WITH <no> INCREMENT BY <no> MIN VAL<no> MAX VAL<no> [CYCLE/NOCYCLE] [CATCH/NOCATCH].
15. What is a synonym?
A. Synonym is a alternative name of the database tables or objects.
16. What is inline view?
A. Inline view is a sub query. It is not a database object. We will define a view temporary in a select statement. We will write the select statement in from clause is called inline view. When we are given alias name is called a view name.
PLSQL FAQ’S
- What is anonymous block?
A. Anonymous block is a block structure but it is not store any where it can’t have any name.
- What is Exception what are they?
A. Exception is error handler statement it is used when the program terminates abnormally at the time the exception will do the abnormal termination to normal termination. They are mainly two types
System defined exception and user defined exception.
The system defined exceptions are:
1. NO_DATA_FOUND
2. DUP_VAL_ON_INDEX
3. CURSOR_ALREADY_OPEN
4. INVALID_CURSOR
5. ZERO_DEVIDE
6. VALUE_ERROR
7. STORAGE_ERROR
8. PROGRAM_ERROR
9. OTHERS etc.
User defined exceptions are:
Generally the oracle is provided some exception names nearly below 50. Suppose we got error out of system defined exception at the time we will have to define the name of the exception. This is called user named exception. We have set the name of exception by using pragma exception_init. User defined exceptions where ever we want to raise exception we will raise the exception.
- What is Raise_application_error?
A. Raise application error is used to terminate the programe at any point of time.
- What is Pragma exception init?
A. Pragma exception init tells the compiler to associate an exception with an oracle error it is used to get the error message of specific oracle error.
- What is Pragma?
A. Pragma is pre compiler directive.
- What is Exception propagation?
A. Exception propagation is a master and child exception handler statement. Suppose we have one parent and child block. There are occurred the error in child block but we are not specified exception in the block the compiled will search the main block for the exception handler statement.
- What is a Cursor?
A. Cursor is private area. This area will provide by oracle engine it is a temporary memory. It is used to get the data from the database. There are two types.
1. Implicit cursor.
2. Explicit cursor.
9. What is implicit cursor?
A. It will fire implicitly when the DML operations are doing. We can’t control this cursor.
10. What is the cursor controls?
A. Open, Fetch, Close
11. What is the implicit cursor attributes?
A. SQL%ISOPEN, SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT.
12. What is explicit cursor?
A. Explicit cursor is a cursor we can control explicitly. The explicit cursor attributes are %IS_OPEN, %FOUND, %NOT_FOUND, and % ROW_COUNT.
The explicit cursors are:
1. General Cursor: In this cursor we will open, fetch, close the cursor manually. In this cursor we will write the select statement with in the declaration part.
2. For Cursor: This is a explicit cursor. In this cursor we will not open, fetch, close the cursor manually. When we are using for cursor it will open and it will fetch all records and it will exit the loop and it will close automatically. There is a one drawback that is it won’t come in between the fetching all records.
Ex: Step1: Define cursor and write sql statement in the declaration part.
Step2: for <variable name> in <cursor name>
loop
<Write statements>
end loop;
3. For Update Cursor: This cursor is used to update a single record. When we are updating the record in this cursor that record will be lock. No body will not use this record this is called record level lock. In this cursor we will use where current off. This command will lock the specific record column value. It will frees the remaining column values. But it is not recommended by using rowed we will update the records.
4. Ref Cursor: Ref cursor is a dynamic cursor. It will populate dynamically we can get a data through by address or pointers. In general cursor we will specify the select statement in the declaration part in the ref cursor we will specify the select statement in the body of the program. They are two types
1. Strong Cursor: It is returning a value.
2. Weak Cursor: It should not return a value.
13. What is Procedure?
A. Procedure is a data base object. It performs a specific action. It contains a multiple statements.
14. What is Function?
A. Function is a data base object. It calculates the value and it must be return a value.
15. What are parameter modes in the procedures and functions?
A. They are three parameter modes in procedure and functions. IN, OUT, INOUT. IN parameter is used to pass a value into procedures and functions. The OUT parameter is used to get the value from procedures and functions. The INOUT parameter is used to pass the input value and get the output value from the procedures and functions. By default parameter is IN.
16. What is difference between procedure and function?
A. Generally procedure should not return a value the function it must be return a value. Functions we can call at select statements directly. Procedures we will execute at PLSQL block only.
17. What is Package?
A. Package is a data base object it is logically related to PLSQL object and sub programs. Package containing the 2 or more procedures and functions. It will give the good performance. They are two parts Specification and Body of the package. In the specification part we have declare all procedures, functions which we are writing this package and global variables, Cursors and user defined exceptions. In body of the package having entire coding of each procedure and function.
18. What is difference between procedure and package?
A. Procedure is a single object package containing a multiple objects. Package containing overloading specialty. When we are compiling the package the package the compilation code will store in SGA(System Global Area) memory permanently. When we are running the package the package will execute from SGA memory it self. When we are calling the procedure at the time the compilation will move to SGA memory temporarily it will execute and it will destroy. That’s why the package is very fast.
19. What is Overloading Procedure or function?
A. Overloading procedure is a procedure we can create more then one procedure with the same name and different in parameters. It will perform different action.
20. What is Trigger?
A. Trigger is a database object. It will fires implicitly when the event occurs.
21. What are types of triggers In PLSQL?
A. They are 12 types that are Before/After, Insert/Update/Delete, For each row and Statement level.
22. What is Mutating error?
A. It wills fires when we are selecting the record while doing the DML operation on the same record and same table. There is no permanent solutions they are having only two partial solutions that’s are: Pragma Autonomous Transaction and Statement level trigger.
23. What is Pragma Autonomous Transaction?
A. Pragma autonomous transaction is a transaction it is used to split the transaction into two different transactions with master and child relation ship. They will execute independently and they will commit independently.
24. What is the Instead Of Trigger?
A. Instead of Trigger is used to do the DML operations on the complex views.
25. What is dynamic SQL?
A. Dynamic SQL is to do the DDL & DML operations with in the PLSQL block. Syntax: EXECUTE IMMIDIAT (‘<ddl/dml statement>’);
26. What is Bind variable?
A. Bind variable is a variable but we are not define the variable any where it can hold the data temporarily it will identified by :< Variable>.
25. What is composite data type?
A. Composite data types are %type and %rowtype.
%type is used to declare the data type of the specific column in the table data type it is used to get the specific column value.
%rowtype is used to declare the specific table entire record data type. It is used to get the entire record at a time.
26. What is PLSQL table?
A. PLSQL table is a collection data type. It is called temporary table. It is like a varray the varray having specific length and plsql table is having dynamic length of memory. We can create the plsql table for the single collection data type or single object and it must be indexed by binary integer.
Syn:
Declare
Type <type name> is table of EMP%type indexed by binary integer;
<Variable name> <table name>;
Begin
<Variable name>(<record number> := <value>;
End:
27. What is the difference between the cursor and plsql table?
A. Both will do same action but when we are using the cursor we will fetch the records statically. When we are using the plsql table we will get the records dynamically..
28. How we can delete the specific record and all records from the plsql table.
A. <table name>.delete (<record number>); is used to delete the specific record.
<Table name>. Delete; is used to delete the all records from the plsql table.
29. What is the Nested table?
A. A table with in table is called a nested table.
30. What is bulk collect? Explain?
A. Bulk collect is used to move bulk of records at a time to variable or plsql table in PLSQL block.
Ex:
Declare
Type emp_type is table of EMP%rowtype index by binary_integer;
T emp_type;
Begin
Select * bulk collect into t from emp;
For i in 1.. 14
Loop
Dbms_output.put_line(t(i).empno||’ ‘||t(i).ename||’ ‘||t(i).sal);
End loop;
Exception
When others then
Dbms_output.put_line(‘Error is :’||sqlerrm);
End;
31. What is bind parameters in PLSQL? What is the use of bind parameters?
A. Bind parameters are temporary parameters we are not create a parameters and we are not set the values into this parameters. There are tow types. : New, old. This parameters are used to call the new or old records in triggers.
32. What are optimization techniques?
A. There are two types of optimization techniques we are followed.
1. Rule based technique
2. Cost based technique.
REPORTS FAQ’S
- What is Repeating frame?
A. Repeating frame is behaves like place holder column. We can get the group wise records from the data base.
- What is Summary Column?
A. Summary column is a system defined computation of an another column data.
3. What is Formula Column?
A. Formula column is a user defined computation of an another column data.
4. What is Place holder column?
A. Place holder column is a column which we will specify the data type and we will set the values into the place holder column.
5. What is the Format trigger?
A. Format trigger is a PLSQL function that are executed before the formatting an object. It facilitates the dynamic changes of formatting attribute value.
6. What is Validation Trigger?
A. Validation trigger is PLSQL function that’s are executed when we are select on the parameter value on the command line. When we are accept the parameter value on the command line.
7. What is the action trigger?
A. Action trigger is a PLSQL procedure that’s executed when we are select the command button on the run time preview. It is used to call the another report.
8. What are triggers in reports?
A. They are 5 types of triggers in reports.
1. Before Parameter form
2. After Parameter form
3. before Report
4. between Pages
5. after Report
9. Bind and Lexical parameter?
A. Bind parameter is a parameter it is used to substitute the record value.
Lexical parameter is a parameter it is used to substitute the DDL/DML statement to the main query or query builder. We can pass the parameter value in the before parameter form.
10. What is the Confine mode and Flexi mode?
A. Confine mode is used to confine the object from its parent. Flexi mode is used to move the object any ware.
11. What is the Srw.message?
A. Srw.message is used to display the message in the reports.
12. What is Srw.do_sql?
A. Srw.do_sql is used to run DDL/DML statement in the reports.
13. What is Srw.abort?
A. Srw.abort is used to stop and exit the report/form at any point of the time.
14. What is Srw.run_report?
A. Srw.run_report is used to run the another report with in the report.
15. What is Srw.run_product?
A. Srw.run_product is used to call another form with in the form or report.
16. What is Anchor?
A. Anchor is used to determine the horizontal and vertical position of the child object from its parent.
17. What is the Conditional format trigger?
A. It is used to format the objects in layout editor. Ex: suppose we are getting one report output I want to display different color who are getting the above 10k sal. At the time we will set the conditional format trigger.
APPS FAQ’S
1. What is the report registration steps in apps?
A. 1. We will create the report according to the client requirement
2. We will move the report appropriate custom top.
3. We will create executable and set the executable method as a oracle report.
4. Define a concurrent program and set the executable into the concurrent program.
5. If our report having any parameters first we will create value sets and set the parameters into the concurrent program.
6. Add the concurrent program into the request group
7. The user must have that specific responsibility, go with that responsibility and view request then submit single request.
3. How many types of executable methods in apps? What are they?
A. In oracle 11.5.10 there are 12 types of executable methods:
1. Host
2. Immediate
3. Java stored program
4. Java concurrent program
5. Multi language function
6. Oracle report
7. PL/SQL stored procedure
8. Request stage function
9. Spawned
10. SQL * Loader
11. SQL * Plus
12. Perl Concurrent Program
4. How many types of value sets? What are they?
A. Value sets are:
1. None (It accepts value at run time it can’t show lov)
2. Table (It can show lov dynamically from the database table columns)
3. Independent (It can show the lov statically when ever we want to add the lov’s we will add manually)
4. Dependent( This value set depends independent value set when ever we are creating dependent value set before that we need to create independent value set and then we will create dependent value set)
5. Translatable Independent (It is Language specification time and independent value set)
6. Translatable Dependent (It is Language specification time and dependent value set)
7. Special (These special and pair value sets are used to display the flex fields data)
8. Pair
5. What is the Dependent Value set?
A. Dependent value set is a value type of value set. This value set will depend another value set. When we creating dependent value set before that we will create independent value set and then create dependent value set. Because the dependent value set will refer previous parameter value.
6. What is the profile?
A. Profiles are a changeable options it is set of options.
7. What is the use of $flex$?
A. $flex$ is used to refer the previous parameter value.
8. What is the use of $profile$?
A. $profile$ is used to get the profile values like user_id, app_id, resp_app_id, etc.
9. What is the mandatory parameter to register the report in apps?
A. P_CONC_REQUEST_ID
10. What is the User Exits? What are they?
A. User Exits are 3rd generation language program it is used to pass the control from Concurrent program to reports then perform specific action and return back. They are 5 types.
1. FND SRWINIT
2. FND SRWEXIT
3. FND FLEXSQL
4. FND FLEXIDVAL
5. FND FARMAT_CURRENCY
11. What is the use of FND SRWINIT?
A. SRWINIT is used to get the concurrent request information and then set up the profile option.
12. What is the use of SRWEXIT?
A. This user exit is used to release the memory to other users.
13. What is the FLEXSQL & FLEXIDVAL?
A. This user exists is used to populate the segments and concatenate the segments.
14. What is use of FORMAT_CURRENCY?
A. This user exit is used convert the currency from one currency to another currency.
15. What are the AIM methods?
A. Application Implementation Methods are:
1. Definition phase
2. Operation analysis phase
3. Solution design phase
4. Build phase
5. Transaction phase
6. Production phase
16. What are the user levels of profiles? Which is the highest & lowest priority?
A. 1. Site
2. Application
3. Responsibility
4. User
User having the highest priority and Site is having lowest priority.
17. What is Multi org structure?
A. Business Group (HRFV_BUSINESS_GROUP)
Set of books (GL_SETS_OF_BOOKS)
Legal entity (HR_LEGAL_ENTITIES)
Operation unit (HR_OPERATING_UNITS)
Inventory organization (MTL_PARAMETERS)
18. What is the Multi Org?
A. By using single installation of an oracle application product it is used to support number of organizations, even if they are using different sets of books.
19. How can you find out our table is multi org or not?
A. SELECT Multi_org_flag from fnd_product_groups Where table_name = <our table name>.
It will show the Y or N, if it is a Y then the table is Multi org else the table is not a Multi org. And ‘_all’ tables all are Multi org tables.
20. How many types of report registration methods?
A. There are two types that are through front end and Back end.
21. What are the mandatory parameters to register the procedure in apps?
A. They are two out parameter and varchar2 data type will require that is errbuff and retcode. Errbuff will return the error message of the latest error code. Retcode is return the status code of the compiler. The status code is displays 0,1,2.
0 is successful
1 is success with warning
2 is error
22. Can we register the plsql function through apps?
A. No we can’t register the function through apps. First we can crate one procedure and then call the function into the procedure then we can register the procedure.
23. What is the request group? And request set?
A. Request group is containing the multiple concurrent programs and it is used to submit the single request at a time. Request set is used to submit a multiple requests at a time.
24. What is the data group?
A. Data group is containing the users and applications and which application is assigned each user.
25. What are the parameters in FND_FORMAT_CURRENCY?
A. 1. Currency code
2. Display width
3. Source column
4. Destination column
26. What is API’S? What are they?
A. API’s is the database packages. That’s are
1. FND_PROGRAM
2. FND_REQUEST
3. FND_SUBMIT
4. FND_REQUEST_SET
5. FND_CONCURENT
6. FND_PROFILE
7 AD_DD
27. How to run the object through back end?
A. We will submit the object through back end by using FND_REQUEST API.
FND_REQUEST.SUBMIT_REQUEST (
Application => <application short name>,
Program => <conc prog short name>,
Description => <description>, (not mandatory)
Start_time => <program start time>, (not mandatory)
Sub_request => <sub request name>, (not mandatory)
Argument1 => <parameter1>, (not mandatory)
“
Argument100 => <parameter100>, (not mandatory));
28. Which API we will use to define a concurrent program and executable?
A. FND_PROGRAM.DEFINE (parameters),
FND_PROGRAM.EXECUTABLE (parameters),
FND_PROGRAM.DELETE (parameters)
Etc,.
.
