Flutterby™! : SQLminus

Next unread comment / Catchup all unread comments User Account Info | Logout | XML/Pilot/etc versions | Long version (with comments) | Weblog archives | Site Map | | Browse Topics


2012-10-05 14:48:43.127073+00 by meuon 6 comments

I'm trying to disassemble an Oracle system so I can suck out some data. A couple hours with SQLplus (server is far far away and my only access is SSH into a Linux box) and I'd like to kill something. It's like the dark ages of databases again. It (Oracle 11g and SQLplus) seem to be missing commands and features (or similar features) that are ANSI SQL standards and well supported in PostgreSQL and MySQL.

I'd kill for: "limit 10" or equivalent when poking at tables I don't (yet) know much about.

At least: desc $tablename works.

[ related topics: Free Software Star Wars Open Source Cryptography Databases Furniture ]

comments in ascending chronological order (reverse):

#Comment Re: made: 2012-10-05 16:26:55.689258+00 by: other_todd

There's a hidden ROWNUM in SQLPlus which you can use like other flavors SQL use LIMIT:

SELECT * FROM some_table WHERE ROWNUM < 20

gets you the first 20 rows of the result.

SQLPlus likely isn't missing anything you actually need, it just does it a little differently from everybody else.

(Any other urgent SQLPlus needs? I work in it fairly heavily because it's so freakin' expensive to run Toad everywhere and Oracle's own GUI SQL/DBA client sucks.)

#Comment Re: made: 2012-10-05 16:31:42.104006+00 by: other_todd

P.S. It amuses me that you're saying "At least DESC(ribe) works" when that was the very first thing I stumbled over the lack of when switching from Oracle-style SQL to other flavors.

Microsoft SQL wonks, in particular, will get all testy with you when you ask them why the hell their SQL doesn't have DESCRIBE and will bend your ear for hours on how it's non-standard and for lazy people and you should query the data dictionary for hours with a spoon the hard way by god like the REAL data miners do and so on and so on.

#Comment Re: made: 2012-10-05 17:04:12.625393+00 by: Dan Lyke

I think many of us who have primarily used databases like PostgreSQL and MySQL are shocked and amazed at the holes in major commercial databases like Oracle and Microsoft SQL...

#Comment Re: made: 2012-10-05 17:35:10.566852+00 by: other_todd

I dunno about holes. They all do things a little differently is all. I like MySQL just fine, but it's definitely got quirks of its own, some lovable, some not. The only one I've really had a gripe with is Microsoft's - in the traditional imperious Microsoft way, they decided they were going to be hardcore about ANSI SQL and not give the user a break. Thus you cannot do multiple joins using only keymatching, but have to explicitly spell out your joins; there is no TO_CHAR or TO_DATE to make your life simpler, you must use only CAST and CONVERT; so on and so forth. (That last is a sore point to me because I spend a lot of time doing date-to-char and char-to-date conversions/formatting.)

It definitely feels like "We know better than you do, so suck it up." Of course, some people claim they get that sort of patronizing vibe from Oracle too.

Oracle people seem to often feel that MySQL is a little lax and low-rent, but there's a couple of things you can do in MySQL easily that are very hard to do in Oracle, so I disregard that.

#Comment Re: made: 2012-10-05 18:07:19.894167+00 by: Dan Lyke

I think pretty much everybody but MySQL users looks down on MySQL, and the MySQL users say "yeah, yeah, whatever, think what you want, how long was that going to take to deliver again...?"

Heck, even I'm pretty down on MySQL, but when you've gotta get something running now it just works.

#Comment Re: made: 2012-10-07 14:00:20.732769+00 by: meuon [edit history]

Other_todd. I'll try the rownum bit just as soon as I have access again. Thanks. There are some much more convoluted ways that I can't seem to get to work, and the connection isn't the kind of thing you wnat to watch 3 million records scroll by for.