?
Solved

value default

Posted on 2014-09-23
8
Medium Priority
?
311 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 77

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 77

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

801 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