Oracle Interview Questions

Ques : What are the Built-ins used for sending Parameters to forms?
Ans : You can pass parameter values to a form when an application executes the call_form New_form Open_form or Run_product.

Ques : Use the ADD_GROUP_COLUMN function to add a column to a record group that was created at design time.I) TRUE II)FALSE
Ans : False.

Ques : What is a timer?
Ans : Timer is an “internal time clock” that you can programmatically create to perform an action each time the timer expires.

Ques : Can a formula column referred to columns in higher group?
Ans : Yes

Ques : How do you reference a parameter indirectly?
Ans : To indirectly reference a parameter use the NAME IN COPY ‘built-ins to indirectly set and reference the parameters value’ Example name_in (‘capital parameter my param’) Copy (‘SURESH’ ‘Parameter my_param’)

Ques : What is difference between open_form and call_form?
Ans : when one form invokes another form by executing open_form the first form remains displayed and operators can navigate between the forms as desired. when one form invokes another form by executing call_form the called form is modal with respect to the calling form. That is any windows that belong to the calling form are disabled andoperators cannot navigate to them until they first exit the called form.

Ques : What are visual attributes?
Ans : Visual attributes are the font color pattern proprieties that you set for form and menu objects that appear in your application interface.

Ques : What are the two ways by which data can be generated for a parameters list of values?
Ans :

1. Using static values.
2. Writing select statement.

Ques : An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form?
Ans : TRUE

Ques : What is trigger associated with the timer?
Ans : When-timer-expired.

Ques : Is the After report trigger fired if the report execution fails?
Ans : Yes.

Ques : Can a field be used in a report without it appearing in any data group?
Ans : Yes

Ques : What are the types of calculated columns available?
Ans : Summary Formula Placeholder column.

Ques : How can I get 5th highest sal. from employee tab?
Select max(sal) from emp where sal<(select max(sal) from emp where sal<(select max (sal) from emp where sal<(select max (sal) from emp where sal<(select max(sal) from emp))));

Ques : Difference between DBMS & RDBMS?
Ans : DBMS stands for Database Management System which is a general term for a set of software dedicated to controlling the storage of data. RDMBS stand for Relational DataBase Management System. This is the most common form of DBMS. Invented by E.F. Codd the only way to view the data is as a set of tables. Because there can be relationships between the tables people often assume that is what the word “relational” means. Not so. Codd was a mathematician and the word “relational” is a mathematical term from the science of set theory. It means roughly “based on tables”.

Ques : Can you have more than one content canvas view attached with a window?
Ans : Yes. Each window you create must have atleast one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.

Ques : What is new_form built-in?
Ans : When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form the operator will be prompted to save them before the new form is loaded.

Ques : What is the difference between two null values in a column?
Ans : No two values are same and if we compare two null values each other the value is garbage. we cannot determine the value

Ques : What are difference between post database commit and post-form commit?
Ans : Post-form commit fires once during the post and commit transactions process after the database commit occurs. The post-form-commit trigger fires after inserts updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires afteroracle forms issues the commit to finalized transactions.

Ques : Which is more faster – IN or EXISTS?
Ans : EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Ques : What is difference between a formal and an actual parameter?
Ans : The variables declared in the procedure and which are passed as arguments are called actual the parameters in the procedure declaration. Actual parameters contain the values that are passed to a procedure and receive results. Formal parameters are the placeholders for the values of actual parameters

Ques : Which datatype is used for storing graphics and images?
Ans : LONG RAW data type is used for storing BLOB’s (binary large objects).

Ques : Can the default values be assigned to actual parameters?
Ans : Yes

Ques : Whet are the difference between primary key and foreign key?
Ans :

1. Primary key is unique key but foriegn key always refers to primary key.
2. Primary keys enforce entity integrity by uniquely identifying entity instances. Foreign keys enforce.
3. Primary key is unique primary key is not NULL and foreign key is NULLforeign key reference as Primary key in another table.
4. Primary key is used to identify a row and it doesnot allow null values. It avoids duplication of rows.whereas foreign key refers to a column or combination of columns included in the defenition of referential integrity.referential integrity by completing an association between two entities.

Ques : How will you delete duplicating rows from a base table?
Ans : Delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); ordelete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from table_name tb where ta.dv=tb.dv);

Ques : What is a cursor?
Ans : Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

Ques : What are various joins used while writing SUBQUERIES?
Ans : Self join-Its a join foreign key of a table references the same table. Outer Join–Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don’t satisfy the join condition.Equi-join–Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second table.

Ques : What should be the return type for a cursor variable.Can we use a scalar data type as return type?
Ans : The return type for a cursor must be a record type.It can be declared explicitly as a user-defined or %ROWTYPE can be used. eg TYPE t_studentsref IS REF CURSORRETURN students%ROWTYPE

Ques : Differentiate between TRUNCATE and DELETE
Ans : TRUNCATE deletes much faster than DELETETRUNCATEDELETEIt is a DDL statementIt is a DML statementIt is a one way trip cannot ROLLBACKOne can RollbackDoesn’t have selective features (where clause)HasDoesn’t fire database triggersDoesIt requires disabling of referential constraints.

Ques : Difference between procedure and function.
Ans : Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself while a Function call is called as part of an expression.

Ques : Can a primary key contain more than one columns?
Ans : Yes

Ques : Difference between NO DATA FOUND and %NOTFOUND
Ans : NO DATA FOUND is an exception raised only for the SELECT….INTO statements when the where clause of the query does not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.