Dynamic Sql

DECLARE      
      vl_str_sql VARCHAR2(255);
       vl_cnt   VARCHAR2(20);
BEGIN
     vl_str_sql  := 'SELECT count(*) FROM emp';
     EXECUTE IMMEDIATE vl_str_sql  INTO vl_cnt;
     dbms_output.put_line(vl_cnt);
END;


DECLARE
  TYPE CUR_TYP IS REF CURSOR;
  c_cursor   CUR_TYPE;
  emp_rec   emp%ROWTYPE;
  v_query   VARCHAR2(255);
BEGIN
  v_query := 'SELECT * FROM emp';

  OPEN c_cursor FOR v_query;
  LOOP
    FETCH c_cursor INTO emp_rec;
    EXIT WHEN c_cursor%NOTFOUND;
    dbms_output.put_line(emp_rec.empno);
  END LOOP;
  CLOSE c_cursor;
END;

DECLARE 
TYPE cur_typ IS REF CURSOR;
  c_cursor     CUR_TYPE;
  emp_rec     emp%ROWTYPE;
  v_query      VARCHAR2(255);
  vl_manager VARCHAR2(3) := 'MANAGER';
BEGIN
  v_query := 'SELECT * FROM emp WHERE job = :job';
  OPEN c_cursor FOR v_query USING vl_manager;
  LOOP
    FETCH c_cursor INTO emp_rec;
    EXIT WHEN c_cursor%NOTFOUND;
    dbms_output.put_line(emp_rec.job);
  END LOOP;
  CLOSE c_cursor;
END;

INDEX

What is index?
An index  is a pointer for to a location of data.The purpose of index is to make sql queries to run faster
 syntax:-

CREATE [UNIQUE] INDEX index_name ON
table_name(column_name[, column_name...])
TABLESPACE table_space;

 

Normal Index?

 

view

View is virtual table or logical table based on one or more tables or views

Syntax :create or replace view as select from ;
To create a view in your own schema, we must have the create view system privileges
grant create view to scott;
Types of view

Simple view

A view create only one table is called simple view
For ex:-
Create or replace view v_emp as select empno,ename,job,sal,deptno from emp where deptno=20;
View is came from one base table we have possible to update, delete insert the rows in to the view With restrictions only.
Restrictions on DML operations for view:
1) If a view is define by a query that contains Setoperations(UNION UNION ALL,INTERSECT,MINUS) or Distinct operator,Group by or having clauses or function, then
Rows cannot be inserted into,updated in, or delete from the base tables using the view.
2) If a view is define with or connect by clauses a row cannot be inserted into,updated in, or delete from the base tables using the view
3) If the view contain Rownum pseudocolumn,not null and default column using an expression such as decode(deptno ,10,”sales”) then rows cannot be inserted into,updated in, or delete
4) from the base tables using the view

object modifiable
user_updatable_colums shows all columns in all tables and views in the uses schema that are modifiable
dba_updatable_columns
all_updatable_views

Forced view:
View Create as non-existent table but view cannot be executed we call such a view with errors
For ex:
create force view v_emp1 as select *from emp1;
output
SQL Command: create force
Failed: Warning: execution completed with warning
 
Complex view
Aview create based on multiple tables is called as complex view
Forex:
CREATE OR REPLACE VIEW Emp_detl AS
SELECT e.empno,e.ename,e.job,d.deptno,d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;


Materalized view
materialized view is the view that is physically present in the database since when  we are working with various databases running in different system

before create materalized view we need privileges form system user or sys


grant query rewrite to scott;
grant create materialized view to scott;
alter session set query_rewrite_enabled = true;

create  materialized view m_dept1  as select d.deptno,d.dname,d.loc from dept d;
insert into dept values(61,'SALES1','Delhi');
commit; 
 select *from m_dept1

DEPTNO   DNAME                  LOC 
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS       BOSTON 

