SAS/ACCESS Interface to Oracle

DBLOAD Procedure Specifics for Oracle

For general information about this feature, see The DBLOAD Procedure for Relational Databases. Oracle examples are available.

The Oracle interface supports all DBLOAD procedure statements. See About DBLOAD Procedure Statements.

Here are the DBLOAD procedure specifics for Oracle.

The PROC DBLOAD step DBMS= value is Oracle .
Here are the database-description-statements that PROC DBLOAD uses:

USER=<'>Oracle-user-name<'>

specifies an optional Oracle user name. If you omit an Oracle password and user name, the default Oracle user ID OPS$sysid is used if it is enabled. If you specify USER=, you must also specify ORAPW=.

ORAPW= <'>Oracle-password<'>

specifies an optional Oracle password that is associated with the Oracle user name. If you omit ORAPW=, the password for the default Oracle user ID OPS$sysid is used, if it is enabled. If you specify ORAPW=, you must also specify USER=.

PATH=<'>Oracle-database-specification<'>

specifies the Oracle driver, node, and database. Aliases are required if you are using SQL*Net Version 2.0 or later. In some operating environments, you can enter the information that is required by the PATH= statement before invoking SAS.

SAS/ACCESS uses the same Oracle path designation that you use to connect to Oracle directly. See your database administrator to determine the path designations that are set up in your operating environment, and to determine the default value if you do not specify a path designation. On UNIX systems, the TWO_TASK environment variable is used, if set. If neither PATH= nor TWO_TASK have been set, the default value is the local driver.

TABLESPACE= <'>Oracle-tablespace-name<'>;

specifies the name of the Oracle tablespace where you want to store the new table. The Oracle-tablespace-name argument can be up to 18 characters long and must be a valid Oracle tablespace name. If the name contains blanks or national characters, enclose the entire name in quotation marks.

If TABLESPACE= is omitted, the table is created in your default tablespace that is defined by the Oracle database administrator at your site.
Here is the PROC DBLOAD step TABLE= statement:

TABLE= <'><Oracle-table-name><'>;

specifies the name of the Oracle table or Oracle view on which the access descriptor is based. This statement is required. The Oracle-table-name argument can be up to 30 characters long and must be a valid Oracle table name. If the table name contains blanks or national characters, enclose the name in quotation marks.

Examples

The following example creates a new Oracle table, EXCHANGE, from the DLIB.RATEOFEX data file. (The DLIB.RATEOFEX data set is included in the sample data shipped with your software.) An access descriptor, ADLIB.EXCHANGE, based on the new table, is also created. The PATH= statement uses an alias to connect to a remote Oracle 7 Server database.

The SQL statement in the second DBLOAD procedure sends an SQL GRANT statement to Oracle. You must be granted Oracle privileges to create new Oracle tables or to grant privileges to other users. The SQL statement is in a separate procedure because you cannot create a DBMS table and reference it within the same DBLOAD step. The new table is not created until the RUN statement is processed at the end of the first DBLOAD step.

libname adlib 'SAS-data-library';
libname dlib 'SAS-data-library';

proc dbload dbms=oracle data=dlib.rateofex;
   user=testuser; 
   orapw=testpass;
   path='myorapath';
   table=exchange;
   accdesc=adlib.exchange;
   rename fgnindol=fgnindolar 4=dolrsinfgn;
   nulls updated=n fgnindol=n 4=n country=n;
   load;
run;

proc dbload dbms=oracle;
   user=testuser; 
   orapw=testpass;
   path='myorapath';
   sql grant select on testuser.exchange to pham;
run;

This next example uses the APPEND option to append rows from the INVDATA data set, which was created previously, to an existing Oracle table named INVOICE.

proc dbload dbms=oracle data=invdata append;    
  user=testuser; 
  orapw=testpass;    
  path='myorapath';    
  table=invoice; 
  load; 
run;

Top of Page