Hi!
Friends, this page mainly deals with the basic of oracle sql & pl/sql. Here, i'm going to present many useful Oracle snippets which can be plugged into your solution. Many of the snippets which are going to be part of this blog are conceptualize and coded by me and many cases i got the idea from our brilliant otn members. I'm sure you people will like all the snippets as useful bricks. Very soon i am going to post many oracle sql & pl/sql .
Here i'm posting some useful SQL snippets which can be plugged into your environment -
SQL:
1. Dynamic Table Alteration:
Here is the sample code that demonstrate this -
Here i'm posting some useful SQL snippets which can be plugged into your environment -
SQL:
1. Dynamic Table Alteration:
Here is the sample code that demonstrate this -
scott>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Elapsed: 00:00:00.09
scott>
scott>create table test_dummy
2 (
3 a varchar2(10)
4 );
Table created.
Elapsed: 00:00:05.00
scott>
scott>
scott>alter table &tab add (& col varchar2 ( 10 ));
Enter value for tab: test_dummy
Enter value for col: b
old 1: alter table &tab add (& col varchar2 ( 10 ))
new 1: alter table test_dummy add (b varchar2 ( 10 ))
Table altered.
Elapsed: 00:00:01.19
scott>
scott>desc test_dummy;
Name Null? Type
-------------------- -------- --------------
A VARCHAR2(10)
B VARCHAR2(10)
2. Alternative Of Break Command:
scott>
scott>SELECT lag(null, 1, d.dname)
over (partition by e.deptno order by e.ename) as dname,
2 e.ename
3 from emp e, dept d
4 where e.deptno = d.deptno
5 ORDER BY D.dname, e.ename;
DNAME ENAME
-------------- ----------
ACCOUNTING CLARK
KING
MILLER
RESEARCH ADAMS
FORD
JONES
SCOTT
SMITH
SALES ALLEN
BLAKE
JAMES
DNAME ENAME
-------------- ----------
MARTIN
TURNER
WARD
14 rows selected.
Elapsed: 00:00:00.52
scott>
3. Can we increase the size of a column for a View:
SQL> create or replace view v_emp
2 as
3 select ename
4 from emp
5 /
View created.
SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(10)
SQL>
SQL> create or replace view v_emp
2 as
3 select cast (ename as varchar2 (30)) ename
4 from emp
5 /
View created.
SQL> desc v_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(30)
And here is the silly way to do this -
create or replace view temp_vv
as
select replace(ename,' ') ename
from (
select rpad(ename,100) ename
from emp
);
4. Combining two SQL Into One:
satyaki>
satyaki>select e.empno,e.deptno,d.loc "DEPT_10"
2 from emp e, dept d
3 where e.deptno = d.deptno
4 and d.deptno = 10;
EMPNO DEPTNO DEPT_10
---------- ---------- -------------
7782 10 NEW YORK
7839 10 NEW YORK
7934 10 NEW YORK
Elapsed: 00:00:00.04
satyaki>
satyaki>select e.empno,e.deptno,d.loc "DEPT_OTH"
2 from emp e, dept d
3 where e.deptno = d.deptno
4 and e.deptno not in (10);
EMPNO DEPTNO DEPT_OTH
---------- ---------- -------------
7369 20 DALLAS
7876 20 DALLAS
7566 20 DALLAS
7788 20 DALLAS
7902 20 DALLAS
7900 30 CHICAGO
7844 30 CHICAGO
7654 30 CHICAGO
7521 30 CHICAGO
7499 30 CHICAGO
7698 30 CHICAGO
11 rows selected.
Elapsed: 00:00:00.04
satyaki>
satyaki>
satyaki>select a.empno,(
2 select d.loc
3 from emp e, dept d
4 where e.deptno = d.deptno
5 and e.empno = a.empno
6 and d.deptno = 10
7 ) "DEPT_10" ,
8 (
9 select d.loc
10 from emp e, dept d
11 where e.deptno = d.deptno
12 and e.empno = a.empno
13 and d.deptno not in (10)
14 ) "DEPT_OTH"
15 from emp a
16 order by a.empno;
EMPNO DEPT_10 DEPT_OTH
---------- ------------- -------------
7369 DALLAS
7499 CHICAGO
7521 CHICAGO
7566 DALLAS
7654 CHICAGO
7698 CHICAGO
7782 NEW YORK
7788 DALLAS
7839 NEW YORK
7844 CHICAGO
7876 DALLAS
EMPNO DEPT_10 DEPT_OTH
---------- ------------- -------------
7900 CHICAGO
7902 DALLAS
7934 NEW YORK
14 rows selected.
Elapsed: 00:00:00.30
satyaki>
Regards.
Satyaki De.