Installing Oracle 10g Express edition in Mandriva 2009

Hi,

This is to share the screenshot of the oracle installation in my mandriva 2009 (Free). The installation is very simple. Download the oracle installation jar from oracle.com. It has only one dependency, considering mandriva 2009, which is libaio.jar. See whether it is coming with Mandriva, or else download from the web. thats it. then install the RPMs.

Once RPMs are installed, database need to be configured. Issue the following command to configure the database.

/etc/init.d/oracle-xe configure

This will take you to a text wizard asking the system username, password etc and complete your installation process. See the screenshot below.

Thats all!

Try to login to your oracle server with the web interface
http://localhost:8080/apex
ofcourse you have the option to change the port also.

happy dbing!

Oracle listener configuration after changing machine name

I was not able to start my oracle instance today. The start and stop server scripts dint show any error, instead they said server started/stopped without any problem. Windows System event log had a error entry saying “The OracleXETNSListener service terminated unexpectedly. It has done this 8 time(s).”when I run lsnrctl start I found the issue.D:Documents and Settingspandian> lsnrctl startLSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 23-NOV-2008 01:08:40Copyright (c) 1991, 2005, Oracle. All rights reserved.Starting tnslsnr: please wait...TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - ProductionSystem parameter file is D:oraclexeapporacleproduct10.2.0servernetworkadminlistener.oraLog messages written to D:oraclexeapporacleproduct10.2.0servernetworkloglistener.logListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.pipeEXTPROC_FOR_XEipc)))Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=acs-59abc34f442)(PORT=1521)))TNS-12545: Connect failed because target host or object does not existTNS-12560: TNS:protocol adapter errorTNS-00515: Connect failed because target host or object does not exist32-bit Windows Error: 1001: Unknown errorListener failed to start. See the error message(s) above...There is the issue. I renamed my host name. So listener is not able to start. I edited the listener.ora found at oraclexeapporacleproduct10.2.0serverNETWORKADMIN folder. I changed the host to localhost. It is working now! 🙂

Oracle 10g Express edition

DB legends are releasing their edition as a miniature, previously MS-SQL came with their own mini server. MySQL is always small doing great jobs. and Here I am giving Oracle 10g Express edition. It is cool, very easy to install and get it running. Add to its great things, It offers a cool web interface (see the screenshot below), like Linux admin tools :). Oracle says it is free to develop, deploy, and distribute. Those who are interested can get their copies at

http://www.oracle.com/technology/products/database/xe/index.html

auto increment in oracle

This post is regarding auto increment in oracle. Usually I used to add AUTO_INCREMENT flag in mysql. But it is not a single word task oracle. we need to create sequence and trigger to do the same. please see the following examples 1. Here is my table structure.

CREATE TABLE FEED_DETAIL
(
FEED_ID NUMBER(6, 0),
FEED_TEXT VARCHAR2(128 BYTE),
FEED_TITLE VARCHAR2(128 BYTE),
FEED_TYPE VARCHAR2(12 BYTE),
XML_URL VARCHAR2(128 BYTE),
HTML_URL VARCHAR2(128 BYTE)
)
2. lets play with FEED_ID now. lets make it auto increment. create a sequence now

create sequence FEED_DETAIL_SEQUENCE
start with 1
increment by 1
nomaxvalue;

A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

3. Now create a trigger. A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

create or replace trigger FEED_DETAIL_TRIGGER
before insert on FEED_DETAIL
for each row
begin
select FEED_DETAIL_SEQUENCE.nextval into :new.feed_id from dual;
end;

Now insert the values

insert into feed_detail (feed_text, feed_title, feed_type, xml_url, html_url) values('a', 'b', 'c', 'd', 'e');
insert into feed_detail (feed_text, feed_title, feed_type, xml_url, html_url) values('a', 'b', 'c', 'd', 'e');

Enjoy auto incrementing column now!

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

