07-22-2014 10:26 PM
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....
connect to oracle (user=userid password=password path=database_name);
execute (execute st_pr_name('parm')) by oracle;
disconnect from oracle;
Is this possible?
07-23-2014 03:37 AM
@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.
07-23-2014 04:19 AM
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.
07-23-2014 04:56 PM
I'm stuck on this one....
Here is the proc in TOAD (It runs correctly):
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);
When I try the pass-through using this SAS code:
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;
I get this error: ORACLE execute error: ORA-06550: line 1, column 79LS-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.
07-24-2014 09:50 PM
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.
07-23-2014 05:57 PM
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.
07-28-2014 06:04 PM
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.