Tag Archives: Oracle

ORA-00972 Error with SQL Pass-Through in SAS

If you are using explicit pass-through syntax in SAS, you can run into problems when naming conventions in the native database are different from those in SAS. For example, this can happen when SAS allows more characters for an identifier than Oracle does. I ran the following statements in SAS to pull some pharmacy claims and got an error in my log:

proc sql;
connect to oracle (path=”pathname” user=xxxxxxx password=xxxxxxxx);
create table sasuser.thrx_rx_claim_2012 as
  select * from connection to oracle
    (
    select a.indiv_enterprise_id
    ,b.clm_pd_dt
    ,b.svc_beg_dt
    ,b.svc_end_dt
    ,b.elgbl_chrg_amt
    ,b.mail_retl_cd
    ,b.new_prscrptn_ind
      from c31497.thrx_id_elig_rsk_cens_dedup_2012 a
      left join ccdm_mart.clm_svc b on a.indiv_enterprise_id=b.indiv_enterprise_id
        where svc_beg_dt >= to_date(’01/01/2012′,’mm/dd/yyyy’)
        and svc_beg_dt <= to_date(’12/31/2013′,’mm/dd/yyyy’)
        and CVRD_AMT<>NOT_COVRD_AMT
        and clm_frmt_ty=’RX’
    )
;
quit;

Here is the error I received in the log:
ORA-00972: identifier is too long

An identifier can refer to a table name or a column (variable) name. In this case the table name thrx_id_elig_rsk_cens_dedup_2012 in the FROM statement is exactly 32 characters, which is the maximum allowable in SAS. However, Oracle gives you a maximum of 30 characters, so the query will not run.

Granting SELECT Access to Your Tables

If you have created a table in your own personal schema in an Oracle database, others do not have permissions to access that object. You may at some point wish to allow SELECT access to another user. To do this in SAS, you can use PROC DBLOAD as follows:

proc dbload dbms=odbc;
user=’yourusername‘;
password=’password‘;
dsn=database; /* this is the name of your ODBC Connection to the database. */
sql grant select on yourschema.yourtablename to anotherusername;
run;

Or you can use explicit pass-through syntax to grant access:

proc sql;
connect to oracle (user=yourusername password=password);
execute (grant select on yourusername.yourtablename
to anotherusername) by oracle;
disconnect from oracle;
quit;

Finally, you can do this directly in the Oracle database itself just by executing the following statement which is passed through in the syntax above:

grant select on yourusername.yourtablename to anotherusername;

Automatic SAS Macro Variables: &sqlxmsg, &sqlxsrc

When I gave my presentation on Explicit SQL Pass-Through syntax, one of the questions asked by the audience was how to retrieve error messages from the DBMS being queried. So, for example, if I query an Oracle database, how can I get Oracle-generated error messages if my query fails? SAS will give you error messages in the log, but sometimes they are not very helpful (ex: ERROR: PROC SQL requires any created table to have at least one column).

Using two automatic SAS macro variables with a %put statement can print more specific information to the log:

&sqlxmsg: contains DBMS-specific error messages
&sqlxrc: contains DBMS-specific error codes

Here’s what it looks like when you use these macro variables in a pass-through query:

proc sql;
connect to oracle (path= prodj.cigna.com user=uid password=pwd);
%put &sqlxmsg &sqlxrc; /*prints error messages to log*/
create table work.test as
select * from connection to oracle
(select * from onesource_o.s1_claim);
%put &sqlxmsg &sqlxrc; /*prints error messages to log*/
disconnect from oracle;
quit;