BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SuryaKiran
Meteorite | Level 14

 

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

Thanks,
Suryakiran
1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20
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).
Data never sleeps
Sven111
Pyrite | Level 9

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
Opal | Level 21

@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
Onyx | Level 15

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1192 views
  • 0 likes
  • 5 in conversation