GrassField

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!

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!!

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!

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.

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