exec  dbms_mview.refresh('m_dept1','C');


DEPTNO   DNAME                  LOC 
61               SALES1                  Delhi
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS       BOSTON

select *from m_dept1

update dept set dname='OPERATIONS3' where deptno=40

exec  dbms_mview.refresh('m_dept1','C');

DEPTNO   DNAME                  LOC 
61               SALES1                  Delhi
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS3       BOSTON

delete from dept  where deptno=61
exec  dbms_mview.refresh('m_dept1','C');

DEPTNO   DNAME                  LOC 

10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS3       BOSTON

or
 AUTO referesh when u'r commit

create materialized view mv_test  refresh complete on commit
 as  select deptno,dname,loc from dept;
 insert into dept values(71,'Accounts','Hyd');

DEPTNO   DNAME                  LOC 
71               Accounts                  Hyd
61               SALES1                  Delhi
10               ACCOUNTING     NEW YORK
20               RESEARCH           DALLAS
30               SALES                   CHICAGO
40               OPERATIONS3       BOSTON

Inline view 
 It is a subquery with an alias ,that can be used within sql statement ,it is not a schema object
 (A named subquery in the from clause of the main query is an example of an inline view)
 For ex:- select e.ename,e.sal,e.deptno,e1.max_sal from emp e,(select deptno,max(sal) max_sal from  emp   group by deptno)e1 where e.deptno=e1.deptno

UTL_FILE

Util_file package can be used to perform read and write operations on text file ,Util_file package which is owned by sys, we have to grant execute privileges to user connect To sys user or system
Steps:
Connect sys/password as sysdba whatever you given password at time of installation
Grant execute on utl_file to public;
Create a logical directory (directory alias) in oracle that points to physical directory in filesystem.
However, directory alias can be created only dba (sys or system).so after you connect in as sys,
Create directory alias for oracle folder in c:\ and grant read and write permission to public as follows
Create directory EXPDP_DIR as ‘C:\oracle’
Grant read on directory EXPDP_DIR to public; -- (public or scott)
Grant write on directory EXPDP_DIR to public; -- (public or scott)
Read/Write into the Util_file using subprograms
fopen function , fclose,put_line,get_line

FOR WRITE
create or replace PROCEDURE EMP_CSV AS
CURSOR c_data IS
SELECT empno,ename,job,mgr,TO_CHAR(hiredate,'DD-MON-YYYY') AS hiredate,sal,comm,deptno FROM emp ORDER BY ename;
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(location => 'EXPDP_DIR',
filename => 'emp_csv.txt',
open_mode => 'w',
max_linesize => 32767);
FOR cur_rec IN c_data LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.empno || ',' ||cur_rec.ename || ',' ||
cur_rec.job || ',' ||cur_rec.mgr || ',' ||
cur_rec.hiredate || ',' ||cur_rec.empno || ',' ||
cur_rec.sal || ',' ||cur_rec.comm || ',' ||
cur_rec.deptno);
END LOOP;
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
END;

READ THE FILE
create or replace PROCEDURE EMP_CSV_read AS
v_file UTL_FILE.FILE_TYPE;
v_input_buffer varchar2(4000);
BEGIN
DBMS_OUTPUT.PUT_LINE('start');
v_file := UTL_FILE.FOPEN(location => 'EXPDP_DIR',
filename => 'emp_csv.txt',
open_mode => 'R',
max_linesize => 32767);
LOOP
UTL_FILE.get_LINE(v_file,v_input_buffer);
DBMS_OUTPUT.PUT_LINE(v_input_buffer);
END LOOP;
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('END');
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
END;

http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/u_file.htm#996728

http://srikanthtechnologies.com/blog/utl_file.html

http://hany4u.blogspot.com/search/label/PL%2FSQL%20Codes

Difference Between Truncate And Delete

1)DELETE
Delete is Dml statement
we can use conditions on delete like where clause,
delete keeps record in buffers(temporary storage) till the first rollback or commit
a bit slower
triggers will fire in delete operation

