Oracle vs. Postgres, the choice is obvious



  • 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 said:

    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 said:

    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 said:

    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 said:

    @roto said:

    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. 



    Tried on Oracle XE with the following program:

     

    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 said:

    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 said:

    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 said:

    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 sqlplus ... that's not comparing like with like. SQLPlus 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 said:

    @roto said:

    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 said:

    @roto said:

    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 said:

    @ammoQ said:
    @roto said:

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

    They are not "broken", but since SQLplus 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 SQLPlus.  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 said:

    @roto said:

    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 said:

    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 said:


    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 said:

    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 said:

    @ammoQ said:

    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.



  • @roto said:

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

    "long" is one of those examples where Oracle didn't make it right the first time, and when they fixed it, the better version got a new name, to maintain compatibility.

    Just like... VARCHAR2 (classic),  DBMS_SQL.DESC_TAB2 (there was a bug in DBMS_SQL.DESC_TAB) .



  • @ammoQ said:

    It's not the garbage collector.

    Good to know, since I don't want to go near Oracle just to try to produce this guy's problem.  Thanks! 



  • @roto said:

    @ammoQ said:

    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.

    To restate this correctly: postgres works more or less exactly the same way, and creating an auto-increment field just sets all that stuff up for you.



  • @seraphim said:

    "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?

    On 10g2 the size of a CLOB can range between 8 and 128TB

    http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#i43142 

    To the OP: It would have been nice if you'd read up some things in the user documentation before mouthing off, such as the text column argument.

    l.



  • @lofwyr said:

    To the OP: It would have been nice if you'd read up some things in the user documentation before mouthing off, such as the text column argument.

    l.

     

    I'm sorry, did my mouthing off offend you?  What would have been "nice" about it? 

     



  • @ammoQ said:

    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).

     

    I'm also using the thin driver, but I noticed that instead of Class.forName("oracle.jdbc.OracleDriver") you are using DriverManager.registerDriver(new oracle.jdbc.OracleDriver).  Do you think this makes a difference?  I will give it a try when I get to work.



  • @roto said:

    @lpope187 said:

    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.

    I don't see any conflict but then again I do have a tendency not to fully explain what I mean.  Oracle really excels at complex systems.  This has nothing to do with whether it is suitable only for certain people or not, but whether it is suitable for certain systems/requirements.  If the system isn't all that complex, then Oracle would most likely be overkill.  I haven't used Oracle's lighter version, so I can't comment on it.

    And in both posts I stated that learning Oracle isn't that difficult if you have a desire.  That's what I was going off about - what appeared to me as an unwillingness to learn new things.  By all means learn Oracle.  By doing so you'll know when it is appropriate to use and when it isn't.


     



  • @roto said:

    I'm also using the thin driver, but I noticed that instead of Class.forName("oracle.jdbc.OracleDriver") you are using DriverManager.registerDriver(new oracle.jdbc.OracleDriver).  Do you think this makes a difference?  I will give it a try when I get to work.

    Honestly, I can't say. I've been using "DriverManager.registerDriver(new oracle.jdbc.OracleDriver)" for nearly a decade now, probably having copied from a book in the first time. Since then, I simply do it, without asking myself "why?". Anyway, doing it like that means that I need the Oracle jdbc lib at compile time, which might be undesirable if you need to write db-agnostic code.

    You could try to use DriverManager.registerDriver((Driver) (Class.forName("oracle.jdbc.OracleDriver").newInstance())); to combine my version and yours.



  • @roto said:

     

    I'm sorry, did my mouthing off offend you?  What would have been "nice" about it?
     

    Me? No. It's always good to know if competition (you) is not able to read user documentation. Gives others, like me, an edge.

     
    l.
     



  • @lofwyr said:

    @seraphim said:

    "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?

    On 10g2 the size of a CLOB can range between 8 and 128TB

    http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#i43142 

    To the OP: It would have been nice if you'd read up some things in the user documentation before mouthing off, such as the text column argument.

    l.

    D'OH! I left off the important bit : 4 gigabytes (x DB_BLOCK_SIZE)

     Ok, so technically it's (4gb-1) x db_block_size ...

    Man, how embarrassing :(

     Of relevance to the OP : maximum number of clobs per table is essentially 1000, for a maximum of 128,000 terabytes or 125 petabytes (if we assume 1 petabyte = 1024 tb) worth of text in CLOBs. It's not still unlimited, but it's pretty good ...



  • @lofwyr said:

    @roto said:
     

    I'm sorry, did my mouthing off offend you?  What would have been "nice" about it?
     

    Me? No. It's always good to know if competition (you) is not able to read user documentation. Gives others, like me, an edge.

     
    l.
     

     

    That is very clever, but I'm sure I'm no competition for you.  You still didn't tell me what would have been nice about me not mouthing off.  You seem to enjoy attacking people about reading documentation, and pointing out how superior you are to them.  This is an internet forum, if I can't be loud mouth jackass here then where can I?  Why don't you take ammoQ's approach and show me how stupid I am rather than just making comments like that.



  • @roto said:

     

    That is very clever, but I'm sure I'm no competition for you.  You still didn't tell me what would have been nice about me not mouthing off. 

    I wrote: It would have been nice if you'd read up some things in the user documentation [b]before[/b] mouthing off.

    Seems to me your reading disability is not limited to user documentation, but since you asked me to show your inability.

     

    l. 

     



  • @lofwyr said:

    @roto said:
     

    That is very clever, but I'm sure I'm no competition for you.  You still didn't tell me what would have been nice about me not mouthing off. 

    I wrote: It would have been nice if you'd read up some things in the user documentation [b]before[/b] mouthing off.

    Seems to me your reading disability is not limited to user documentation, but since you asked me to show your inability.

     

    l. 

     

     

    That makes no sense.  You are a genius. 



  • @roto said:

     

    That makes no sense.  You are a genius. 

    You're obviously not.

    l.
     



  • @seraphim said:

    @lofwyr said:
    @seraphim said:

    "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?

    On 10g2 the size of a CLOB can range between 8 and 128TB

    http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#i43142 

    To the OP: It would have been nice if you'd read up some things in the user documentation before mouthing off, such as the text column argument.

    l.

    D'OH! I left off the important bit : 4 gigabytes (x DB_BLOCK_SIZE)

     Ok, so technically it's (4gb-1) x db_block_size ...

    Man, how embarrassing :(

     Of relevance to the OP : maximum number of clobs per table is essentially 1000, for a maximum of 128,000 terabytes or 125 petabytes (if we assume 1 petabyte = 1024 tb) worth of text in CLOBs. It's not still unlimited, but it's pretty good ...

    I use a database that supports 14 exobytes in one field. so NYAH.



  • Database system

    +

     

    Before anyone asks.



  • @Angstrom said:

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

    SQLPlus 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.

     

    No...why would Oracle write a test editor when they let you use your own:

    edit <- uses your environment dependant text editor (notepad for windows, vi for unx, unless you have these changed)



  • @GeneWitch said:

    Database system

    +

     

    Before anyone asks.

    Don't you mean: 

     

    Database system

    <font size="6">*</font>

     



  • @rewind said:

    No...why would Oracle write a test editor when they let you use your own:

    edit <- uses your environment dependant text editor (notepad for windows, vi for unx, unless you have these changed)

    Actually, sqlplus includes an editor, though it's a bit like ed (Unix/Linux) or edlin (DOS)...

     SQL> select 22
      2  from dual;

           2
    2
    ----------
             4

    SQL> 2
      2* from dual
    SQL> c/dual/emp
      2* from emp
    SQL> l
      1  select 22
      2
    from emp
    SQL> 1
      1* select 22
    SQL> c/2/3
      1
    select 32
    SQL> l
      1  select 3
    2
      2* from emp
    SQL>



  • @ammoQ said:

    @rewind said:

    No...why would Oracle write a test editor when they let you use your own:

    edit <- uses your environment dependant text editor (notepad for windows, vi for unx, unless you have these changed)

    Actually, sqlplus includes an editor, though it's a bit like ed (Unix/Linux) or edlin (DOS)...

     SQL> select 22
      2  from dual;

           2
    2
    ----------
             4

    SQL> 2
      2* from dual
    SQL> c/dual/emp
      2* from emp
    SQL> l
      1  select 22
      2
    from emp
    SQL> 1
      1* select 22
    SQL> c/2/3
      1
    select 32
    SQL> l
      1  select 3
    2
      2* from emp
    SQL>

    Well I do know that SQLPlus does have the direct editing abilities, but the original (unfounded) gripe was that it had no proper text editing features, and my only point was if the plain old way (every thing on 1 line) or enhanced (as you have shown) was unsatisfactory, just use your own, as they let you do that too. Not that he could operate VI anymore than anything else if he didn't know the terminal was responsible for for the backspace not working.



  • Which is exactly why whenever I work with Oracle, Toad is a requirement.  Even though the interface still sucks, at least it is usable.  BTW, are Oracle 10g's tools any better now?

     



  • @lpope187 said:

    Which is exactly why whenever I work with Oracle, Toad is a requirement.  Even though the interface still sucks, at least it is usable.  BTW, are Oracle 10g's tools any better now?

     

    Two different nice Oracle tools...one simple one it the handy Application Express, which is not much good for anything but doing stuff specifically related to setting up an ApEx program, or SQLDeveloper, which I have been fine to stop using Toad once this hit version 1.0. DBA features aren't there yet, so if you use those in Toad you'll be hesitant to let it go...but as far as query access, pl/sql debug, object manipulation, it is darn good.
     



  • @rewind said:

    Not that he could operate VI anymore than anything else if he didn't know the terminal was responsible for for the backspace not working.

    Not exactly true... in vi, you don't really need backspace, cursor keys etc. Just use H, J, K, L in command mode for cursor movement, X for deleting a character etc.



  • @ammoQ said:

    @rewind said:

    Not that he could operate VI anymore than anything else if he didn't know the terminal was responsible for for the backspace not working.

    Not exactly true... in vi, you don't really need backspace, cursor keys etc. Just use H, J, K, L in command mode for cursor movement, X for deleting a character etc.

    I was actually refering to user's functional ability, not the terminal's.



  • @ammoQ said:

    You could try to use DriverManager.registerDriver((Driver) (Class.forName("oracle.jdbc.OracleDriver").newInstance())); to combine my version and yours.

     

    Doesn't make a difference, the only thing that works is setting statements, resultsets and connections to null.  Going to try hibernate.

     @ammoQ said:

    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.

     

     

    Moved long data types to clob. Hibernate maps clob to java.sql.Clob, not String.  There are ways around this but they require creating my own hibernate types.  Once again, I have to write extra code to accommodate Oracle.

     



  • I didn't notice anybody else on the thread reply to your post, so I'll take a stab at it. Your analysis looks good from a technical viewpoint, but the way a business guy would look at it, there are other reasons to use Oracle for small databases. If one of them made a list of reasons in order of importance, it might look like this:

    1. Oracle is the only choice for high volume, high performance systems (OK there's DB2 as well), and managers usually multiply the actual requirement by their own level of anxiety.
    2. You never get fired for buying Oracle. That's the reason most software of any kind is bought, come to think of it.
    3. Economy of scale brings the price down. If Oracle is the company standard, you can get resources of all kinds more easily. 
    4. Odds are that if your database needs to talk to another database, the other one is in Oracle. That's not a big deal to techies, but non-technical people always overestimate the scale of the problem.
    5. You can find a thousand Oracle DBA's for every other DBMS, and developers are easy to find as well. Ditto for training and sales guys.

     Note that most of these factors don't make a lot of technical or economic sense, but what they all have in common is mitigate risk, especially the personal risk to the decision-makers. That's OK with me because these same people give me lots of money too, but it's a shame that many techies blame Oracle for it. That's throwing the baby out with the bathwater.

     



  • Roto,

     

    Good post. I've used Oracle, SQL Server, Teradata, MySQL, PostgreSQL and even Access. From the programmer's viewpoint MySQL is the easiest and then maybe PostgreSQL or Access. Then SQL Server. I remember the days when Teradata was required for any databse taking terabytes. Today Oracle, MySQL, and PostgreSQL do just fine. You and I both know the proper use of sequences is the #2 performance issue. The #1 is bad software design. Jumping on a network for every data lookup shows real inexperience with computer software design but is the common practice for web-based system. Caching is often a performance optimization.

    My thought is a synchronized read database (MySQL supports such a config) with a db copy on every machine would provide much better performance.

     I know alot of people love Oracle. I hated having to move to Java from C++ too. But, again, MySQl is by far the easiest for programmers with the most documentation, support groups, easiest to port a database etc. It's easy to over-glorify one's reason for existence. RDBMS vendors have been doing this for 1-2 decades. Face it, Codd's rules are close to 30 years old.
     



  • BTW, you're correc ton the connection issue. It must be addressed int eh appserver connection pool timing and also in the oracle connections. TOAD is particularly bad about it as I remember it takes 3 connections or something. I use SQuirreL SQL Client in general because it is RDBMS and OS agnostic. Get work done rather than futz with vendor abnormalities.

     That said, I talked even today with an Oracle expert who claims MySQL has deficiencies such as no continuous rollback support (continuously making savepoints), only one sequence per table, and only one FK per table. Oracle folks do not realize Oracle and other RDBMS's historically had to recreate a filesystem and other OS services for portability and performance reasons. Things like a logging filesystem et al are now built into industrial strength OS's like Linux. The tuning in Open Source databases also is more dependent on understanding the OS tuning capabilities rather than RDBMS-specific settings.
     



  • The problem roto mentioned is a very common issue with Java applications accessing Oracle databases, ie the connections pile up and don't get closed when an application closes.  They just sit there as 'inactive'.  Maybe you're a better programmer than everyone else, but this occurs frequently.  

     About the sequence issue: Sybase has an 'identity' column; MySQL has an 'auto_increment' option, but Oracle only has a sequence.   The sequence by itself does not in any way duplicate the identity/auto_increment featurs of the two previously mentioned databae engines.  YOU actually have to create a trigger (a procedure) on a table to use the sequence for an auto_increment feature.  That and sqlldr were 2 of the most annoying 'features' I had to learn when I started with Oracle.  They are second-nature now, but these are much more difficult to learn and use than they need to be.  

     sqlplus: SUCKS!  I use gqlplus; it has a recall command buffer and some editing capability.  However, sqldeveloper is better (I cannot recommend Tora due to screen and data corruption issues I ran into).  But I think many Oracle developers us 'TOAD'.  Haven't used it myself, but it sounds like sqldeveloper.  

    jwhite

     


Log in to reply