DBLOAD Procedure Specifics for Oracle

Overview

For general information about this feature, see DBLOAD Procedure. 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 databases that have been set up in your operating environment and also the default values if you do not specify a database. To learn more about how to set up default connections to an Oracle database without specifying a value for the PATH environment variable, see the information about TWO_TASK (on UNIX) or LOCAL (on Windows) in the environment variables section of your Oracle documentation.

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

  specifies the name of the Oracle tablespace where you want to store the new table. The Oracle-tablespace-name option 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 specified 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 option 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

This example creates a new Oracle table, EXCHANGE, from the DLIB.RATEOFEX data file. Based on the new table, an access descriptor, ADLIB.EXCHANGE, is also created. The PATH= statement uses an alias to connect to a remote Oracle 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-library';
libname dlib 'SAS-library';

proc dbload dbms=oracle data=dlib.rateofex;
   user=myusr1;
   orapw=mypwd1;
   path='mysrv1';
   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=myusr1;
   orapw=mypwd1;
   path='mysrv1';
   sql grant select on myusr1.exchange to pham;
run;

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

proc dbload dbms=oracle data=invdata append;
  user=myusr1;
  orapw=mypwd1;
  path='mysrv1';
  table=invoice;
  load;
run;