Error occurred when creating new connection on first time in SQL Developer

I have experienced a problem of creating a new connection through the SQL developer, after first time I install SQL developer. I gave all the parameters correctly when creating new connection.

Although I have given proper connection name, correct username and password with correct SID, following error alert when I click the test button on the SQL developer interface.

“Status: Failure – Test failed: ORA-00604: error occurred at recursive SQL level    1ORA-01882: timezone region not found”

See the following figure:

To resolve this error I had to edit the “sqldeveloper.conf” file in the ‘\sqldeveloper\sqldeveloper\bin’ directory. It need to add the  following line and save the file.

AddVMOption -Duser.timezone=SAST-2

Close the SQL developer and reopen. Try now. It will show “Status: Success”.

Now you can connect to the database successfully.

Auto increasing column in Oracle

Most of the times we need to increase our database table column automatically increasing on data insertion.  As primary key column is unique and not null it is very useful to set the primary key column as automatic.

Create a table named My_table

CREATE TABLE my_table

    (tid         NUMBER(5,0) NOT NULL,

     tname       VARCHAR2(20),

     CONSTRAINT pk_tid PRIMARY KEY (tid))

/

Create a sequence named seq_my_tab

CREATE SEQUENCE seq_my_tab

    start with 1

    increment by 1

/

Now you can create a trigger to set the sequence to the table column get increment on data insertion.

CREATE OR REPLACE TRIGGER trg_my_tab

BEFORE INSERT ON my_table

FOR EACH ROW

BEGIN

    SELECT seq_my_tab.NEXTVAL INTO :new.tid FROM dual;

END;

/

Let’s insert some records in to the table.

INSERT INTO my_table (tid, tname) VALUES (100,'Sachin');

INSERT INTO my_table (tname) VALUES ('Vidusha');

INSERT INTO my_table (tname) VALUES ('Malinga');

SELECT * FROM my_table;

TID	TNAME
1	Sachin
2	Vidusha
3	Malinga

In the output first inserted tid overwrite by the trigger generated value.  We can modify our trigger to insert user required values. Here I am going to customize my trigger bit more.

CREATE OR REPLACE TRIGGER trg_my_tab

BEFORE INSERT ON my_table

FOR EACH ROW

DECLARE

    max_val NUMBER;

    cur_val NUMBER;

   

BEGIN

IF :new.tid IS NULL THEN

       SELECT seq_my_tab.NEXTVAL INTO :new.tid FROM dual;

    ELSE

        SELECT greatest(nvl(max(tid),0),:new.tid) INTO max_val FROM my_table;

        SELECT seq_my_tab.NEXTVAL INTO cur_val FROM dual;

        WHILE cur_val < max_val

        LOOP

            SELECT seq_my_tab.NEXTVAL into cur_val from dual;

        END LOOP;

    END IF;

END;

/

Let’s insert some records in to the table.

INSERT INTO my_table (tid, tname) VALUES (200,'Chani');

INSERT INTO my_table (tid,tname)  VALUES (400,'Minchi');

INSERT INTO my_table (tname)      VALUES ('Kumani');

SELECT * FROM my_table;

TID	TNAME
200	Chani
400	Minchi
401	Kumani

It can be inserting any number for the primary key column and next automatic number will 1+ max number.

Uninstall oracle 10g

Oracle uninstallation is not just uninstalling from Oracle Universal Installer. There are some more files to remove from different locations. Although you de-install the software from OUI still related files remain in your computer.

You first use oracle universal installer to de-install Oracle.

Click on the De-install Products button.

Now you can see it has been removed from program list.

Go to registers by typing ‘regedit’ in the run in the start menu.

Delete ‘HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE’ key.  This contains registry entries for all oracle product details.

Delete any reference to oracle in following paths in the registry.

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Ora*

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Application/Ora*

HKEY_CURRENT_USER/SOFTWARE/oracle

Control panel services still may show old services until you restart.            

Delete oracle home directory.   D:\oracle

