SQL Loader (Different Types of Files/Contents)

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

When we speak of the SQL*Loader environment, we are referring to the database, the SQL*Loader executable, and all the different files that you need to be concerned with when using SQL*Loader. These are shown in Figure

The SQL*Loader environment

The SQL*Loader Control File

The SQL*Loader control file is the key to any load process. The control file provides the following information to SQL*Loader:

• The name and location of the input data file
• The format of the records in the input data file
• The name of the table or tables to be loaded
• The correspondence between the fields in the input record and the columns in the database tables being loaded
• Selection criteria defining which records from the input file contain data to be inserted into the destination database tables.
• The names and locations of the bad file and the discard file

The Log File

The log file is a record of SQL*Loader's activities during a load session. It contains information such as the following:

• The names of the control file, log file, bad file, discard file, and data file
• The values of several command-line parameters
• A detailed breakdown of the fields and data types in the data file that was loaded
• Error messages for records that cause errors
• Messages indicating when records have been discarded
• A summary of the load that includes the number of logical records read from the data file, the number of rows rejected because of errors, the number of rows discarded because of selection criteria, and the elapsed time of the load

Always review the log file after a load to be sure that no errors occurred, or at least that no unexpected errors occurred. This type of information is written to the log file, but is not displayed on the terminal screen.

The Bad File and the Discard File

Whenever you insert data into a database, you run the risk of that insert failing because of some type of error. Integrity constraint violations undoubtedly represent the most common type of error. However, other problems, such as the lack of free space in a tablespace, can also cause insert operations to fail. Whenever SQL*Loader encounters a database error while trying to load a record, it writes that record to a file known as the bad file.

Discard files, on the other hand, are used to hold records that do not meet selection criteria specified in the SQL*Loader control file. By default, SQL*Loader will attempt to load all the records contained in the input file.

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.

Autonomous Transactions

Autonomous Transaction is a new feature in ORACLE. It allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or roll back those operations (without any effect on the main transaction), and then return to the main transaction.

Being independent of the main transaction (almost like a separate session), an autonomous transaction does not see the uncommitted changes from the main transaction. It also does not share locks with the main transaction. As a result, it can get into a deadlock with its parent … something the application developer should watch out for.

As expected, changes committed by an autonomous transaction are visible to other sessions/transactions immediately, regardless of whether the main transaction is committed or not. These changes also become visible to the main transaction when it resumes, provided its isolation level is set to READ COMMITTED (which is the default).

Any of the routines can be marked as autonomous simply by using the following syntax anywhere in the declarative section of the routine (putting it at the top is recommended for better readability):

E.g.

PRAGMA AUTONOMOUS_TRANSACTION;

Here is an example of defining a stored procedure as autonomous:

CREATE PROCEDURE process_ord_line_shipment

       (p_order_no number, p_line_no number) AS

   PRAGMA AUTONOMOUS_TRANSACTION;

   l_char_1     varchar2(100);

BEGIN

   ...

END;

Normalization / De-Normalization

Normalization:  It's the process of efficiently organizing data in a database.  There are two goals of the normalization process:  eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table).  Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

1. Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2. Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3. Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
4. Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5. Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.

1st Normal Form (1NF)

Def: A table (relation) is in 1NF if

1. There are no duplicated rows in the table.

2. Each cell is single-valued (i.e., there are no repeating groups or arrays).

3. Entries in a column (attribute, field) are of the same kind.

Note: The order of the rows is immaterial; the order of the columns is immaterial.

Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns).

2nd Normal Form (2NF)

Def: A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.

Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in 1NF and if it has no partial dependencies."

3rd Normal Form (3NF)

Def: A table is in 3NF if it is in 2NF and if it has no transitive dependencies.

Boyce-Codd Normal Form (BCNF)

Def: A table is in BCNF if it is in 3NF and if every determinant is a candidate key.

4th Normal Form (4NF)

Def: A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.

5th Normal Form (5NF)