2)TRUNCATE
Truncate is DDL statement
we can't using conditions in truncate
no rollback segments in truncate
much faster than delete
no triggers will fired in truncate

Fundamentals

Data Retrieval Language the commands used to select data from the database
SELECT
Data Definition Language(DDL)statements are used to define the database structure or schema
o Create -to create objects in database
o ALTER - alters the structure of the database
o Drop -drop objects from database
o Truncate -remove all record from table,including all spaces allocated for the records removed
o Comments -add comments to the data dictionary
o Rename -rename an object
o Describe -Meta data of the table

Data Manipulation Language(DML)statements are used for managing data within schema object
o Insert - insert data into the table
o Update -update existing data within a table
o Delete -delete all records from a table.the space for the records remain
o Merge -Upsert operation(insert or update)
o Call -call a pl/sql or java subprogram
o Explain plan -Explain access path to data
o Lock table -control concurrency
Data Control Language (DCL) statements. Some examples:
o GRANT - gives user's access privileges to database
o REVOKE - withdraw access privileges given with the GRANT command
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
o COMMIT - save work done
o SAVEPOINT - identify a point in a transaction to which you can later roll back
o ROLLBACK - restore database to original since the last COMMIT
o SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

How to find Server Log errors

CREATE TABLE LOG_ERRORS
( "ERR_NUM" NUMBER,
"ERR_MSG" VARCHAR2(3000 BYTE),
"ERR_DATETIME" TIMESTAMP (6),
"ERR_DEPTH" VARCHAR2(2000 BYTE)
);

Create trigger For If any query or server error happen in the database errnum,errmsg,querystatment insert into the bove table

create or replace trigger log_err after servererror
on schema
declare
v_num number;
sql_text ora_name_list_t;
v_msg varchar2(3000);
v_depth varchar2(2000);
stmt_ varchar2(2000) := null;

begin
v_num := server_error(1);
v_msg := server_error_msg(1);
--v_depth := server_error_param;
for i in 1 .. ora_sql_txt(sql_text) loop
stmt_ := stmt_ || sql_text(i);
end loop;

insert into log_errors(err_num, err_msg, err_datetime, err_depth) values(abs(v_num),v_msg, sysdate,stmt_);
end;

Import and Export Database

Using Exp?
C:\>Exp system/manager@orcl FULL=y file=c:\system.dmp Log=c:\system.log

Two user with multiple tables
EXP SYSTEM/password FIlE=C:\expdat.dmp TABLES=(scott.emp,hr.countries)
Single user
EXP scott/tiger FILE=dumptables.dmp TABLES=(emp,dept)

Using Imp?
Import Full database
imp SYSTEM/password FULL=y FIlE=C:\dba.dmp
To import just the dept and emp tables from the scott schema
imp SYSTEM/password FIlE=C:\dba.dmp FROMUSER=scott TABLES=(dept,emp)
To import tables and change the owner
imp SYSTEM/password FROM USER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)

Analytical functions

Analytic functions compute an aggregate value based on a group of rows.

1)return deptartments and their employee count. non group by columns not allowed in select clause.
SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;

DEPTNO DEPT_COUNT
---------------------- ----------------------
20 5
30 6

Using Analytical Function Correct query

SELECT empno,deptno,count(*) over(partition by deptno)dept_count
FROM emp where deptno in(10,20);

EMPNO DEPTNO DEPT_COUNT
7934 10 3
7782 10 3
7839 10 3
7902 20 5
7876 20 5
7566 20 5
7369 20 5
7788 20 5

For Reference http://www.orafaq.com/node/55

Agreegate Functions

What is Agreegate Functions?
Agreegate functions return a single result row based on group of rows

AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
REGR_ (Linear Regression) Functions
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE

First And Last row in table

First row in Emp Table

