LAG() AND LEAD() function Example from EMTTRK

SELECT BADGENO,

DRIVERNAME,

OUT_DATE,

OUT_TIME

from (

SELECT BADGENO,

DRIVERNAME,

OUT_DATE,

OUT_TIME,

NVL((TO_DATE(OUT_TIME,'HH24:MI') - TO_DATE(LAG(OUT_TIME) OVER
(PARTITION BY BADGENO ORDER BY
BADGENO,OUT_DATE,OUT_TIME),'HH24:MI'))*24*60,20) PREV_VALUE,

(TO_DATE(OUT_TIME,'HH24:MI') - TO_DATE(LEAD(OUT_TIME) OVER
(PARTITION BY BADGENO ORDER BY BADGENO,OUT_DATE,OUT_TIME),'HH24:MI'))*24*60
NEXT_VALUE

FROM (

SELECT BADGENO,

DRIVERNAME AS DRIVERNAME,

TO_DATE(TO_CHAR(OUT_DATETIME,'DD/MM/RRRR'),'DD/MM/RRRR') AS
OUT_DATE,

TO_CHAR(OUT_DATETIME,'HH24:MI') AS OUT_TIME

FROM EMPTY_CONTR

WHERE TRUCK_CO = 'GMS'

AND TRANSTYPE = 'BAK'

AND TO_DATE(OUT_DATETIME,'DD/MM/RRRR') >=
TO_DATE(:FROM_DATE,'DD/MM/RRRR')

AND TO_DATE(OUT_DATETIME,'DD/MM/RRRR') <
TO_DATE(:END_DATE,'DD/MM/RRRR')+1

ORDER BY BADGENO,OUT_DATE,OUT_TIME)

)

WHERE PREV_VALUE >= 15

LAG() AND LEAD() FUNCTION

THIS LAG() AND LEAD() FUNCTION RETURNS THE PREVIOUS OR NEXT ROW VALUE.

EXAMPLE:

LAG(OUT_TIME) OVER (PARTITION BY BADGENO ORDER BY
BADGENO,OUT_DATE,OUT_TIME)

Here the BADGENO is taken as group by and user orderby according to the

specified sequence in the cluase.

Using Analytic Functions in Oracle Lead & Lag

Using
<http://www.myoracleportal.net/blog1.php/2009/03/18/using_oracle_analytics>
Analytic Functions in Oracle

There are two more analytical functions which are called LAG() and LEAD()

LAG() points to the immediately previous row of the current value
LEAD() points to the immediately next row of the current value

A simple example can demonstrate these functions.

Now If I want to sort the emp_ids under manager_id 101 in ascending order
then:

select emp_id from emp where mgr_id = 101 order by emp_id;

LEAD():
----

If I want the result like:

201, next value of 201 which is 202
202, next value of 202 which is 203
203, next value of 203 which is 204
204, next value of 204 which is NULL (because there is no emp_id for mgr_id
101 after 204)

we can do something like:

SQL> select emp_id,
2 lead(emp_id) over (partition by mgr_id order by emp_id) next_value
3 from emp
4 where mgr_id = 101;

EMP_ID NEXT_VALUE
---------- ----------
201 202
202 203
203 204
204

If we do not want NULL in the last row and instead we want zero there, we
could do:

SQL> select emp_id,
2 lead(emp_id,1,0) over (partition by mgr_id order by emp_id) next_value
3 from emp
4 where mgr_id = 101;

EMP_ID NEXT_VALUE
---------- ----------
201 202
202 203
203 204
204 0

LAG()
----

Now in the same case as above, if we want to have result like following

201, previous value of 201 which is NULL (because there is no emp_id for
mgr_id 101 before 201)
202, previous value of 202 which is 201
203, previous value of 203 which is 202
204, previous value of 204 which is 203

we can do something like:

SQL> select emp_id,
2 lag(emp_id) over (partition by mgr_id order by emp_id) previous_value
3 from emp
4 where mgr_id = 101;

EMP_ID PREVIOUS_VALUE
---------- --------------
201
202 201
203 202
204 203

If we do not want NULL in the first row, and instead we want zero there, we
could do:

SQL> select emp_id,
2 lag(emp_id,1,0) over (partition by mgr_id order by emp_id) previous_value
3 from emp
4 where mgr_id = 101;

EMP_ID PREVIOUS_VALUE
---------- --------------
201 0
202 201
203 202
204 203

Now Both LAG and LEAD together:

SQL> select emp_id,
2 lag(emp_id,1,0) over (partition by mgr_id order by emp_id) lag,
3 lead(emp_id,1,0) over (partition by mgr_id order by emp_id) lead
4 from emp
5 where mgr_id = 101;

EMP_ID LAG LEAD
---------- ---------- ----------
201 0 202
202 201 203
203 202 204
204 203 0

Best regards,

Abdoul Rahaman

IT Consultant

Globe Marine Services Co

Jeddah

Mobile : +966507654847

Connecting Oracle Forms 6i to Oracle XE 10G

Connecting Oracle Forms 6i to Oracle XE 10G

To make Oracle Forms 6i able to connect to Oracle XE 10g I set the database
character set to UTF8.

Steps to do are as follows:

First I shutdown the database:

SQLPLUS>shutdown immediate

Then I start the database in restrict mode:

SQLPLUS>startup restrict

Then I change the database character set as follows:

SQLPLUS>ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;

Then I shutdown the database:

SQLPLUS>shutdown immediate

Then I start the database in normal mode:

SQLPLUS>startup

After this my colleague was able to connect to the Oracle XE database
through Forms 6i.

Arabic Support in Oracle Database 10g and Forms 6i.

Arabic Support in Oracle Database and Forms.

You can user any database version. For Free use 10g Express edition with
Universal Language.

1. Create a database with character set as UTF8.
2. Change the Forms NLS_LANG to AR8MSWIN1256 in regedit.
3. Change the HORA Keeptools Oracle TNS Configuration file to
Developer6i Tnsnames.
4. Change the Regional Settings tab in Windows to English and Local
menu as United States. and in Advanced TAB select Arabic Encoding to use.

That's it. Now you are fully working in Arabic Environment.

Today is the day man landed on moon

Moon Mission

/*Script to count the number of rows in tables*/

/*Script to count the number of rows in tables*/

 

set serveroutput on

 

declare

numrows integer;

cursor c1 is select table_name from user_tables order by table_name;

function rowcount(tablename in user_tables.table_name%type)

return integer is

cursornum    integer;

numrows        integer;

ignore        integer;

begin

cursornum := dbms_sql.open_cursor;

dbms_sql.parse(cursornum,

'select count(*) from ' || tablename,

dbms_sql.v7);

dbms_sql.define_column(cursornum, 1, numrows);

ignore := dbms_sql.execute(cursornum);

ignore := dbms_sql.fetch_rows(cursornum);

dbms_sql.column_value(cursornum, 1, numrows);

dbms_sql.close_cursor(cursornum);

return numrows;

end;

begin

dbms_output.enable(100000);

dbms_output.put_line('Table                           Rows      ');

dbms_output.put_line('——————————  ———-');

for c1rec in c1 loop

numrows := rowcount(c1rec.table_name);

dbms_output.put_line(rpad(c1rec.table_name, 32) || numrows);

end loop;

end;

/