Kbase P140899: How does Oracle and MS SQL server DataServer communicate with Stored procedures and funtions?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
28/04/2009 |
|
Status: Verified
GOAL:
How does Oracle and MS SQL server DataServer communicate with Stored procedures and functions?
GOAL:
How to communicate with Oracle or MS SQL server's stored procedures or functions using RUN STORED-PROC?
GOAL:
How does proc-status differ from error-status?
GOAL:
How many ways can a 4GL application communicate with Oracle and MS SQL Server stored procedures / functions?
GOAL:
How is error retrieved from Stored Procedures from 4GL applications?
FACT(s) (Environment):
All Supported Operating Systems
MS SQL DataServer
Oracle DataServer
FIX:
There are four ways to get "feedback" from a remote procedure call using RUN STORED-PROC.
1. Error messages
2. Result sets
3. Output parameters
4. PROC-STATUS
1. Error messages can be handled by the ABL application in the same way for both DataServers as follows:
RUN STORED-PROC change_password iProcHdl = PROC-HANDLE NO-ERROR ('prism$','demo','demo',OUTPUT cMessage) .
MESSAGE "1" ERROR-STATUS:ERROR VIEW-AS ALERT-BOX.
IF ERROR-STATUS:ERROR THEN DO :
DO i = 1 TO ERROR-STATUS:NUM-MESSAGES :
MESSAGE "2" ERROR-STATUS:GET-NUMBER(i) SKIP
ERROR-STATUS:GET-MESSAGE(i)
VIEW-AS ALERT-BOX ERROR BUTTONS OK
TITLE "Error " + STRING(i) +
" of " + STRING(ERROR-STATUS:NUM-MESSAGES).
END.
2. Results sets can be handled similarly for both DataServers using proc-text-buffer as follows. You do not need to CLOSE the stored procedure to get your result sets. In Oracle only, if the result set is generated by a SQL query in the procedure, a CURSOR data type can be passed to the stored procedure as a reference to the results which can then be read by the application using the CURSOR output value.
FOR EACH proc-text-buffer WHERE PROC-HANDLE = iProcHdl :
ASSIGN cmessage = proc-text.
END.
3. Output parameters are available only after CLOSE STORED-PROC. for both DataServers. Output parameters in MSS are the OUTPUT parameters you defined in the signature of your stored procedure. The return value from an MSS stored procedure is NOT an output parameter.
In Oracle a function MUST return a value whereas a procedure CANNOT return a value. The return statement in an Oracle stored procedure just returns control to its caller, not a value, while a function's return statement must return control and a value. Also, while both Oracle procedures and functions can pass OUT parameters, only functions can return a value. However, since the PROC-STATUS in Oracle is reserved for another use, the return value from a function, seen after the CLOSE STORED-PROC statement, look like just another output parameter.
4. PROC-STATUS in MSS is the stored procedure return value passed to the caller from the return statement. This is seen as the PROC-STATUS in MSS (as opposed to as another output value as seen from an Oracle function). The PROC-STATUS in MSS can be any data type that is also supported as a parameter to a stored procedure. The return value is part of the stored procedure's signature definition and is mapped like any other foreign data type into the schema definitions.
PROC-STATUS for Oracle is tied to a RAISE_APPLICATION_ERROR statement executed inside an Oracle procedure or function. This statement allows the developer to raise an exception and associate an error number and message with the procedure. So the application, rather than Oracle, assigns this error information. Oracle reserves error numbers -20000 to -20999 for user error codes while Oracle claims all the other codes. The error number generated by RAISE_APPLICATION_ERROR is the value that is passed back to the PROC-STATUS after CLOSE STORED-PROC is executed. The PROC-STATUS in Oracle is always a numeric value and is not part of the signature defined t.o the Oracle procedure or function.
So, for example:
RAISE_APPLICATION_ERROR(-20000,'Error in change_password: ' || SQLERRM);
produces PROC-STATUS of "-20000". The error message part can be blank or can contain actual error message text. Both the PROC-STATUS and the text can only be obtained after CLOSE STORED-PROC.
Following is an example for demonstration purpose:
Oracle stored procedure
CREATE or replace PROCEDURE pcust (num IN INT, orders OUT INT, states OUT INT)
AS BEGIN
IF num IS NULL THEN
raise_application_error (-20101, 'Cust Num is missing');
ELSE
SELECT COUNT (*) INTO orders FROM customer, order_
WHERE customer.Cust_num = order_.Cust_num AND customer.Cust_num > num;
SELECT count(*) INTO states FROM customer WHERE cust_num > num;
END IF;
END;
=================
ABL program
DEFINE VARIABLE iStat AS INTEGER NO-UNDO.
DEF VAR ix AS INTEGER NO-UNDO.
RUN STORED-PROC pcust
(PARAM num = ?, OUTPUT PARAM orders = 0, OUTPUT PARAM states = 0).
CLOSE STORED-PROC pcust iStat = PROC-STATUS.
IF ERROR-STATUS:ERROR THEN DO:
DO ix = 1 TO ERROR-STATUS:NUM-MESSAGES:
MESSAGE "error" ERROR-STATUS:GET-NUMBER(ix)
ERROR-STATUS:GET-MESSAGE(ix).
END.
IF iStat = 0 THEN
DISPLAY pcust.orders pcust.states.
ELSE
DISPLAY iStat.
END.
===========
When the ABL program is run, the iStat (proc-status) returns -20101, and first error-status message is
2307 ORACLE raise_application_error called with status -20101 (2307)
1502 cust num is missing
Note: If the stored-proc calls RAISE_APPLICATION_ERROR then upon CLOSE STORED-PROC, that error will be raised on the client, which you can suppress and test for by using the PROC-STATUS option, but in that case, since the procedure raises error, do not expect the output parameter to have a value.
.