SELECT *FROM emp where rowid=(SELECT min(rowid) from emp);
Last row in Emp table

SELECT *FROM emp where rowid=(SELECT max(rowid) from emp);

both First and Last rows in Emp table

SELECT *FROM emp where rowid in (SELECT min(rowid) from emp union select max(rowid) from emp);

How to Delete duplicate rows AND count Duplicate Rows in table?

CREATE TABLE dupli(sname varchar2(10),sno number(5));

SNAME SNO
---------- ----
santhos 10
suresh 20
suresh 10
murali 30
murali 20
suresh 20

5 row selected

DELETE FROM dupli a where rowid <>(SELECT max(rowid) FROM dupli b where a.sno=b.sno);

3 rows deleted

SNAME SNO
---------- -------
santhos 10
murali 30
suresh 20

3 rows selected

count Duplicate rows
select count(*),sno from dupli having count(*)>1 group by sno

COUNT(*) SNO
--------- ------
3 20
2 10

2 rows selected

SQL *Loader

what is sql*Loader and what is it used for?
sql*loader is a bulk loader utility used for moving data from external files into oracle database. its syntax is similar to that of the DB2 load utility

c:\sqllr\sqlldr.bat

@echo ON
@echo "LOADING TABLE...."
sqlldr userid=scott/tiger@omkara control=C:\sqlldr\sqlldr.CTL log=C:\sqlldr\sqlldr.log bad=c:\sqlldr\sqlldr.bad
pause
Exit;

c:\sqlldr\sqlldr.ctl --control file

LOAD DATA
INFILE 'C:\sqlldr\sqlldr.txt'
REPLACE INTO TABLE t
FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY ' " '
TRAILING NULLCOLS
(
INSUREDID
)
NOTE:
for raw data in sqlldr.txt file other wise append raw data same .ctl at last line
as below
LOAD DATA
INFILE 'C:\sqlldr\sqlldr.txt'
REPLACE INTO TABLE t
FIELDS TERMINATED BY ' ' OPTIONALLY ENCLOSED BY ' " '
TRAILING NULLCOLS
(
INSUREDID
)662575
662669
659726
661462
665099
[or]
c:\sqlldr\sqlldr.txt

662575
662669
659726
661462
665099

How to spool the data into notepad

what is spool?
stores query results in a file,or optinally send the file to printer.

cmd>prompt or create file as .bat file

c:\spool/spool.bat

echo off
@echo "##########SPOOL ##################"
sqlplus scott/tiger@omkara @C:\spool\spool.SQL
@echo on;

PAUSE
Exit;

C:\spool\spool.SQL

set pagesize 1000;
set linesize 1000;
spool C:\spool\spool.txt --data spool into the spool.txt file automatically
select *from emp;
spool off;
quit
/

Debugging in sqldeveloper

Good Site How to debugg the pl/sql procedure in sqldeveloper find the link for good practise
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/appdev/sqldev/plsql_debug/plsql_debug_otn.htm

how to group by range

i have table it having age column i need count between age groupsbelow query all individual age record counts

create table agelimit(age number(10));

insert into agelimit values(10);
insert into agelimit values(10);
insert into agelimit values(12);
insert into agelimit values(20);
insert into agelimit values(30);
insert into agelimit values(40);
insert into agelimit values(45);
insert into agelimit values(60);
commit

SELECT CASE WHEN age <= 10 THEN '1-10'
WHEN age <= 20 THEN '11-20'
WHEN age <=30 THEN '21-30'
WHEN age <=40 THEN '31-40'
WHEN age <=50 THEN '41-50'
ELSE '51+' END AS age, COUNT(*) AS n
FROM agelimit GROUP BY CASE WHEN age<= 10 THEN '1-10' WHEN age <= 20 THEN '11-20'
WHEN age <=30 THEN '21-30'
when age <=40 THEN '31-40'
WHEN age <=50 THEN '41-50'
ELSE '51+' END