value default

Hi experts:

i have this table
create table TA_PAIS
(
  IDPAIS          NUMBER(8) not null,
  CODIGOPAIS      VARCHAR2(4) not null,
  PAIS            VARCHAR2(20) not null,
USER CREATION VARCHAR2 (20) not null,
CREATION DATE TIMESTAMP (6) not null,
  USUARIOEDICION  VARCHAR2(20),
  FECHAEDICION    TIMESTAMP(6)
)

I need to put default values ​​for columns
USER CREATION VARCHAR2 (20) not null,
CREATION DATE TIMESTAMP (6) not null,
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Do you really want spaces in field names? - I seriously doubt it!

Is your question about the syntax needed for defaults, or are you asking us what values should be used as defaults?
 (or both)?

the creation date would use DEFAULT SYSTIMESTAMP

But I have no idea what default value you want for the "USER CREATION" field (I would call it CREATED_BY)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trinitrotolueneDirector - Software EngineeringCommented:
you can do this as follows. I'm defaulting to the system time stamp and 1 for the other column
USER CREATION VARCHAR2 (20) default 1 not null,
CREATION DATE TIMESTAMP (6) default systimestamp not null,

Open in new window

0
Wasim Akram ShaikCommented:
Yes, I agree with Paul, using spaces in field names have to be changed else you would always need an effort in calling the field from your code.

DEFAULT clause is something which you have to decide on, it gives you the flexibility to insert value with some value rather than null

Oracle will not accept INSERT INTO <table> or INSERT INTO <table> VALUES () as valid SQL. You must specify at least one column, but you may use the DEFAULT keyword to allow the default value rather than hard-coding a value, so the following is valid syntax that will create a row with all DEFAULT values.

For Eg:
create table TABLE_X(COL1 char default 'Y',datecol date default sysdate);

insert into TABLE_X(col1) values (default);

select * from TABLE_X;

Your Sample code for your default, the syntax be like this

create table TA_PAIS
(
  IDPAIS          NUMBER(8) not null default <SOMEVALUE>,
  CODIGOPAIS      VARCHAR2(4) not null default <SOMEVALUE>,
  PAIS            VARCHAR2(20) not null   default <SOMEVALUE>,
USER CREATION VARCHAR2 (20) not null default <SOMEVALUE>,
CREATION DATE TIMESTAMP (6) not null default systimestamp,
  USUARIOEDICION  VARCHAR2(20) default <SOMEVALUE>,
  FECHAEDICION    TIMESTAMP(6) default systimestamp
)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

slightwv (䄆 Netminder) Commented:
Wasim,
>>IDPAIS          NUMBER(8) not null default <SOMEVALUE>,

I believe the syntax is incorrect here.

I think the not null has to go after the default as shown in a previous post:
...
IDPAIS          NUMBER(8) default <SOMEVALUE> not null,
...
0
Wasim Akram ShaikCommented:
yeah steve.. my mistake.. agree with you
author, please note..
0
enrique_aeoAuthor Commented:
i agree with CREATED_BY,
there are no spaces between the names of the columns, it was my mistake.
this table will have an initial charge and need to record the name of the user who has connected to the database, as I get it?
0
slightwv (䄆 Netminder) Commented:
>> and need to record the name of the user who has connected to the database, as I get it?

I don't believe you can use a function as the default value for a column.  You will most likely need a trigger to populate the column.

Either using the SYS_CONTEXT call or the user pseudo column.
an example of the user pseudo column:  
select user from dual;

SYS_CONTEXT:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions184.htm#SQLRF06117
0
enrique_aeoAuthor Commented:
thanks,

finally
CREATE TABLE TA_PAIS(
       idPais NUMBER(8) PRIMARY KEY,
       codigoPais VARCHAR2(4) NOT NULL,
       pais VARCHAR2(20) NOT NULL,
       usuarioCreacion VARCHAR2(20) DEFAULT USER NOT NULL,
       fechaCreacion TIMESTAMP (6) default systimestamp NOT NULL,
       usuarioEdicion VARCHAR2(20),
       fechaEdicion TIMESTAMP,
       CONSTRAINT UNQ_TA_PAIS
                  UNIQUE (codigoPais)
);
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.