OTN Discussion Forums : Things i wish SQL supported. The lazy ...

N Gasparotto

Posts: 19,247
Registered: 08/22/02

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 7:26 AM

in response to: shoblock

Reply

besides, the "!" is for unix. on windows and VMS it's "$"

Yeah, "host date" should work on both of them.

Nicolas.

RACER

Posts: 383
Registered: 10/12/07

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 7:29 AM

in response to: shoblock

Reply

ah... i see...

yeah, i moved over from sybase... and i guess i would say i'm relatively new to oracle (about a year)... by no means an expert.

but one of the first things that puzzled me was this dual dummy table.

things in that were easy to do in sybase, in oracle i'm required to use dual.
don't get me wrong, i love oracle and am learning so much just browsing the forum, but this dummy table still doesnt' make sense to me.

shoblock

Posts: 2,715
Registered: 06/22/06

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 7:35 AM

in response to: RACER

Reply

DUAL predates PL/SQL. back in the day, it was needed in programming for things like getting USER and SYSDATE (which can now be done as simple assignments - v_start_time := sysdate).

and in sqlforms v2 (character based screens, no popups or scroll bars, and no pl/sql) the only way to perform validation was to do a select and check whether any rows were returned. so to verify that field A was less than field B (form fields, not database columns), you did "select 1 from dual where :A < :B", and then had branching logic based on "found" vs "not found".

and DUAL was used in the data dictionary (and still is), and in v5, when you connected in sqlplus, a "select user from dual" was done internally by sqlplus.exe to verify that you actually connected.

shoblock

Posts: 2,715
Registered: 06/22/06

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 7:40 AM

in response to: N Gasparotto

Reply

Yeah, "host date" should work on both of them.

true, but "host date" isn't very useful on windows for seeing the time:

SQL> host date
The current date is: Fri 05/02/2008
Enter the new date: (mm-dd-yy)
 
SQL>

and it sits there waiting for me to enter a new machine date, or to hit return. the "/t" option avoid that, but you still need a different command for the local time (which doesn't include seconds)

SQL> host date /t
Fri 05/02/2008
 
SQL> host time /t
10:37 AM

and it still doesn't give me the machine time of the database server when connected remotely.

I think the VMS command is "show time", so it would be "host show time". yeah, "show sysdate" would be a nice addition to the sqlplus command set.

Sentinel

Posts: 1,167
Registered: 03/13/07

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 7:52 AM

in response to: shoblock

Reply

Yeah but if you want to actually see the time you need to make sure that your NLS Date Format includes the time portion otherwise you are just going to get the date which unless you are working with a system accross the international date line from you is going to give you the same date as your local system.

How about show systime[stamp] since the default NLS format for timestamps includes the time portion.

Vadim Tropashko

Posts: 505
Registered: 01/10/01

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:05 AM

in response to: RACER

Reply

would very much rather do:
select sysdate;

than
select sysdate from dual;

Agreed, the concept of "dual" table is unnecessary:

http://vadimtropashko.wordpress.com/2007/09/20/no-dual-required/

Brian Tkatch

Posts: 379
Registered: 11/29/07

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:14 AM

in response to: shoblock

Reply

SP2-0158: unknown SHOW option "SYSDATE"

you started this thread as a "wish list". so why can't I wish for "show sysdate"?

1) I didn't realize that was a wish. I thought you meant "why didn't you use..."
2) SHOW is already used for parameters. I think DB2's VALUES would be more apropos.

shoblock

Posts: 2,715
Registered: 06/22/06

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:15 AM

in response to: Sentinel

Reply

well, since it's all just a pipe dream anyway...

seeing as it would be a sqlplus command, it would internally query the database to get the time. it could get either sysdate or systimestamp (only difference would be fractions of seconds). and since the "show" command would be outputting the results, it wouldn't have to be bound by nls_date_format settings. it could be something similar to the DL and TS format models (which both rely on nls language and territory settings):

SQL> select to_char(sysdate,'DL TS') from dual;
 
TO_CHAR(SYSDATE,'DLTS')
-----------------------------------------
Friday, May 2, 2008 11:12:34 AM

or perhaps it could internally get systimestamp, and then it can also show the timezone info:

SQL> select to_char(systimestamp,'DL TS TZH') from dual;
 
TO_CHAR(SYSTIMESTAMP,'DLTSTZH')
---------------------------------------------
Friday, May 2, 2008 11:13:18 AM -4
 
