Help using Base SAS procedures

Oracle Stored Procedure DBMS_OUT in SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

Oracle Stored Procedure DBMS_OUT in SAS

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


Accepted Solutions
Solution
‎04-16-2015 08:50 AM
Super User
Super User
Posts: 7,992

Re: Oracle Stored Procedure DBMS_OUT in SAS

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


All Replies
Solution
‎04-16-2015 08:50 AM
Super User
Super User
Posts: 7,992

Re: Oracle Stored Procedure DBMS_OUT in SAS

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.

Frequent Contributor
Posts: 133

Re: Oracle Stored Procedure DBMS_OUT in SAS

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 593 views
  • 3 likes
  • 2 in conversation