BookmarkSubscribeRSS Feed
jbear
Calcite | Level 5

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

10 REPLIES 10
jakarman
Barite | Level 11

@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 --<-----
jbear
Calcite | Level 5

Thanks, this make makes more sense now.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jbear
Calcite | Level 5

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

jbear
Calcite | Level 5

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.

Tom
Super User Tom
Super User

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.

jbear
Calcite | Level 5

Hi Tom,

That was exactly what was happening.

jakarman
Barite | Level 11

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 --<-----
jbear
Calcite | Level 5

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

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4615 views
  • 10 likes
  • 5 in conversation