topic Re: Using SQL Pass-Through to retrieve data for one organization in SAS Procedures

Using SQL Pass-Through to retrieve data for one organization

leviathand — Fri, 05 Aug 2016 16:24:29 GMT

I have an Oracle table I would like to acces in SAS but using either the datastep or proc sql it returns the column headers but no rows of data. In SQL developer the same issue occurs, but I am able to resolve it by first running the SQL command below:

begin
apps.mo_global.set_policy_context('S',101);
end;

I have tried to run that exact SQL statement using the SQL pass-through facility as follows:

proc sql;
   connect to oracle as ALIAS (user=username orapw=password path='path');
   execute(begin
           apps.mo_global.set_policy_context('S',org#);
           end; ) by ALIAS;
   disconnect from ALIAS;
quit;

The issue is that whenever I run this I get an error that the table or view does not exist. I do not know if there the problem is with my proc sql or if there is other code that is the SAS equivalent of the SQL command above. I have tried it in both SAS 9.3 and Enterprise Guide 5.1.

Any help would be greatly appreciated. Thank you.

Re: Using SQL Pass-Through to retrieve data for one organization

LinusH — Fri, 05 Aug 2016 21:25:43 GMT

I don't know about this error, but an execute block will not return anything to the SAS session.
What do you mean by "column headers"?
Perhaps there's some data dictionary tables that you could query using a select clause instead?

Re: Using SQL Pass-Through to retrieve data for one organization

leviathand — Fri, 05 Aug 2016 21:34:15 GMT

I can see why that wasn't clear. If I run a general query using a select clause I get an empty dataset. So all of the variables in the view returned, but no observations. That is what I meant by "column headers". Does this make sense?

I am attempting to use the execute clause to pass the SQL command directly through SAS so that I can then query the database and have data returned. That is where the error I described is coming up.

Thank you.

Re: Using SQL Pass-Through to retrieve data for one organization

SASKiwi — Sat, 06 Aug 2016 03:13:49 GMT

Your set_policy_context code is different between the Oracle version and the SAS one. Is that correct or is it a typo?

Re: Using SQL Pass-Through to retrieve data for one organization

Patrick — Sat, 06 Aug 2016 03:23:46 GMT

What you're trying to do should be possible.

As @SASKiwi already points out: Make sure that you run exactly the same code via Pass-through SQL than what you're using in SQL Developer (or the like).

Are you always using the same user and password?

Also make sure that once your first statement works that you issue your queries within the same session/connection.

Re: Using SQL Pass-Through to retrieve data for one organization

leviathand — Sat, 06 Aug 2016 03:25:50 GMT

I'm sorry, that's a typo. The command should be the same

Re: Using SQL Pass-Through to retrieve data for one organization

SASKiwi — Sat, 06 Aug 2016 03:52:09 GMT

Are you running the EXECUTE and the query in the same Oracle CONNECT step? If not, please try it.

Re: Using SQL Pass-Through to retrieve data for one organization

leviathand — Mon, 15 Aug 2016 15:48:29 GMT

Hi, this seemed to solve the problem. Running them in the same Oracle CONNECT step each time worked. Thank you for your help.