Re: Default column value as Username

Er, as one with egg on his face for having said it couldn't be done (and one who has therefore learnt something from this thread), can I offer this?

SQL> connect scott/tiger
Connected.
SQL> create table test (
  2 col1 number,
  3 col2 varchar2(15) default (user));
Table created.

SQL> insert into test (col1) values (1); 1 row created.

SQL> commit;
Commit complete.

SQL> select * from test;

      COL1 COL2
---------- ---------------

         1 SCOTT SQL> connect system/manager
Connected.
SQL> insert into scott.test (col1) values (2); 1 row created.
SQL> commit;
Commit complete.

SQL> select * from scott.test;

      COL1 COL2
---------- ---------------

         1 SCOTT
         2 SYSTEM

This was done on 9i Release 2 (Windows), so maybe it's a version dependent thing. But yes, given the (possible) restriction on version, it does work and is quite a nice feature.

Regards
HJR "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message news:8f6utu8fnvhgqm1q42oqho4qsjqfu73im0_at_4ax.com...
> On 22 Nov 2002 17:00:43 -0800, rx20010101_at_yahoo.com (Rx) wrote:
>
> >Simply put "default (user)" after the column datatype definition.
> >
> >"Frank Ashley" <frank.ashley_at_ntlworld.com> wrote in message
news:<FGaD9.2615$0p6.411412_at_newsfep2-win.server.ntli.net>...
> >> I need to put the current user name as a column default. I can get the
> >> username from
> >> SELECT USERNAME FROM V$SESSION
> >> but how do I put this in the default clause?
> >>
> >> Frank
>
>
> You've obviously never tried this. I did. It doesn't work.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Sat Nov 23 2002 - 01:32:05 CST