Developer At Your Service

– CASE is a expression – DECODE is a function - CASE expression is ANSI SQL Statement - DECODE is specific to Oracle Syntax - CASE syntax contains WHEN and THEN  - DECODE will not have this - CASE allow expressions or Scalar Sub query Expressions inside the CASE statement - DECODE won't allow expressions inside the DECODE function

In Oracle, an  INDEX  is a database object intended to improve the performance of  SELECT  queries. Indexes are created on table columns, and the index stores all the values of the column under index segments. If the  SELECT  query uses an indexed column in any of the  WHERE  conditions, the query uses the index segment instead of performing a full table scan. This results in enhanced query performance.

1) Block Processing Triggers 2) Interface Event Triggers 3) Master/Detail Triggers 4) Message-Handling Triggers 5) Navigational Triggers 6) Query-Time Triggers 7) Transactional Triggers 8) Validation Triggers

The Oracle Server consists of an Oracle instance and an Oracle database. Oracle instance : Is a means to access an Oracle database. Its Always opens one and only one database. An Oracle instance is the combination of the background processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a system global area (SGA) is allocated and Oracle background processes are started.The SGA is a memory area used to store database information that is shared by database processes. Oracle database : An Oracle database is a collection of data that is treated as a unit. The general purpose of a database is to store and retrieve related information. The database has a logical structure and a physical structure. The physical structure of the database is the set of operating system files in the database.

A SELECT statement retrieves information from the database. You can do the following: Projection: You can use the projection capability in SQL to choose the columns in a table that you want returned by your query. You can choose as few or as many columns of the table as you require. Selection: You can use the selection capability in SQL to choose the rows in a table that you want returned by a query. You can use various criteria to restrict the rows that you see. Joining: You can use the join capability in SQL to bring together data that is stored in different tables by creating a link between them. Here Link is nothing but joining tables.

Data retrieval SELECT Data manipulation language (DML) INSERT UPDATE DELETE MERGE Data definition language (DDL) CREATE ALTER DROP RENAME TRUNCATE Transaction control COMMIT ROLLBACK SAVEPOINT Data control language (DCL) GRANT REVOKE

What is SQL? SQL Stands for Structured Query Language. I t is the  international  standard language for relational database management systems. It is useful for  accessing and manipulating data in a databases.  To access the database, you execute a structured query language (SQL) statement, which is the American National Standards Institute (ANSI) standard language for operating relational databases.

A relational database is a collection of relations or two-dimensional tables. For example, you might want to store information about all the employees in your company. In a relational database, you create several tables to store different pieces of information about your employees, such as an employee table, a department table, and a salary table.

A database is an organized collection of information. To manage databases, You need database management systems (DBMS). A DBMS is a program that Stores, Retrieves, and Modifies data in the database on request

This column has focused on where and how to define code in Oracle Database, but the same rules and logic apply to the Oracle Developer environment. I suggest these guidelines: If your program could be useful on both the server side and the client side, move it to the server, because it can be invoked from both sides there. If the program is used only in and relevant to client-side modules (it may, for example, manipulate the contents of a field in a form) and you think or know that it will be useful in more than one module, put it into a shared library. If your client program is very specific to a current form or report, define it within that module.

In the first stage of the development, you determine what the user needs and expects. While it may be tempting to skip this stage and start building right away, it is not a good idea to do so. Without a clear understanding of the users and their reporting needs, it is virtually impossible to create effective enterprise reports. To define user requirements: 1) Gather relevant policies, business rules, and existing documentation 2) Observe users and their daily job activities 3) Interview a wide variety of users Helpful questions to help you determine the user requirements: 1) What data will people want, and in what priority? 2) How is the data stored? 3) Is there a corporate standard that must be met? If so, define standard templates. 4) Will users want Web reports, paper reports, or both? 5) For Web reports, will the reports be static or dynamic? 6) Will users want charts in the report? If so, what data will be used in the graph? 7) Will users want to drill down on da...

DUAL is a table automatically created by Oracle along with the data dictionary. DUAL is in the schema of the user SYS, but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value ’X’. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table