As I have told you in my previous post, I was trying to read from a table within a loop. I am opening a new connection, new statement and firing the query to get the resultset. It was working fine for a few records, finally it dies with this exception

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Alas, I forgot to close the resultset!!

ORA-12519, TNS:no appropriate service handler found

I dint write for two weeks. I could not sit in these weekends. so, there are much more error messages left behind my desk. here is the situation. I am doing multiple reads from database. I am opening a connection, creating a statement, and executing a query to get resultset. it was working fine, but when loop goes beyond 100, I got the following error.

java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
152.200.100.5:1521:PANDIAN

I think, the problem is with connection leak. connection or resultset somethign is not closed properly. I removed the connection opening part and replaced with connection pool. Now it is working fine!

java.lang.UnsatisfiedLinkError: no ojdbc14 in java.library.path

This month I am logging only the errors 😦 I am setting up a backup of a working application, where I came across the previous errors. Here is another one.

java.lang.UnsatisfiedLinkError: no ojdbc14 in java.library.path

Let me tell you the environment,

I have the application, whose lib folder contains class12.jar and ojdbc14.jar. Both the jars are in classpath, then I replaced this ojdbc14.jar of the application with the ojdbc14.jar from the local oracle installation folder. That doesnt solve the issue, later i need to replace classes12.jar also. That application lib folder files are working fine for Oracle 9i. but not for 10g. So, this is basically a version incompatibility.

SQL Loader in Oracle

Previously When I used blogger, I have written something on loading raw files into MySQL Database. This is a similar post, but with Oracle. I am trying to upload a CSV to Oracle Database. Here are the various steps involved in it.1. prepare a .CTL (control) file
Control file is one which tells the SQL Loader how to upload the file. A sample CTL file contains the following lines

load data
infile 'c:StatCounter-Log-3329852-modified.csv'
into table statcounter
fields terminated by "," optionally enclosed by '"'
(Date_and_Time,IP_Address,IP_Address_Label,Browser,Version,OS,Resolution,Returning_Count,Page_URL,Page_Title,Came_From,SE_Host,SE_Name,SE_Term)

It tells from which file, to which table, to which fields i am uploading the data.

2. execute sqlloader with the specified CTL file

sqlldr system/manager control=statcounter.ctl

C:>sqlldr system/manager control=statcounter.ctl
SQL*Loader: Release 9.0.1.1.1 - Production on Sun Jan 20 03:19:55 2008
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 63

Thats all!

But I have a problem with this, Though it says 63 records have been uploaded. I am seeing only 10+ records. I dint get any errors also. I am thinking why 😦

ORA-01830: date format picture ends before converting entire input string

This is another real-time mistake I faced today. I was parsing a log file and uploading into DB. One of the dates in the log file doesnt follow the date format. So I got this error. I cant give that code in this post, I am giving an example below.

SQL> select to_date('20051101 00:00:01', 'YYYYMMDD') from dual ;
select to_date('20051101 00:00:01', 'YYYYMMDD') from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

I hope you have noticed the error. The query should be like this.

SQL> select to_date('20051101 00:00:01', 'YYYYMMDD hh24:mi:ss') from dual ;

TO_DATE('
---------
01-NOV-05

You can have a detailed view on the Oracle date formats here
http://www.ss64.com/orasyntax/fmt.html

ORA-24381: error(s) in array DML

I cant explain this problem better than the following URL.

http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Bulk_Binding_Enhancements/Handling_And_Reporting_Exceptions.htm

I am using a PL/SQL array object to populate a table. The exceptions are being saved, so that the row wise problems can be logged and the process cannot be stopped because of that problem or exception. While updating the columns, there was an exception while parsing a string as date, – I hit with this issue. (I disable exception save and find out this 🙂 ), When I rectfied that date parsing issue, I came out of this problem!

Are you asking about that date problem, I will be writing a separate post tomorrow! feeling sleepy now!