Oracle Stored Procedure DBMS_OUT in SAS

[metallon]

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

[RW9 · Accepted solution]

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]

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.