Sql Tips & Tricks

LAG() AND LEAD() function Example from EMTTRK

2009-10-05T00:46:00.000-07:00

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

2009-10-05T00:45:00.000-07:00

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

2009-10-04T04:54:00.000-07:00

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

2009-09-02T04:00:00.000-07:00

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.

2009-08-26T03:39:00.000-07:00

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

2009-07-22T05:41:00.001-07:00

Moon Mission

/Script to count the number of rows in tables/

2009-03-31T05:49:00.000-07:00

/*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;

Limiting User Commands, Roles & Sessions!! One Session / User.

2008-12-04T02:13:00.001-08:00

There are a number of options available for limiting user access to the database. You can grant/restrict access of a specific user based on IP address as discussed in the article Tricky Oracle Access by IP Address!! However sometimes we need to limit user access instead of restricting it completely. In this article we will discuss some great tips that will help you limit your user access to the database.

Oracle PRODUCT_PROFILE Table:

Oracle PRODUCT_PROFILE table can be used for restricting a user or group of users from running a specific command. You can use it to disable roles and report the current restrictions in the profile table. If PRODUCT_PROFILE does not exist, then you can create it by running the PUBBLD.SQL script.

Limiting User from running Specific Commands:

Consider a scenario where you own a large database of some hundred tables and you do not want Schema Owner to run the DELETE command on any of the tables. One solution is to use the GRANT / REVOKE commands. However the simple solution is to insert a row in the PRODUCT_PROFILE table. Below command will restrict user SCOTT from running DELETE command on any table.

INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value) VALUES('SQL*Plus','SCOTT','DELETE','DISABLED');

You can re-enable the command by simply deleting that row from the PRODUCT_PROFILE table.

DELETE FROM PRODUCT_PROFILE WHERE (product='SQLPlus' AND userid='SCOTT' AND attribute=' DELETE ' AND char_value=' DISABLED')

Note that you must be logged on as SYSTEM in order to delete any row from the PRODUCT_PROFILE table.

Limiting User Roles:

Sometimes it happens that we want to assign some specific role to user for the purpose of running application but we don't want user to have that role while running ad-hoc commands from SQLPlus. However you can achieve this goal by inserting below row into the PRODUCT_PROFILE table.

INSERT INTO PRODUCT_PROFILE (product, userid, attribute, char_value) VALUES ('SQLPlus','SCOTT','ROLES','PAYROLL_ADMINISTRATOR');

You can re-enable the command by simply deleting that row from the PRODUCT_PROFILE table.

DELETE FROM PRODUCT_PROFILE WHERE (product='SQLPlus' AND userid='SCOTT' AND command='ROLES' AND char_value='PAYROLL_ADMINISTRATOR')

Assigning only one Oracle session to User:

Sometimes our requirement is to allow only one session to each user in the database. One cumbersome solution to this table is to run a batch job that analyzes V$SESSION table and kills sessions of those users who have more than 1 session. Another approach is to create a LOGIN table and insert a row into it whenever user logs in and remove the row with log out. However this approach has lots of problems especially when a row does not gets removed due to application crash. A simple solution to this problem is described below whereby any user with the ONE_SESSION profile can log on only once.

SQL> CREATE PROFILE ONE_SESSION LIMIT SESSIONS_PER_USER 1;
PROFILE CREATED.

SQL> ALTER USER SCOTT PROFILE ONE_SESSION;
USER ALTERED.

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
SYSTEM ALTERED. Read Again!!

Best regards,

Abdoul Rahaman

IT Consultant

Globe Marine Services Co

Jeddah

Mobile:966-507654847

Tricky Oracle Access by IP Address!!

2008-12-04T02:07:00.001-08:00

Sometimes it happens that we want to grant access to our Oracle database filtered by IP address. A typical scenario can be where you want to allow a number of IP addresses to connect to your database or ban a number of IP addresses from log in to your database. In this article we will discuss the secrets of restricting access to your database through a list of IP addresses.

Oracle enables you to restrict database access based on IP address by modifying the SQLNET.ORA file. The SQLNET.ORA file is Oracle configuration file that typically resides $ORACLE_HOME/NETWORK/ADMIN directory on UNIX systems and ORACLE_HOME\network\admin directory on Windows systems. If SQLNET.ORA file is not found there then you will have to see if you have a TNS_ADMIN environment variable pointing to a different directory because SQLNET.ORA file can also be stored in the directory specified by the TNS_ADMIN environment variable.

Below steps can be used to authorize users from accessing Oracle database based on their IP Address.

1. Turn On Hostname/IP Checking for Listeners:

Open SQLNET.ORA file in a text editor and add below line

tcp.validnode_checking = yes

2. Supply lists of nodes to be Allowed/Denied:

Now you will have to use tcp.invited_nodes and tcp.excluded_nodes to supply a list of nodes that you want to allow or deny for getting access to your database. Make sure that you always enter localhost as an invited node. Also you must ensure that all node addresses come in one line and no wildcards are used. Remember the list of included nodes have higher precedence over the list of excluded nodes.

tcp.invited_nodes = (localhost,hostname1,hostname2)
tcp.excluded_nodes = (hostname1,hostname2)

One thing that you should keep in mind is that if you are only using the tcp.invited_nodes then only those specific nodes will be allowed to access your database and all other IP addresses will be denied from accessing your database.

Similarly if you are only using tcp.excluded_nodes then only those specific nodes will be denied from getting access your database and all other IP addresses will be allowed to access your database.

3. Restart Listeners:

Finally you will need to restart your listeners by running below commands.

$LSNRCTL STOP
$LSNRCTL START

SQLNET.ORA for Allowed IP Addresses:

Suppose you want to allow users from IP addresses 70.127.349.101 and 70.127.349.160 only to access your database. In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,70.127.349.101,70.127.349.160)

SQLNET.ORA for Banned IP Addresses:

Suppose you want to ban users from IP addresses 70.127.349.216, 192.176.420.301 and 70.127.349.191 from getting access to your database. In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.excluded_nodes = (70.127.349.216, 192.176.420.301, 70.127.349.191)

Best regards,

Abdoul Rahaman

IT Consultant

Globe Marine Services Co

Jeddah

Mobile:966-507654847

How to use index efficiently in SQL (Oracle)

2008-10-06T03:12:00.001-07:00

SELECT /*+ INDEX(GMS_TSP_PROPOSE GMS_TSP_PROPOSE_IDX1) */

