Insert with Order on Oracle

INSERT with ORDER on Oracle

You can not reliably control in what order Oracle retrieve the row of a table without an ORDER BY.

Furthermore, without the /*+APPEND*/ hint, Oracle will store the rows physically in a heap table where there is room, which may not be at the end of the table ! You may think Oracle inserts them sequentially but any DML or concurrent activity (2+ sessions inserting) might produce a different physical organization.

You could use an INDEX ORGANIZED table to store the rows in the order of the PK. Most simple queries thereafter on that table will produce a sorted set of rows. This would not however guarantee that oracle will select the rows in that order if you don't specify an ORDER BY (depending on the query and the access path, the rows may come in any order).

You could also use a view with an order by, this is probably your best bet if you can't touch the application (rename the table, create a view with the name of the table, let the application think it queries the table). I don't know if it is feasible in your case.

How does Oracle Insert Into work when order of values is not defined?

The order of columns in a table in Oracle IS defined. Take a look at the ALL_TAB_COLUMNS view - there's a COLUMN_ID column which defines the order of columns within the table. If a field list is not given in a SELECT (i.e. SELECT * FROM MY_TABLE) the columns from MY_TABLE will be returned in ALL_TAB_COLUMNS.COLUMN_ID order. This is also the same way columns are ordered in a %ROWTYPE variable, and it's the way that an INSERT which doesn't have a field list specified expects fields to be ordered.

Rowid not in order when insert into an oracle table

Don't rely on ROWID as it can be changed. For example, if you export schema and then import it back, ROWID might change so - if your code relies on (wrong) assumption that it is constant - code will break and you'll have a problem.

Use something else as an identifier and a value to sort rows in a table. For example, a sequence.

insert rows in different order in oracle table

We found a solution .If you are using ODI you need only put nothing in INSERT_HINT input text .let it empty like in picture below...And it will save your lines as they are in the plat file

Make sure Oracle insert statement in order

Unless you define sort order, you cannot rely on order of elements. The best way is to create primary key from sequence and order by it.

Alternatively you may try oracle specific rownum pseudocolumn:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
but IMHO it is not what you want.

EDIT

CREATE SEQUENCE mySequence;

PreparedStatement statement = conn.prepareStatement("insert into table XY values (mySequence.nextval, ?, ?)");
statement.setObject(1, myJB.getColumn1);
statement.setObject(2, myJB.getColumn2);
statement.executeUpdate();

And then

select * from XY order by Z asc

where Z is name of first column will do what you want

insert into... select ... with subquery or without column order

No, you can't use a subquery to generate the column list as part of an SQL statement.

You can generate the full statement from the data dictionary:

select 'insert into cl ("'
  || listagg(column_name, '","') within group (order by column_id)
  || '") select "'
  || listagg(column_name, '","') within group (order by column_id)
  || '" from clt'
from user_tab_columns where table_name = 'CLT';

and then either copy and paste that, or use dynamic SQL from an anonymous block:

declare
  stmt varchar2(4000);
begin
  select 'insert into cl ("'
    || listagg(column_name, '","') within group (order by column_id)
    || '") select "'
    || listagg(column_name, '","') within group (order by column_id)
    || '" from clt'
  into stmt
  from user_tab_columns where table_name = 'CLT';

  dbms_output.put_line(stmt); -- to check and debug
  execute immediate stmt;
end;
/

With a couple of dummy tables:

create table clt (col1 number, col2 date, col3 varchar2(10));
create table cl (col3 varchar2(10), col1 number, col2 date);

insert into clt (col1, col2, col3) values (42, date '2018-07-12', 'Test');

insert into cl
select * from clt;

SQL Error: ORA-00932: inconsistent datatypes: expected NUMBER got DATE

running that block gives:

insert into cl ("COL1","COL2","COL3") select "COL1","COL2","COL3" from clt

PL/SQL procedure successfully completed.

select * from cl;

COL3             COL1 COL2      
---------- ---------- ----------
Test               42 2018-07-12

You could also turn that anonymous block into a procedure that takes two table names if this is something you're likely to want to do often (you said it needed to be reusable, but that could mean for the same tables, and could just be a block in a script).

You could also go further and only include columns that appear in both tables, or verify data types match exactly; though that's a bit more work and may well not be necessary.

Python SQL insert not inserting in the correct order?

I dont have a primary key in the table

Well that's bad practice but anyway.

I just want it to append to the very end of the table so it goes in order by HOUR.

The database does not guarantee order of records in a table. A table is an abstraction over a tablespace which is another abstraction over a system disk (which itself is quite likely to be an abstraction over a lot of little disks).

The ordering of records only matters when we query the data. That is why the only mechanism for guaranteeing the order of records is the ORDER BY clause of a SELECT statement. In your case that would be

select *
from cards
order by date, hour

And presumably nothing else.

---

Related Topics