Sunday, April 27, 2014

Toad how to test Stored Procedure with return values (how to see result)

if you have stored procedure return single or multiple values separately not in cursor you can't see the stored Procedure Result

follow the following steps
1- right click on you stored procedure that returns single values as output parameters
2-Click Execute Procedure
3-Copy Code Generated in execute window and paste it to new Editor
4-Right Click On Script Output panel and check DBMS output

5-after Execute Line write the flowing
 DBMS_OUTPUT.PUT_LINE('DESCRIPTION TEXT ' || TO_CHAR(YOUR_OUTPUT_VAR));
EXAMPLE:
DECLARE   P_SEGELNO VARCHAR2(200);  P_ORDERNO VARCHAR2(200);  P_ORDDATEH VARCHAR2(200);  P_BEGDATEH VARCHAR2(200);  P_PERIOD NUMBER;  P_ENDDATEH VARCHAR2(200);  P_EMP_NAME VARCHAR2(200);  P_MESSAGE_TEXT VARCHAR2(200);
BEGIN   P_SEGELNO := '1073286849';  P_ORDERNO := NULL;  P_ORDDATEH := NULL;  P_BEGDATEH := NULL;  P_PERIOD := NULL;  P_ENDDATEH := NULL;  P_EMP_NAME := NULL;  P_MESSAGE_TEXT := NULL;
  SHR.NET_EMP_MULTI.NOT_ALLOW_EXTEND ( P_SEGELNO, P_ORDERNO, P_ORDDATEH, P_BEGDATEH, P_PERIOD, P_ENDDATEH, P_EMP_NAME, P_MESSAGE_TEXT );    DBMS_OUTPUT.PUT_LINE('ORDER NUMBER = ' || TO_CHAR(P_ORDERNO));     DBMS_OUTPUT.PUT_LINE('ORDER DATE = ' || TO_CHAR(P_ORDDATEH));    DBMS_OUTPUT.PUT_LINE('ORDER PERIOD = ' || TO_CHAR(P_PERIOD));    DBMS_OUTPUT.PUT_LINE('ORDER ENDDATEH = ' || TO_CHAR(P_ENDDATEH));    DBMS_OUTPUT.PUT_LINE('EMP NAME = ' || TO_CHAR(P_EMP_NAME));    DBMS_OUTPUT.PUT_LINE('EMP NAME = ' || TO_CHAR(P_MESSAGE_TEXT));      COMMIT; END; 

6-Click on DBMS or Press Keyboard ALT_CTR_D
you should see your result


No comments:

Post a Comment