DISTINCT SUBSTR(TSP_BL_NUMBER,5) AS TSP_BL_NUMBER,

TSP_FINAL_DESTINATION,

TSP_REFNO,

TSP_BL_REFNO,

VESSEL_NAME,

TSP_VOYAGE,

TSP_PORT

FROM GMS_TSP_PROPOSE, GMS_VESSEL_MASTER

WHERE TSP_STATUS = 'P' AND

TSP_Vessel = VESSEL_CODE

Note: Never use Table Alias in SQL with will not allow to use index.

Oracle User Expiry Lock

2008-08-18T03:28:00.000-07:00

LOGON('SYSTEM','HTP'||'@'||:PARAMETER.TNSNAMES);

:GLOBAL.EXP := 'N';

DECLARE

my_stmt VARCHAR2(2000);

my_stmt2 VARCHAR2(2000);

ABC VARCHAR(200);

A VARCHAR(1);

BEGIN

my_stmt2 := 'ALTER USER '||CHR(34)||'HTP'||CHR(34)||'ACCOUNT UNLOCK';

FORMS_DDL(MY_STMT2);

END;

LOGOUT;

LOGON('HTP','HTP'||'@'||:PARAMETER.TNSNAMES);

IF to_date(SYSDATE,'DD/MM/RRRR') >= TO_DATE(:PARAMETER.EXP_DATE,'DD/MM/RRRR') THEN

DECLARE

my_stmt VARCHAR2(2000);

BEGIN

my_stmt := 'ALTER USER '||CHR(34)||'HTP'||CHR(34)||'ACCOUNT LOCK';

FORMS_DDL(MY_STMT);

