Showing posts with label Interview Questions. Show all posts
Showing posts with label Interview Questions. Show all posts
Saturday, June 18, 2011
16.Difference between Char and Varchar2?
Varchar2 is similar to char but can store available number of characters and while querying the table varchar2 will trims the extra spaces and fetches the rows that exactly match the criteria.
17.Difference between LOB and LONG data types?
The maximum size of an LOB is 4GB. It will support random access to data where in LONG maximum size is 2GB. It will support sequential access to data.
18.Single Row functions: It will work on single row and give result for all the rows.
Ex: to_char, to_date etc.
19.Group Functions: It will work on group of rows in a table and gives a single row result. Ex: Sum(), Avg(), min(), max().. Etc.
20.String Handling Functions?
Instr – it returns the position of the string where it occur according to the parameters.
Instrb – instr and instrb returns same but in the form of bytes.
Substr – It returns the portion of a string depending on the parameters from and to.
Substrb – Substr and Substrb returns the same thing but Substrb returns in the form of bytes
21.Sign: Sign is a function it will take numbers, as inputs and it will give
i. 1 for positive integer
ii. -1 for negative integer
iii. 0 for ZERO
SQL> Select sign(-1234) from dual; O/P: -1
22.Differences between UNION and UNION ALL?
Union: The values of the first query are returned with the values of the second query eliminating the duplicates.
Union All: The values of the first query are returned with the values of the second query including the duplicates.
23.Difference between NVL and NVL2 functions?
NVL is used to fill a NULL value to known value. NVL2 will identify the NULL values and Filled values it returns exp3 if it is null otherwise it returns exp2. We have to pass 3 parameters for NVL2 and 2 parameters for NVL.
24.How can we compare range of values with out using the CASE?
By using Decode with in Decode.
25.Can we Decode with in a Decode?
YES
26.Decode and Case Difference?
Case compares a Range of values and Decode will work as if else statement.
27.Difference between Replace and Translate?
Replace is used to replace the whole string and we can pass null values in replace. Translate is used to translate character-by-character here we have to pass the three parameters.
28.Difference between where and having clause?
Where used to specify condition and used to restrict the data. Having used to specify the condition on grouped results and used to filter the data.
29.Difference between IN and EXISTS clause?
EXISTS gives the status of the inner query. If the inner query is success then it returns true other wise it returns false and IN will compare the list of values.
30.Difference between subquery and correlated subquery?
Query with in a query is subquery. Inner query will executes first and based on the result the outer query will be displayed. Correlated subquery outer query will executes first and then inner query will be executed.
Saturday, June 18, 2011 by Team search · 0
1. Difference between DCL and DML commands?
DCL commands will commit implicitly where in DML commands we have to commit explicitly.
2. Table, Rowid, Rownum
Table is a database object, which is having more than one column associated with its data type.
Rowid is the unique binary address of the row in a table
Rownum it is a temporary number in a memory and was assigned to each row selected by the statement.
3. Pseudo-Columns
Columns that are not created explicitly by the user and can be used explicitly in queries. The pseudo-Columns are rowid, rownum, currval, nextval, sysdate, and level
4. What is a View?
View is Virtual Table, which hold the data at runtime
5. Difference between ordinary view and complex view?
Simple views can be modified easily and these cant hold the data where as complex views cant be modified directly and complex view can hold the data to modify a complex view we have to use INSTEAD OF TRIGGERS.
6. Forced view
Creating a view on a table, which is not there in the database.
7. Inline view
Inline view is basically a subquery with an alias that you can use like a view inside a SQL statement.
8. What is an INDEX and what are the types?
INDEX is a database object used in oracle to provide quick access to rows.
9. Synonym
Synonym is an alias name for any database object like tables, views, functions, procedures.
10. SELECT statement syntax?
SELECT From
Where Group by
Having
Connect prior
11. What is Constraint? Different Constraints?
Constraints are representators of the columns to enforce data entity and consistency. UNIQUE, NOT NULL, Primary key, foreign key, Check.
12. Difference between Primary key and Unique + Not Null constraints?
Unique + Not Null is a combination of two constraints and we can use more than one Unique + Not Null in any table. Primary Key is a single constraint we can use only one time for a table. It can be a referential key for any column in any table.
13. What is NULL?
Default Value.
14. Dual Table
It is a one row, one column table with value X.
15. Difference between Truncate and Delete?
Truncate will delete all the rows from the table with out any condition. It will commit automatically when it fires Where delete will delete all or specified rows based upon the condition here we have to commit explicitly.
by Team search · 0
Thursday, February 10, 2011
In this post, I prepared the possible Interview questions related to Cross Validation Rules. I believe it will be useful for my audience.
1. What is Cross Validation Rules?
Cross-Validation rules define whether segment values of a particular segment can be combined with other segment values of another particular segment when new combinations are created. In other words, cross validation rules prevents the creation of invalid code combinations. It is applicable for Key Flexfields only.
2. Illustrate the appropriated need for Cross-Validation rules with example?
Let us take Accounting Flexfield for our example, we can use cross validation rules for achieving the below business requirements
- To ensure all balance sheet accounts to be associated only with the balance sheet cost center or corporate cost center
- To ensure that profit and loss accounts to be associated with specific cost centers other than corporate cost center.
- To restrict data entry of cost centers that is not legitimate for a specific company.
3. A user trying to violate the cross validation rules while data entry, what happens and what are the possibilities?
The user will receive an error message given as part of the cross validation rules definition.
- The error messages can be made very informative and tell the user exactly what is wrong with the flexfield combination.
- The invalid segment can also be highlighted.
4. What Cross Validation Rules are made up of?
A cross validation rules is composed of one or more cross validation rules elements. There are two types of rule elements and they are “Include” and “Exclude”. Each cross validation rule must have at least one include rule element because cross validation rules exclude all the values unless they are specifically included via rule element. Exclude rule elements override include rule element.
5. What is Oracle recommendation regarding rule elements definition?
We can accomplish our business requirement either by including specific ranges explicitly or by including all possible ranges and exclude the specific ranges. But the Oracle recommendation is
“One Include Rule Element which encompasses all possible values with several exclude cross validation rule elements”
6. How do you define an all-encompassing include cross-validation rule element?
An all-encompassing include for numeric segments is “O” through “9”, while for alpha numeric segments it is from “ O” through “Z”. For Example, if our flexfield has three segments with First segment of two Numbers, Second of four characters and third segment of six characters then the all encompassing include is 00-0000-000000 through 99-ZZZZ-ZZZZZZ.
7. What will be a blank value do in a cross validation rule element?
While defining the rule element, leaving the segment blank includes all possible values
8. What is the use of error segment and error message?
While defining a cross validation rule, we need to enter a mandatory error message that will appear if an enabled cross validation rule is being violated. Next we can choose to enter an error segment and an effective date range. The error segment will be highlighted if this particular cross validation rule is violated.
9. What is the difference between disabling a cross validation rule vs deleting it?
The difference is when we think about reusing the cross validation rules i.e. deleted cross validation rule needs a redefinition while disabled cross validation doesn’t need a redefinition just a tick in check box is enough to re enable it.
10. Whether a single flexfield structure can have multiple cross validation rules?
Yes
11. What is the need for multiple cross validation rules?
Even though we can achieve cross validation across more than one segment via single cross validation rule, it is advisable to go for multiple cross validation rules so that a clear error message and error segment can be highlighted to the user which directly point to the specific issue. Moreover, using multiple simple cross validation rules is better than single complex cross validation rule.
12. When a combination is valid for a flexfield structure which has multiple cross validation rules?
Combination is valid only if they are in at least one include rule element and outside of all exclude elements.
13. What is the navigation to define a cross validation?
Responsibility: General Ledger Vision Operations
Thursday, February 10, 2011 by Team search · 1
Monday, January 24, 2011
21. Can you use view as an event alert table?
Ans: You cannot use a view as the event table for your alert.
22. What are the four types of actions in alert can be specified?
Ans: There are four types of actions you can create
a) Message actions
b) Concurrent program actions
c) Operating script actions
d) SQL statement script actions
23. Are there any limitations in Response processing when getting inputs back from user?
Ans: Yes, User must respond answer in a specific format and content.
24. How does Alert work?
Ans: Oracle Alert checks your database for the exceptions you want to know about using several concurrent programs
a) Periodic Alert Scheduler
b) Check Periodic Alert
c) Check Event Alert
d) Response Processor
The Periodic Alert Scheduler (ALEPPE) is a concurrent program that automatically checks your scheduled periodic alerts.
25. How Check Event Alert Works?
Ans: Once you define an event alert to monitor a table for any insert or update to the table will trigger the event alert. When an insert or update to an event table occurs, Oracle Alert submits To the concurrent manager, a request to run a concurrent program called Check Event Alert (ALECTC). The concurrent manager runs this request according to its priority in the concurrent queue. When the request is run, Check Event Alert executes the alert Select statement. The Response Processor (ALPPIM) is the Oracle Alert concurrent program that processes responses to an alert message.
26. What is the purpose of: DATE_LAST_CHECKED ?
Ans: An Oracle Alert implicit input that contains the date and time that an alert was last checked Oracle Alert automatically provides the value for: DATE_LAST_CHECKED. You must use DATE_LAST_CHECKED to create self–referencing alerts.
27. What is meant by Action History?
Ans: A record of the actual actions performed for each action set check that includes the value substituted for each output.
28. Can you perform actions if a recipient doesn’t respond?
Ans: Yes, Using No Response field to specify the actions you want Oracle Alert to perform if the recipient does not respond within the number of Response Days specified in the Action Details window of the Alerts form known as the response processor.
29. Can one create event alerts on custom tables, triggered by custom applications?
Ans: Yes, Oracle Alerts can be integrated with your custom application and detailed information is available in the below link
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=60879.1
30. How often should the Alert tables be purged?
Ans: There is no recommended purge frequency. It all depends on how many alerts have been enabled and their frequency.
Monday, January 24, 2011 by Team search · 2
Subscribe to:
Posts (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.