How to Show / Get, Year Month and Date between two date ?

 Use bellow sql to show year month and date..
   
    SELECT    TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE) / 12) as YEARS,
MOD(TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE)), 12) as MONTHS,
            (  TO_DATE (:END_DATE)- ADD_MONTHS (:START_DATE,TRUNC (MONTHS_BETWEEN (:END_DATE, :START_DATE))))  as Date
      FROM DUAL;

How to Show/ Get First Date of a Month in Oracle Sql ? (Part-2)

In my earlier post i show one method of retrieving first day of a month by  oracle built in function, which is ADD_MONTHS.

Now i show another method. Write the following code and get the megic.

SELECT TO_DATE(TO_CHAR(SYSDATE,'MONRRRR'),'MONRRRR')
FROM DUAL

Helpful ? Need more stuff. just leave you comments.

How to Install Oracle Developer Suite 10g on Windows Vista / 7?

When you try to install Oracle Developer Suite on your windows Vista or Windows 7 PC but it’s failing giving an error message "Checking Operating system version must be 5.0, 5.1 or 5.2..... Actual 6.0 or 6.1 - Failed"

What will you do?

Don’t worry, simply follow the following steps, and it will works.

Before installation, right click on setup.exe then click properties, from the tab select compatibility and select windows xp service pac3/pac2 from compatibility mode settings.

Click ok…

And now try to install... it works

How to Compile All Oracle Forms(.fmb to .fmx) Together ?

You need to write a script on the operating system to call "frmcmp" executable.

1.Pls open a note pad and paste the following lines...

::compile_forms.bat

cls

Echo compiling Forms....

for %%f IN (*.fmb) do frmcmp.exe userid=user_name/password@db  module=%%f batch=yes

    module_type=form compile_all=yes window_state=minimize

ECHO FINISHED COMPILING

2. change the user name , password and the database alias accordingly...
3. Pls save the file with a name.bat then...
4.put this file in the forms folder then...
5.Double click on that batch it will compile all the forms in that folder consequently...

How to get/show First Date of the Month ?

If you want to show or if you need the month first day, there is no built-in function in Oracle. But you can get it easily by using some other single-row date function. Here bellow an example of this.

SELECT LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1
FROM DUAL;

Run the above sql and you will get the first day of current month. For last day of current month just use the LAST_DAY function like thisLAST_DAY(SYSDATE)

Need more help ? Just Leave you comments...

Cheers

How to Show Time with Date in a Session ?

If your want to show time with date in your sql environment like, working with Sql Developer or Sql Plus or iSql Plus then change the value of NLS_DATE_FORMAT.

To change the value log in the sql environment and write:

alter session set NLS_DATE_FORMAT ='DD-MON-RRRR HH:MI:SS AM';

then check the value...you are done..!!!

How to Apply Patch Set 10.2.0.5 on Oracle Database 10gR2 ?

Oracle Database upgrade to 10.2.0.5 from 10.2.0.3, you need Patch Set No. 8202632. Download the patch set from oracle metalink.

Here is the guide for windows environment, how to apply this patch set.

Shut down the following Oracle Database 10g services in the order specified before installing the patch set:

    Shut down all services in the Oracle home that might be accessing a database, for example Oracle Enterprise Manager Database Control:

                C:\>cd C:\oracle\product\10.2.0\db_1\bin

                C:\oracle\product\10.2.0\db_1\bin>set oracle sid=ait

                C:\oracle\product\10.2.0\db_1\bin>emctl stop dbconsole

                C:\oracle\product\10.2.0\db_1\bin>isqlplusctl stop

You can also stop these services using the Services Control Panel. These services are referred to as OracleDBConsoleSID and OracleSIDiSQL*Plus respectively.

                C:\>sqlplus /nolog

                SQL> conn sys/password@ait as sysdba

                SQL> shutdown

Use the Services Control Panel to stop the Oracle Database service OracleServiceSID where SID is the system identifier of the database.

If any problem arises, then go to service control panel. Set all oracle service property to manual and restart the pc.

To install the Oracle Database 10g patch set interactively:

1. Log on as a member of the Administrators group to the computer on which you are going to install Oracle components. If you are installing on a Primary Domain Controller or a Backup Domain Controller, log on as a member of the Domain Administrators group.

2. Start Oracle Universal Installer located in the unzipped area of the patch set. For example, Oracle_patch\setup.exe.

3. On the Welcome screen, click Next.

4. In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next

5. Enter your e-mail address and password for your My Oracle Support account to be informed of security issues, then click Next.

Note:

The registration and configuration can also be done manually after patch set installation.

The OCM registration page appears only while applying 10.2.0.5 patch over existing Database instances alone.

-----======

6. If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears.

7. On the Summary screen, click Install.

When the installation is complete, the End of Installation screen appears.

Start the listner..

For Oracle single-instance installations, start the listener as follows:

C:\> lsnrctl start

or use service control panel.

Start the Oracle Database Upgrade Assistant:
From the Start menu, select Programs, then Oracle - HOME_NAME, then Configuration and Migration Tools and then Database Upgrade Assistant to start the Oracle Database Upgrade Assistant.

Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:

1. On the Welcome screen, click Next.
2. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.

Note:

For Oracle RAC, enter the SYS password to do the upgrade.

3. On the Recompile invalid objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.
4. If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, mention the Path, then click Next.
5. On the Summary screen, check the summary, then click Finish.
6. On the End of Upgrade screen, click Exit, then click Yes to exit from Oracle Database Upgrade Assistant.

How to Patch/Upgrade Oracle Database 9i ?

This will guide you to patch or upgrade oracle 9.2.0.1.0 to 9.2.0.8.0. If you don't know which patchset number is this, for your help, it is 4547809. Download it from oracle support Link.

1. After download extract the zip file.
2. Stop all running oracle services.
3. double click on setup.exe under extract file/disk1
4. follow the on screen instruction. for example
5. On the Welcome screen, click Next.
6. On the Specify File Locations screen, click Browse next to the Path field in the Source section.
7. Select the products.xml file from the stage directory where you unpacked the patch set files, then click Next. For example:

Oracle_patch\stage\products.xml

8. In the Name field in the Destination section, select the name of the Oracle home that you want to update from the drop down list, then click Next.
9. If you are installing the patch set on an Oracle RAC cluster, click Next when the Selected Nodes screen appears.

10.  After installation complete, Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:

11. Use SQL*Plus to login to the database as the SYS user with SYSDBA privileges:

12. C:\> sqlplus /NOLOG

13. SQL> CONNECT SYS/SYS_password AS SYSDBA

14. Start the database:

15. SQL> STARTUP

16. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):

17. SQL> SHOW PARAMETER PFILE;

This command displays the name and location of the server parameter file or the initialization parameter file.

18. Determine the current values of these parameters:

19. SQL> SHOW PARAMETER SHARED_POOL_SIZE

20. SQL> SHOW PARAMETER JAVA_POOL_SIZE

21. If the system is using a server parameter file:
1. If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:

b.    SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;

3. If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:

d.    SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;

22. If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
23. Shut down the database:

24. SQL> SHUTDOWN

Enter the following SQL*Plus commands:

1.    SQL> STARTUP MIGRATE

2.    SQL> SPOOL patch.log

3.    SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catpatch.sql

4.    SQL> SPOOL OFF

5. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
6. This list provides the version and status of each SERVER component in the database.
7. If necessary, rerun the catpatch.sql script after correcting any problems.
8. Restart the database:

9.    SQL> SHUTDOWN

10. SQL> STARTUP

11. Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.

12. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sql

U’r Done…..