Loading SAS data into Oracle

[SuryaKiran]

Hi,

       I have a SAS table of 10,000 records with 7 columns to be loaded into Oracle. One of my variable is "Row_Num" values as 1-10,000 is sorted and loaded into Oracle. When I loaded this table into Oracle it is not as same sort order as SAS Table. Is there a way I can send the data to Oracle in the same way how it was there in SAS table.

I used PROC APPEND with DBCOMMIT=0 to load SAS data to an empty Oracle table. I belive It is doing a parallel batch insert randomly. 

When I tried to use bulkload=yes, I get the following error:

ERROR: BCP=/BULKLOAD= option not supported by this datasource.

Environment:

Connected to Oracle using ODBC, SAS 9.4 M2( Linux), SAS EG 7.12 running through CITRIX (Windows server 2012)

Thanks,

Suryakiran

[LinusH]

Perhaps this more of an Oracle question rather than a SAS one.
I think that RDBMS usually doesn preserve row order in the physical storage. That shouldn't be a problem in your case since you already have a row_num column which could do ORDER BY on (or be sorted implicit on by using BY from any SAS step).

[Sven111]

I don't believe Oracle will retain the ordering of a table when loading or storing data, unless maybe you use an Index-Organized table.  Even with an IOT (which I actually don't know a whole lot about) I think the index is generally used for faster access than actually storing the data in order on disk.  Normally you'd just sort the data to whatever order you want when you're accessing it.

[Patrick]

@SuryaKiran

Oracle doesn't work this way and you always need an Order By to control the order of queries.

What triggered your question? Why is the sort order important to you?

[TomKari · Accepted solution]

It's a base concept of relational databases that you can't assume the records will be in any sequence. As stated, you need to use an "ordered by" clause to retrieve them in a sequence. Even if they were loaded in a sequence, again it's a base concept that in the absence of an "ordered by" clause, you can't expect the records to be returned in any particular sequence.

Tom