topic PROC SQL Pass-Through facility (Oracle procedure) in SAS Procedures

PROC SQL Pass-Through facility (Oracle procedure)

jbear — Wed, 23 Jul 2014 02:26:32 GMT

Hello All,

I don't have a lot of experience with calling Oracle procedures as a pass-through, but I have a problem where this might be the solution.

After reading this SAS note (18350 - Calling stored procedures using SAS/ACCESS® Interface to Oracle), I am not sure what, "However, this will only run non-query type statements. Therefore, result sets cannot be returned." means.

I've been calling an Oracle procedure using the approach below with a list of values (1M rows) I want decoded and returned by the procedure in a table....

proc sql;

connect to oracle (user=userid password=password path=database_name);

execute (execute st_pr_name('parm')) by oracle;

disconnect from oracle;

quit;

Is this possible?

Thanks,

Joel

Re: PROC SQL Pass-Through facility (Oracle procedure)

jakarman — Wed, 23 Jul 2014 07:37:44 GMT

@jbear, SQL was intended as an internal standard language that every DBMS would understand. Something like Esperanto.

What happened is a lot of dialects not understanding each other. SAS is kept their implementation near to the ANSI-SQL when using that, still some differences.

Their programming languages is offering a lot of other functionality not present in SQL.

What is done is translating each request to SQL when SAS is needing data rfrm a DBMS. This is done implicit (eg using a libname) you are not exactly coding the sql or you code it manual without SAS being involved on that, that is explicit.

In the explicit pass through, this is the sample you have given, you can code everything the external DBMS can understand and that will run.

Could defnining a table adding users autorisation anything. That is not quering data, you cannot expect some data coming back.

That is strange doing a query for data (SQL) and not getting any.

Makes sense in your case as the called procedure could only do some cleanups.

Re: PROC SQL Pass-Through facility (Oracle procedure)

RW9 — Wed, 23 Jul 2014 08:19:16 GMT

I would just like to add to Jaap Karman's post that if you are using Oracle, I would assume that you have Toad, or SQL Developer or some SQL interface to Oracle installed. What I would suggest is that you write the code there that you want to execute and test it. Once it works then copy it over and paste it in between the brackets on the execute statement. Then, assuming your connection information is correct that whole block should be sent to OC's SQL parser to run, much like when run through Toad, and hence should then return results. TBH I have not seen that type of call either, generally speaking we would use SQL to extract data from a database, e.g. select * from table, and hence get some results back. I have no idea what the function: st_pr_name, is actually doing, it could just be a comment for instance hence no results.

Re: PROC SQL Pass-Through facility (Oracle procedure)

jbear — Wed, 23 Jul 2014 14:17:08 GMT

Thanks, this make makes more sense now.

Re: PROC SQL Pass-Through facility (Oracle procedure)

jbear — Wed, 23 Jul 2014 14:18:14 GMT

Thanks. I'm gonna try it in TOAD, the procedure called is supposed to decode VINs.

Re: PROC SQL Pass-Through facility (Oracle procedure)

jbear — Wed, 23 Jul 2014 20:56:12 GMT

I'm stuck on this one....

Here is the proc in TOAD (It runs correctly):

DECLARE

P_A CHAR(2);

P_B CHAR(3);

P_C VARCHAR2(32767);

P_D VARCHAR2(32767);

P_E VARCHAR2(32767);

P_F NUMBER;

P_G NUMBER;

P_H CHAR(2);

P_I CHAR(4);

P_J VARCHAR2(32767);

P_K NUMBER;

P_L VARCHAR2(20);

P_M VARCHAR2(40);

P_NNUMBER;

BEGIN

P_A := '99';

P_B := 'AA';

P_C := 'H';

P_D := 'Y';

P_E := '99999999999999999';

P_F := NULL;

P_G := NULL;

P_H := NULL;

P_I := NULL;

P_J := NULL;

P_K := NULL;

P_L := NULL;

P_M := NULL;

P_N := NULL;

PKG_PROC ( P_A, P_B, P_C, P_D, P_E, P_F, P_G, P_H, P_I, P_J, P_K, P_L, P_M, P_N);

dbms_output.put_line(P_M);

dbms_output.put_line(P_N);

END;

When I try the pass-through using this SAS code:

/**/

proc sql;

connect to oracle (user= 'UserID' password= 'Pass' path= server);

execute (call PKG_PROC ('99','AA','H','Y','99999999999999999',null,null,null,null,null,null,null,null,null)) by oracle;

disconnect from oracle;

quit;

I get this error: ORACLE execute error: ORA-06550: line 1, column 79:PLS-00363: expression ' NULL' cannot be used as an assignment targetORA-06550

If try to omit the null parameters I get another error for not having enough parameters....I don't know how to pass the nulls. Also in the SAS documentation it says that you have to call a proc with another proc that calls that one and writes results in a temp table in oracle which you can then view from SAS...I wonder if that's another issue.

Re: PROC SQL Pass-Through facility (Oracle procedure)

jakarman — Wed, 23 Jul 2014 21:57:14 GMT

I cannot check this myself at the moment, but I know this: The logic on the Null is quite not logical.

In an DBMS environment it has the meaning: record/field has not been used/filled. There is no value as nothing exist. Better coding would be "not exist" This architectural/conceptual mistakes happen as shit happens. http://en.wikipedia.org/wiki/Null_(SQL). Null is reserved word no value.

This is quite different in statistical approaches (SAS) where the missing us one (ore more) special values. Those are really different concepts.

I do not understand what toad is doing with those declares. I can see your call is not exactly the same in Toad as in SAS.

The call statement to oracle with SAS explicit pass through is coded specfiying all values. Null is no value as explained. The Oracle error is giving you that as error.

Did you try?

execute (call PKG_PROC ('99','AA','H','Y','99999999999999999',,,,,,,,,)) by oracle;

Remember that PKG_Proc is build by somebody at your location. You should ask him how to specify the hardcoded values.

Re: PROC SQL Pass-Through facility (Oracle procedure)

jbear — Thu, 24 Jul 2014 21:21:20 GMT

Thanks. It didn't quite work with SAS so I outsourced it to PL/SQL and then imported results to SAS.

Re: PROC SQL Pass-Through facility (Oracle procedure)

Tom — Fri, 25 Jul 2014 01:50:58 GMT

Sounds like the package is expecting to be passed variable names that it can write values into.

In particular the one P_M and P_N that you are then displaying after the package call.

Hence it does not work when you pass it values instead of variable names. There is no variable for it to store its results.

Re: PROC SQL Pass-Through facility (Oracle procedure)

jbear — Mon, 28 Jul 2014 14:51:22 GMT

Hi Tom,

That was exactly what was happening.

Re: PROC SQL Pass-Through facility (Oracle procedure)

SASKiwi — Mon, 28 Jul 2014 22:04:18 GMT

If your Oracle stored procedure could be changed to produce a view instead of a result set then this would work fine in SAS.

We use this approach a lot - the EXECUTE step runs the stored proc creating a view then a second step doing a select * from the view reads the result set.