An error occurs when you attempt to alter a database table using implicit pass-through processing


An error message similar to the following might be issued when you attempt to alter a database table, depending on the relational database management system (RDBMS) table that you are attempting to alter when using implicit pass-through processing (via a LIBNAME engine in SAS/ACCESS® software).

ERROR: The HEADER/VARIABLE UPDATE function is not supported by the ORACLE engine.
ERROR: View ORA1.BASELINE cannot be altered.

The following example illustrates an SQL procedure that replicates the problem when it accesses the Oracle table:

libname ora1 oracle user=user-ID password=password path=host_string;
proc sql;
drop table ora1.baseline;
create table ora1.baseline as select * from final;
alter table ora1.baseline add constraint pk_pt primary key(pt);
quit;

SAS/ACCESS® LIBNAME engines support creating tables as well as reading from and writing to tables, but they do not support table altering.

To alter an RDBMS table, you must use explicit pass-through processing (also known as PROC SQL pass-through), as shown in the following example:

proc sql ;
connect to oracle(user=user-ID password=password path=host_string);
execute( alter table table-name ...specific-Oracle-syntax...)by oracle;
disconnect from oracle;
quit;