Web Programming in VB .NET & ASP .NET

Overview
This course will familiarize you with the most powerful tool for designing and developing Web based applications i.e

Visual Studio.NET. It's the most powerful tool for developing applications at a RAPID speed. The simplicity of

learning and applying the concepts is its most powerful asset.
A balanced blend of lectures and programming exercises provides in-depth understanding as well as hands-on

experience. A powerful learning cycle process is repeatedly used, first to integrate basic concepts, and then to

reuse those concepts to master more advanced topics.
Every main feature of ASP.NET is the object of detailed programming exercises. Ample source-code examples are

provided and many source-code walkthroughs are conducted. By the end of the course, participants should feel

comfortable building ASP.NET applications.

Objectives
The objectives of this course are:
•    To learn the VB.NET language syntax.
•    To learn the Object Oriented Programming Concepts.
•    To use ADO.NET Objects to develop database applications.
•    To understand the importance of Component Based Developments.
•    To develop and deploy and configure ASP.NET Web Applications.
•    To proivde you with in-depth concepts of the ASP.NET, which will also help you to pass the MCP Certification

in ASP.NET
At the end of the course participants would be provided with a concept implementing project work.
Audience
This course is designed to meet the needs of those who want to be professional Web developers using either VB.NET as

programming language. The course will also guide you for Microsoft Certification Exam in ASP.NET.
Prerequisite
Students should be familiar with basic programming techniques and Operating system features.
Faculty
Sandeep Soni (Microsoft Certified Professional for ASP.NET)
Couse Outline
MS.NET Framework
•    Problems with DNA Architecture
•    The .NET Framework - an Overview
•    Architecture of .NET Framework
•    The Common Language Runtime (CLR)
•    Common Type System (CTS)
•    Types of JIT's
•    Role of METADATA
•    .NET Memory Management
•    What's .NET Class Framework
•    .NET Assemblies and Modules
VB.NET
•    Essentials of Object-Oriented Programming
•    Object and Class Definition
•    Understanding Identity, State, and Behavior
•    Using encapsulation to combine methods and data in a single class
•    Inheritance and Polymorphism.
•    Understanding Data Types and Variables
•    Common Type System
•    Naming Variables
•    Using Data Types
•    Converting Data Types
•    Value type and Reference type variables
•    Developing Console Application
•    Sub Main
•    Command Line Parameters
•    Compilation
•    Operators and Statements
•    Types of Operators
•    Types of Statements
•    Arrays, Collections and String
•    Declaring and Using Arrays
•    Overview of Collection classes
•    Dictionary and Lists
•    Stack and Queue
•    String Handling
•    Working with Classes and Objects
•    Adding Variables and Methods
•    Properties and Indexers
•    Constructors and Destructors
•    Memory Management and Garbage Collection
•    Shared Members
•    Method Overloading
•    Attributes and their Usage
•    Inheritance, Interface and Polymorphism
•    Deriving classes
•    Calling base class constructor
•    Overriding Methods
•    Using overridable, overrides and shadows keywords
•    Non-Inheritable Clases.
•    Abstract Class
•    Interface Inheritance
•    Namespace and Access Modifiers
•    Delegates and Events
•    Delegates
•    Events
•    Exception Handling
•    Handling Exceptions using try and catch
•    Raising Exceptions using throw
•    Pre-defined Exception classes
•    Custom Exception classes
•    Data Streams and Files
•    Byte Streams
•    Binary Streams
•    Readers and Writers.
•    Console and File I/O
•    Serialization
•    Working with Components
•    Creating a Simple .NET Framework Component
•    Singing Assemblies with Strong Names
•    Deploying .NET Assemblies in Global Assembly Cache
•    Creating a .NET Framework client application to use a Component
•    Using a COM Component in .NET Framework application
•    Using a .NET Assembly in COM based application
•    Data Access using ADO.NET
•    Comparison between ADO and ADO.NET
•    ADO.NET Concepts and Overview
•    Managed Providers
•    ADO.NET Classes
•    Replacing Recordsets with Datasets?
•    Structure of a DataSet
•    Creating a DataSet From a Database.
•    Adding, Deleting and Modifying records in a Dataset
•    Data Tables, Data Columns and DataRows
•    Constraints and DataRelations
•    Using DataViews
eXtensible Markup Language (XML)
•    Introduction to XML
•    Why XML?
•    Where is XML Used
•    XML Design Principles.
•    Structure of a Basic XML Document
•    XML Declarations
•    Tags & Elements
•    Attributes
•    Entities & Entity References
•    Document Type Definition (DTD)
•    Validation of XML File
•    Element Declaration
•    Attribute Declaration
•    Entity and Notaion Declaration
•    XML Namespace
•    Purpose of Namespace
•    Declaring Namespace
•    Default Declaration
•    Explicit Declaration
•    XML Schema
•    How are Schemas better than DTD
•    Builtin Data types
•    Custom Data types
•    Schema Vocabulary and elements
•    Content Model
•    Element and Attribute Declaration
•    Element Inheritance
•    Document Object Model (DOM)
•    Introduction
•    DOM Interfaces
•    Nodes and Objects
•    Applying DOM using Javascript and VB.NET
•    Cascading Style Sheet
•    CSS Properties Values
•    BOX Properties
•    Text and Font Properties
•    Color and Background Properties
•    Borders and List Properties
•    XSL (eXtensible Stylesheet Language)
•    XSLT & XPath
•    XSL Template
•    XSL Pattern
•    Transformation to HTML Document
•    Transformation to XML Document
ASP
•    ASP Architecture
•    ASP Objects
•    Request and Response Structure
•    Forms and Querystring
ASP.NET
•    Overview
•    ASP.NET Architecture.
•    New Features over ASP
•    Web Forms
•    Introduction
•    Advantages
•    Comparison with HTML Form
•    Page Processing States
•    Event Model
•    View State Management
•    Using Web Controls
•    HTML Server Controls
•    ASP.NET Sever Controls
•    Validation Controls
•    Web User Controls
•    Loading Controls Dynamically
•    Web Custom Controls
•    Developing Composite Controls
•    ASP.NET State Management
•    ViewState Object
•    Cookies
•    Session Management
•    Application State
•    ASP Intrinsic Objects
•    Request and Response
•    Session Objects
•    Application Objects
•    Server and Context
•    Data Access Controls
•    Data Binding Server Controls
•    Binding to Simple Properties and Collections
•    Data Grid
•    Data List and Repeater
•    ASP.NET Web Application
•    Creating Virtual Directories
•    Global.asax
•    Page Life Cycle
•    Managing Application Events
•    HttpApplication Instance
•    Configuration
•    Configuration Overview
•    Machine.Config and Web.Config
•    Inheritance of Configuration Settings
•    Configuration File Format
•    Retrieving Configuration Date
•    Trace Functionality
•    Overview of Tracing
•    Trace Information
•    Page-Level Trace
•    Application-Level Trace
•    Caching
•    Overview
•    Page Output Caching
•    Page Fragmet Caching
•    Data Caching
•    Expiration Policies
•    Security
•    Authentication & Authorization
•    Configuring in web.config file and IIS
•    Windows Based Authentication
•    Form Based Authentication
•    Authorizing Users and Roles
•    Impersonation
•    Debugging and Tracing
•    Debug and Release Compilation
•    Debug and Trace Objects
•    Tracing Listeners
•    Tracing Switches
•    Packaging and Deployment
•    Types of Deployment Modules
•    Creation of Strong Named Assemblies
•    Registering with Global Assembly Cache
•    Working with a Setup Project
•    Working with a Merge Module Project
•    Globalization and Localization
•    Request and Response Encoding
•    Right to Left Mirroring
•    Culture specific formatting
•    Localizing a Web Project
•    Creating a Resource fle
•    Creating a Satellite Assembly
•    Interoperability
•    Instantiate and Invoke COM Objects
•    Using COM+ Services
•    Calling Native functions
•    Developing Transactional Aplications
•    E-Mails
•    Configuring SMTP Server
•    Sending and Receiving e-mails
•    Web Services
•    Architecture and Advantage.
•    SOAP Significance.
•    Building Services
•    Hosting Services
•    Discovering and Using Web Services (vsdisco and disco files)
•    Using WSDL to Build Proxy for clients
•    Working with SOAP Headers
•    Asynchronous access to WebService
•    Setting up a 3-Tier Application
•    Securing Web Services

Triggers - A Depth View...

TRIGGERS
Triggers are block of PL/SQL code that you write to perform your specific tasks.They  are pre-defined runtime events

for which oracle will provide you to associate a trigger.All the trigger name correspond to these events.The events

which can be trapped are GUI events  as well as transactional events.

What are all theValid Statements in Trigger Text
•    Select statements
•    DML statements
•    Restricted built-in subprograms
•    Unrestricted built-in subprograms

