Category Archives: Oracle

SQL is a most common uses database for small and medium size database. Some times we are fetching problem for generate report from SQL database, here I will show to you how How Generate SQL Report from Table.This is a very simple query & it is also for beginner. stay with us & search the blog for more SQL related post.
Lets See…..
we will generate report from the table name is :emp.dept and others are table field. So data will be generate from emp.dept table.

select ename,job,dept.dname,sal from emp.dept
where dept.DEPTNO=emp.DEPTNO
and upper(dept.DNAME)=upper(:dname);

A SQL Trigger is a special type of stored procedure that automatically executes on the server database when event occurs. Data manipulation language for a user to execute DML (DML) triggers when someone tries to modify the data. DML event insert, update, or delete statement on a table or view. Fire trigger when a valid event, to be fired regardless of whether or not any table rows are affected. see a simple code.
:sales_date:=sysdate;
when form will run current date will appear automatic into sales_date field.
when-form-instance

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

Code for sequential serial provide:
(PRE-INSERT)
select nvl (max(employee_id),0)+1 into :employee_id from employee;
——————————————————————–
POST Query
if : department_id is not null then

select department_name
into : disp_department
from hr_department
where id=: department_id;
end if;

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;