Help using Base SAS procedures

PROC SQL Pass-Through facility (Oracle procedure)

Reply
Contributor
Posts: 29

PROC SQL Pass-Through facility (Oracle procedure)

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

Trusted Advisor
Posts: 3,211

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

@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.  

---->-- ja karman --<-----
Contributor
Posts: 29

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

Thanks, this make makes more sense now.

Super User
Super User
Posts: 7,942

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

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.

Contributor
Posts: 29

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

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

Contributor
Posts: 29

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

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 79Smiley TongueLS-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.

Super User
Super User
Posts: 7,039

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

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.

Contributor
Posts: 29

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

Hi Tom,

That was exactly what was happening.

Trusted Advisor
Posts: 3,211

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

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.

---->-- ja karman --<-----
Contributor
Posts: 29

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

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

Super User
Posts: 3,250

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

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.

Ask a Question
Discussion stats
  • 10 replies
  • 1854 views
  • 10 likes
  • 5 in conversation