What is Restricted Built-in ?
Restricted built-ins are those built-ins which initiate navigation like NEXT_ITEM etc
and also those that involve database transactions,such as CREATE_RECORD.

Different Trigger types

1.    BLOCK PROCESSING TRIGGERS
   
•    WHEN-CLEAR-BLOCK
Fires just before the oracle forms clears the data from the current block.It does not fire when the Oracle forms 

clears the current block during the CLEAR_FORM event.
Defn Level         :         Form or Block.
Enter-Query Mode     :        Yes
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
•    use this trigger to perform action everytime oracle forms flushes the current block.For example you might

want to perform automatic commit whenever this condition occurs
•    The value of  SYSTEM.RECORD_STATUS  is unreliable because there is no current record. An alternative is to

use GET_RECORD_PROPERTY to obtain the record status.because GET_RECORD_PROPERTY requires you to reference a specific

record,its value is always accurate.
Failure of Trigger    :        No effect of block clearing
Fires in        :        CLEAR_BLOCK
                    COUNT_QUERY
                    ENTER-QUERY
                    Open the Query.

•    WHEN-CREATE-RECORD
Fires when Oracle forms creates a new record or the form operator presses the [insert  key] or while scrolling he

scrolls to the last record in the set.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
use this trigger to perform a action every time when oracle attempts to create  a new record.   
Failure of the Trigger    :        Prevents the New record to be created and
                    the control returns to previous location if
                    possible.
Fires in        :        CREATE_RECORD.

•    WHEN-DATABASE-RECORD
Fires when the oracle marks the record as INSERT or UDATE.That is the oracle forms through validation process

determines that record should processed as INSERT or UPDATE in the Next Post or Commit Process.This Occurs after a

Operator modifies the first item of a record and tries to navigate out of the Item.
Defn Level         :        Form or Block
Enter-Query Mode      :        No
Legal Commands    :        SELECT statements,unrestricted built-ins.

When to Use ?
Use this trigger to perform a action when oracle forms marks a record as Insert or Update
Failure of Trigger    :        Nothing happens.

•    WHEN-REMOVE-RECORD
Fires whenever the operator or the application Clear record or Delete the Record.
Defn Level        :        Form,Block and Item
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins

When to use ?
Use this trigger to perform a action when oracle forms tries to clear a record or delete record.
Failure of Trigger    :        Oracle Forms navigates to Block level with
                    or without validation and tries to put the
                    cursor at the target block.
Fires in        :        CLEAR_BLOCK
                    DELETE_RECORD
                    PREVIOUS_RECORD.

2.     MASTER-DETAIL TRIGGERS

•    ON-CHECK-DELETE-MASTER
Oracle forms creates this trigger automatically when we define a master-detail relation ship ans set Master Deletes

property to NON- ISOLATED.It fires when a attempt is made to DELETE  record in Master-block  of a master-detail

relation-ship.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,unrestricted built-ins
                    and DML statements.

   
When to use ?
It is used to check whenever you try to delete the master record,whether any detail records of the master exists, in

case any details are present then the Master record is prevented from deletion.

Failure of Trigger    :        Prevents the deletion of the Master record.
Fires in        :        MASTER-DETAIL COORDINATION.

•    ON-CLEAR-DETAIL
Fires whenever a coordination causing event occurs in Master-Block.The coordination causing event is the event which

makes the a different master record as the current record, in the Master Block.
Defn Level        :        Form or Block.
Enter-Query Mode    :        No
Legal Commands    :        Any commands including restricted built ins

Failure of Trigger    :        Causes the coordination-causing operation and any
                    scheduled coordination triggers to abort.
Fires in        :        MASTER-DETAIL COORDINATION.

•    ON-POPULATE-DETAIL
Whenever we create a master-detail relation ship,oracle forms automatically will create this trigger.It fires when

oracle forms normally needs to populate the detail block in master-detail relation.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,PL/SQL,unrestricted and
                    restricted built-ins.
When to Use ?
•    Use this trigger to replace the default populate phase of a Query in Master-detail.
•    When the immediate Coordination is set this causes the details of the instantiated master to be populated

immediately. By default the Coordination is immediate.
•    WhenDeffered is coordination status,then this trigger fires,Oracle forms marks the blocks as needing to be

coordinated.
•    If you intend to manage the coordination on your own then there is built in called as the

SET_BLOCK_PROPERTY(COORDINATION_STATUS).
Note : This trigger will not fire if there is no ON-CLEAR-DETAILS trigger.
Failure of Trigger    :        Causes an inconsistent state in the Form
Fires in        :        MASTER-DETAIL COORDINATION.


3.     MESSAGE-HANDLING TRIGGERS

•    ON-ERROR
This trigger fires whenever a Oracle forms would normally cause an Error message to display.
Defn Level           :        Form,Block or Item
Enter-Query Mode    :        Yes
Legal Commands    :        SELECT statements,unrestricted built-ins
When to Use ?
•    Use this trigger to trap and recover from a error
•    To replace a default error message with a custom error message
Failure of Trigger    :        No effect

•    ON-MESSAGE
This trigger fires whenever oracle form would normally cause a message to display.This trigger will replace the

writing of the message to the message line.
Defn Level        :        Form,Block or Item
Enter-Query Mode    :        Yes
Legal Commands    :        SELECT statements,unrestricted built ins.
When to Use ?
•    Use this trigger to exclude or to replace a message with a custom message.The GET_MESSAGE built does not

return any value.
Failure of Trigger    :        No effect

3.     TRANSACTIONAL TRIGGERS

•    ON-CHECK-UNIQUE
This trigger fires during the commit operation,when oracle forms noramally checks that the primary key values are

unique for each INSERT or UPDATE in a base table.It fires once for each record UPDATED or INSERTED.This record

replaces the DEFAULT checking of UNIQUENESS for records,when PRIMARY key property of the record is set to true.
Defn Level        :        Form or Block
Enter-Query Mode    :        No
Legal Commands    :        SELECT statements,PL/SQL and unrestricted
                    built ins
When to Use ?
To check the uniqueness of items before they are inserted or updated.To do the Default processing from this trigger

call the built in CHECK_RECORD_UNIQUENESS.
Failure of Trigger    :        No effect
Fires in        :        CHECK RECORD UNIQUENESS
                    POST AND COMMIT TRANSACTIONS.
•    ON-CLOSE
This trigger normally fires when the operator or an application closes a query.The query is closed by default when

all the records identified by query criteria have been fetched or when the operator or the application aborts the

query.

SQL - A depth View

What is SQL and where does it come from?
Structured Query Language (SQL) is a language that provides an interface to relational database systems. SQL was

developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard.

SQL is often pronounced SEQUEL.
In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data

Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called

SQL2. SQL3 support object extensions and will be (partially?) implemented in Oracle8.


What are the difference between DDL, DML and DCL commands?
DDL is Data Definition Language statements. Some examples:
•    CREATE - to create objects in the database
•    ALTER - alters the structure of the database
•    DROP - delete objects from the database
•    TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
•    COMMENT - add comments to the data dictionary
•    GRANT - gives user's access privileges to database
•    REVOKE - withdraw access privileges given with the GRANT command
DML is Data Manipulation Language statements. Some examples:
•    SELECT - retrieve data from the a database
•    INSERT - insert data into a table
•    UPDATE - updates existing data within a table
•    DELETE - deletes all records from a table, the space for the records remain
•    CALL - call a PL/SQL or Java subprogram
•    EXPLAIN PLAN - explain access path to data
•    LOCK TABLE - control concurrency
DCL is Data Control Language statements. Some examples:
•    COMMIT - save work done
•    SAVEPOINT - identify a point in a transaction to which you can later roll back
•    ROLLBACK - restore database to original since the last COMMIT
•    SET TRANSACTION - Change transaction options like what rollback segment to use
How can I eliminate duplicates values in a table?
Choose one of the following queries to identify or remove duplicate rows from a table leaving one record:
Method 1:
   SQL> DELETE FROM table_name A WHERE ROWID > (
     2    SELECT min(rowid) FROM table_name B
     3    WHERE A.key_values = B.key_values);
Method 2:
   SQL> create table table_name2 as select distinct * from table_name1;
   SQL> drop table_name1;
   SQL> rename table_name2 to table_name1;
Method 3:
   SQL> Delete from my_table where rowid not in(
   SQL>     select max(rowid) from my_table
   SQL>     group by my_column_name );
Method 4:
   SQL> delete from my_table t1
   SQL> where  exists (select 'x' from my_table t2
   SQL>                 where t2.key_value1 = t1.key_value1
   SQL>                   and t2.key_value2 = t1.key_value2
   SQL>                   and t2.rowid      > t1.rowid);
