Skip to Main Content


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
 r_abc  abc%rowtype;
select a, b, c
into r_abc
from abc;

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

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 (...)


and Connor said...


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.


  (2 ratings)

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


Using cursor as template

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

Option 4

  cursor c is select a, b, c from abc;
  var c%rowtype;
  select a, b, c into var from abc;

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

Nice addition.


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