message('Trail version Expired. Plz ');

:GLOBAL.EXP := 'Y';

END;

ELSE

LOGOUT;

LOGON('HTP','HTP'||'@'||:PARAMETER.TNSNAMES);

END IF;

:global.location := '';

:global.curr := '';

:global.user_name := '';

:global.company_name := '';

Nokia Codes..

2008-07-27T23:09:00.000-07:00

*3370# This Nokia code activates Enhanced Full Rate Codec (EFR) - Your Nokia cell phone uses the best sound quality but talk time is reduced my approx. 5%
#3370# Deactivate Enhanced Full Rate Codec (EFR) .
*#4720# Activate Half Rate Codec - Your phone uses a lower quality sound but you should gain approx 30% more Talk Time.
*#4720# With this Nokia code you can deactivate the Half Rate Codec.
*#0000# Displays your phones software version, 1st Line : Software Version, 2nd Line : Software Release Date, 3rd Line : Compression Type .
*#9999# Phones software version if *#0000# does not work.
*#06# For checking the International Mobile Equipment Identity (IMEI Number) .
#pw+1234567890+1# Provider Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols).
#pw+1234567890+2# Network Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols) .
#pw+1234567890+3# Country Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols).
#pw+1234567890+4# SIM Card Lock Status. (use the "*" button to obtain the "p,w" and "+" symbols) .
*#147# This lets you know who called you last (Only vodofone).
*#1471# Last call (Only vodofone) .
*#21# This phone code allows you to check the number that "All Calls" are diverted to.
*#2640# Displays phone security code in use .
*#30# Lets you see the private number.
*#43# Allows you to check the "Call Waiting" status of your cell phone.
*#61# Allows you to check the number that "On No Reply" calls are diverted to .
*#62# Allows you to check the number that "Divert If Unreachable (no service)" calls are diverted to .
*#67# Allows you to check the number that "On Busy Calls" are diverted to
*#67705646# Phone code that removes operator logo on 3310 & 3330
*#73# Reset phone timers and game scores.
*#746025625# Displays the SIM Clock status, if your phone supports this power saving feature "SIM Clock Stop Allowed", it means you will get the best standby time possible .
*#7760# Manufactures code.
*#7780# Restore factory settings.
*#8110# Software version for the nokia 8110.
*#92702689# Displays - 1.Serial Number, 2.Date Made, 3.Purchase Date, 4.Date of last repair (0000 for no repairs), 5.Transfer User Data. To exit this mode you need to switch your phone off then on again.
*#94870345123456789# Deactivate the PWM-Mem.
**21*number# Turn on "All Calls" diverting to the phone number entered.
**61*number# Turn on "No Reply" diverting to the phone number entered .
**67*number# Turn on "On Busy" diverting to the phone number entered .
12345 This is the default security code .
press and hold # Lets you switch between lines.

2008-07-27T04:03:00.000-07:00

Cold backup in Oracle
Making a Cold Backup
A cold backup requires the database to be shut down. That means, as opposed to a hot backup, users cannot do anything on the database. So, first, SHUTDOWN the database (not a shutdown abort). Then copy all necessary files to a safe place. Those files are: The datafiles:
select name from v$datafile
The control files:
select name from v$controlfile
The online redo logs: Note, the online redo logs need not be backed up.
select member from v$logfile
The parameter file init.ora or spfile (The parameter file cannot be found using dynamic performance views) and the password file (if used). Also, some network configuration files such as the listener.ora, tnsnames.ora and sqlnet.ora might be backed up. Some might also want to backup the Oracle Software along with the inventory, the oratab file and startup scripts. Here is a shell script that shuts down the database, copies the files of the database to a safe place and then starts up the database again. After we have copied these files to a safe place, we can safely delete the archived redo log files.
Simulating Media Failure
create some tables, and insert something into the tables. And then delete the datafiles
create table after_backup (a number, b varchar2(40));
insert into after_backup values (1, 'before commit');
insert into after_backup values (2, 'also before commit');
commit;
insert into after_backup values (3, 'after commit');
Make sure to not commit this session until you delete the datafiles. Open another session instead and create a user or something.
create user user_after_backup identified by pw;
grant dba to user_after_backup;
Now, crash the instance and delete the database. Open the init.ora file at the backuped place and modify its control_file initializtion parameter to point to the backed up control files. Then startup mount pfile=c:\oracle\ora81\admin\backup\initadpdb.ora the database. Note, I am using the backed up initadpdb.ora file. After you've mounted the database, rename the datafile (so that they point to the new files):
alter database rename file 'C:\ORACLE\ORA81\ADMIN\ADPDB\SYSTEM01.DBF'
to 'C:\ORACLE\ORA81\ADMIN\backup\SYSTEM01.DBF';
Note: Online redo log files should never be backed up

