Example of Bulk Load (BULKLOAD) to Oracle

The SAS^© program starts by deleting a table named ORBULK13 in the `mydblib` library, if it exists, to ensure a clean test environment. Then, it creates and populates this same table using a DATA step and the `BULKLOAD=YES` option, a key SAS^©/ACCESS feature for optimizing performance when inserting large quantities of data into an external database like Oracle. The `BL_OPTIONS` are used to fine-tune the bulk load behavior: `ERRORS=999` allows up to 999 insertion errors before interrupting the process, and `LOAD=2000` limits the number of rows actually loaded to 2000, even if the DATA step logic generates 10000 observations. Finally, the `ORBULK13` table is deleted again to clean up resources.

Data Analysis

Type : CREATION_INTERNE

The data (10000 observations) is artificially generated directly within the DATA step via a `DO` loop. It does not come from pre-existing external data sources or standard SAS libraries like SASHELP.

1 Code Block

PROC DELETE

Explanation :
Deletes the `ORBULK13` table from the `mydblib` library if it exists. This is often done to ensure a clean state before recreating or loading data.

Copied!

1	PROC DELETE DATA=mydblib.ORBULK13;
2	RUN;

2 Code Block

DATA STEP Data

Explanation :
This DATA step creates the `ORBULK13` table in the `mydblib` library. The `BULKLOAD=YES` option enables bulk loading for optimized performance when inserting large amounts of data. `BL_OPTIONS` specifies bulk loading specific parameters: `ERRORS=999` allows up to 999 errors before stopping, and `LOAD=2000` indicates to load only the first 2000 generated rows, even if the `DO` loop produces 10000 observations.

Copied!

1	DATA mydblib.ORBULK13 ( bulkload=yes
2	BL_OPTIONS=' ERRORS=999, LOAD=2000' );
3	DO i=1 to 10000 ;
4	c1=1; OUTPUT;
5	END;
6	RUN;

3 Code Block

PROC DELETE

Explanation :
Deletes the `ORBULK13` table from the `mydblib` library again, thus cleaning up the environment after the example execution.

Copied!

1	PROC DELETE DATA=mydblib.ORBULK13;
2	RUN;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste