I'm a programmer, not a dba.  Of course I know how to setup a database system create my users set my permissions and create my schema.  I know the basics of a relational database system: SQL, triggers, stored procedures, etc.  For years I've used Postgres and MySQL with no problems.  I've enjoyed luxuries like auto incrementing numeric fields, more than one unlimited text field in a table, not being required to specify the data file on the file system and decide how big I want it to be, using a client app that supports things like backspace, moving the cursor and history (sqlplus doesn't work very well on linux or solaris).  These small things I had to kiss goodbye when I started working at my current job, because here we use Oracle.  I've had the pleasure of discovering that when you close a jdbc statement, result set or connection, it doesn't actually close.  Instead it becomes "inactive" and eventually your database will stop accepting connections.  I know that this can be solved by changing some default settings, but like I said, I am a programmer not a dba.

I'm not trying to rag on Oracle here, although I think they could work harder to make their product more convienient, I realize that this would mean lots of money in training courses, and consultants out the window.  The point I am trying to make is Oracle only makes sense for those huge shops dealing with millions of hits a month and who employ at least one full time Oracle dba.  Where I work, we have managers who have decided that we need to use Oracle, but don't understand the difference between Oracle, Postgres, MySQL, their ass,...   And then who ends up spending their time administering the Oracle servers and not programming?  Me!  So maybe I am a dba and not a programmer, maybe I need a new job.

 

Backspace not working is not the fault of SQL*Plus. Setup your terminal window correctly.

Anyway, if you need an interactive tool on Linux or Solaris, use SQL developer (free) from Oracle. Or Tora (free).

About that JDBC thingy... it that was true, my phone would hardly ever stop ringing. Bullshit.

Of course you have to explicitely close your statements and connections, don't rely on the garbage collector.
 

I don't rely on the garbage collector, as I stated in my original post, "close a statement, result set or connection"  not "let the garbage collector take care of my objects".  Do this for me:  install Oracle (express or enterprise), don't change the default settings, create a table or two, create a Java class that opens a connection, opens and closes a couple statements and result sets, closes the connection, go to enterprise manager and view sessions.  There will be an inactive session for each statement you created even though you closed them.  Do a quick google search, I'm not the only person to encounter this problem.  Apparently using the Oracle driver manager, instead of the java.sql.DriverManager will fix this problem.  If you do that, congratulations your code is no longer db independent.  Also setting the object to null will fix the problem.  Great now all my code that worked fine on Postgres or MySQL, needs to be updated to work with Oracle. 

Why would I download one of those other tools, when sqlplus is included with the install.  My terminal is default  gnome-terminal settings, I don't know what your idea of "correct" is, please enlighten me.  My "incorrect" settings work just fine with mysql and psql.

Funny how you didn't have a response to the lack of auto incrementing numeric fields, or more than one unlimited text field per table.  Those are the biggest WTFs.

The point I'm trying to make here is that Oracle is a pain in the ass, not impossible.  I don't have to change how I do things with Postgres or MySQL.

Ceci n'est un helpdesk, but I'm curious.  Does backspace work properly when running nslookup(1) with no arguments under gnome-terminal?  I'm betting not.

gnome-terminal, as of last time I used it in FC4, claims to be an xterm (through the TERM environment variable) but it does some non-xterm-y things.  It *is* actually broken, but GNU Readline (which both mysql and psql use, along with bash itself) covers the problem.  Try setting term to 'gnome' (export TERM=gnome) to see if that fixes the problem; if so, don't make that part of your login or profile configurations but do change the compatability settings in gnome-terminal to something more sensible.
 

Angstrom:

Ceci n'est un helpdesk, but I'm curious.  Does backspace work properly when running nslookup(1) with no arguments under gnome-terminal?  I'm betting not.

gnome-terminal, as of last time I used it in FC4, claims to be an xterm (through the TERM environment variable) but it does some non-xterm-y things.  It *is* actually broken, but GNU Readline (which both mysql and psql use, along with bash itself) covers the problem.  Try setting term to 'gnome' (export TERM=gnome) to see if that fixes the problem; if so, don't make that part of your login or profile configurations but do change the compatability settings in gnome-terminal to something more sensible.
 

That fixes the backspace, but using the arrow keys, home, end and delete keys are still broken. 

roto:

install Oracle (express or enterprise), don't change the default settings, create a table or two, create a Java class that opens a connection, opens and closes a couple statements and result sets, closes the connection, go to enterprise manager and view sessions. 

I forgot to mention keep the jvm running, like a web application or something running in a container. 

roto:

That fixes the backspace, but using the arrow keys, home, end and delete keys are still broken. 

They are not "broken", but since SQL*plus does not use readline, it makes no use of those keys. 

roto:

roto:

install Oracle (express or enterprise), don't change the default settings, create a table or two, create a Java class that opens a connection, opens and closes a couple statements and result sets, closes the connection, go to enterprise manager and view sessions. 

I forgot to mention keep the jvm running, like a web application or something running in a container. 

 

import java.sql.*;

public class BullShit {
        public static Connection createConnection(String dbHost, int dbPort, String dbService, String dbUsername, String dbPassword) throws SQLException {
                Connection conn = DriverManager.getConnection ( "jdbc:oracle:thin:@//"+dbHost+":"+dbPort+"/"+dbService, dbUsername, dbPassword );

                return conn;
        }

        public static void main(String[] args) {
                try {
                        DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
                        for (int i=0; i<500; i++) {
                                System.out.println("Iteration "+i+" ");
                                Connection conn = createConnection(args[0], Integer.parseInt(args[1]), args[2], args[3], args[4]);
                                for (int j=0; j<100; j++) {
                                        System.out.print('.');
                                        Statement stmt = conn.createStatement();
                                        ResultSet rset = stmt.executeQuery("select sysdate from dual");
                                        while (rset.next()) {
                                                // interesting stuff here
                                        }
                                        rset.close();
                                        stmt.close();
                                }
                                conn.close();
                                System.out.println();
                        }
                }
                catch (SQLException e) {
                        System.err.println(e.toString());
                }

                System.out.println("press any key...");
                try {
                        System.in.read();
                }
                catch (java.io.IOException couldnotcareless) {}
        }
}

Can't reproduce the behaviour your talk about...  ( of course I looked into v$session before I pressed the any key, so java is still running)

About the auto-incement fields: Oracle uses the so-called SEQUENCE objects to generate unique numbers. Together with a before-insert trigger, it's not difficult to make a field behave like an auto-increment field. Of course it's a bit tedious, but on the other hand, you gain flexibility.
 

roto:

Funny how you didn't have a response to the lack of auto incrementing numeric fields, or more than one unlimited text field per table.  Those are the biggest WTFs.

Auto incrementing fields.. see previous post.

more than one unlimited text field per table... don't know what you are talking about. Bullshit.

 SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 16 02:19:46 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create table bullshit (bull clob, shit clob);

Table created.

That fixes the backspace, but using the arrow keys, home, end and delete keys are still broken.

SQL*Plus is probably using stdio directly, in which case there is no command history or line editing features to use in the first place.  Limitation, not bug.

roto:

I don't have to change how I do things with Postgres or MySQL.

That's the real crux of the matter.  Prior to my current position, my only experience with Oracle was reading data from it to populate a data warehouse.  But at my current position, I'm responsible for maintaining 5 separate instances of Oracle and I had to learn how to do it. And it really wasn't much of a pain in the ass either.  Took me all of a week to figure out the intricacies of Oracle compared to my experiences with DB2, MsSQL, and MySQL.
 

If you want to go through life never trying something new or expanding your horizons, you are in the wrong field. 

roto:

Why would I download one of those other tools, when sqlplus is included with the install.  My terminal is default  gnome-terminal settings, I don't know what your idea of "correct" is, please enlighten me.  My "incorrect" settings work just fine with mysql and psql.

Funny how you didn't have a response to the lack of auto incrementing numeric fields, or more than one unlimited text field per table.  Those are the biggest WTFs.

The point I'm trying to make here is that Oracle is a pain in the ass, not impossible.  I don't have to change how I do things with Postgres or MySQL.

I don't know anything about Java, so can't remark upon that.

Auto incrementing numeric fields? Sequence. You'll need to combine with a trigger, true, for an "auto incrementing" field, but since sequences aren't limited in use to a single table they're phenomenally useful.

"Unlimited" text field per table? Don't follow you. The CLOB datatype allows text of up to 4 gigabytes per column, and you can certainly have more than one CLOB in a table. Could you give an example of the problem you're hitting?

 As to why you'd want to use SQL Developer when you've got sql*plus ... that's not comparing like with like. SQL*Plus is a CLI-based client, SQL Developer is a full GUI client aimed primarily at developers rather than end users, but can still be used as a general query tool.

Given that ammoQ's example doesn't show the problem, but google suggests it definitely exists, and given that nulling the connection reference apparently fixes it, I wonder if Oracle's implementation of java.sql.Connection has a finalize method that does some cleanup that close doesn't do.  It would be unlikely, but not impossible, for the garbage collector to have decided the connection is finalizable as soon as ammoQ's first try{} completes.

I didn't see anything on the Oracle site to that effect, but I wasn't able to find any actual javadoc, so who knows.  I'm not about to download the driver and run it through jad to see what it's doing; I'm not getting paid enough to read Oracle's code.

ammoQ:

roto:

That fixes the backspace, but using the arrow keys, home, end and delete keys are still broken. 

They are not "broken", but since SQL*plus does not use readline, it makes no use of those keys. 

Those keys work fine on windows. 

ammoQ:

roto:

Funny how you didn't have a response to the lack of auto incrementing numeric fields, or more than one unlimited text field per table.  Those are the biggest WTFs.

Auto incrementing fields.. see previous post.

more than one unlimited text field per table... don't know what you are talking about. Bullshit.

 SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 16 02:19:46 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create table bullshit (bull clob, shit clob);

Table created.

I've been using long, and reading the docs Oracle recommends replacing long with lob types.  Thanks for the tip :)
 

roto:

ammoQ:

