Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Scott.

Asked: November 20, 2015 - 8:10 am UTC

Last updated: November 20, 2015 - 11:42 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Hi guys,

It has been many years since my last question ;p

Consider a basic table

create table abc(
  a varchar2(20)
  ,b date
  ,c number
);

insert into abc values ('Scott',sysdate,1);


This will run fine
declare
 r_abc  abc%rowtype;
begin
select a, b, c
into r_abc
from abc;
end;
/


This will raise ora-01858
declare
 r_abc  abc%rowtype;
begin
select  b,a, c -- note column order
into r_abc
from abc;
end;
/


So if the code is working fine in one db, and in another the column order in the table is different, then I will get an error because of the positional notation.

Is there another method beyond reordering columns to fix this?
It seems reminiscent of using this without naming column order.
insert into abc values (...)


Scott

and Connor said...

Options

1) When querying into rowtype variables.... use the *row*, not the constituent columns, ie

select * into l_my_rowtype_var

This also insulates you to some degree from column changes/additions down the track.

2) Explicit column usage in the code

select b,c,a into l_row.b, l_row.c, l_row.a

3) Use 'alter table col invisible/visible' commands to "re-order" the columns without having to reload the data.

Cheers mate.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Using cursor as template

Karthick, November 20, 2015 - 10:16 am UTC

Option 4

declare
  cursor c is select a, b, c from abc;
  var c%rowtype;
begin
  select a, b, c into var from abc;
end;
/



Connor McDonald
November 20, 2015 - 11:41 pm UTC

Nice addition.

Perfect

Scott, November 20, 2015 - 12:34 pm UTC

Thanks mate, maybe I've been away from plsql too long. I now remember the first option is an exception to the general * rule, and the second as an explicit workaround
Connor McDonald
November 20, 2015 - 11:42 pm UTC

Thanks for stopping by. See you at the Perth conference