actually a view is required -- no idea why you say "view is not required".

what the heck is "where last first" -- last first??


Use the view, short of dropping and recreating the table from scratch that is your only option -- period.

Your method of this "dynamic insert" sounds very suspicious and fragile -- this simple exercise here is showing it's fragileness. You might rethink that logic.

But -- all else aside -- the view is required, unless you want to rebuild the table which you said "i don't want to rebuild the table".

you can make it BIGGER

alter table t modify c varchar2(BIGGER)


you cannot make it "smaller", but you can add a check constraint to make it so that only the smaller field can be put into there.

views man -- this is what views are all about.

it would take about 5 seconds with a view -- no muss, no fuss.

Never will I understand the FUD surrounding the use of views.

Congratulations!

You win my first ever Rube Goldberg Award. Outstanding...

"The purpose of a Rube Goldberg Machine is to build the most complicated machine possible to perform a simple everyday task."

</code> http://www.rube-goldberg.com/html/today.htm <code>

you are kidding -- you won't recreate the table -- but you'll use the "curly shuffle" to totally scramble it and make it as bad as possible.

wow, amazing. amazing what hoops people will go through when the answer is staring them -- literally staring them -- in the face. totally amazing.


you'll write, rewrite, write, rewrite and totally scramble the table but you won't use a view, you won't just recreate the table as select -- both of which (in order) are infinitely superior options. Ok.....

I think Andrew, who put the comment right above yours -- before you put it up here, hit the proverbial nail on the head.

possible = y
advisible = n

cool -- stealth feature!! that one totally slipped by me, thanks so much:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x varchar2(25) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'x' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t modify x varchar2(5);
alter table t modify x varchar2(5)
                     *
ERROR at line 1:
ORA-01441: column to be modified must be empty to decrease column length

<b>versus</b>

ops$tkyte@ORA9I.WORLD> create table t ( x varchar2(25) );

Table created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> insert into t values ( 'x' );

1 row created.

ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> alter table t modify x varchar2(5);

Table altered.




 

sure, IF the column is empty.

else no. it would take a

a) add new column
b) update new using old column
c) drop old column
d) rename new to old

ops$tkyte%ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  -- subpartition by hash(x)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  ;

Table created.


ops$tkyte%ORA9IR2> alter table t add num number;

Table altered.


works in 8i too - always good to "try things" 

Modifying a column to have a default will only affect rows that come AFTER the alter - it doesn't touch existing data.

therefore, the table undergoes "nothing", no drop, no rebuild, nothing.

ops$tkyte%ORA10GR2> create table t ( x varchar2(30), y int );

Table created.

ops$tkyte%ORA10GR2> insert into t (y) values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t (x,y) values ( 'hello world', 2 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t modify x default 'N/A';

Table altered.

ops$tkyte%ORA10GR2> insert into t (y) values ( 3 );

1 row created.

ops$tkyte%ORA10GR2> insert into t (x,y) values ( 'hello world', 4 );

1 row created.

ops$tkyte%ORA10GR2> select * from t;

X                                       Y
------------------------------ ----------
                                        1
hello world                             2
N/A                                     3
hello world                             4

ask the people that make toad questions about toad please. I do not use toad myself.

I don't know how to copy a table from A to B if (1) is true.

I don't know what it means to copy a table form A to B is (2) is true - what would you expect to have happen???

(3) is trivial, log into B and issue "delete from table; insert into table select * from a.table;"

how can you call

"2. Table is present in both schemas but differ in column structure."

a simple copy ??? the structures are different - that is hugely complex all of a sudden.


... I need Objects in Schema B are same as Schema B. ...

er? not sure what you mean.


... But I am not sure what
happens with this COPY command if you have tables in both schema but different
strucure,. ...

laughing out loud - I'm not sure what should happen in your mind when this occurs. How do you do that? What is the process.

... To make it simple, lets consider a prod alike schema needs to be created.
...

a prod alike? or do you mean a "prod like schema"

I would say that data pump or export import would be the path of least resistance - either one can remap schema names - but you would start with an EMPTY schema.


This is not very clear at all - especially when you throw in #2.


If you just want a copy of a schema - table by table, column by column - exp/imp is the way to go using fromuser touser, or datapump - depending on your version.

you want to have a set of creates and alters created for you to make schema "A" look like schema "B"

(you do not want to copy all of the objects by the way, that would imply you wanted to COPY the objects - not synchronize the physical schemas of two things)

I hate this - you would, in a proper environment, have the "patch scripts" you would pull out of source code control that take your schema from version 1 to version 2. Having to "diff" two schemas to make them the same means "someone missed the boat big time".


sql developer has such a capability:
http://docs.oracle.com/docs/cd/E15846_01/doc.21/e15222/dialogs.htm#sthref599

Tell me - how did production and this environment get to "differ" - where are THOSE scripts - those scripts that changed the one environment should have been

a) checked in
b) tested
c) available for you to now check out and run yourself.

see my answer above - I provided a name (sql developer) and a link (to feature of sql developer).

triggers have no impact on views


we just don't have it - that is all, no reason. to me it isn't a big deal because select * is something you only have in demo's anyway, the order of columns in the table doesn't even guarantee what select * will resolve to, it is just an empirical observation that it seems to be the order of columns in the create....

ah, but that is apples and banana's

the before/after 'trick' is purely a metadata thing, it doesn't really put the data before/after - just makes that column "appear" there.


you have to rebuild the entire table to put a column "in the middle" of a table - and we can do that, dbms_redefinition will do it, create table as select will do that.


there is a big difference between the syntactic sugar that puts column X between A and B in metadata

and actually having column X between A and B physically in the database block.

it is not possible, no