1 row selected.

shoblock

Posts: 2,715
Registered: 06/22/06

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:18 AM

in response to: Brian Tkatch

Reply

2) SHOW is already used for parameters. I think DB2's VALUES would be more apropos.

USER is not a parameter

SQL> show user
USER is "MIKE"

and db2's VALUES might be more appropriate for people with a db2 background.

the heck with it. I just saved the sql I posted above to a file time.sql, and put it in one of the folders in my SQLPATH.

SQL> @time
 
TO_CHAR(SYSTIMESTAMP,'DLTSTZH')
---------------------------------------------
Friday, May 2, 2008 11:18:1 AM -4
 
1 row selected.

this wishing stuff isn't nearly as good as the "just doing it yourself" approach

SQL> alter session set nls_Territory=mexico;
 
Session altered.
 
SQL> alter session set nls_language=spanish;
 
Session altered.
 
SQL> @time
 
TO_CHAR(SYSTIMESTAMP,'DLTSTZH')
--------------------------------------------------
Viernes 2 de Mayo de 2008 11:20:25 AM -4
 
1 row selected.

Message was edited by:
shoblock
added mexican, in honor of upcoming Cinco De Mayo
(started drinking margaritas last night - real ones, not frozen)

Brian Tkatch

Posts: 379
Registered: 11/29/07

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:19 AM

in response to: Vadim Tropashko

Reply

would very much rather do:
select sysdate;
than
select sysdate from dual;

Agreed, the concept of "dual" table is unnecessary:

No! SELECT are for queries, and queries are on TABLEs.

This is one of those points where i think SQL Server has becomes a developer's tool rather than a a database.

DB2's answer was to use a new keyword called VALUES. Don't usurp SELECT for non-TABLE based queries.

Um, i don't meant to scream. I just feel strongly about this one.

Brian Tkatch

Posts: 379
Registered: 11/29/07

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:23 AM

in response to: shoblock

Reply

2) SHOW is already used for parameters. I think DB2's VALUES would be more apropos.

USER is not a parameter

SQL> show user
USER is "MIKE"

Point taken.

Perhaps because it is a static value for the session?

and db2's VALUES might be more appropriate for people with a db2 background.

I like it because it is a separate keyword. I actually don't like VALUES because it is used elsewhere. But it is a nice solution.

<snip>

this wishing stuff isn't nearly as good as the "just doing it yourself" approach

And that's why we love you.

shoblock

Posts: 2,715
Registered: 06/22/06

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:28 AM

in response to: Vadim Tropashko

Reply

Agreed, the concept of "dual" table is unnecessary:

yes, in your imaginary database language. but in the real world, we have FROM clauses.

which is not to say that dual isn't overused. it is. how much pl/sql is out there that does "select sysdate into v_start_time from dual;"? I've seen tons of it (or "tonnes" for all you brits) and it shouldn't be there. a simple assignment is all that's needed (v := sysdate).

but it still serves a purpose (one that was on the forum recently) -

insert into t ( c1, c2 )
select v1, v2 from dual
where not exists (
select null from t where c1 = v1
)

shoblock

Posts: 2,715
Registered: 06/22/06

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:30 AM

in response to: Brian Tkatch

Reply

And that's why we love you.

aww, you big lug...

go ahead and scream all you want, we don't mind

Vadim Tropashko

Posts: 505
Registered: 01/10/01

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:39 AM

in response to: Brian Tkatch

Reply

No! SELECT are for queries, and queries are on TABLEs.

It is rather deep question what queries really are. It goes far beyond what you may have learned from syntax diagrams in Oracle SQL Reference. Queries combine tables/views and predicates in rather sophisticated way. What I'm saying (in the accidentally deleted post) is that the conceptual difference between tables and predicates is not that big.

http://vadimtropashko.wordpress.com/2007/09/20/no-dual-required/
http://vadimtropashko.files.wordpress.com/2007/06/tables-vs-predicates.pdf

shoblock

Posts: 2,715
Registered: 06/22/06

Re: Things i wish SQL supported. The lazy man's list.
Posted: May 2, 2008 8:45 AM

in response to: Vadim Tropashko

Reply

conceptually, (using Venn diagrams) the tables/views are the circles, and the predicates define in what way the circles overlap. so I'd say that there's quite a big difference between tables and predicates. predicates act on the data sets. without the tables, there are no data sets.