Solved

value default

Posted on 2014-09-23
8
304 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 48

Accepted Solution

by:
PortletPaul earned 125 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 125 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 125 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 rs - Sum each category by month 4 31
SQL Syntax 5 33
sql server computed columns 11 29
SQL Syntax: How to force case sensitive query? 2 20
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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. …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

816 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now