Note: If you create an index on the joined fields in the inner loop, you, for all intents purposes, eliminate N^2

operations (no need to loop through the entire table on each pass by a record).
How can I generate primary key values for my table?
Create your table with a NOT NULL column (say SEQNO). This column can now be populated with unique values:
SQL> UPDATE table_name SET seqno = ROWNUM;
or use a sequences generator:
SQL> CREATE SEQUENCE sequence_name START WITH 1 INCREMENT BY 1;
SQL> UPDATE table_name SET seqno = sequence_name.NEXTVAL;

Finally, create a unique index on this column.
How can I get the time difference between two date columns
Look at this example query:
select floor(((date1-date2)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600 -
       (floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
       || ' SECS ' time_difference
from   ...
How does one count different data values in a column?

        select dept, sum(  decode(sex,'M',1,0)) MALE,
                     sum(  decode(sex,'F',1,0)) FEMALE,
                     count(decode(sex,'M',1,'F',1)) TOTAL
        from   my_emp_table
        group  by dept;
How does one count/sum RANGES of data values in a column?
A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:
        select f2,
               sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
               sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
               sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
        from   my_table
        group  by f2;
For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...).

Eg.
        select ename "Name", sal "Salary",
               decode( trunc(f2/1000, 0), 0, 0.0,
                                          1, 0.1,
                                          2, 0.2,
                                          3, 0.31) "Tax rate"
        from   my_table;
Can one retrieve only the Nth row from a table?
provided this solution to select the Nth row form a table:
        SELECT f1 FROM t1
        WHERE  rowid = (
           SELECT rowid FROM t1
           WHERE  rownum <= 10
          MINUS
           SELECT rowid FROM t1
           WHERE  rownum < 10);
Alternatively...
        SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
           (SELECT rowid FROM emp WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even

help in the odd situation.
Can one retrieve only rows X to Y from a table?
To display rows 5 to 7, construct a query like this:
        SELECT *
        FROM   tableX
        WHERE  rowid in (
           SELECT rowid FROM tableX
           WHERE rownum <= 7
          MINUS
           SELECT rowid FROM tableX
           WHERE rownum < 5);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even

help in the odd situation.
How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
        SELECT *
        FROM   emp
        WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)
                             FROM   emp);
Method 2: Use dynamic views (available from Oracle7.2):
        SELECT *
        FROM   ( SELECT rownum rn, empno, ename
                 FROM emp
               ) temp
        WHERE  MOD(temp.ROWNUM,4) = 0;
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may

even help in the odd situation.
How does one select the TOP N rows from a table?
Form Oracle8i one can have an inner-query with an ORDER BY clause. Look at this example:
        SELECT *
        FROM   (SELECT * FROM my_table ORDER BY col_name_1 DESC)
        WHERE  ROWNUM < 10;
Use this workaround with prior releases:
        SELECT *
          FROM my_table a
         WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
                        FROM my_table b
                       WHERE b.maxcol >= a.maxcol)
         ORDER BY maxcol DESC;
How does one code a tree-structured query?
Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also,

this feature is not often found in other database offerings.
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This

table is perfect for tesing and demonstrating tree-structured queries as the MGR column contains the employee number

of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of

up to 255 levels. Look at this example:
        select  LEVEL, EMPNO, ENAME, MGR
          from  EMP
        connect by prior EMPNO = MGR
          start with MGR is NULL;
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and

concatenate that to the string. Look at this example:
        select lpad(' ', LEVEL * 2) || ENAME ........
One uses the "start with" clause to specify the start of the tree. More than one record can match the starting

condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables.

The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this

programmatically is difficult as one has to do the top level query first, then, for each of the records open a

cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and

the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table

for later retrieval.
How does one code a matrix report in SQL?
Look at this example query with sample output:
        SELECT  *
        FROM  (SELECT job,
                      sum(decode(deptno,10,sal)) DEPT10,
                      sum(decode(deptno,20,sal)) DEPT20,
                      sum(decode(deptno,30,sal)) DEPT30,
                      sum(decode(deptno,40,sal)) DEPT40
                 FROM scott.emp
                GROUP BY job)
        ORDER BY 1;

        JOB           DEPT10     DEPT20     DEPT30     DEPT40
        --------- ---------- ---------- ---------- ----------
        ANALYST                    6000
        CLERK           1300       1900        950
        MANAGER         2450       2975       2850
        PRESIDENT       5000
        SALESMAN                              5600
How does one implement IF-THEN-ELSE in a select statement?
The Oracle decode function acts like a procedural statement inside an SQL statement to return different values or

columns based on the values of other columns in the select statement.
Some examples:
        select decode(sex, 'M', 'Male',
                           'F', 'Female',
                           'Unknown')
        from   employees;

        select a, b, decode( abs(a-b), a-b, 'a > b',
                                       0,   'a = b',
                                            'a < b')
        from  tableX;

        select decode( GREATEST(A,B), A, 'A is greater than B', 'B is greater than A')...
Note: The decode function is not ANSI SQL and is rarely implemented in other RDBMS offerings. It is one of the good

things about Oracle, but use it sparingly if portability is required.
From Oracle 8i one can also use CASE statements in SQL. Look at this example:
        SELECT ename, CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END
        FROM   emp;
How can one dump/ examine the exact content of a database column?

        SELECT DUMP(col1)
        FROM tab1
        WHERE cond1 = val1;

        DUMP(COL1)
        -------------------------------------
        Typ=96 Len=4: 65,66,67,32
For this example the type is 96, indicating CHAR, and the last byte in the column is 32, which is the ASCII code for

a space. This tells us that this column is blank-padded.



Can one drop a column from a table?
From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE

table_name DROP COLUMN column_name; command.
With previous releases one can use Joseph S. Testa's DROP COLUMN package that can be downloaded from

http://www.oracle-dba.com/ora_scr.htm.
Other workarounds:
1. SQL> update t1 set column_to_drop = NULL;
   SQL> rename t1 to t1_base;
   SQL> create view t1 as select <specific columns> from t1_base;

2. SQL> create table t2 as select <specific columns> from t1;
   SQL> drop table t1;
   SQL> rename t2 to t1;
Can one rename a column in a table?
No, this is listed as Enhancement Request 163519. Some workarounds:
1. -- Use a view with correct column names...
   rename t1 to t1_base;
   create view t1 <column list with new name> as select * from t1_base;

2. -- Recreate the table with correct column names...
   create table t2 <column list with new name> as select * from t1;
   drop table t1;
   rename t2 to t1;

3. -- Add a column with a new name and drop an old column...
   alter table t1 add ( newcolame datatype ); 
   update t1 set newcolname=oldcolname;
   alter table t1 drop column oldcolname;
How can I change my Oracle password?
Issue the following SQL command: ALTER USER <username> IDENTIFIED BY <new_password>
/
From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type

"password user_name".
How does one find the next value of a sequence?
Perform an "ALTER SEQUENCE ... NOCACHE" to unload the unused cached sequence numbers from the Oracle library cache.

This way, no cached numbers will be lost. If you then select from the USER_SEQUENCES dictionary view, you will see

the correct high water mark value that would be returned for the next NEXTVALL call. Afterwards, perform an "ALTER

SEQUENCE ... CACHE" to restore caching.
You can use the above technique to prevent sequence number loss before a SHUTDOWN ABORT, or any other operation that

would cause gaps in sequence values.
Workaround for snapshots on tables with LONG columns
You can use the SQL*Plus COPY command instead of snapshots if you need to copy LONG and LONG RAW variables from one

location to another. Eg:
COPY TO SCOTT/TIGER@REMOTE     -
CREATE IMAGE_TABLE USING       -
       SELECT IMAGE_NO, IMAGE  -
       FROM   IMAGES;
Note: If you run Oracle8, convert your LONGs to LOBs, as it can be replicated.

Interesting DataBase Questions and Answers

TABLE SALESPEOPLE

SNUM         SNAME                  CITY                      COMM
1001         Peel                     London                .12
1002         Serres                   San Jose                  .13
        1004         Motika                   London                 .11
        1007         Rafkin                   Barcelona               .15
1003         Axelrod                  New york               .1


TABLE CUST

     CNUM         CNAME          CITY              RATING          SNUM
 2001         Hoffman        London               100              1001
 2002         Giovanne       Rome                 200              1003
 2003         Liu                San Jose             300              1002
 2004         Grass          Brelin               100              1002
 2006         Clemens        London               300              1007
 2007         Pereira        Rome                 100              1004


ORDERS

