Using External table

EXTERNAL TABLE or STAGING TABLE (to load csv files into staging table)

You can user external table feature to access external files as if they are tables inside the database.

When you create an external table, you define its structure and location within oracle.

When you query the table, oracle reads the external table and returns the results just as if the data had been stored within the database.

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, you must first use the create directory command to define a directory object pointing to the external file location

Users who will access the external files must have the read and write privilege on the directory.

Create user defined directory

Grant create any directory to scott;

GRANT READ, WRITE ON DIRECTORY REDDYS_DIR TO SCOTT;

SQL>Create directory REDDYS_dir as 'C:\ORA FILES';  /* directory on the Oracle database server */

Directory created.

CREATE EXTERNAL TABLE (whenever client put the data into directory external table takes the data automatically)

  SQL> Create table dept_ext

  2           (deptno number(2),

  3           Dname varchar(14),

  4           Loc varchar(13))

  5           Organization external  ( type oracle_loader

  6                                                   Default directory REDDYS_dir

  7                                                   Access parameters

  8                                                   ( records delimited by newline

  9                                                      Fields terminated by ","

 10                                                      ( deptno number(2),

 11                                                        Dname varchar(14),

 12                                                        Loc varchar(13)))

 13*          Location ('C:\ORA FILES\DEPT.LST'));

Table created.

We can create the procedure using UTL_FILES for Moving data from external table into oracle database.

SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from dept_ext;

This will read from dept.lst which is a operating system level file.

LIMITATIONS ON EXTERNAL TABLES

a)   You cannot perform insert, update, and delete operations

b)   Indexing not possible

c)   Constraints not possible

BENEFITS OF EXTERNAL TABLE

a)   Queries of external tables complete very quickly even though a full table scan id required with each access

b)   You can join external tables to each other or to standard tables

Imp queries

 How to display 1 to 10 numbers

select level from dual connect by level<=10;

How to delete duplicates records

Delete emp where rowid not in (select min(rowid) from emp group by sal);

How to find highest salary in a table

select * from emp where sal in (select  max(sal) from emp);

How to find second highest salary

1. Select * from emp where sal=(select max(sal) from emp where sal <(select max(sal) from emp));

2. Select * from (select sal,rank() over (order by sal desc) as rnk from (select  distinct sal from emp)) where rnk=2;

3. Select level,max(sal) from emp where level=&levelno connect by prior sal> sal group by level;

4. Select max(sal) from (select distinct sal from emp where sal not in (select max(sal) from emp));

5. select sal from(select sal from  (select distinct sal from emp order by sal desc) where rownum<=2 order by sal asc) where rownum=1;

To find highest and lowest values

select max(sal) from emp

union

select min(sal) from emp;

How to get fist and last record from a table in oracle?

select * from EMP where rownum=1

union

select * from EMP where rowid=(select max(rowid) from EMP);

To find the count of duplicate rows

Select ename, count (*) from emp group by ename having count(*) >= 1;

To see the duplicate records

SELECT * FROM  EMP WHERE (SAL IN (SELECT sal FROM emp GROUP BY sal HAVING COUNT(sal) > 1));

Query to find a_b format names

select * from emp where ename like '%A_B%';

Explain set operators

Create Materialized Views Privileges

Oracle10g Privileges to Create Materialized Views

grant query rewrite to scott;

grant create materialized view to scott;

alter session set query_rewrite_enabled = true;

I mentioned the privileges required to create materialized views in Oracle8i and Oracle9i Standard Edition. In Oracle10g, the requirement appears to be much more simplified.
Just needed to grant the CREATE MATERIALIZED VIEW system privilege.
REDDYS> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
________________________________________
REDDYS CREATE MATERIALIZED VIEW NO

Roles granted are listed below for completeness of the topic.
REDDYS> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
________________________________________
REDDYS CONNECT NO YES NO
REDDYS RESOURCE NO YES NO
We are running Oracle10g Release 2 Standard Edition in which QUERY REWRITE is not an enabled feature. Describing what I mean, I create a test table below upon which the materialized view will be based.
REDDYS> create table t1 as select owner,object_name,object_type from all_objects;
Table created.
REDDYS> select count(*) from t1;
COUNT(*)
———-
4196
Again when I attempt to create the materialized view with the “enable query rewrite” clause, I encounter the “feature not enabled” error.
REDDYS> create materialized view mv_test
2 refresh complete on commit
3 enable query rewrite
4 as
5 select object_type, count(*)
6 from t1
7 group by object_type;
from t1
*
ERROR at line 6:
ORA-00439: feature not enabled: Materialized view rewrite
I remove the “enable query rewrite” clause and the materialized view is created successfully.
REDDYS> create materialized view mv_test
2 refresh complete on commit
3 as
4 select object_type, count(*)
5 from t1
6 group by object_type;
Materialized view created.

pl/sql questions

Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.