Data guard

2008-07-22T02:54:00.000-07:00

When you configure your database for datagaurd the start the database only with that pfile which has the configuration. Then you can work smooth in the database or else it will hangs unnecessary.

How to convert Column to Rows using SYS_CONNECT_BY_PATH

2008-05-29T03:19:00.000-07:00

SELECT DEL_REFNO ID,MAX(LTRIM(SYS_CONNECT_BY_PATH(DEL_DELIVERY_NO,','),',')) RES FROM (SELECT DEL_REFNO,DEL_DELIVERY_NO, ROW_NUMBER() OVER(PARTITION BY DEL_REFNO ORDER BY DEL_DELIVERY_NO) RN FROM HTP_DELIVERY_DETAIL WHERE DEL_REFNO = :Z_SHIPMENT_NO) START WITH RN = 1 CONNECT BY PRIOR RN = RN -1 AND PRIOR DEL_REFNO = DEL_REFNO GROUP BY DEL_REFNO;

How to connect to Oracle server without username and password.

2007-04-30T04:10:00.001-07:00

1.     start Sql with /nolog in username
2.    connect / as sysdba.
3.     alter user sys identified by manager

ISS Not working with Visual Web Developer 2005.

2007-02-14T02:48:00.000-08:00

When connecting to local IIS server and if returns error that IIS cannot
connect to Metabase then run the following command.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis -i

This will reinstall the ASP NET 2 and you have done.

Peace in your life.

2007-01-29T01:31:00.000-08:00

How to Install Microsoft office document image writter.

2007-01-29T00:05:00.001-08:00

If you have installed Office and deleted this printer you can get back by the running the following command.

cscript c:\windows\system32\prnmngr.vbs -a -p "Microsoft Office Document Image Writer" -m "Microsoft Office Document Image Writer Driver" -r "Microsoft Document Imaging Writer Port:"

Getting Dates whether Transaction is there on not

2007-01-24T04:25:00.000-08:00

SELECT TO_DATE(:FROM_DATE,'DD/MM/YYYY')+(ROWNUM-1) AS SUPV_DATE,
NULL AS SUPV_HRS,
NULL AS SUPV_OT
FROM ALL_OBJECTS
WHERE TO_DATE(:FROM_DATE,'DD/MM/YYYY') + (ROWNUM - 1) <= TO_DATE(:END_DATE,'DD/MM/YYYY')

Best Usage of Index

2006-12-13T23:16:00.000-08:00

Never format the Database column like to_date,to_char etc since this will ignore the index usage.

Correct type:
Eg:SELECT DISTINCT ADD_WAYBILL FROM EX_CON_WAYBILL
WHERE CURDATE >= TO_DATE(:FROM_DATE,'DD-MM-YYYY')
AND CURDATE < truck_co =" 'GMS'">

Worng Type:
SELECT CURDATE, ADD_WAYBILL FROM TEST1
WHERE TO_DATE(CURDATE,'DD-MM-YYYY HH:MI:SS') >= TO_DATE(:FROM_DATE,'DD-MM-YYYY HH:MI:SS')
AND TO_DATE(CURDATE,'DD-MM-YYYY HH:MI:SS')<>
AND ADD_WAYBILL IS NOT NULL
AND TRIP_CODE IS NOT NULL
AND TRUCK_CO = 'GMS'