BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leviathand
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

View solution in original post

7 REPLIES 7
LinusH
Tourmaline | Level 20
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
leviathand
Calcite | Level 5
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.
SASKiwi
PROC Star

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

leviathand
Calcite | Level 5
I'm sorry, that's a typo. The command should be the same
SASKiwi
PROC Star

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

leviathand
Calcite | Level 5

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

Patrick
Opal | Level 21

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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