1) Row level trigger - An event is triggered for each row updated, inserted or deleted. 
2) Statement level trigger - An event is triggered for each sql statement executed. 

Cursor: It is automatically created by oracle for all sql dml statements including the query that returns one row.
Explicit cursor: These are created and managed by the user. And used for multi row select statement.

What are the cursors attributes in plsql

%ISOPEN - to check whether cursor is open or not

% ROWCOUNT - number of rows fetched/updated/deleted.

% FOUND - to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND - to check whether cursor has fetched any row. True if no rows are fetched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

Stored Procedure/Function is a compiled database object, which is used for fast response from Oracle Engine. Difference is Stored Procedure must return multiple value and function must return single value.

How to debug the procedure
We can also do line by line execution using tools such as Pl/SQL Developer, Toad etc

If you want to tune a package then you can use dbms_profiler.

if you   want to debug use dbms_debug procedure.

 To see the user priviliges

Select *from user_SYS_PRIVS

What are difference between a cursor declared in a procedure and cursor declared in a package specification

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
One more difference is cursor declared in a package specification must have RETURN type
Or 

·        A cursor declares in the package specification that can be accessed in the other procedure or procedures of the package.

·        A cursor declares in the procedure
that can't be accessed by other procedure.

IN, OUT, IN-OUT parameters.

1) IN parameter is the default mode which acts as a constant inside calling environment. value passed in this parameter can not be changed.
OUT parameter mode is used to pass value from calling environment into main block, here we can change the value. It acts as a variable inside calling environment.

INOUT parameter mode which pass value into calling environment and will get the value back in main block.

IN parameter mode uses call by reference method to pass value from formal parameter to actual parameter.
OUT & INOUT parameter mode uses call by value method to pass values.
Or 
2) You can also give a compiler hint - 'NOCOPY' when defining function parameters.
e.g. : create or replace function add(var1 in number,var2 innumber, result inout nocopy number)

Main purpose of using NOCOPY:

Even if exception occured in function block your result state is maintained in defined variable (parameter: result).

If you haven't declared NOCOPY then the processed value for 'result'  parameter will gets copied only when function ends.
Or 
3) IN ,OUT,and INOUT

parameters are actual and formal

-- IN mode is default and is passed as constant
IN parameters cannot change their values in procedure
--OUT parameters can change their values in procedure and ispassed as a variable
--INOUT parameters can change their values in procedure and arepassed back to the calling environment as a new value. a nocopy hint can also be used 

What is overloading of procedure

Same procedure name with different parameters called procedure overloading, parameters may different by their data types, sequence and position.

What is sql*loader

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control filedescribes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename <options>.

What is the use of dynamic sql in oracle

Dynamic SQL enables you to write programs that reference SQL statement whose full text is not known until runtime.

Write a pl/sql block to update the sal of an emp whose empno is 7369 and print the msg as ? record updated

begin
update employee(tablename) set salary=(salary)*o.1 where empno=7839;
dbms_output.put_line('record updated');
end;

If the application is running very slow? At what points you need to go about the database in order to improve the performance

For improving performance, we need to check the sql statementblocks , because for every sql satement execution transfor to sql engine and come back to plsq engine that process takes more time to process the plsql block

What is a database trigger ?name some usage of db trigger

Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

Is it possible to use tcl statements in db trigger? Why?

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

yes WE can use COMMIT and  ROLLBACK triggers, but by using PRAGAMA AUTONAMOUS_TRANSATIONS. Now the transation treated as a autonomous tansation.

Advantages of package:

A. Modularity

  - Encapsulate related constructs.

B. Easier Application Design

  - Code and compile specification and body separately.

C. Hiding Information

  - Only the declarations in the pacakge specification

    are visible and accessible to application.

  - Private constructs in the package body are hidden

    and inaccesible.

  - All coding is hidden in the package body.

D. Added Functionality

  - Persistency of variables and cursors.

E. Better Performance

  - The entire package is loaded into memory when the

    package is first referenced.

  - There is only one copy in memory for all users.

  - The dependency hierarchy is simplified.

F. Overloading

  - Multiple subprograms of the same

ODBC Provider: ODBC Provides access to any data source that has an ODBC driver.

Unit Testing:

Unit testing focuses verification efforts on the smallest unit of software design module. The unit test is always white box oriented. The tests that occur as part of unit testing are testing the module interface, examining the local data structures, testing the boundary conditions, execution all the independent paths and testing error-handling paths.

      

 Integration Testing:

Integration testing is a systematic technique or construction the program structure while at the same time conducting tests to uncover errors associated with interfacing. Scope of testing summarizes the specific functional, performance, and internal design characteristics that are to be tested. It employs top-down testing and bottom-up testing methods for this case.

Performance Testing:

          Timing for both read and update transactions should be gathered to determine whether system functions are being performed in an acceptable timeframe