Def: A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain-Key Normal Form (DKNF)

Def: A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.

De-Normalization:

Denormalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply Denormalization in the process of deriving a physical data model from a logical form.

EID MUBARAK

Cognizant Mega Job Openings for Freshers || Salary : 3.02 LPA || 07 Locations

Company Name : Cognizant

Designation : Programmer Analyst Trainee

Qualification :  B.E / B.Tech

Experience : Freshers

Location : Bangalore, Chennai, Coimbatore, Hyderabad, Kolkata, New Delhi, Pune

Salary : 3.02 LPA

Skill Set :

• Min 60% in recent education degree (for Universities in other states except Andhra Pradesh, Karnataka, Kerala, Pondicherry & Tamilnadu). For PG candidates 60% in preceding UG degree is acceptable
• No Standing Arrears at the time of Interview process
• Recent Education - convocation or provisional certificate and consolidated or semester wise mark sheet is also mandatory during interview process
• Strong written and oral communication skills
• Open only to Indian nationals.

Apply Link : Click Here

Source : Click Her

Freshers Job Openings In Wipro || Hiring In 14 Locations || salary : 2.75 - 3.25 LPA

Company Name : Wipro                                                                  

Designation : Software Developer

Qualification : B.Tech/B.E

Experience : Freshers

Location :
Bangalore, Bhubaneshwar, Chennai, Coimbatore, Greater Noida, Gurgaon,Hyderabad, Kochi,Cochin, Kolkata, Mumbai, Mysore, Pune, Visakhapatnam

Salary : 2.75 - 3.25 LPA

About Wipro :
Wipro Ltd (NYSE:WIT) is a global information technology, consulting and outsourcing company with 156,866 employees serving clients in 175 cities across 6 continents. The company posted revenues of $7.3 billion for the financial year ended Mar 31, 2014.

Wipro helps Freshers customers do business better by leveraging our industry-wide experience, deep technology expertise, comprehensive portfolio of services and vertically aligned business model. Our 55 dedicated emerging technologies ‘Centers of Excellence’ enable us to harness the latest technology for delivering business capability to our clients.

Freshers Jobs to Apply

Wipro is globally recognized for its innovative approach towards delivering business value and its commitment to sustainability. Wipro champions optimized utilization of natural resources, capital and talent. Today we are a trusted partner of choice for global businesses looking to ‘differentiate at the front’ and ‘standardize at the core’ through technology interventions.

Job Details : 

Develop features across multiple subsystems within our applications, including collaboration in requirements definition, prototyping, design, coding, testing and deployment.
Provide engineering support (when necessary) to other teams who are responsible for deployment and help them with debugging issues ( when needed).
Participate with the engineering and operations teams to define plans for standardizing, scaling and enhancing our products and the services utilized to deploy/install/release those products.
Investigate, analyze and make recommendations to management regarding technology improvements, upgrades and modifications.

Apply Mode : Online

Apply Link : Click Here

Source : Click Here

Alerts Styles

An alert is a modal window that displays a message notifying the operator of some application condition. There are three styles of alerts: Stop, Caution, and Note.  Each style denotes a different level of message severity.  Message severity is represented visually by a unique icon that displays in the alert window.

Model/Modeless Windows (Difference)

A window can be either modeless or modal.  A modal window (often a dialog) requires the end user to respond before continuing to work in the current application.  A modeless window requires no such response.

You can display multiple modeless windows at the same time, and end users can navigate freely among them. Modeless windows remain displayed until they are dismissed by the end user or hidden programmatically.  You can set the Hide on Exit property for a modeless window to specify whether it should remain displayed when the end user navigates to another window. Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms, modal windows are "always-on-top" windows that cannot be layered behind modeless windows. The Hide on Exit property does not apply to modal windows. Modal dialog windows cannot have scroll bars

Property Class & Visual Attributes (Difference)

