SAS/ACCESS Software for Relational Databases: Reference

SQL Procedure Pass-Through Facility: Oracle Rdb Specifics

For a complete description of the PROC SQL statements, see SQL Procedure Pass-Through Facility Statements. The following section describes the Oracle Rdb-specific arguments that you use in the CONNECT statement.

Arguments to Connect to Oracle Rdb

CONNECT is an optional statement that can be used when connecting to Oracle Rdb. Oracle Rdb has one database-connection-argument that you can specify in this statement. It can also be used to connect to multiple Oracle Rdb systems.

CONNECT TO RDB <AS alias> <(DATABASE=<'>Oracle-Rdb-pathname<'>;>

(DATABASE=<'>Oracle-Rdb-pathname<'>)

specifies the name and physical location of the Oracle Rdb database. The name can be the operating-system-specific pathname or an OpenVMS logical name that points to the fully qualified Oracle Rdb database name. The entire DATABASE= argument must be enclosed in parentheses. The .RDB extension is optional.

If you are accessing a remote database, you can specify the OpenVMS node name as part of the OpenVMS pathname of the database:

(DATABASE=<'><Oracle-Rdb-net-node::> OpenVMS-pathname<'>)

The DATABASE= argument is optional. If you specify a database, it must exist. If you do not know the names of your databases, contact your database administrator.

If the CONNECT statement or the DATABASE= argument is omitted, the default action is to use the value of the OpenVMS logical name SQL$DATABASE. For more information about SQL$DATABASE, see your Oracle Rdb documentation.

Note: Double quotation marks cannot be used with this option. [cautionend]

The following example connects to Oracle Rdb and sends it two EXECUTE statements. The database name is TEXTILE, and it is located on an OpenVMS node named ATLANTA.

proc sql;
   connect to rdb 
     (database=atlanta::disk1:[root]textile.rdb);
   execute (create view whotookord as
            select ordernum, takenby, firstname, 
              lastname, phone
              from orders, employees
              where orders.takenby=employees.empid)
           by rdb;
   execute (grant select on whotookord to sasdemo) 
     by rdb;
   disconnect from rdb;
quit;

Note: Rdb has a 30-character limit on its database column names. Therefore, when you create an Rdb table using the PROC SQL EXECUTE statement, be sure the column names are no longer than 30 characters. [cautionend]

Requesting READONLY Access to an Oracle Rdb Table Using the Pass-Through Facility

The READONLY option is used in the CONNECT statement of the SQL Procedure Pass-Through Facility. It enables you to use a PROC SQL view without locking other users out of the table.

The option values are READONLY | NOREADONLY. The default value is NOREADONLY. This example illustrates the option:

connect to rdb(database=atlanta::disk1:[root]
textile.rdb
  readonly);

When you specify the READONLY option value, a SET TRANSACTION READ ONLY command is executed after the connection to the database is established. This option is useful if you want to create a permanent PROC SQL view that contains a Pass-Through query. This view can then be used without locking other users out of the table. For example, when you use FSBROWSE on the view, the data is read in a READ ONLY transaction. The following example connects to Oracle Rdb and creates a view using the READONLY option. The database name is TEXTILE, and is located on an OpenVMS node named ATLANTA.

proc sql;
   connect to rdb
     (database=atlanta::disk1:[root]textile.rdb
      readonly);
   create view invoice as
            select *
              from connection to
              (select * from invoice);
   disconnect from rdb;
quit;

When you create a PROC SQL view, any arguments that you specify in the corresponding CONNECT statement are stored also. Thus, when the PROC SQL view is used in a SAS program, the SAS System can establish the appropriate connection to the DBMS.

Chapter Contents
Previous
Next
Top of Page