How to get time difference between two date columns in oracle Sql

First all both column should have time stored in it wether it in date Datatype or Datetime Datatype .

To get time  diff between two date column you can use this single  sql statement

Select substr(numtodsinterval([END_TIME]-[START_TIME],0),’day’),12,5)  from [TABLE_NAME];

Where [TABLE_NAME] si table where column is stored and [END_TIME],[START_TIME] are the come with date datatype having time stored in it. Out put will be rounded to minutes.

E.g

if  Start_Time=12/25/2011 8:40:21 AM

and End_time= 12/25/2011 5:20:21 PM  

output will be          08:40  

It saves my lot of time to convert and apply math on date columns. Hope its use full for some…

 

 

How to see all compilation errors when creating a database object

Some times Sql plus does not show any error by “Show Error” command however if you  compile  a pl/sql code it shows  Compilation Warnings.

For that there is another way to see all errors occured in compiling  pl/sql code of any database object  from any oracle Client e.g Sql plus,Toad,Pl/sql Developer etc.You can see all error using following query

select text from dba_errors where name = ‘[OBJECT_NAME]‘ and owner = ‘[OBJECT_OWNER]‘;

Where [OBJECT_NAME] is then name of Database object which is created by this code

[OBJECT_OWNER] is the name of schema in which you are trying to create object.

Hope it will help…

How to do validation before KEY-NEXT-ITEM trigger in Oracle Forms

Yesterday i found a good tip about Oracle Forms Item validation . I was having a form  which had a item with both Trigger WHEN-VALIDATE-ITEM and KEY-NEXT-ITEM . As you know we can not navigate in validation trigger so there was hell lot of code written on key next item trigger so if any user entered wrong data ( character instead of number or decimal instead of integer) then it crashed.

To change the validation scope  you should write following line on top of KEY-NEXT-ITEM—

validate(item_scope);

if form_failure then

raise form_trigger_failure;

end if;

Now it will fire WHEN-VALIDATE-ITEM first and validate input before getting into code written on KEY-NEXT-ITEM.

Oracle not available Shared memory realm doesnot exist

After Restarting the machine, when I tried to log on database through sql I got the
problem.It says

“oracle not available”
“Shared memory realm does not exist”

Solution done by me after searching google:–

1.> through Sql
conn / as sysdba;

2.> startup nomount;
It shows the message

ORACLE instance started.

Total System Global Area 113246208 bytes

Fixed Size 787708 bytes

Variable Size 61864708 bytes

Database Buffers 50331648 bytes

Redo Buffers 262144 bytes

3.>alter database mount;

4.> alter database open;

Now your data base is ready to use.

The password will expire within 10 days 11g database

If you get the following error in 11 g

ORA-28002: the password will expire within 10 days

Follow these Steps

1.connect to system/manager

2.ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Then reset all users passwords

e.g  alter user hr identified by hr;

alter user scott identified by tiger;

and so on then you will never need to reset password.It will set expiry limit of all password unlimited.

After a long time i am here for blogging again . During these days i was unable to blog due to various reasons.Any way it was  a  great and mature journey for me.

I have learned many things in this period during working on some Projects . I hope all people are enjoying their life .Thanks to all of you who liked my post and very sorry for not to reply some comments.

Lets start a new journey for exploring new things and share the knowledge .

How To disable Case Sensitive logon feature in Oracle Database 11g

The New Feature of Oracle Database 11g is having Case Sensitive Logon.

But In some cases where a form application which having hard coded username & password running smooth on oracle database 10g will not work on 11g.

then we got only two option either we have to change all hard coded password on each form or disable the case sensitive logon in 11g.

First option generally not feasible when an application having large no. of forms & reports.Actully if you choose not to disable case sensitive logon then you have to agree with capital password because when a form trying to run a report then it send the userid & password to report server in capital . So you have to change password of all user in capital letters & write the same on all Forms.

For Second Option just run the following Command & u will be able to run same application on 11g as it was running on 10g..

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = false

How to send an email to group of contacts using gmail

Do you send emails to more than one recipient often? Are the recipients often the same?

You can turn these contacts into a group in Gmail and address messages to all of them much easier and faster. Instead of entering the addressees individually, you just type the group name and let Gmail do the rest.

Set up an Address Book Group for List Mailing in Gmail

To create a group for list mailing from existing contacts in Gmail:

• Select Contacts from the left side navigation bar in Gmail.

• Check all the contacts you want to add to the new list.
• Click the Add to group … drop-down menu.
• Select New group … from the menu.
  • To add the selected contacts to an existing group, select it instead.
• Type the name by which you want the new address book group to go.
• Click OK.

Of course, you can also create an empty group:

• Select Contacts from the left navigation bar.
• Go to the Groups tab.
• Click Create Group.
• Type the desired name of the group under Group name:.
• Enter an initial set of members under Add contacts:.
  • You can add contacts already in your Gmail address book but type fresh email addresses, too. The latter will be added to your regular address book as well.

Edit the Members of a Gmail Group

To add new members to a group in Gmail, proceed as above. To remove or edit members of a mailing list:

• Select Contacts from the left navigation bar.
• Go to the Groups tab.
• Click the name of the group you want to edit.
• Check members and choose Remove from group to delete them from the group.
• Click on individual contacts to edit their details.

Send Group Mail in Gmail

With your groups set up and populated, you can now send messages to all members easily and swiftly.

How to unlock OE schema to follow Oracle Forms & Reports whitepaper

For working with tutorial of Build Internet Application with Oracle Forms 9i & Reports 9i. you need to work on the tables which is locked in oracle. for Unlocking these tables you have to follow these steps

1. Login in sqlplus or sqlplus or isqlplus as a user “system” with respective password.

2. Execute the following command

ALTER USER OE IDENTIFIED BY password ACCOUNT UNLOCK;

here password is your passord which you want to use for your account remember this password for further use.

3. Login in with user HR with password specified above

Now you can use all tables specified in tutorial of Oracle Forms & Reports 9i with all table.

Please leave your comment If you encounter any problem…

How to Install Oracle 9i on Windows XP/2000

Installing Oracle 9i on Windows NT/2000

The following outline describes the steps required to install Oracle9i (version 9.0.1.1 or 9.0.2.1) on the Microsoft Windows NT/2000 operating system. It is assumed that this is a fresh install and that no other Oracle products are installed at this time. If this is not the case, then these instructions may not work on your system.