Home » SQL & PL/SQL » SQL & PL/SQL » How to select Default value from column ?
How to select Default value from column ? [message #188107] Thu, 17 August 2006 02:45 Go to next message
anjaw
Messages: 1
Registered: August 2006
Location: Germany
Junior Member

Hi all,

i'm a newbie in Oracle, PlSQL, and i'm working with HTMLDB right now.

i'm updating my table-columns with default values, i'm using "alter table...."
but now i have problems with selecting these default value !

i use
"select default(columname) into P1_test from table"
but it doesn't work Sad

can somebody please tell me how to do that??

thanks a lot!!
anja
Re: How to select Default value from column ? [message #188113 is a reply to message #188107] Thu, 17 August 2006 03:08 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do you just want to know what the default value for a given column is?
SELECT data_default
FROM   user_tab_columns
WHERE  table_name = 'MY_TABLE'
AND    column_name = 'MY_COLUMN'


Ross Leishman
Re: How to select Default value from column ? [message #188114 is a reply to message #188107] Thu, 17 August 2006 03:08 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Default column value can be found in USER_TAB_COLUMNS:
SELECT data_default
FROM user_tab_columns
WHERE table_name = 'SOME_TABLE'
  AND column_name = 'SOME_COLUMN_NAME';
But, what do you need it for? This value exists in order to fill this column with this, predefined default value, if nothing else is specified. In other words, if there's a column named "today" and its default value is "sysdate", this will do the job:
SQL> create table test (id number, today date default sysdate);

Table created.

SQL> insert into test(id) values (1);

1 row created.

SQL> select * From test;

        ID TODAY
---------- --------
         1 17.08.06

SQL> select data_default from user_tab_columns
  2  where table_name = 'TEST'
  3    and column_name = 'TODAY';

DATA_DEFAULT
----------------------------------------------
sysdate

SQL>
Re: How to select Default value from column ? [message #188117 is a reply to message #188114] Thu, 17 August 2006 03:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Bwaaaaaaaa-ha-ha-ha (evil laugh - think Dracula). Fast and loose answer slips in just ahead of detailed and thoughtful. That's twice this week...
Re: How to select Default value from column ? [message #188118 is a reply to message #188117] Thu, 17 August 2006 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Huh, yes; if I remember well, it was JRowbottom last time Smile
Re: How to select Default value from column ? [message #188123 is a reply to message #188118] Thu, 17 August 2006 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yup, I was robbed of my well deserved glory too Cool
Re: How to select Default value from column ? [message #188313 is a reply to message #188123] Thu, 17 August 2006 21:41 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yeah, I'm an equal-opportunity glory-thief. But I try to stick to you Northerners if I can Wink
Previous Topic: Missing output parameters values after using RAISE
Next Topic: error while executing the procedure
Goto Forum:
  


Current Time: Sun Dec 04 16:29:07 CST 2016

Total time taken to generate the page: 0.09461 seconds