ONUM        AMT        ODATE              CNUM          SNUM
3001             18.69         03-OCT-94          2008              1007
3003          767.19         03-OCT-94          2001              1001
3002        1900.10         03-OCT-94          2007              1004
3005       5160.45         03-OCT-94          2003              1002
3006       1098.16         04-OCT-94          2008              1007
3009       1713.23         04-OCT-94          2002              1003
3007             75.75         05-OCT-94          2004              1002
3008       4723.00        05-OCT-94          2006              1001
3010       1309.95         06-OCT-94          2004              1002
3011       9891.88         06-OCT-94          2006              1001


Problems :

1.     Display snum,sname,city and comm of all salespeople.
    Select snum, sname, city, comm
    from salespeople;
2.     Display all snum without duplicates from all orders.
    Select distinct snum
    from orders;
3.     Display names and commissions of all salespeople in london.
    Select sname,comm
    from salespeople
    where city = ‘London’;
4.     All customers with rating of 100.
    Select cname
    from cust
    where rating = 100;
   
   
   
5.     Produce orderno, amount and date form all rows in the order table.
    Select ordno, amt, odate
    from orders;
6.     All customers in San Jose, who have rating more than 200.
    Select cname
    from cust
    where rating > 200;
7.     All customers who were either located in San Jose or had a rating above 200.
    Select cname
    from cust
    where city = ‘San Jose’ or
               rating > 200;
8.     All orders for more than $1000.
    Select *
    from orders
    where amt > 1000;
9.     Names and citires of all salespeople in london with commission above 0.10.
    Select sname, city
    from salepeople
    where comm > 0.10 and
               city = ‘London’;
10.     All customers excluding those with rating <= 100 unless they are located in Rome.
    Select cname
    from cust
    where rating <= 100 or
               city = ‘Rome’;
11.     All salespeople either in Barcelona or in london.
    Select sname, city
    from salespeople
    where city in (‘Barcelona’,’London’);
12.     All salespeople with commission between 0.10 and 0.12. (Boundary values should be excluded)
    Select sname, comm
    from salespeople
    where comm > 0.10 and comm < 0.12;
13.     All customers with NULL values in city column.
    Select cname
    from cust
    where city is null;
14.     All orders taken on Oct 3Rd   and Oct 4th  1994.
    Select *
    from orders
    where odate in (‘03-OCT-94’,’04-OCT-94’);
   
   
   
15.     All customers serviced by peel or Motika.
    Select cname
    from cust, orders
    where orders.cnum = cust.cnum and
                orders.snum in ( select snum
         from salespeople
                                   where sname in 'Peel','Motika'));
16.     All customers whose names begin with a letter from A to B.
    Select cname
    from cust
    where cname like ‘A%’ or
                cname like ‘B%’;
17.     All orders except those with 0 or NULL value in amt field.
    Select onum
    from orders
    where amt != 0 or
    amt is not null;
18.     Count the number of salespeople currently listing orders in the order table.
    Select count(distinct snum)
    from orders;
19.     Largest order taken by each salesperson, datewise.
    Select odate, snum, max(amt)
    from orders
    group by odate, snum
    order by odate,snum;
20.     Largest order taken by each salesperson with order value more than $3000.
    Select odate, snum, max(amt)
    from orders
    where amt > 3000
    group by odate, snum
    order by odate,snum;
21.     Which day had the hightest total amount ordered.
    Select odate, amt, snum, cnum
    from orders
    where amt = (select max(amt)
    from orders)
22.     Count all orders for Oct 3rd.
    Select count(*)
    from orders
    where odate = ‘03-OCT-94’;
23.     Count the number of different non NULL city values in customers table.
    Select count(distinct city)
    from cust;
   
   
   
   
24.     Select each customer’s smallest order.
    Select cnum, min(amt)
    from orders
    group by cnum;
25.     First customer in alphabetical order whose name begins with G.
    Select min(cname)
    from cust
    where cname like ‘G%’;
26.     Get the output like “ For dd/mm/yy there are ___ orders.
    Select 'For ' || to_char(odate,'dd/mm/yy') || ' there are '||
    count(*) || ' Orders'
    from orders
    group by odate;
27.     Assume that each salesperson has a 12% commission. Produce order no., salesperson no., and amount of salesperson’s commission for that order.
    Select onum, snum, amt, amt * 0.12
    from orders
    order by snum;
28.     Find highest rating in each city. Put the output in this form. For the city (city), the highest rating is : (rating).
    Select 'For the city (' || city || '), the highest rating is : (' ||
    max(rating) || ')'
    from cust
    group by city;
29.     Display the totals of orders for each day and place the results in descending order.
    Select odate, count(onum)
    from orders
    group by odate
    order by count(onum);
30.     All combinations of salespeople and customers who shared a city. (ie same city).
    Select sname, cname
    from salespeople, cust
    where salespeople.city = cust.city;
31.     Name of all customers matched with the salespeople serving them.
    Select cname, sname
    from cust, salespeople
    where cust.snum = salespeople.snum;
32.     List each order number followed by the name of the customer who made the order.
    Select onum, cname
    from orders, cust
    where orders.cnum = cust.cnum;
   
   
   
   
33.     Names of salesperson and customer for each order after the order number.
    Select onum, sname, cname
    from orders, cust, salespeople
    where orders.cnum = cust.cnum and
               orders.snum = salespeople.snum;
34.     Produce all customer serviced by salespeople with a commission above 12%.
    Select cname, sname, comm
    from cust, salespeople
    where comm > 0.12 and
               cust.snum = salespeople.snum;
35.     Calculate the amount of the salesperson’s commission on each order with a rating above 100.
    Select sname, amt * comm
    from orders, cust, salespeople
    where rating > 100 and
              salespeople.snum = cust.snum and
              salespeople.snum = orders.snum and
              cust.cnum = orders.cnum
36.     Find all pairs of customers having the same rating.
    Select a.cname, b.cname,a.rating
    from cust a, cust b
    where a.rating = b.rating and
              a.cnum != b.cnum
37.     Find all pairs of customers having the same rating, each pair coming once only.
    Select a.cname, b.cname,a.rating
    from cust a, cust b
    where a.rating = b.rating and
              a.cnum != b.cnum and
                          a.cnum < b.cnum;
38.     Policy is to assign three salesperson to each customers. Display all such combinations.
    Select cname, sname
    from salespeople, cust
    where sname in  ( select sname
         from salespeople
                                             where rownum <= 3)
    order by cname;
39.     Display all customers located in cities where salesman serres has customer.
    Select cname
    from cust
    where city = ( select city
                           from cust, salespeople
               where cust.snum = salespeople.snum and                  sname = 'Serres');
   
   
   
    Select cname
    from cust
    where city in ( select city
                              from cust, orders
                                        where cust.cnum = orders.cnum and
                                        orders.snum in ( select snum
       from salespeople
                                                                  where sname = 'Serres'));
40.     Find all pairs of customers served by single salesperson.
    Select cname from cust
     where snum in (select snum from cust
                    group by snum
                    having count(snum) > 1);
   
    Select distinct a.cname
    from cust a ,cust b
    where a.snum = b.snum and a.rowid != b.rowid;
41.     Produce all pairs of salespeople which are living in the same city. Exclude combinations of salespeople with themselves as well as duplicates with the order reversed.
    Select a.sname, b.sname
    from salespeople a, salespeople b
    where a.snum > b.snum and
          a.city = b.city;
42.     Produce all pairs of orders by given customer, names that customers and eliminates duplicates.
    Select c.cname, a.onum, b.onum
    from orders a, orders b, cust c
    where a.cnum = b.cnum and
              a.onum > b.onum and
                          c.cnum = a.cnum;
43.     Produce names and cities of all customers with the same rating as Hoffman.
    Select cname, city
    from cust
    where rating = (select rating
                                from cust
                  where cname = 'Hoffman')
    and cname != 'Hoffman';
44.     Extract all the orders of Motika.
    Select Onum
    from orders
    where snum = ( select snum
       from salespeople
       where sname = ‘Motika’);
   
   
   
   
45.     All orders credited to the same salesperson who services Hoffman.
    Select onum, sname, cname, amt
    from orders a, salespeople b, cust c
    where a.snum = b.snum and
              a.cnum = c.cnum and
              a.snum = ( select snum
                                from orders
                                            where cnum = ( select cnum
                                                                      from cust
                                                                      where cname = 'Hoffman'));
46.     All orders that are greater than the average for Oct 4.
    Select *
    from orders
    where amt > ( select avg(amt)
                            from orders
                                        where odate = '03-OCT-94');
47.     Find average commission of salespeople in london.
    Select avg(comm)
    from salespeople
    where city = ‘London’;
48.     Find all orders attributed to salespeople servicing customers in london.
    Select snum, cnum
    from orders
    where cnum in (select cnum
      from cust
                              where city = 'London');
49.     Extract commissions of all salespeople servicing customers in London.
    Select comm
    from salespeople
    where snum in (select snum
                              from cust
                              where city = ‘London’);
