Home » SQL & PL/SQL » SQL & PL/SQL » Assigning of %rowtype variable from different table
Assigning of %rowtype variable from different table [message #270266] Wed, 26 September 2007 06:22 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

hi,
i created 2 tables, table1 and table2. both table are having same set of columns but in different sequence. i created 2 rowtype variables in plsql block. trying to fetch from table1 to variable1, working.
when we are trying to assign this variable value to other variable , its giving error (ORA-00932: inconsistent
datatypes: expected DATE got NUMBER) question is why..?

1. internally rowtype variable is going to assign every column values. does sequence of column matters?

In real scenario, we are expecting that both table are having same no of columns and column sequence. only then our code will work, but if this type of condition arises then what will be the solution? using variable for each column is not feasible because table is having 180 columns.
--============================================================
create table table1 (col_num number, col_dat date, col_chr char);
create table table2 (col_dat date, col_chr char, col_num number);
insert into table1 values(10, sysdate,'A');
insert into table2 values(sysdate,'B', 20);
  
declare
t1 table1%rowtype;
t2 table1%rowtype;
t3 table2%rowtype;
begin
select a.* into t3 from table1 a;

dbms_output.put_line('t1 Values('||t1.col_num||', '||t1.col_dat||', '||t1.col_chr||')');
t2:=t1;
dbms_output.put_line('t2 Values('||t2.col_num||', '||t2.col_dat||', '||t2.col_chr||')');
-- Same number of columns (in table 2) but with different column sequence. 
t3:= t1;
dbms_output.put_line('t3 Values('||t3.col_num||', '||t3.col_dat||', '||t3.col_chr||')');
end;

[Updated on: Wed, 26 September 2007 06:59] by Moderator

Report message to a moderator

Re: Assigning of %rowtype variable from different table [message #270285 is a reply to message #270266] Wed, 26 September 2007 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER ever use *, ALWAYS use explicit column names, then you will not have this problem.
Yes, the order is important in %rowtype.

In addition,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Wed, 26 September 2007 07:02]

Report message to a moderator

Re: Assigning of %rowtype variable from different table [message #270293 is a reply to message #270285] Wed, 26 September 2007 07:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd have to disagree about the SELECT * in this one case only.

As the variable that is being selected into is a RowType, there is no way that a modification to the table structure will cause this to stop working.

I've used this a couple of times for tables who's structure changed with every release of the system, and it saved a considerable amount of coding.

In general - you're right, and it can lead to many problems, but in this case, I reckon it's ok
Re: Assigning of %rowtype variable from different table [message #270298 is a reply to message #270285] Wed, 26 September 2007 07:54 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Thanks Guys,
yes,i will check formating before posting topic.
using explicit column names in this particular case is not
possible because table columns are 180.

so the solution is, i need to chage the colum sequence matching
with rowtype variable sequence..right?
Re: Assigning of %rowtype variable from different table [message #270302 is a reply to message #270293] Wed, 26 September 2007 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See what happens and this is often the case (from my experience).
You have 2 tables with the same structure (t1 and t2) and 2 variables but instead of using "v1 t1%rowtype" and "v2 t2%rowtype", "v2 t1%rowtype" is used.
It is a logical thing as the 2 tables must have the same structure (as far as the specifications tell it) but one of the tables is recreated and then bang! if you are lucky you get an error, if you are not, you corrupt your data.

Regards
Michel
Re: Assigning of %rowtype variable from different table [message #270307 is a reply to message #270298] Wed, 26 September 2007 08:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could create a view on T2 to order the columns into the same order as T1
Re: Assigning of %rowtype variable from different table [message #270309 is a reply to message #270307] Wed, 26 September 2007 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You could if you know that the table was recreated.
Think it is the DBA that did it behind the scene, noone knows why but he did it (maybe he thought it is better to have your bl... LONG column as the last one, or someone said that columns named start% is at better place before all columns named end%)...

Regards
Michel

Re: Assigning of %rowtype variable from different table [message #270314 is a reply to message #270309] Wed, 26 September 2007 09:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ah - confusion reigns.
The view suggestion was to the OP, although if he objects to cutting and pasting 180 column names, it probably won't help.

I's say that selecting a row from a table into a rowtype from a different table is a bug, even if the tables are meant to be the same.

I agree that it can cause problems, but I have also used SELECT * INTO <rowtype> in controled circumstances to make code maintenance much easier.
Re: Assigning of %rowtype variable from different table [message #270328 is a reply to message #270314] Wed, 26 September 2007 09:58 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

for testing this i created one more table table3 same
column structure with table1. It is allowing me to fetch
from table1 into a rowtype of table3. So it looks like its not
seeing table names. matching fetch columns with rowtype columns.
Re: Assigning of %rowtype variable from different table [message #270330 is a reply to message #270328] Wed, 26 September 2007 10:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you do a SELECT * INTO <rowtype>, Oracle simply moves through the columns returned (an order that is probably, but not definitely the order that they are listed in in DBA_TAB_COLUMNS) and tries to put the i-th value returned into the i-th field in the Rowtype.
As long as the rowtype that you are fetching into is one defined from the table you are selecting from you will have no problems.
Any other solution should be looked upon as storing up trouble for the future, and avoided.

Better, as Michel advocates, avoid SELECT * entirely, and use a named list of columns. The argument that there are too many columns to typ is a red herring, as you can easily generate the list of column names frm a query on DBA_TAB_COLUMNS, and paste that into your code.
Re: Assigning of %rowtype variable from different table [message #270335 is a reply to message #270330] Wed, 26 September 2007 10:16 Go to previous message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Putting all the column names names in select, i am not convinced. Any way this is really a great help from ‘JRowbottom‘ and ‘Michel’.
Thanks.
Previous Topic: How to loop through Unix files in a PL/SQL Package
Next Topic: Unload data to flat file, fixed length
Goto Forum:
  


Current Time: Sun Dec 04 13:00:20 CST 2016

Total time taken to generate the page: 0.12561 seconds