Monthly Archives: July 2013

Table name: item_info
Colum_name:PRODUCTS_NAME
Code for duplicate data filter, for that you have to use Trigger-WHEN-VALIDATE-ITEM
Code:

declare

a number;

begin
select count(1) into a from item_info
where lower(PRODUCTS_NAME)=lower(:PRODUCTS_NAME);
if a>0 then
message(‘Already Exist In Database’);
message(‘Already Exist In Database’);
raise form_trigger_failure;
end if;
end;

Output:
WHEN-VALIDATE-ITEM

1.POST-TEXT-ITEM
declare
a number;
begin

if : DISP_EMPNO is not null then

select count(*) into a from emp
where empno=: DISP_EMPNO;

if a>0 then
select ename,sal into : DISP_EMPNAME,: DISP_SALARY
from emp
where empno=: DISP_EMPNO;
else
: DISP_EMPNAME:=”;
: DISP_SALARY:=”;

end if;

end if;

exception
when others then null;

end;
—————————————————————-
Continue reading

select sysdate from dual;
select to_char(sysdate,’MM’) pdate from dual;
select to_char(sysdate,’MM-DD’) pdate from dual;
select to_char(sysdate,’MM-DD-YYYY’) pdate from dual;
select to_char(sysdate,’MM-DD-YYYY HH24′) pdate from dual;
select to_char(sysdate,’MM-DD-YYYY HH24:MI’) pdate from dual;
select to_char(sysdate,’MM-DD-YYYY HH24:MI:SS’) pdate from dual;
select CURRENT_DATE from dual; Continue reading

SELECT INITCAP(‘the soap bangladesh’) “Capitals” FROM DUAL;
SELECT LOWER(‘MR. SCOTT MCMILLAN’) “Lowercase” FROM DUAL;
SELECT UPPER(‘asraf’) “Uppercase” FROM dual;
select concat(‘Jewel’,’ Asraf’) from dual;
SELECT SUBSTR(‘MR.JEWEL’,4,5) FROM DUAL;
SELECT length(‘MR.JEWEL’) FROM DUAL;
SELECT instr(‘MR.JEWEL’,’.’) FROM DUAL;
select LPAD(121331, 12, ‘0’) “LPAD” from dual;
select lpad(sal,6,’0′) from emp;
select RPAD(121331, 12, ‘0’) “LPAD” from dual;
select rpad(ename,20,’-Oracle Student’) from emp;
SELECT LTRIM(‘Jewel’, ‘J’) FROM dual;
SELECT RTRIM(‘Jewel’, ‘l’) FROM dual;
select trim(‘ Jewel Sharmin ‘) from dual;
select trim(ename) from emp;

SELECT ABS(-15) “Absolute” FROM DUAL;

Absolute
———-
15

select ceil(268651.1) from dual;
SELECT FLOOR(15.9) “Floor” FROM DUAl;
SELECT ROUND(15.193,2) “Round” FROM DUAL;
SELECT MOD(11,4) “Modulus” FROM DUAL;
SELECT TRUNC(15.79,1) “Truncate” from dual;

SELECT ceil(15.197) “ceil”,FLOOR(15.197)”FLOOR”, ROUND(15.197,2) “Round”,MOD(15.197,2) “Modulus” ,TRUNC(15.197,2) “Truncate” FROM DUAL;