50.     Find all customers whose cnum is 1000 above the snum of serres.
    Select cnum, cname from cust
    where cnum > ( select snum+1000
                              from salespeople
                              where sname = 'Serres');
51.     Count the customers with rating  above San Jose’s average.
    Select cnum, rating
    from cust
    where rating > ( select avg(rating)
                               from cust
                               where city = 'San Jose');
52.     Obtain all orders for the customer named Cisnerous. (Assume you don’t know his customer no. (cnum)).
    Select onum, odate
    from orders
   
    where cnum = ( select cnum
                                from cust
                                where cname = ‘Cisnerous’);
53.     Produce the names and rating of all customers who have above average orders.
    Select max(b.cname), max(b.rating), a.cnum
    from orders a, cust b
    where a.cnum = b.cnum
    group by a.cnum
    having count(a.cnum) > ( select avg(count(cnum))
            from orders
                                               group by cnum);
54.     Find total amount in orders for each salesperson for whom this total is greater than the amount of the largest order in the table.
    Select snum,sum(amt)
    from orders
    group by snum
    having sum(amt) > ( select max(amt)
               from orders);
55.     Find all customers with order on 3rd Oct.
    Select cname
    from cust a, orders b
    where a.cnum = b.cnum and
                odate = ‘03-OCT-94’;
56.     Find names and numbers of all salesperson who have more than one customer.
    Select sname, snum
    from salespeople
    where snum in ( select snum
                                 from cust
                                 group by snum
                                 having count(snum) > 1 );
57.     Check if the correct salesperson was credited with each sale.
    Select onum, a.cnum, a.snum, b.snum
    from orders a, cust b
    where a.cnum = b.cnum and
                a.snum != b.snum;
58.     Find all orders with above average amounts for their customers.
    select onum, cnum, amt
    from orders a
    where amt > (  select avg(amt)
    from orders b
    where a.cnum = b.cnum
    group by cnum);
59.     Find the sums of the amounts from order table grouped by date, eliminating all those dates where the sum was not at least 2000 above the maximum amount.
   
   
    Select odate, sum(amt)
    from orders a
    group by odate
    having sum(amt) > ( select max(amt)
                                      from orders b
                                      where a.odate = b.odate
                                      group by odate);
60.     Find names and numbers of all customers with ratings equal to the maximum for their city.
    Select a.cnum, a.cname
    from cust a
    where a.rating = (  select max(rating)
             from cust b
                         where a.city = b.city);
61.     Find all salespeople who have customers in their cities who they don’t service. ( Both way using Join and Correlated subquery.)
    Select distinct cname
    from cust a, salespeople b
    where a.city = b.city and
               a.snum != b.snum;
   
    Select cname
    from cust
    where cname in ( select cname
         from cust a, salespeople b
         where a.city = b.city and
                                           a.snum != b.snum );
62.     Extract cnum,cname and city from customer table if and only if one or more of the customers in the table are located in San Jose.
    Select * from cust
    where 2 < (select count(*)
               from cust
               where city = 'San Jose');
63.     Find salespeople no. who have multiple customers.
    Select snum
    from cust
    group by snum
    having count(*) > 1;
   
64.     Find salespeople number, name and city who have multiple customers.
    Select snum, sname, city
    from salespeople
    where snum in ( select snum
                                 from cust
                                 group by snum
                                 having count(*) > 1);
   
   
   
65.     Find salespeople who serve only one customer.
    Select snum
    from cust
    group by snum
    having count(*) = 1;
66.     Extract rows of all salespeople with more than one current order.
    Select snum, count(snum)
    from orders
    group by snum
    having count(snum) > 1;
67.     Find all salespeople who have customers with a rating of 300. (use EXISTS)
    Select a.snum
    from salespeople a
    where exists ( select b.snum
                             from cust b
                             where b.rating = 300 and
                                        a.snum = b.snum)
68.     Find all salespeople who have customers with a rating of 300. (use Join).
    Select a.snum
    from salespeople a, cust b
    where b.rating = 300 and
                a.snum = b.snum;
69.     Select all salespeople with customers located in their cities who are not assigned to them. (use EXISTS).
    Select snum, sname
    from salespeople
    where exists ( select cnum
                            from cust
                            where salespeople.city = cust.city and
                                       salespeople.snum != cust.snum);
70.     Extract from customers table every customer assigned the a salesperson who currently has at least one other customer ( besides the customer being selected) with orders in order table.
    Select a.cnum, max(c.cname)
    from orders a, cust c
    where a.cnum = c.cnum
    group by a.cnum,a.snum
    having count(*) < ( select count(*)
                                               from orders b
                                                where a.snum = b.snum)
    order by a.cnum;
71.     Find salespeople with customers located in their cities ( using both ANY and IN).
    Select sname
    from salespeople
    where snum in ( select snum from cust
   
                             where salespeople.city = cust.city and
                                        salespeople.snum = cust.snum);
   
    Select sname
    from salespeople
    where snum = any ( select snum
                                       from cust
                                         where salespeople.city = cust.city and
                                                               salespeople.snum = cust.snum);
72.     Find all salespeople for whom there are customers that follow them in alphabetical order. (Using ANY and EXISTS)
    Select sname
    from salespeople
    where sname < any ( select cname
               from cust
                                       where salespeople.snum = cust.snum);
   
    Select sname
    from salespeople
    where exists ( select cname
    from cust
                            where salespeople.snum = cust.snum and
                                       salespeople.sname < cust.cname);
73.     Select customers who have a greater rating than any customer in rome.
    Select a.cname
    from cust a
    where city = 'Rome' and
               rating > ( select max(rating)
                               from cust
                               where city != 'Rome');
74.     Select all orders that had amounts that were greater that atleast one of the orders from Oct 6th.
    Select onum, amt
    from orders
    where odate != '06-oct-94' and
                     amt > ( select min(amt)
                                 from orders
                                         where odate = '06-oct-94');
75.     Find all orders with amounts smaller than any amount for a customer in San Jose. (Both using ANY and without ANY)
    Select onum, amt
    from orders
    where amt < any ( select amt
                      from orders, cust
                      where city = 'San Jose' and
                            orders.cnum = cust.cnum);
   
   
   
   
    Select onum, amt
    from orders
    where amt < ( select max(amt)
                      from orders, cust
                      where city = 'San Jose' and
                            orders.cnum = cust.cnum);
76.     Select those customers whose ratings are higher than every customer in Paris. ( Using both ALL and NOT EXISTS).
    Select * from cust
     where rating > any (select rating from cust
                         where city = 'Paris');
   
    Select *
    from cust a
    where not exists ( select b.rating from cust b
                              where b.city != 'Paris' and
                                    b.rating > a.rating);
77.     Select all customers whose ratings are equal to or greater than ANY of the Seeres.
    Select cname, sname
    from cust, salespeople
    where rating >= any ( select rating
                                                      from cust
                                                      where snum = (select snum
                                                                                from salespeople
                                                                                where sname = 'Serres'))
                and sname != 'Serres'
    and salespeople.snum(+) = cust.snum;
78.     Find all salespeople who have no customers located in their city. ( Both using ANY and ALL)
    Select sname
    from salespeople
    where snum in ( select snum
                                from cust
                               where salespeople.city != cust.city and
                                          salespeople.snum = cust.snum);
   
    Select sname
    from salespeople
    where snum = any ( select snum
                                     from cust
                                  where salespeople.city != cust.city and
                                                               salespeople.snum = cust.snum);
79.     Find all orders for amounts greater than any for the customers in London.
    Select onum, amt
    from orders
   
   
    where amt > any ( select amt
                                    from orders, cust
                                    where city = ‘London’ and
                                                orders.cnum = cust.cnum);
80.     Find all salespeople and customers located in london.
    Select sname, cname
     from cust, salespeople
     where cust.city = 'London' and
           salespeople.city = 'London' and
           cust.snum = salespeople.snum;
81.     For every salesperson, dates on which highest and lowest orders were brought.
    Select a.amt, a.odate, b.amt, b.odate
    from orders a, orders b
    where (a.amt, b.amt) in (select max(amt), min(amt)
        from orders
                           group by snum);
82.     List all of the salespeople and indicate those who don’t have customers in their cities as well as those who do have.
    Select snum, city, 'Customer Present'
    from salespeople a
    where exists ( select snum from cust
                   where a.snum = cust.snum and
                         a.city = cust.city)
    UNION
    select snum, city, 'Customer Not Present'
    from salespeople a
    where exists ( select snum from cust c
                   where a.snum = c.snum and
                         a.city != c.city and
                         c.snum not in ( select snum
                              from cust
                              where a.snum = cust.snum and
                                 a.city = cust.city));