A property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it.  An object based on a property class can inherit the setting of any property in the class that makes sense for that object. Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a universal subclassing parent. Property classes are separate objects, and, as such, can be copied between modules as needed. Perhaps more importantly, property classes can be subclassed in any number of modules. Property class inheritance is a powerful feature that allows you to quickly define objects that conform to your own interface and functionality standards. Property classes also allow you to make global changes to applications quickly.  By simply changing the definition of a property class, you can change the definition of all objects that inherit properties from that class.

Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application's interface. Visual attributes can include the following properties: Font properties, Color and pattern properties. Every interface object has a Visual Attribute Group property that determines how the object's individual visual attribute settings (Font Size, Foreground Color, etc.) are derived

Named visual attributes define only font, color, and pattern attributes; property classes can contain these and any other properties.

You can change the appearance of objects at runtime by changing the named visual attribute programmatically; property class assignment cannot be changed programmatically.

When an object is inheriting from both a property class and a named visual attribute, the named visual attribute settings take precedence, and any visual attribute properties in the class are ignored.

Property Class has triggers and Visual Attributes don’t have same.

Inline Views & Top N Analysis

The Inline view: It is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.

A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.

           

Top N Analysis: The task of retrieving the top or bottom N rows from a database table. You can do so either by using the ROWNUM pseudocolumn available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK () and DENSE_RANK ().

Using the ROWNUM Pseudocolumn
One-Way to solve this problem is by using the Oracle pseudocolumn ROWNUM. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.

E.g. To select top 5 rows

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal

   FROM

   (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal

      FROM Emp

      ORDER BY NVL (Sal, 0) DESC)

   WHERE ROWNUM < 6;

Utilizing Oracle 8i's Ranking Functions

Another way to perform a top-N query uses the new Oracle 8i feature called "analytic functions.

SELECT Empno, Ename, Job, Mgr, Sal,

   RANK () OVER

      (ORDER BY SAL Desc NULLS LAST) AS Rank,

   DENSE_RANK () OVER

      (ORDER BY SAL Desc NULLS LAST) AS Drank

   FROM Emp                             

   ORDER BY SAL Desc NULLS LAST;

The difference between RANK () and DENSE_RANK () is that RANK () leaves gaps in the ranking sequence when there are ties. In our case, Scott and Ford tie for second place with a $3,000 salary; Jones' $2,975 salary brings him in third place using DENSE_RANK () but only fourth place using RANK (). The NULLS FIRST | NULLS LAST clause determines the position of rows with NULL values in the ordered query.

List Item Types

Poplist: The poplist style list item appears firstly as a single field (similar to a text item field).  When the end user selects the list icon, a list of available choice appears.

Tlist: The Tlist style list item appears as a rectangular box, which displays a permanent number of values.  When the Tlist contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the end user to view and select undisplayed values.

Combo Box: The combo box style list item combines the features found in poplists and text items. It displays fixed values and can accept a user-entered value.

Different Database Triggers

Database triggers are PL/SQL, Java, or C procedures that run implicitly whenever a table or view is modified or when some user actions or database system actions occur. Database triggers can be used in a variety of ways for managing your database. For example, they can automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.

Row Triggers

A row trigger is fired each time the table is affected by the triggering statement.

 For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement.

If a triggering statement affects no rows, a row trigger is not executed at all.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.

Statement Triggers

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected).

 For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.

For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.

BEFORE vs. AFTER Triggers

When defining a trigger, you can specify the trigger timing.

That is, you can specify whether the trigger action is to be executed before or after the triggering statement.

BEFORE and AFTER apply to both statement and row triggers.

BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:

BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.

BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:

AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.

If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

Combinations

Using the options listed in the previous two sections, you can create four types of triggers:

BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.

BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.

AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.

AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

New Database Triggers

Startup, Shutdown, Logon, Logoff, Alter, Create, Drop

Various Cursor Attributes

SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.

SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.

SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.

SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.

