Help using Base SAS procedures

Using SQL Pass-Through to retrieve data for one organization

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Using SQL Pass-Through to retrieve data for one organization

[ Edited ]

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.


Accepted Solutions
Solution
‎08-15-2016 11:49 AM
Super User
Posts: 3,105

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

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 5,256

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

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?
Data never sleeps
New Contributor
Posts: 4

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

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.
Super User
Posts: 3,105

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

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

New Contributor
Posts: 4

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

I'm sorry, that's a typo. The command should be the same
Solution
‎08-15-2016 11:49 AM
Super User
Posts: 3,105

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

[ Edited ]

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

New Contributor
Posts: 4

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

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

Respected Advisor
Posts: 3,890

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

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 347 views
  • 1 like
  • 4 in conversation