83.     Append strings to the selected fields, indicating weather or not a given salesperson was matched to a customer in his city.
    Select a.cname, decode(a.city,b.city,'Matched','Not Matched')
    from cust a, salespeople b
    where a.snum = b.snum;
84.     Create a union of two queries that shows the names, cities and ratings of all customers. Those with a rating of 200 or greater will also have the words ‘High Rating’, while the others will have the words ‘Low Rating’.
   
    Select cname, cities, rating, ‘Higher Rating’
    from cust
    where rating >= 200
    UNION
   
   
    Select cname, cities, rating, ‘Lower Rating’
    from cust
    where rating < 200;
85.     Write command that produces the name and number of each salesperson and each customer with more than one current order. Put the result in alphabetical order.
    Select 'Customer Number ' || cnum "Code ",count(*)
    from orders
    group by cnum
    having count(*) > 1
    UNION
    select 'Salesperson Number '||snum,count(*)
    from orders
    group by snum
    having count(*) > 1;
86.     Form a union of three queries. Have the first select the snums of all salespeople in San Jose, then second the cnums of all customers in San Jose and the third the onums of all orders on Oct. 3. Retain duplicates between the last two queries, but eliminates and redundancies between either of them and the first.
    Select 'Customer Number ' || cnum "Code "
    from cust
    where city = 'San Jose'
    UNION
    select 'Salesperson Number '||snum
    from salespeople
    where city = 'San Jose'
    UNION ALL
    select 'Order Number '|| onum
    from Orders
    where odate = '03-OCT-94';
87.     Produce all the salesperson in London who had at least one customer there.
    Select snum, sname
    from salespeople
    where snum in ( select snum
       from cust
                               where cust.snum = salespeople.snum and
                                          cust.city = 'London')
                and city = ‘London’;
88.     Produce all the salesperson in London who did not have customers there.
    Select snum, sname
    from salespeople
    where snum in ( select snum
       from cust
                                      where cust.snum = salespeople.snum and
                                                      cust.city = 'London')
                          and city = 'London';
   
89.     We want to see salespeople matched to their customers without excluding those salesperson who were not currently assigned to any customers. (User OUTER join and UNION)
Select sname, cname
from cust, salespeople
where cust.snum(+) = salespeople.snum;

Select sname, cname
from cust, salespeople
where cust.snum = salespeople.snum
UNION
select distinct sname, 'No Customer'
from cust, salespeople
where 0 = (select count(*) from cust
     where cust.snum = salespeople.snum);

Guess the Answers - JAVA

1.    COM & CORBA. DCOM & CORBA?
2.    Differences between VB4 and VB5 ?
3.    Features of VB5?
4.    Explain Java /CORBA advantages ?
5.    Will Java overcome all the languages ?
6.    Will Java compete with C++/C in C/s applications ?
7.    Design of indexes ?

JAVA FAQs

1.    What do you understand by client-server concept?
A.    A software component that services requests from another software component is called a server.  The component making the requests is the client.
The idea of client/server programming is to maximize the benefits of the desktop. The client and server can be present on the same computer or on geographically distant computer connected over a network . Some of the benefits of C/s computing are :
Flexibility, scalability and cost savings in terms of resource utilization and centralized control.

2.    What is meant by 3-Tier architecture ?
A.    3 Tier architecture is a method of deploying the client/server technology in real –world situations. The 3 tiers are :

•    GUI front-end        - Takes care of the end-user interaction and validation of input
  data
•    Business logic layer    - It encapsulates the business processing logic for the data
entered by the user or the data to be processed and given as output to the user. It can be implemented as a collection of reusable and upgradable software components .
•    Data-processing layer    - It takes care of the data processing , storage and
retrieval of the stored data.

3.    What are the advantages of 3-Tier architecture over 2-Tier architecture?
A.    In 2-tier architecture we only have the application layer and the data-processing layer.
The application layer is a combination of the GUI front-end and the business logic.
The following are the disadvantages of the 2-tier architecture (which in turn are the advantages of 3-tier over 2-tier ) :
•    The application is not easily upgradable.
•    The size of the application is large.

3-tier arch. Provides :
•    Maximum performance
•    Security
•    Ease of maintenance.
   
4.    What do normalization and Denormalization mean?
Normalization is the process of simplifying the relationship between data elements in a data structure .

                OR
The task of the database designer is to structure the data in a way that eliminates unnecessary duplication and provides a rapid search path to all necessary information. The process of dividing the information into separate tables that meet these goals is called Normalization.

Denormalization is the reverse process of normalization wherein the normalized data is merged to form a larger data structure that might have redundant data.
 

5.          What are the different degrees of normalization?
A.    A relation is in 1st Normal form if and only if all underlying domains contain atomic values only.

A relation is in 2nd normal form if and only if it is in 1NF and every nonkey attribute is fully dependent on the primary key.

A relation is in 3rd Normal form if and only if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key.

A relation R in 4th normal form if and only if , whenever there exists a multi-valued dependency in R, say A ->-> B (attribute B is multi-dependant on attribute A), then all attributes of R are also functionally dependent on A .

A relation R is said to be in fifth normal form also called Projection Join Normal form (PJNF) if and only if every join dependency in R is implied by the candidate keys of R.

6.    Do you know ODBC?
A.    ODBC is Open-database Connectivity. It provides access to any database which has a
ODBC database driver .
The database can be a client/server database that conform to ODBC standards
eg. SQL Server , Oracle etc.

An ODBC driver-manager provides an interface from the host language to the specific
Back-end data source driver.  The driver manager is responsible for :
•    Loading the remote database drivers specified in the DSN entry
•    Initializing the interface
•    Providing entry points to driver entry points
•    Validating parameters and managing serialization of ODBC functions

ODBC driver is a DLL that can interface with a specific back-end database engine
Eg. MS-SQL server , Oracle , sybase etc.
An ODBC driver takes advantage of specific back-end features such as cursors or parameterized queries . It can also implement some other features not supported by the back-end. If the driver doesn’t support a specific feature it returns an error stating that it doesn’t support the feature .

7.    What are the factors that influence ODBC response time ?
A.    The main factors that influence the ODBC response time are :
•    Network load
•    The complexity of the SQL queries
•    The speed of the machine on which the server executes
•    The RAM of the machine on which the server executes
•    The number of records that are likely to be returned as result of the query.
•    The physical location of the database and number of connections to database

8.    In how many ways can VB implement ODBC ?
A.    VB can implement ODBC in the following ways :
•    Using Data control and connecting to a database like SQL Server , Oracle
•    Using DAO objects and using the dbUseODBC parameter in the CreateWorkspace methods ie. Using ODBC Direct
•    Using Remote Data Control
•    Using Remote Data Objects
•    Using ODBC API calls

9.    What is OOPS ?
A.    OOPS is Object Oriented Programming.  The main features of OOPS are :
Data hiding & encapsulation
Polymorphism
Inheritance
Data Abstraction

    The advantages of OOPS over structured programming are :
•    Data hiding
•    Code reusability
•    Lower costs in building systems
•    Flexibility

10.    How VB 5.0 implements OOPS ?
A.    VB implements OOPS concepts in the following ways :\
•    Data hiding is implemented using the “Public , Private  scoping keywords “
•    Polymorphism is implemented using the “Implements “ , ParamArray , Optional keywords
•    Inheritance is implemented in the sense of interface inheritance and not implementation inheritance (ie. Not like C++ inheritance)

11.    Explain code-reusability in OOPS programming / VB ?
A.    In VB we have the concept of ActiveX code components and ActiveX controls. The code for a
certain functionality need not be re-written if the code components /controls are available to the developer. The references to the code components and controls can be set in VB and the functionality exposed by them can be used in one’s projects.  This decreases development time .

12.    What is the difference between the following lines of code ?
Dim X as Object
Dim X as class_name

In the first statement the compiler uses Late Binding of the object. The object to be used is not known until run-time. Because the late binding does not use the type library during the design time, a reference is not set to the component.

In the second statement the compiler uses Early Binding. The object variable refers to a discreet object type and hold pointers only to that object type. VB will have explicit knowledge of the object through its type library by setting a reference to the component. With this VB will be able to display data about the available methods and properties, as well as the syntax of the each method or property called.   

13.    Explain something about Active X and how do you us it?
A.    An ActiveX component is a unit of executable such as an exe or dll or ocx file that follows ActiveX specification for providing objects . An ActiveX component exposes objects that can be used by other applications .
ActiveX components used are ActiveX code components, ActiveX documents , ActiveX  Controls , ActiveX clients.

14.    What is an OLE Server?
A.    An OLE server is a code component that exposes some of its methods and properties to
other applications. An OLE Server can be an In-Process server (implemented as an OLE DLL) or an out-of-process server (OLE EXE) .
 

