Solved

value default

Posted on 2014-09-23
8
297 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

747 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

9 Experts available now in Live!

Get 1:1 Help Now