P/L SQL Tables / Arrays

PL/SQL tables are declared in the declaration portion of the block. A table is a composite datatype in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named. The column can be any scalar type but primary key should be a BINARY_INTEGER datatype.

Rules for PL/SQL Tables:

1.      A loop must be used to insert values into a PL/SQL Table

2.      You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.

Diff b/w Package Spec & Body

Packages provide a method of encapsulating and storing related procedures, functions and other package constructs as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).

Package specification contains declarations of public constructs where as the package body contains definitions of all those public constructs and declarations & definitions of private constructs.

Column Mode Property in Reports

The Column Mode property controls how Report Builder fetches and formats data for instances of repeating frames.  With Column Mode set to yes, the next instance of a repeating frame can begin formatting before the previous instance is completed. With Column Mode set to No, the next instance cannot begin formatting before the previous instance is completed. Column Mode is used mainly for master repeating frames or repeating frames that contain fields that may expand vertically or horizontally (i.e., elasticity is Variable or Expand). 

Lexical & Bind Parameters in Reports

Lexical Parameters: Lexical references are placeholders for text that you embed in a SELECT statement.  You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

You cannot make lexical references in a PL/SQL statement.  You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL.  Look at the example below.

You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name.  A default definition is not provided for lexical references. Therefore, you must do the following:

Ø  Before you create your query, define a column or parameter in the data model for each lexical reference in the query.  For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value.  Report Builder uses these values to validate a query with a lexical reference.

Ø  Create your query containing lexical references.

Bind Parameters: Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date.  Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.  Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

You create a bind reference by entering a colon (:) followed immediately by the column or parameter name.  If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

Matrix Reports (Matrix By Groups)

A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels.  A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database. 

To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells.  The groups can belong to a single query or to multiple queries. 

A matrix with group report is a group above report with a separate matrix for each value of the master group.

A nested matrix (cross tab) report is a matrix report in which at least one parent/child relationship appears within the matrix grid. 

           The new Child Dimension property of the nested group enables you to eliminate empty                              rows and/or columns in your single-query nested matrix.

Types of Matrix Reports

Simple Matrix Report:

Is a matrix with only two dimensions

Nested Matrix Report: Has multiple dimensions going across and/or down the page

Multi-Query Matrix with Break: Is similar to a nested matrix report in that it has more than two dimensions. Does not display records that do not contain data

Matrix Break Reports: Contains a new matrix for each master record

Flex & Confine Mode in Reports

Confine mode:

1. Switched on by default; change via View® View Options® Layout...
2. It prevents operations which would cause a report not to work e.g. moving a field outside its parent repeating frame

Flex mode:

Moves the object it’s enclosing objects and objects in their push path simultaneously to maintain the same overall relationship in the report. E.g. if you try to move a field outside its repeating frame, the Repeating Frame will grow to accommodate the field and so will any objects around the repeating frame.

Only one object can be moved/resized at one time in flex mode - if you try more than one only one whose control point is clicked on will be done, the other objects will be de-selected.

Objects can be moved/resized horizontally or vertically; not diagonally. 

Static & Dynamic LOV/Format Triggers (What are they)

1.     Static & Dynamic LOV

The static LOV contains the predetermined values while the dynamic LOV contains values that come at run time

2.     Format Triggers (What are they)

A format trigger is a PL/SQL function executed before an object is formatted.  A trigger can be used to dynamically change the formatting attributes of the object. 

Report Level Triggers (Sequence)

• Before parameter form
• After parameter form
• Before Report
• Between Pages
• After Report

Various Block Co-ordination Properties

The various Block Coordination Properties are

a) Immediate

            Default Setting. The Detail records are shown when the Master Record are shown.

b) Deffered with Auto Query

Oracle Forms defer fetching the detail records until the operator navigates to the detail block.

c) Deferred with No Auto Query

The operator must navigate to the detail block and explicitly execute a query

 How to attach same LOV to multiple items

We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.