15.    Which is more advantageous – using DLLs or objects ?
A.    See in Answer 14.

16.    Do you know about COM / DCOM  ?
A.    COM is Component Object Model. It is an object-oriented architecture for building applications and the foundation on which ActiveX components are built. ActiveX components allow programmers to combine re-usable pieces of code into applications and services .

It specifies the blue-print of how objects can be created from their component.
How a client accesses features of an object.
Object responsibility to destroy itself when not in use .

Advantages of COM :
•    Binary compatibility and cross-platform development
•    Code-resuability
•    Automatic Versioning (Version Control)

DCOM is Distributed Component Object Model. It is an extension of COM. It is a protocol that enables s/w components to directly communicate over a network in a reliable,secure and efficient manner. Some of the key features of DCOM are :

Location independence
Connection mgmt
Scalability
Performance
Security
Band-width and latency
Load balacing
Ease of deployment.
Protocol and Platform neutrality.

17.    What is the difference between ActiveX and Java Beans ?
A.    ActiveX and Java Beans are comparable the only difference which I perceive is that Java Beans are platform neutral and ActiveX controls are dependant on the Windows platforms.

18.    What is Java’s future ?
A.    Microsoft is working on Windows Foundation Classes which proposes to recode the Windows DLLs using Java . Microsoft proposes to enable Java applications to use the WFC and thus bind the Java language to the Windows platforms. This is being strongly contested by Sun .

PL/SQL - Interview Question Collections

What is PL/SQL and what is it used for?
PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax, structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance). PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.



Should one use PL/SQL or Java to code procedures and triggers?
Internally the Oracle database supports two procedural languages, namely PL/SQL and Java. This leads to questions like "Which of the two is the best?" and "Will Oracle ever desupport PL/SQL in favour of Java?".
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database Proprietary to Oracle and difficult to port to other database systems Data manipulation is slightly faster in PL/SQL than in Java Easier to use than Java (depending on your background)
Java:
Open standard, not proprietary to Oracle Incurs some data conversion overhead between the Database and Java type systems Java is more difficult to use (depending on your background)



How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary. One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
        SELECT OBJECT_NAME,
               TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATE_TIME,
               TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
               STATUS
        FROM   USER_OBJECTS
        WHERE  LAST_DDL_TIME > '&CHECK_FROM_DATE';



How can one search PL/SQL code for a string/ key value?
The following query is handy if you want to know where a certain table, field or expression is referenced in your PL/SQL source code.
        SELECT TYPE, NAME, LINE
        FROM   USER_SOURCE
        WHERE  UPPER(TEXT) LIKE '%&KEYWORD%';



How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This way one can easily revert to previous code should someone make any catastrophic changes. Look at this example:
        CREATE TABLE SOURCE_HIST                     -- Create history table
          AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
             FROM   USER_SOURCE WHERE 1=2;

        CREATE OR REPLACE TRIGGER change_hist        -- Store code in hist table
               AFTER CREATE ON SCOTT.SCHEMA          -- Change SCOTT to your schema name
        DECLARE
        BEGIN
          if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                          'PACKAGE', 'PACKAGE BODY', 'TYPE') then
             -- Store old code in SOURCE_HIST table
             INSERT INTO SOURCE_HIST
                SELECT sysdate, user_source.* FROM USER_SOURCE
                WHERE  TYPE = DICTIONARY_OBJ_TYPE
                  AND  NAME = DICTIONARY_OBJ_NAME;
          end if;
        EXCEPTION
          WHEN OTHERS THEN
               raise_application_error(-20000, SQLERRM);
        END;
        /
        show errors



How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code.
This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
    wrap iname=myscript.sql oname=xxxx.plb



Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:
    set serveroutput on
    begin
       dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
    end;
    /
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.



Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
    DECLARE
      fileHandler UTL_FILE.FILE_TYPE;
    BEGIN
      fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
      UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
      UTL_FILE.FCLOSE(fileHandler);
    EXCEPTION
      WHEN utl_file.invalid_path THEN
         raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
    END;
    /



Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the DBMS_SQL package (see FAQ about Dynamic SQL).
    begin
       EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
    end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.



Can one use dynamic SQL statements from PL/SQL?
Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that statements are NOT terminated by semicolons:
    EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';

    -- Using bind variables...
    sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
    EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

        -- Returning a cursor...
    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at these examples:
    CREATE OR REPLACE PROCEDURE DYNSQL AS
      cur integer;
      rc  integer;
    BEGIN
      cur := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
      rc := DBMS_SQL.EXECUTE(cur);
      DBMS_SQL.CLOSE_CURSOR(cur);
    END;
    /
More complex DBMS_SQL example using bind variables:
    CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
      v_cursor integer;
      v_dname  char(20);
      v_rows   integer;
    BEGIN
      v_cursor := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
      DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
      DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
      v_rows := DBMS_SQL.EXECUTE(v_cursor);
      loop
        if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
           exit;
        end if;
        DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
        DBMS_OUTPUT.PUT_LINE('Deptartment name: '||v_dname);
      end loop;
      DBMS_SQL.CLOSE_CURSOR(v_cursor);
    EXCEPTION
      when others then
           DBMS_SQL.CLOSE_CURSOR(v_cursor);
           raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
    END;
    /



What is the difference between %TYPE and %ROWTYPE?
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. Example:
    DECLARE
       v_EmpRecord  emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
    DECLARE
       v_EmpNo  emp.empno%TYPE;



What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
    declare
      a number := NULL;
      b number := NULL;
    begin
      if a=b then
         dbms_output.put_line('True, NULL = NULL');
      elsif a<>b then
         dbms_output.put_line('False, NULL <> NULL');
      else
         dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
      end if;
    end;



How does one get the value of a sequence into a PL/SQL variable?
As you might know, one cannot use sequences directly from PL/SQL. Oracle (for some silly reason) prohibits this:
    i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
    select sq_sequence.NEXTVAL into :i from dual;
Thanks to Ronald van Woensel



Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.



How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
    DECLARE
       CURSOR dept_cur IS
       SELECT deptno
         FROM dept
        ORDER BY deptno;
       -- Employee cursor all employees for a dept number
       CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
       SELECT ename
         FROM emp
        WHERE deptno = v_dept_no;
    BEGIN
       FOR dept_rec IN dept_cur LOOP
          dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));
          FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
             dbms_output.put_line('...Employee is '||emp_rec.ename);
          END LOOP;
      END LOOP;
    END;
    /



How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
    FOR records IN my_cursor LOOP
       ...do some stuff...
       COMMIT;
    END LOOP;
... to ...
    FOR records IN my_cursor LOOP
       ...do some stuff...
       i := i+1;
       IF mod(i, 10000) THEN    -- Commit every 10000 records
          COMMIT;
       END IF;
    END LOOP;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.



I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL. Choose one of the following solutions:
•    Grant direct access on the tables to your user. Do not use roles!
    GRANT select ON scott.emp TO my_user;
   
•    Define your procedures with invoker rights (Oracle 8i and higher);
•    Move all the tables to one user/schema.



What is a mutating and constraining table?
"Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
•    A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger) .
•    A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
•    Etc.



Can one pass an object/table as an argument to a remote procedure?
The only way the same object type can be referenced between two databases is via a database link. Note that it is not enough to just use the same type definitions. Look at this example:
    -- Database A: receives a PL/SQL table from database B
    CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS
    BEGIN
       -- do something with TabX from database B
       null;
    END;
    /

    -- Database B: sends a PL/SQL table to database A
    CREATE OR REPLACE PROCEDURE pcalling IS
       TabX DBMS_SQL.VARCHAR2S@DBLINK2;
    BEGIN
       pcalled@DBLINK2(TabX);
    END;
    /



Is it better to put code in triggers or procedures? What is the difference?
In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers.



Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.



Is there a limit on the size of a PL/SQL block?
Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. You can run the following select statement to query the size of an existing package or procedure:
    SQL> select * from dba_object_size where name = 'procedure_name';


PL

Telephonic Interview - for DBA - SQL

C: What is your experience on handling large databases ?
P:  I have handled a database of size 2GB.

C: Can you tell me the memory structure of your database ?
P : The SGA  size was of 20MB. I mentioned the size of database buffer, shared pool and redo log buffer caches.

C: How do you arrive at the SGA size ?
P: It depends on various parameters such as number of concurrent transactions at the peak hours, transaction size, number of applications using the database, number of other processes using the system resources and system configuration (dedicated/multi-threaded). We have to also ensure that no paging and swapping occurs for SGA.

C: How do you find whether the allotted memory resources are sufficient ?
P: We have to look for possible contention for memeory structures. for example, if the hit ratio for database buffer cache is low, we can allot more database buffers. Likewise, we can monitor the contention for library cache, redolog buffers, redo log latches and rollback segments.

