Home » SQL & PL/SQL » SQL & PL/SQL » How can I model a column in a new table after a column in an existing table?
How can I model a column in a new table after a column in an existing table? [message #682641] Wed, 04 November 2020 12:45 Go to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
Hi all:

Most of my background is on MS SQL, and I know very basics of Oracle. When I create CRUD stored procedures, I use the underlying table column's type for parameters all the time.

v_col1 IN schema.table.column%TYPE
Can I do the same for some but not for all columns, when I create tables that depend on an existing table? For instance, if I have

create table a(a varchar2(10));
How can I

create table b(b ...%type);
where type would be derived from a.a and be the same varchar2(10) but without declaring it explicitly?

Thank you!
Al

[Updated on: Wed, 04 November 2020 12:49]

Report message to a moderator

Re: How can I model a column in a new table after a column in an existing table? [message #682642 is a reply to message #682641] Wed, 04 November 2020 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

create table b (b,...) as select a,... from a where 1=2;
or, simpler, if the columns have same names:
create table b as select * from a where 1=2;

Re: How can I model a column in a new table after a column in an existing table? [message #682643 is a reply to message #682642] Wed, 04 November 2020 13:30 Go to previous messageGo to next message
Darth Waiter
Messages: 41
Registered: October 2020
Member
This models the whole table after the original one, but I am only looking to model some columns.
I understand that I can use literals for the rest but that will require a cast on each of them which outweighs the benefit of modeling some columns after the original.
Re: How can I model a column in a new table after a column in an existing table? [message #682644 is a reply to message #682643] Wed, 04 November 2020 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If your "model" has not the same columns or column definitions then, anyway, you have to express what you want in the SELECT part and if the types do not match you indeed have to specify/cast the type (but how is this a model then?).

In short, it is:
create table b as select <what I want in b> from a where 1=2;
Maybe I don't understand what you want, in this case I think you should specify in an example.

Re: How can I model a column in a new table after a column in an existing table? [message #682658 is a reply to message #682644] Thu, 05 November 2020 10:02 Go to previous message
EdStevens
Messages: 1333
Registered: September 2013
Senior Member
If the two tables don't match, then you are going to have to do a lot of hand-coding anyway. At that point, you are already looking at the 'source' table, so you already know the data types. I don't see any real gain in trying to 'model' the data types of specific, selected columns.
Previous Topic: A regular user cannot create Varchar2(32767)
Next Topic: How can I execute multiple SQL statements in one batch?
Goto Forum:
  


Current Time: Mon Aug 02 13:12:54 CDT 2021