roto:

That fixes the backspace, but using the arrow keys, home, end and delete keys are still broken. 

They are not "broken", but since SQL*plus does not use readline, it makes no use of those keys. 

 
Those keys work fine on windows. 

That's a feature of Windows console applications, not a feature of SQL*Plus.  Try it by running "type CON" in cmd.exe (use ctrl-Z to exit).  The 'type' command doesn't maintain its own command history. 

lpope187:

roto:

I don't have to change how I do things with Postgres or MySQL.

That's the real crux of the matter.  Prior to my current position, my only experience with Oracle was reading data from it to populate a data warehouse.  But at my current position, I'm responsible for maintaining 5 separate instances of Oracle and I had to learn how to do it. And it really wasn't much of a pain in the ass either.  Took me all of a week to figure out the intricacies of Oracle compared to my experiences with DB2, MsSQL, and MySQL.
 

If you want to go through life never trying something new or expanding your horizons, you are in the wrong field. 

 

Its not that I don't want to learn something new.  All the time I spend dealing with issues (whether they are my fault or not) is less time I spend learning a new development framework or improving my code.  Like I said, I'm a programmer not a dba.  Most shops have at least one full time Oracle dba, and actually need the advanced features of Oracle, thats not us. 

ammoQ:

About the auto-incement fields: Oracle uses the so-called SEQUENCE objects to generate unique numbers. Together with a before-insert trigger, it's not difficult to make a field behave like an auto-increment field. Of course it's a bit tedious, but on the other hand, you gain flexibility.
 

 

You don't gain flexibility.  This is no more flexible than Postgres, just lacking the convenience.

lpope187:

If you want to go through life never trying something new or expanding your horizons, you are in the wrong field. 

 

Your response surprises me, because in this post you are saying that people like me shouldn't use Oracle, and confirming that it is too complicated for people like me who don't need it but are forced to use it.

Angstrom:

Given that ammoQ's example doesn't show the problem, but google suggests it definitely exists, and given that nulling the connection reference apparently fixes it, I wonder if Oracle's implementation of java.sql.Connection has a finalize method that does some cleanup that close doesn't do.  It would be unlikely, but not impossible, for the garbage collector to have decided the connection is finalizable as soon as ammoQ's first try{} completes.

I didn't see anything on the Oracle site to that effect, but I wasn't able to find any actual javadoc, so who knows.  I'm not about to download the driver and run it through jad to see what it's doing; I'm not getting paid enough to read Oracle's code.

It's not the garbage collector. I've changed the example a bit:

import java.sql.*;

public class BullShit {
        public static Connection createConnection(String dbHost, int dbPort, String dbService, String dbUsername, String dbPassword) throws SQLException {
                Connection conn = DriverManager.getConnection ( "jdbc:oracle:thin:@//"+dbHost+":"+dbPort+"/"+dbService, dbUsername, dbPassword );

                return conn;
        }

        public static void main(String[] args) {
                Connection[] conn = new Connection[500];

                try {
                        DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
                        for (int i=0; i<500; i++) {
                                System.out.println("Iteration "+i+" ");
                                conn[i] = createConnection(args[0], Integer.parseInt(args[1]), args[2], args[3], args[4]);
                                for (int j=0; j<100; j++) {
                                        System.out.print('.');
                                        Statement stmt = conn[i].createStatement();
                                        ResultSet rset = stmt.executeQuery("select sysdate from dual");
                                        while (rset.next()) {
                                                // interesting stuff here
                                        }
                                        rset.close();
                                        stmt.close();
                                }
                                conn[i].close();
                                System.out.println();
                        }
                }
                catch (SQLException e) {
                        System.err.println(e.toString());
                }

                System.out.println("press any key...");
                try {
                        System.in.read();
                }
                catch (java.io.IOException couldnotcareless) {}
        }
}

Instead of reusing the same conn variable (which allows Java to garbage collect the closed connection), it now uses an array of 500 Connections, which are opened, used and closed. Still no problem. (Now tested against a Database 10g Enterprise Edition Release 10.2.0.1.0). Maybe it depends on which JDBC driver is used, I always use the thin driver (ojdbc14.jar).

roto:

ammoQ:

About the auto-incement fields: Oracle uses the so-called SEQUENCE objects to generate unique numbers. Together with a before-insert trigger, it's not difficult to make a field behave like an auto-increment field. Of course it's a bit tedious, but on the other hand, you gain flexibility.
 

 

 

You don't gain flexibility.  This is no more flexible than Postgres, just lacking the convenience.

Sequences allow me to have two databases, one creating the IDs 1-1000000 and the othere one 1000001-2000000, so that the data can easily be merged without conflicting keys. Or one creates even IDs and the other one odd ones. If I use one sequence for all tables, IDs are unique over all tables, which might be usefull to avoid the possibility that an incorrect join over the ID ever gives an result. If I fell like doing that, I could use IDs 2007000001-2007999999 this year, and 2008000001-2008999999 next year, so the ID tells me the creation year.

That all said, most of my sequences are just replacements for autoincrement fields, going up from 1 step 1.