C: Can you tell me the procedure to monitor rollback contention ?
P: Well, Using V$waitstat and V$sysstat we can see the how many times a server process waited for a free extent in rollback segment. If the percentage of waiting is more than one, we can conclude that the number of rollback segments are insufficient.

C: Why is ‘OPTIMAL’ parameter used in rollback segment ?
P: OPTIMAL parameter is used to bring back the rollback segment size if it were to grow due to long transactions. To arrive at the OPTIMAL size, We can monitor using ‘MONITOR ROLLBACK’ from SQL*DBA. Using this, we can see how much space ever allocated for each rollback segment in bytes, Cumulative number of times a transaction writing from one extent in a rollback segment to another existing extent and average number of bytes in active extents in the rollback segment, measured over a period of time.   

C: What was your backup strategy ?
P: Everyday I used to take hot backup which includes taking of relevent datafiles and then control file. Maintenance of archived redologs,  taking of cold backup and maintaining ASCII version of a few tables data were the other backup strategies formulated.

C: Have you done recovery ?
P: Yes, I have done recovery a lot many times.

C: How do you recover if a datafile is lost/damaged ?
P: First step is to see whether the database is in archivemode. Secondly, Whether the disk containing the datafile is accessible. If not accessible, rename the datafile  to another usable disk. If  archived, restore the affected datafile and archived redologs from backups; if the damaged file was part of the system tablespace, keep the database mounted and closed; use alter database option to do recovery, if it belongs to a non-system tablespace, use alter tablespace command to recover it after taking the tablespace offline.

C: How to recover if the database was in noarchivemode ?
P: Only way to recover the database is to restore the most recent complete backup.
 
C: Have you done tuning ?
P: Yes, I have tuned both applications and database.

C: How to tune an application ?
P: First, we have to ensure that the application uses well tuned SQL statements. We can use SQL trace facility and TKPROF to see the execution plan for SQL statement. If we are not satisfied with the current execution plan, use EXPLAIN PLAN command to see the execution plan for different versions of the SQL  statement. We can choose the SQL statement which gives faster response time as well as consumes less system resources.     

C: OK. All right. You will get a call tomorrow.  Do have any question to ask ?
P: Yes, I want to know what will be my role in your project.

M: The system is under development. You will be involved at this stage itself. It is expected to go to production in Jan’97.

Sample Personal Interview Questions

1)     Are you going to work here on a full time basis?
A)  (Thinking that she meant full time or part time) Yes, but what is meant by full time basis.

2)     I mean to say that are you coming here for a permanent position?
A)   No, I came here as a contractor through my company ODSi.

3)     Actually, this requirement is for a permanent  position, but if we cannot find anybody for permanent position then I will have to convince my boss that we may have to go for contractor as we could not get anybody for permanent position and since we are in crises. Do you know about our company?
A)   A very little.

4)     Ok let me explain about our company in brief.
(Talked for about half an hour about the Rational Software. While concluding…)

5)     We have already implemented Accounts Payable, General Ledger, Purchasing. Currently we are working on Order Entry and Accounts Receivable. Also, we will be implementing MRP and BOM in near future. However, currently the requirement is for Order Entry  and Accounts Receivable. Have you worked on in any of these two?
A)     No. But I have worked on Accounts Payable and General Ledger and Purchasing.. So it should not be a problem for me as earlier I was not worked on these modules either.
   
6)     How long you are in Oracle Financials?
A)     I am in Oracle Financials for about last one and half years. First I was in Honeywell working on custom purchasing reports which were developed in reports 2.0 then for Starkey labs on forms 4.0 and reports 2.0 and again for Honeywell on year end reports on report 2.0. All of which were on Oracle Financials.

7)     Tell me something about your last project, about year end reports you worked at Honeywell.
A)     The reports which I developed for Honeywell were all year end reports and developed in reports 2.0 like Invoices on Hold. I was the part of the IS team and we used to discus with the user about the reports. The user used to give us the layout and should they require the report urgently they used to give me the specifications also as to which table or column to pick. Otherwise, I used to find it out for myself . Then I used to design the report and implement it in the Oracle Financials with the help of AOL.

8)     Here there is nobody to give you specifications. So you may have to use manuals or contact Oracle. Tell me about this report invoices on hold that you have developed.
A)     Invoices on the hold report they were using to find out the total invoices which they have on hold along with the invoice amount and vendor details. It uses the tables AP_INVOICES, AP_INVOICE_DISTRIBUTIONS, PO_VENDORS, PO_VENDOR_SITES. I used to discus with the users about the requirements about their reports and used to design and then implement in AOL.

9)     What all things  you did in AOL?
A)     Mainly I implemented the reports using AOL. I also created or disabled users and their  responsibilities using the AOL.
10)     Ok so mainly implementation of custom reports, menus and forms.
A)     Yes, though I could not get a chance to implement menus and forms it should not be a problem as the most difficult part is the implementation of  reports as it require the report parameters also.

11)     Have you defined any Flexfields?
A)     Yes. As one of the requirement of the last project was to print the check reconciliation report, which they used to do it on the weekly basis. One of the user used to run a SQL script by specifying the dates every week. Rather than doing this, we defined one of the attribute of AP_CHECKS as R for reconciled and V for Void. Then we run a SQL script for one  time just to change the existing attribute to R or V and after that now it is running every week marking R and V automatically  as it is running through automatic job schedular called Autosys.

12)     Have you implemented key code combinations? Have you been in before the implementation stage any time?
A)     No. I was mostly in the post implementation stage only. Though I have not implemented any  key code combinations but my project at Starkey was of similar type. They had the old Legacy system which they want to convert into Oracle Financials and eventually they wanted to discontinue with the Legacy system. Initially it was decided to convert the data with SQL*Loader, since there were some systems which were still running in the Legacy system because of which one person may be involved permanently for this job, rather than we  decided to develop a package with which they could change the data in the flat file format into temporary oracle tables and then used to updated in the appropriate tables after verifying the data and mapped with the key code combinations which they have already entered and validated at time of entering. However, this was not implemented in Oracle Financials because it is going to be used till they transfer all the data from Legacy system to Oracle tables.

13)     Here we are also in the post implementation stage. We require someone for doing reports and assisting  our users. He should have good knowledge of the Oracle Tools and oracle modules particularly in Order Entry and Accounts Receivable.
A)     Yes, I am quite good in Oracle Tools such as Forms and reports 2.0 and also in SQL*Plus and PL/SQL. I am also familiar with Forms 4.5 and Reports 2.5.

14)     That’s great! Ming is our technical person who is a senior DBA here. He will ask you some technical questions. You have any questions?
A)     You said you have lot of projects here to be done. In case if I joined what would be your highest priority?

15)     Well our priority is for Order Entry and Accounts Receivable. There are lots of reports to be finished first.
A)     Which versions of forms and reports are  you using right now?

16)     As of now we do not want to modify Oracle Forms or Tables. But we are using forms 2.3 and SQL*Plus as reports and for some complex reports we are using Reports 2.0. But we may go for Forms 4.5 and reports 2.5 if we migrate from Oracle financials 10.5 to 10.6 in near future.
A)     I am quite familiar with Forms 4.5 and Reports 2.0 though I haven’t worked on live projects.

17)     That’s great! Any other questions?
A)     No. Not at the moment.

18)     Great I will call Ming now, he may ask you some technical questions. Thanks!
A)   Thanks Sharie.


Then Sharie introduced me to Ming and she left.

1)     How long you are in Financials?
A)     About one and half years.

2)     Have you worked on Accounts Receivable or Order Entry?
A)     No. But I worked on Accounts Payable, General Ledger and Purchasing.

3)     Allright. But our main requirement is for Order Entry and Receivable. Tell me about the project you have done.
A)     (Explained about the Year end report  project for Honeywell and Interface mapping project for Starkey)

4)     Oh! We have Legacy system here and we have a similar requirement for transferring data from Legacy  to Oracle tables.
A)     What hardware platform you are working on?

5)     We have a Sun Sparc stations here.
A)     Oh! I worked for Sun Microsystems on the same platform.

6)     That’s great. Did you find any difference in HP and Sun sparc.
A)     I was using pentium PCs which were connected to the HP  unix boxes. But I liked Sun Sparcs better.

7)     Yeah! We also have some emulators which connects to the Unix system. I think I am done. Do you have any questions?
A)     What would be your highest priority?

8)     We have lot of things to be done in Order Entry and Accounts Receivables.
A)     When do you think you can get back to me about this project?

9)     Well, I have no authority. Sharie is my project manager and she will get back to you. Do you have any other questions?
A)     No.

10)     Ok! Thanks!
A)   Thanks  a lot