topic Re: Checking Null Exception Oracle Table with SAS in Administration and Deployment

Checking Null Exception Oracle Table with SAS

SS_B — Thu, 21 Apr 2022 17:23:07 GMT

Hello,

Could you please help me to check the Oracle SQL definition (including null exception) with SAS EG? We don't have access to Oracle that's why unable to check null exception in oracle.

Below SAS EG query not return sql exception.

proc sql;

describe table abc;

run;

Re: Checking Null Exception Oracle Table with SAS

gwootton — Fri, 22 Apr 2022 15:16:25 GMT

Its unclear to me what you are asking here. It sounds like you are getting an error when trying to access an Oracle table. Can you provide the exact error you are receiving?
How are you connecting to Oracle from SAS? Are you issuing a libname statement or is this library defined / preassigned in SAS Management Console? If you're issuing a libname statement, what is it? If preassigned, can you try assigning manually using a libname statement and see if you get the same issue?

Documentation on the Oracle LIBNAME engine statement can be found here:

LIBNAME Statement for the Oracle Engine
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1qft7bzdij79zn1bxh59mc3w8xj.htm

Re: Checking Null Exception Oracle Table with SAS

Sajid01 — Sun, 24 Apr 2022 15:46:59 GMT

What I understand is that you can connect to oracle through SAS code, but cannot access using SQL developer or some other tool. Happens when the code is executed through a generic ID and SAS ins running on a server (no local install).

Now NULL means nothing. In other words no values is associated with the variable in that observation. If that variable is being used in an operation requiring a value and it encounters null, then there is a null exception.

There could be multiple approaches to handle the presence of Null depending what is needed.

The Oracle functions NVL and NVL2 can be used in expressions to handle NULL.
Oracle function NVL(Var, some_value)., assigns 'some_value' to the variable to the expression if Var is NULL..
The oracle function NVL2(Var, value1,value2) assigns value1 to the expression if the value of Var is null otherwise it assigns Value2..

Re: Checking Null Exception Oracle Table with SAS

Patrick — Sun, 24 Apr 2022 22:16:54 GMT

It makes a developer's life harder if one can't directly access a DB via a client. IF you've got the credentials the SAS library uses to access Oracle then the easiest way would be to get a client like DBeaver or SQL Developer. There are portable versions around so it's software you can just download and use even if you can't install software on your local machine.

If you're using a pre-assigned SAS library and though might not know the credentials then you can still query Oracle directly.

Let's assume the libref of your pre-assigned library a blah.

The following statement writes the library definition to your SAS log - one component of it is the Oracle schema name.

libname bla list;

You can also determine the schema name with below syntax.

%let ora_schema=;
proc sql noprint;
  select sysvalue into :ora_schema trimmed
  from dictionary.libnames
  where 
    libname="BLAH"
    and sysname='Schema/Owner'
  ;
quit;
%put &=ora_schema;

You can issue explicit Oracle SQL syntax out of SAS to query Oracle dictionary tables and so derive the DDL of a table.

proc sql;
  connect using blah;
  select * from connection to bla
  (
    <Oracle SQL syntax to be Googled>
  );
  disconnect from blah;
quit;

How the Oracle syntax needs to look like so it works for you (also dependent on the permissions your Oracle user has) is something you need to Google. A very quick try led me to this link.