SAS/ACCESS Software for Relational Databases: Reference

DBLOAD Procedure: ORACLE Specifics

The following section describes the DBMS-specific statements that you use in the SAS/ACCESS interface to ORACLE.

DBLOAD Procedure Statements for ORACLE

To create and load an ORACLE table, the SAS/ACCESS Interface to ORACLE uses the following statements in interactive-line or batch mode.

PROC DBLOAD DBMS=ORACLE <DATA= <libref.>SAS-data-set> <APPEND>;

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

USER= <'>ORACLE-user-name<'>;

ORAPW= PW= | PASSWORD= <'> ORACLE-password<'>;

PATH=<'> ORACLE-database-specification<'>;

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

ACCDESC= <libref.>access-descriptor;

COMMIT= commit-frequency;

DELETE variable-identifier-1 <...variable-identifier-n>;

ERRLIMIT= error-limit;

LABEL;

LIMIT= load-limit;

NULLS variable-identifier-1 = Y | N <...variable-identifier-n = Y | N>;

QUIT;

RENAME variable-identifier-1 = <'>column-name-1<'>
<... variable-identifier-n = <'>column-name-n<'>>;

RESET ALL | variable-identifier-1 <...variable-identifier-n>;

SQL ORACLE-SQL-statement;

TYPE variable-identifier-1 = 'column-type-1'
<...variable-identifier-n = 'column-type-n'>;

WHERE SAS-where-expression;

LIST <ALL | COLUMN | variable-identifier>;

LOAD;

RUN;

PROC DBLOAD Statements

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

identifies the ORACLE table that you want to create. The TABLE= 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.

USER= <'>ORACLE-user-name<'>;

specifies an optional ORACLE userid. If the user name contains blanks or national characters, enclose the name in quotation marks. If you omit an ORACLE user name and password, the default ORACLE user ID OPS$sysid is used, if it is enabled. USER= must be used with ORAPW=.

ORAPW= | PW= | PASSWORD= <'>ORACLE-password<'>;

specifies an optional ORACLE password that is associated with the ORACLE user ID that is specified in the USER= statement. If omitted, the password for the default ORACLE user ID OPS$sysid is used, if it is enabled. ORAPW= must be used with USER=.

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 omitted, the table is created in the user's default tablespace that is defined by the ORACLE database administrator at your site.

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 the SAS System.

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 have been 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.

DBLOAD Procedure Examples

The following example creates a new ORACLE table, EXCHANGE, from the DLIB.RATEOFEX data file. 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.

Note: The DLIB.RATEOFEX data set is included in the sample data shipped with your software. [cautionend]

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

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

The next example uses the APPEND option to append rows from the INVDATA data set to an existing ORACLE table named INVOICE.

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

Note: The next example uses a previously created data set, INVDATA. [cautionend]

Chapter Contents
Previous
Next
Top of Page