From: "Sybrand Bakker" <postbus@sybrandb.demon.nl>
Subject: Re: column name order & insert into tab select
Date: 2000/03/03
Message-ID: <952106021.21825.2.pluto.d4ee154e@news.demon.nl>#1/1
X-NNTP-Posting-Host: sybrandb.demon.nl:212.238.21.78
References: <89osh9$o13$1@nnrp1.deja.com>
X-Trace: news.demon.nl 952106021 pluto:21825 NO-IDENT sybrandb.demon.nl:212.238.21.78
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2014.211
Newsgroups: comp.databases.oracle.misc
X-Complaints-To: abuse@nl.demon.net


That was sheer luck.
Add 'order by column_position' to your select on all_tab_columns.

Hth,

Sybrand Bakker, Oracle DBA

<jeanch@my-deja.com> wrote in message news:89osh9$o13$1@nnrp1.deja.com...
> Folks,
>
> Here is a good one right;
>
> I am building a string that contains like
> str := 'insert into tab select col1,col2,...,2,'tiger',coln
> from tab where id = 23';
>
> I obtain the column list for that table from all_tab_columns.
> SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
> WHERE TABLE_NAME = tab AND OWNER = 'scott';
>
> Up to now the order (given by the select to all_tab_columns) in which I
> was putting columns into the string
> was what oracle expected when I submit str;
>
> However for some reason it does not anymore.
>
> My question is how I can guarantie that the order in which I
> put columns and value in str will always be OK;
> Or where can I fetch the column list from a table and be sure that
> the order I am given will be fine for a insert into select type of
> transaction.
>
> Cheers
> JC
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.