Oracle Bulk Loader Delimiter

Reply
Occasional Contributor
Posts: 11

Oracle Bulk Loader Delimiter

Hi

Does anybody know, if there is an option to change the default delimiter of external file created by SAS when using Oracle Bulk Loader?

I want to specify comma as the delimiter. SAS version is 9.3 and is installed on Linux.

Thanks

Vinay

Respected Advisor
Posts: 4,173

Re: Oracle Bulk Loader Delimiter

Why would you want to do that?

Occasional Contributor
Posts: 11

Re: Oracle Bulk Loader Delimiter

I want to do it because the default delimiter has quite a bit of whitspaces in between fields and as just the size of external file created by SAS can become huge if there are lot of columns

Respected Advisor
Posts: 4,173

Re: Oracle Bulk Loader Delimiter

I'm not aware of any option where you could tell the bulk-loader to remove trailing blanks.

There are storage systems with real-time compression meaning that blanks or not wouldn't be that relevant in regards of disk I/O (I assume though that network traffic still gets the uncompressed file and though blanks matter).

Trusted Advisor
Posts: 3,211

Re: Oracle Bulk Loader Delimiter

suggested docs are:

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (Bulk Loading for Oracle)

If you want to give parmeters for SQLoader: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (parfile)

Needing a lot of space for the datafile of SQLloader SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (datafile)

SAS builds that file and does a call to sqlloader (X-cmd needed or internally check bypassed) 

---->-- ja karman --<-----
Trusted Advisor
Posts: 3,211

Re: Oracle Bulk Loader Delimiter

The best reference for the bulkloader is: Importing Flex Assets with the BulkLoader Utility - 11g Release 1 (11.1.1.8.0) (oracle bulkloader)

There is no comression for a flat file mentioned (step-3)


The only reasons for asking this question could be:

- running out of space on the default location (home) -> relocate that to one big enough

- suspected performance issues -> sequential IO is normal the fastest possible  All options like caching and read-ahead are in place for that.

As bulk loading should not be a regular task in data management, it is initial load. There shoul be no issue.

On the other hand when this is your last escape in a bad perfroming DBMS than evaluate the DBMS.

It could be one of the wrong type. A DBMS designed for OLTP is designed and behaivng different than one that is meant for analytics.

Just when you are handling small ammounts of data you won't notice the difference. 

---->-- ja karman --<-----
Super User
Posts: 5,424

Re: Oracle Bulk Loader Delimiter

Just want to comment on bulk-load situations. I've have implemented bulk-load as in some daily (and weekly/monthly as well) loads, like insert of daily/weekly transactions - this is usually the most efficient way to load data into a DW, in most scenarios you can't configure the "normal" SAS/ACCESS engine to load records as fast as bulk-load.

Data never sleeps
Respected Advisor
Posts: 4,173

Re: Oracle Bulk Loader Delimiter

Just to confirm what sais. Using bulk-load is often the fastest way to append large volumes of data from SAS to Oracle. It's a common practice and not only used for an initial load.

Trusted Advisor
Posts: 3,211

Re: Oracle Bulk Loader Delimiter

You do not by accident have issues with NLS or datatypes

Oracle & Oracle: ORACLE - NLS_NUMERIC_CHARACTERS, Decimal character and Group Separator

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition (datatypes oracle/sas)

---->-- ja karman --<-----
Occasional Contributor
Posts: 11

Re: Oracle Bulk Loader Delimiter

Thanks all for the responses.

Seems like SAS only exports the data file in fixed poisition format only, which  results in increase in size of datafile if there are lots of character columns(as the data value might not occupy the entire length of the column). Storage although is no an issue for me but I was thinking if SAS somehow supported delimiters for the datafile.

Ask a Question
Discussion stats
  • 9 replies
  • 715 views
  • 0 likes
  • 4 in conversation