Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

value default

Posted on 2014-09-23
8
Medium Priority
?
314 Views
Last Modified: 2014-09-24
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,
0
Comment
Question by:enrique_aeo
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40340751
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
 
LVL 12

Assisted Solution

by:trinitrotoluene
trinitrotoluene earned 500 total points
ID: 40340791
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
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 500 total points
ID: 40340801
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40341439
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
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40341475
yeah steve.. my mistake.. agree with you
author, please note..
0
 

Author Comment

by:enrique_aeo
ID: 40341496
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 40341593
>> 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
 

Author Comment

by:enrique_aeo
ID: 40341840
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question