Sign Up Login


  1. Categories >> Software >> Databases >> SQL PLSQL
  2. Suggest New Category

Create table emp
(id number(9), name varchar2(20),salary
number(9,2));
The table has 100 records after table created.Now i nee to
change id's Datatype is to be Varchar2(15). now

Alter table emp modify(id varchar2(15),name varchar2(20),
salary number(9,2));

Whether it will work or returns error? post answer with
explanation.

Question Posted / guru

Answers were Sorted based on User's Feedback



Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / rinson

It will raise error ora-1439 column to be modifiedy must be
empty to change datatype.

Is This Answer Correct ?    26 Yes 2 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / subbarayulu

It Will not work properly. when ever the table with data
user can't chage the column's datatype. It allows to
increase the size of the data type only.

Is This Answer Correct ?    14 Yes 2 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / imran

It wo'nt work because if there is an data exist in
(id number(9)).You cannot modify if colmun is not empty.

Is This Answer Correct ?    15 Yes 5 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / sathish p

We cant change column datatype while the column having
values.. Bcos error will come when u run the Alter table
modify statement...

Is This Answer Correct ?    5 Yes 1 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / ramesh

It will work 100%.
becos. we are increasing the length. if we are decreasing
the length then it gives error.

Is This Answer Correct ?    3 Yes 0 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / abhishekjaiswal

They are asking change datatype from number to varchar2..So it is not possible if table have data..this will give error 'ORA-01439: column to be modified must be empty to change datatype'.You can alter table and modify that column in same data type.
See Example 
select * from a;
   N F
----- -------------------
    5
    1
    3
alter table a modify  (n varchar2(10))
                       *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

  1* alter table a modify  (n number)
SQL> /

Table altered.<<<<<<<<<<<<<<<<<

Is This Answer Correct ?    2 Yes 0 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / lakshmanakumar

There should be error raised as below
ORA-01439: column to be modified must be empty to change
datatype

Is This Answer Correct ?    2 Yes 1 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / shailesh j

If column contains data then we can only modify size of
column without changing its data type. But if the column is
empty then we can modify its size as well data type.

Is This Answer Correct ?    1 Yes 0 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / krishna

It wil not work

Is This Answer Correct ?    2 Yes 2 No

Create table emp (id number(9), name varchar2(20),salary number(9,2)); The table..

Answer / reddi

It won't work coz number can't datatype can't be changed to
varchar. If data does not exits then its work fine.

Is This Answer Correct ?    2 Yes 2 No

Post New Answer

More SQL PLSQL Interview Questions

What are expressions?

0 Answers  

What are inner outer left and right joins in sql?

0 Answers  

What is equi join in sql?

0 Answers  

What is nosql db?

0 Answers  

Can variables be used in sql statements?

0 Answers  




How is data stored in sql?

0 Answers  

How can you maintain the integrity of your database on instances where deleting an element in a table result in the deletion of the element(s) within another table?

0 Answers  

i have a table like this. i want to output like this c1 c2 c1 c2 1 10 1 10 2 20 2 30 3 30 3 60 4 40 4 100 5 5 5 105 c1 and c2 are columns in a table .i want output like this c2 values are 10,10+20,10+20+30,10+20+30+40.10+20+30+40+5. write a sql query.pls help this i want urgent.

2 Answers   IBM, Loginworks, OFSS,

what are the difference between clustered and a non-clustered index? : Sql dba

0 Answers  

Hi All, I am new to both this blog and technology. I was able to see a response for one of the questions on triggers as below. I would like to know why are we using " if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then" instead, can't we use " if sysdate = 'sunday' then". I can understand the use of "rtrim", but dont know y v r using to_char. I have seen this in many cases but did not get a convincible explaination. Please help me with this and do excuse if this question sounds silly. Thanks in advance...... create or replace trigger trg_sun before insert on <table name> begin if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then raise_application_error(-20345,'no transaction in sunday'); end if; end trg_sun;

2 Answers  

What is the use of stored procedures?

0 Answers  

How to execute multiple sql statements in a single annonymous block irrespective of the sql statement result?

2 Answers  

For more SQL PLSQL Interview Questions Click Here
Categories