Delete oracle directory from program files.    C:\Program Files\Oracle

Delete any reference to oracle in following path

Start->Settings->Control Panel->System->Advanced->Environment Variables

Now oracle has been totally removed from your system. 

Error on execution of Oracle setup…. Fail to run the Oracle Installation

I have the Oracle set up for oracle 10g to my desktop folder named “Database software setup”. I extract the zip file and try to run the “setup.exe”. Command window for “Starting oracle universal installer” have popped up. It was unable to continue the installation. Following error message popup and end the installation process.  (Figure 1)

“An unhandled win32 exception occurred in oui.exe [3020]”

Figure 1

I try several times to run the setup but it was fail. Then I just copy the setup to in to D:\ drive and try again.  This time it got executed without any issue.

Previously my “setup.exe” was in this path.

C:\Documents and Settings\chinthakas\Desktop\Oracle-10.2.0.1.0-WinNT-Base\10201_database_win32\database

Current path was:

  D:\database

The problem was my oracle installation setup containing folder path was too long......!

How to import data from one table to another table in different Databases

To import data from one table to another table in different database you have to create a database link.

Source:

Database: 192.168.0.16/GLOBAL

Schema: GLOBBASE

Table: GLOB_COUNTRY

Destination:

Database: 192.168.0.36/LOCAL

Schema: LOCALBASE

Table: LOCAL_COUNTRY

Connect to GLOBAL database and view data form GLOB_COUNTRY

select * from GLOB_COUNTRY;

CODE    NAME           gC_CODE

1       Afghanistan    af

2       Albania        al

3       Algeria        dz

10      Argentina      ar

14      Australia      au

15      Austria        at

16      Azerbaijan     az

19      Bahrain        bh

…

Connect to LOCAL database.

Create table same as GLOB_COUNTRY table.

CREATE TABLE LOCAL_COUNTRY

    (code                           FLOAT(126),

    name                           VARCHAR2(255),

    lc_code                         VARCHAR2(255))

/

Create database link from the LOCAL database to GLOBAL database.

create database link glocal_link connect to GLOBBASE identified by password using '192.168.0.16/GLOBAL';

Insert data using database link

insert into LOCAL_COUNTRY

select * from GLOB_COUNTRY@glocal_link;

commit;

Select data from new table

select * from LOCAL_COUNTRY;

CODE    NAME           lC_CODE

1       Afghanistan    af

2       Albania        al

3       Algeria        dz

10      Argentina      ar

14      Australia      au

15      Austria        at

16      Azerbaijan     az

19      Bahrain        bh

….

Data have been load to the LOCAL_COUNTRY table. 

Error on Installing oracle 10g patch in linux

After installing oracle 10g in Linux machine, I have install the patch Oracle-10.2.0.4.0.  The following error raised and could not continue.

This is the command I entered:
./runInstaller –silent –responseFile /home/oracle/10g-patch10205.rsp

Error Given: 

SEVERE:OUI-10029:You have specified a non-empty directory to install this product. It is recommended to specify either an empty or a non-existent directory. You may, however, choose to ignore this message if the directory contains Operating System generated files or subdirectories like lost+found.

You have to add the “force” command with the “./runInstaller “ command.

./runInstaller –silent –responseFile –force /home/oracle/10g-patch10205.rsp

Installation runs without any interruptions. 

Find list of all Procedures, Functions and Views in SQL server 2005

You can find a SP, Function or View definition by using following command.

Syntax:

     exec sp_helptext <object name>

Eg:

exec sp_helptext 'dbo.ADD_NEW'

Some instances we need to find list of Procedures, Functions and Views those exits in the database.  Following script will help you to resolve your problem.

Eg:

USE MUBASHER_PRICE;

GO

SELECT * FROM SYSobjects

 WHERE [type] = 'P'

   AND [NAME] IN ('ADD_NEWS','ADM_ADD_WS_SPLIT','ADM_ADDNEW_APP_FILES')

ORDER BY [name];

GO