Encryption

CREATE OR REPLACE FUNCTION fnc_encryption(pi_src IN VARCHAR2) RETURN CLOB
IS
v_enc_val raw(32767);
v_key1 VARCHAR2(16) := '1111111111111111';
v_key2 VARCHAR2(16) := '2222222222222222';
v_charterset VARCHAR2(8) := 'AL32UTF8';
v_mod number := DBMS_CRYPTO.ENCRYPT_AES128
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
v_enc_val := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW(pi_src, v_charterset),
typ => v_mod,
key => UTL_I18N.STRING_TO_RAW(v_key1, v_charterset),
iv => UTL_I18N.STRING_TO_RAW(v_key2, v_charterset)
);
RETURN RAWTOHEX(v_enc_val);
END fnc_encryption;
/



Scenario 1: Use the function to encrypt all of the rows in the table and then repeat the operation - no error occurs:

SQL> update TEST
2 SET NAME=fnc_encryption(name);

3 rows updated.

SQL> update TEST
2 SET NAME=fnc_encryption(name);

3 rows updated.

Time ; Avoid duplicate

select DBMS_UTILITY.GET_TIME from dual;

select * from v$timer;

----------------------------------------

Avoid duplicate

To avoid duplicate rows there are three steps

1) Use DISTINCT Keyword
2) Use UNIQUE Keyword
3) Use GROUP BY Keyword

Examples

1) Select distinct vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast;
2) Select unique vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast;
3) Select vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast
group by vcname,empcode,deptcode,addr,age,phoneno;

Level queries

To get the higher manager of a particular employee


select yy.empno,yy.mgr,yy.ename,rt.empno,rt.mgr,rt.ename from
(select * from emp t
connect by prior t.empno=t.mgr start with mgr is null) yy,
emp rt
where yy.empno=rt.mgr --and rt.empno=7782

-------------------------------------------------------------------------------

To get repeating data

select deptno,dname,loc,ltrim(ename,' ,') ename,ltrim(hiredate,' ,') hiredate,ltrim(mgr,' ,') mgr
,ltrim(job,' ,') job
from (
select deptno,dname,loc,SYS_CONNECT_BY_PATH (ename,' ,') ename,SYS_CONNECT_BY_PATH (hiredate,' ,') hiredate,
SYS_CONNECT_BY_PATH (job,' ,') job,SYS_CONNECT_BY_PATH (mgr,' ,') mgr
from (
select r.deptno,r.dname,r.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm ,
row_number() over (partition by r.deptno order by r.deptno) r,
row_number() over (partition by r.deptno order by r.deptno) -1 c
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
) start with r = 1
connect by c = prior r
order by rownum desc)
where rownum = 1 ;


OR


select r.deptno,r.dname,r.loc,wmsys.wm_concat(e.ename),wmsys.wm_concat(e.job),wmsys.wm_concat(e.mgr),wmsys.wm_concat(e.hiredate),wmsys.wm_concat(e.sal),wmsys.wm_concat(e.comm)
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
group by r.deptno,r.dname,r.loc

References

1) www.orafaq.com/wiki/Main_Page

2) www.orafaq.com/wiki/Scripts

3) www.orafaq.com/wiki/SQL_FAQ

OS-Version

How to Identify OS or Oracle 64 bit or 32 bit

select * from v$version;

---------------------------------------------------------------------------