Are value sets mandatory for DFF segment

Values sets for the segments in DFF are not mandatory. In DFF form on check box as unable / disable you can use as required.

Is it possible to enable/ disable only few field of any DFF?

Yes, it is possible to enable or disable few fields in DFF by using check box.

What are the different parameter Types?

TEXT_PARAMETER

DATA_PARAMETER

Text_Parameter: When a single value passed as a parameter to the called form, the parameter type must be set to ‘’Text_Parameter’.

Data_Parameter: When a record group is being passed as a parameter, the parameter type must be set to ‘Data_parameter’.
When the value of a data parameter being passed to a called product is always the name of a record group defined in the current form. Data parameters are used to pass data to products invoked with the RUN_PRODUCT built-in subprogram.

What are the parameters?

Parameters provide a simple mechanism for defining and setting the values of inputs that are required by a form at startup. Form parameters are variables of type CHAR, NUMBER, or DATE that you define at design time.

A built-in package procedure named CREATE_PARAMETER_LIST create a parameter list and return the memory address of the parameter list. A variable of type PARAMLIST must be declare to hold the memory address returned by the function.

Each parameter list created using CREATE_PARAMETER_LIST() must be assigned a unique name. A parameter list can be referenced by its ‘Name’ or ‘Variable’ that holds the pointer to parameter list.

A build in package procedure ADD_PARAMETER add parameter to the parameter list. This procedure accepts the following parameter:

A.    Name of the parameter list

B.    Name of the parameter

C.    Type of parameter

D.    Value of the parameter

e.g.

Declare

Client_list paramlist;

Begin

Client_list := create_parameter_list(‘client_info’);

Add_parameter(client_list,’p_client_no’,text_parameter,’C00001’);

End;

What is CALL_FORM built in?

When one form invokes another form by executing CALL_FORM the called form is a model with respect to the calling form. That is any window that belong to the calling form are disable & operator can’t navigate to them until they first exist the called form.

The CALL_FORM function opens the form in dialog mode.

Syntax:

CALL_FORM (‘form_name’,display,switch_menu,query_mode,parameter_list)

Display: This argument can be set to ‘HIDE’ or ‘NO_HIDE’. If this argument is set to ‘HIDE’, the calling form will be made invisible before calling the called form. If this argument is set to ‘NO_HIDE’, the called form will be opened without hiding the called form.

Switch_menu: This argument can be set to ‘NO_REPLACE’ or ‘DO_REPLACE’. If this argument is set to ‘NO_REPLACE’, the menu of the called form will not be replaced with the menu of calling form. If this argument is set to ‘DO_REPLACE’, the menu of the called form will be replaced with the menu of calling form.

Query_mode: This argument can be set to ‘NO_QUERY_ONLY’ or ‘QUERY_ONLY’. If this argument is set to ‘QUERY_ONLY’, the called form is opened for query purpose only. If this argument is set to ‘NO_QUERY_ONLY’, the called form can be used data insertion, updation, deletion & viewing.

Parameter_list: This argument must be set to the name of the parameter list created using create_parameter() function.

What is OPEN_FORM built in?

When one form invokes another form by executing OPEN_FORM the first form remain displayed & operator can navigate between the forms.

The OPEN_FORM function opens the form in document mode.

What is NEW_FORM built in?

When one form invoke another form by executing NEW_FORM oracle form exit the first form and release its memory before loading the new form. Calling new form completely releases its memory before loading the new form and replaces the first with second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.

What is bulk binding please explain me in brief?

Bulk Binding is used for avoiding the context switching between the sql engine and pl/sql engine. If we use simple FOR loop in pl/sql block it will do context switching between sql and pl/sql engine for each row processing that degrades the performance of pl/sql block. So that for avoiding the context switching between two engine we use FORALL keyword by using the collection pl/sql tables for DML. FORALL is pl/sql keyword.

To do bulk binds with Select statements you include the Bulk Collect INTO a collection clause in the SELECT Statement instead of using simply into. It will provide good result and performance increase.

FOR UPDATE in PLSQL

The FOR UPDATE clause locks the rows selected by the query. Once you have selected a row for update, other users cannot lock or update it until you end your transaction. The FOR UPDATE clause signals that you intend to insert, update, or delete the rows returned by the query, but does not require that you perform one of these operations. A SELECT statement with a FOR UPDATE clause is often followed by one or more UPDATE statements with WHERE clauses.

The FOR UPDATE clause cannot be used with the following other constructs:

·         DISTINCT operator

·         GROUP BY clause

·         set operators

·         group functions

FOR UPDATE OF in PLSQL

Note that the columns in OF clause only specify which tables’ rows are locked. The specific columns of the table that you specify are not significant. If you omit the OF clause, Oracle7 locks the selected rows from all the tables in the query.

1)     The following statement locks rows in the EMP table with clerks located in New York and locks rows in the DEPT table with departments in New York that have clerks:

    SELECT empno, sal, comm

    FROM emp, dept

    WHERE job = ’CLERK’

    AND emp.deptno = dept.deptno

    AND loc = ’NEW YORK’

    FOR UPDATE;

