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

Hi SAS Experts,

Is there a way to display the text that is generated with DMBS_OUT in SAS?

The stored procedure contains plenty of lines like:

DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' Rows inserted into table T-xxxx'); 

I can see them using the oracle sql developer client when executing but when I want to execute them in SAS this information gets omitted.

proc sql;

connect to oracle as test(user=xxxx password=yyyyy path=DB);

     create table positive as

     (

       select *

           from connection to ccccc         

             (

              EXEC P_mystuff(2014);

             )

     );

   disconnect from test;

quit;

Bye

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

As far as I am aware, no, its not possible.  The reason being is that SAS sends the statements in the passthrough to the database.  The database does its thing.  If a table is created, then that table is returned to SAS.  Anything else is done only on the database, e.g. each database does its own thing differently. 

More interestingly though, why would you want to do this?  The database and SAS are different applications.  The SQL passthrough is merely a method of transferring data between the two.  So irrespective of what is being put the log in the database, this may have not bearing whatsoever on the data thats being processed?  It sounds a bit like your trying to run the database from SAS, me personally I wouldn't, I would use the admin tools provided with the database.  SAS doesn't really provide anything over and above a database to warrant controlling the DB from SAS.

View solution in original post

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

As far as I am aware, no, its not possible.  The reason being is that SAS sends the statements in the passthrough to the database.  The database does its thing.  If a table is created, then that table is returned to SAS.  Anything else is done only on the database, e.g. each database does its own thing differently. 

More interestingly though, why would you want to do this?  The database and SAS are different applications.  The SQL passthrough is merely a method of transferring data between the two.  So irrespective of what is being put the log in the database, this may have not bearing whatsoever on the data thats being processed?  It sounds a bit like your trying to run the database from SAS, me personally I wouldn't, I would use the admin tools provided with the database.  SAS doesn't really provide anything over and above a database to warrant controlling the DB from SAS.

metallon
Pyrite | Level 9

Hi RW9.

Why I want to do it? Because it's an end user requirement to run the whole data processing done in Oracle Stored Procedures from SAS.

Reason, they dont have ETL Software like SAS DI, they don´t know how to use SQL Clients, they just know SAS and they want to know if the

Procedures run. Alternative: I write all the "messages" of success and stuff in a Log table. That always works.

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
  • 2 replies
  • 2126 views
  • 4 likes
  • 2 in conversation