How to Find Last Row in the Table from SQL query!

Last Row in TABLE 

The only way to get the last row is for the Developer to have put a tiemstamp or some sequence number in there.Then you can

select * from t where
x = (select max(x) from t;

select * from emp
where rowid in(select max(rowid) from emp);

select * from emp
minus
select * from emp
where rownum < (select count(*) from emp);

Retrieve Data From Two Tables

Use of cursor (Explicit) in SQL 

Declare
Cursor emp_cur is
Select emp.ename,dept.dname,dept.deptno
From emp,dept
Where emp.deptno = dept.deptno;
Begin
For emp_rec in emp_cur loop
If emp_rec.deptno = 30 then
Dbms_output.put_line('Employee'||'  '||emp_rec.ename||'  '||'work in the'||'  '||emp_rec.dname||'  '||'Department');
End if;
End loop;
End;

So find these Result:

Employee  ALLEN  work in the  SALES  Department
Employee  WARD  work in the  SALES  Department
Employee  MARTIN  work in the  SALES  Department
Employee  BLAKE  work in the  SALES  Department
Employee  TURNER  work in the  SALES  Department
Employee  JAMES  work in the  SALES  Department

Normalization in Database with Example

Normalization Example:

Assumption:A customer can have multiple orders and order can include multiple products.

0NF:
customer order: custname,orderno,prodno,proddesc,qty,custaddress,dateordered

1NF: Remove multivalued attributes
customer:custname,custaddress
order:custname,orderno,prodno,proddesc,qty,dateordered

2NF: Remove partial dependencies
customer:custname,custaddress
order line:orderno,prodno,qty,dateordered
product:prodno,proddesc
order:orderno,custname

3NF, BCNF: Remove transitive dependencies
customer:custname,custaddress
customer order:custname,orderno
order:custname,orderno,prodno,qty,dateordered
product:prodno,proddesc

how to hide drop Table in Cat list in oracle 10g

New Command of Oracle 10g:

Purge is useful in oracle 10g when you want to really truly drop a table in oracle 10g table can be undropped. for example in sql
SQL> desc t;
it show name type etc   x     null      varchar2(2000)      now you want to drop this table by
SQL> drop table t;
table dropped.
SQL> desc t;
object  t does not exist ;
SQL> flashback table t to before drop;
flashback complete.
SQL> desc t;
show table  t like   x    null   varchar2(2000)
SQL> select object_name from recyclebin;
object_name   Bin$totcup302.... after flashback

Now everything you drop a table, we just hide it not really drooped same with index etc.
SQL>drop table t purge;
table drooped and not show in object list.

Useful query for Accounts Ledger Report in Oracle

select a.account_id,a.account_name,a.voucher_date, a.DOC_TYPE_ID
  ,a.VOUCHER_NO,b.debit,b.credit,b.narration from
(
select a.account_id ,sum(b.debit)-sum(b.credit) open from
(
select a.account_id,b.debit,b.credit
from voucher_dtl b,voucher_mst a
where a.account_id=:account_id
AND DOC_TYPE_ID='JV'
union all
select a.account_id ,b,debit,b.credit from voucher_mst a,voucher_dtl b
where DOC_TYPE_ID='JV'
)
group by account_id
)a
,
(SELECT a.voucher_date, a.VOUCHER_NO,
a.DOC_TYPE_ID , ACCOUNT_ID, A.ACCOUNT_NAME,
(B.DEBIT) DEBIT, B.NARRATION, (B.CREDIT) CREDITt
FROM voucher_mst a,voucher_dtl b
where ACCOUNT_ID=:ACCOUNT_ID
AND DOC_TYPE_ID='JV'
and
(DEBIT>0
or
CREDIT>0
)

)b,
(
select account_code ,sum(dr_amount)-sum(cr_Amount) due from
(
select account_code,dr_Amount,cr_amount
from MATRIX.journal
where account_code=:accounts
and cDate<=:date_2
AND decode(:STATUS,'ORIGINAL',o_r,'IT',I_T,'GST',G_T)='YES'
AND decode(:ORG,'ALL','ALL',COMPANY)=:ORG
and voucher_type<>'Open'
union all
select account_code ,dr_Amount,cr_amount from MATRIX.journal
where voucher_type='Open'
AND decode(:STATUS,'ORIGINAL',o_r,'IT',I_T,'GST',G_T)='YES'
AND decode(:ORG,'ALL','ALL',COMPANY)=:ORG
and account_code=:accounts
)
group by account_code
)c,MATRIX.ledger
where a.account_code=b.account_code(+)
and a.account_code=c.account_code(+)
and a.account_Code=ledger.accountid

order by cdate

Useful Loops for Oracle Developer

BEGIN
    GO_BLOCK('MY_BLOCK');
    FIRST_RECORD;
    LOOP
    MESSAGE(:MY_BLOCK.DSP_NAME);
    EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
    NEXT_RECORD;          
    END LOOP;  
END;

---------------------------------------------------------------
DECLARE
   /* All the jobs in the Fall to prepare for the Winter */
   CURSOR fall_jobs_cur
   IS
      SELECT task, expected_hours, tools_required, do_it_yourself_flag
        FROM winterize
       WHERE year = TO_CHAR (SYSDATE, 'YYYY')
         AND completed_flag = 'NOTYET'
       FOR UPDATE OF task;
BEGIN
   /* For each job fetched by the cursor... */
   FOR job_rec IN fall_jobs_cur
   LOOP
      IF job_rec.do_it_yourself_flag = 'YOUCANDOIT'
      THEN
         /*
         || I have found my next job. Assign it to myself (like someone
         || is going to do it!) and then commit the changes.
         */
         UPDATE winterize SET responsible = 'STEVEN'
          WHERE task = job_rec.task
            AND year = TO_CHAR (SYSDATE, 'YYYY');
         COMMIT;
      END IF;
   END LOOP;
END;
-----------------------------------------------------------

This is possible. In when validate record you write like this,

declare
v_rec_no number;
begin
if :system.cursor_record > 1 then
v_rec_no = :system.cursor_record ;
first_record;
for i in 1 to v_rec_no loop
< your validation>
.....
.....
next_record;
end loop;
end;