2)  The following statement only locks rows in the EMP table with clerks located in New York; no rows are locked in the DEPT table:

    SELECT empno, sal, comm

    FROM emp, dept

    WHERE job = ’CLERK’

    AND emp.deptno = dept.deptno

    AND loc = ’NEW YORK’

    FOR UPDATE OF emp;

When ‘Where Current of’ clause is used.

This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row.

If the cursor is not open, the CURRENT OF clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception NO_DATA_FOUND.

WHERE CURRENT is used as a reference to the current row when using a cursor to UPDATE or DELETE the current row.

Is space acquired in blocks or extents?

In extents.

Difference between % Type & % Row Type?

The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. i.e: v_EmpRecord  emp%ROWTYPE;

%TYPE is used to declare a field with the same type as that of a specified table's column. i.e:

v_EmpNo  emp.empno%TYPE;

Describe Joins in PLSQL

Join is the process of combining data from two or more tables using matching columns. This relational computing feature consolidates multiple data tables for use in a single report. The SQL JOIN statement is used to combine the data contained in two relational database tables based upon a common attribute.

Equi-Join: A join statement that uses an equivalency operation (i.e: colA = colB). The converse of an equijoin is a nonequijoin operation. In the Equi-Join two (or more) tables are linked via a common domain attribute. This is the most common form of joining used in relational data manipulation.

An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.

Outer Join: The Outer-Join is the opposite of an Equi-Join. It searches for records that exist outside of the specified Join condition. The (+) symbol is the Outer Join operator which causes the join condition to be inverted.

The outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join condition. Such rows are not returned by a simple join. To write a query that performs an outer join of tables A and B and returns all rows from A, apply the outer join operator (+) to all columns of B in the join condition. For all      rows in A that have no matching rows in B,

Self Join: A join in which a table is joined with itself. Sometimes we need to join a table to itself in order to search for the correct data.

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that are used to qualify column names in the join condition.

                Ex :-

            SELECT e1.ename||’ works for ’||e2.ename

            ”Employees and their Managers”

            FROM emp e1, emp e2 WHERE e1.mgr = e2.empno

Non equijoin: A join statement that does not use an equality operation (i.e: colA <> colB). The converse of a non equijoin is an equijoin.

How to retrieve DDL from Database?

DBMS_METADATA package will work for tables, indexes, views, packages, functions, procedures, triggers, synonyms, and types. 

DBMS_METADATA.GET_DDL (object_type, name, schema)

DBMS_METADATA.GET_XML (object_type, name, schema)

SELECT DBMS_METADATA.GET_DDL (‘TABLE’, ‘EMP’, ‘SCOTT’) from dual;

What could happen if we use WHEN OTHERS before any predefined exceptions

According to the Oracle standards “When Others“ exception must be the last exception. All the Predefined exceptions must be used before the “When others” exception.

If “When others” exception used before any pre-defined exceptions then procedure/function shows the compilations errors.

Properties of a Transaction (ACID)?

The essential properties of a transaction are commonly termed as ACID, an abbreviation that can be expended as follows:

A          Atomicity

C          Consistency

I           Isolation

D          Durability

ATOMICITY: Atomicity means that the effect of the transaction it either full or null. It is assumed that a system always starts from a consistent state and moves to another consistent state after executing a complete transaction.

CONSISTENCY: Consistency means that a transaction should generate consistent data defined by application logic and inconsistency should not be generated due to the nature of the system, such as job scheduling or slow disk I/O.

ISOLATION: Isolation indicates the level of interference in one transaction due to the other transaction. A strict isolation level is the basic condition for executing of a transaction as if it is lonely executed.

DURABILITY: The effect of a transaction should be durable, irrespective if the nature of storage, such as volatile, nonvolatile memory.

Bitmap/B-Tree Index (Difference, Advantages)

A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.

Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform.

Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).

Standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values.

What is Transaction (Its ACID Property)

A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly committed or rolled back. A transaction is an atomic unit of work that either fails or succeeds. There is no such thing as a partial completion of a transaction. Since a transaction can be made up of many steps, each step in the transaction must succeed for the transaction to be successful. If any one part of the transaction fails, then the entire transaction fails. When a transaction fails, the system needs to return to the state that it was in before the transaction was started. This is known as rollback. When a transaction fails, then the changes that had been made are said to be "rolled back." In effect, this is acting similar to the way the Undo command works in most word processors. When you select undo, the change that you just may have made is reversed. The transaction processing system is responsible for carrying out this undo.

When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics. ACID is an acronym for atomicity, consistency, isolation, and durability.

An atomic transaction is either fully completed, or is not begun at all.

A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state.

When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time.

A transaction is durable in that once it has been successfully completed; all of the changes it made to the system are permanent.

How to spool sql statement to log file

spool /home/xxx/update_xxx.log

sqlplus -s <!
set serveroutput on
set echo on
set time on
set timing on

select *
from MTL_CATEGORIES_B;

Spool off;