topic Re: Oracle Stored Procedure DBMS_OUT in SAS in SAS Procedures

Oracle Stored Procedure DBMS_OUT in SAS

metallon — Thu, 16 Apr 2015 11:25:10 GMT

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

Re: Oracle Stored Procedure DBMS_OUT in SAS

RW9 — Thu, 16 Apr 2015 12:50:40 GMT

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.

Re: Oracle Stored Procedure DBMS_OUT in SAS

metallon — Thu, 30 Apr 2015 07:42:09 GMT

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.