Testing a Stored Procedure with Return Parameters 2

[THoey]

I wrote a stored procedure that requires several parameters and then loads a record into a table. The procedure is supposed to return a 1 if the load was succesfull and a 0 if it failed. The code is here:

CREATE OR REPLACE  PROCEDURE INSERT_CALL_RECORD   (
    INPUT_TN              IN  VARCHAR2,
    INPUT_WTN             IN  VARCHAR2,
    INPUT_BTN             IN  VARCHAR2,
    INPUT_DATEOFCALL      IN  VARCHAR2,
    INPUT_CUSTLEVEL       IN  VARCHAR2,
    INPUT_LANGUAGE        IN  VARCHAR2,
    INPUT_CALLDISPOSITION IN  VARCHAR2,
    INPUT_STATE           IN  VARCHAR2,
    INPUT_IVR_APPNAME     IN  VARCHAR2,
    OUTPUT_VALUE          OUT NUMBER)
AS
   current_TN              VARCHAR2(10);
   current_WTN             VARCHAR2(10);
   current_BTN             VARCHAR2(10);
   current_DATEOFCALL      DATE;
   current_CustLevel       VARCHAR2(12);
   current_Language        VARCHAR2( 3);
   current_CALLDISPOSITION VARCHAR2(10);
   current_State           VARCHAR2( 3);
   current_IVR_APPNAME     VARCHAR2(10);

   success_value  NUMBER := 1;
   failure_value  NUMBER := 0;
BEGIN

   -- *****************************************************
   -- * Test to see if BTN and WTN Values were passed in. *
   -- * If BTN or WTN doesn't exist, search on TN.        *
   -- *****************************************************
   current_TN := INPUT_TN;

   IF INPUT_BTN IS NULL THEN
      current_BTN := INPUT_TN;
   ELSE
      current_BTN := INPUT_BTN;
   END IF;

   IF INPUT_WTN IS NULL THEN
      current_WTN := INPUT_TN;
   ELSE
      current_WTN := INPUT_WTN;
   END IF;

   -- ***********************************************
   -- * Test to see if other values were passed in. *
   -- ***********************************************
   IF INPUT_DATEOFCALL IS NULL THEN
      current_DATEOFCALL := SYSDATE;
   ELSE
      current_DATEOFCALL := TO_DATE(current_DATEOFCALL, 'YYYYMMDDHH24MISS');
   END IF;

   IF INPUT_CUSTLEVEL IS NULL OR INPUT_CUSTLEVEL = '' THEN
      current_CUSTLEVEL := 'UNMARKED';
   ELSE
      current_CUSTLEVEL := UPPER(INPUT_CUSTLEVEL);
   END IF;

   IF INPUT_LANGUAGE IS NULL THEN
      current_Language := 'ENG';
   ELSE
      current_Language := UPPER(INPUT_LANGUAGE);
   END IF;

   IF INPUT_CALLDISPOSITION IS NULL THEN
      current_CALLDISPOSITION := 'XFER';
   ELSE
      current_CALLDISPOSITION := UPPER(INPUT_CALLDISPOSITION);
   END IF;

   IF INPUT_STATE IS NULL THEN
      current_State := 'TX';
   ELSE
      current_State := UPPER(INPUT_STATE);
   END IF;

   IF INPUT_IVR_APPNAME IS NULL THEN
      current_IVR_APPNAME := 'NONE';
   ELSE
      current_IVR_APPNAME := UPPER(INPUT_IVR_APPNAME);
   END IF;

   -- ****************
   -- * Insert data. *
   -- ****************
   INSERT INTO ALL_CALLS(TN, WTN, BTN, DATEOFCALL, CUSTLEVEL, LANGUAGE,
               CALLDISPOSITION, STATE, IVR_APPNAME)
   VALUES (current_TN, current_WTN, current_BTN, current_DATEOFCALL,
           current_CUSTLEVEL, current_LANGUAGE, current_CALLDISPOSITION,
           current_STATE, current_IVR_APPNAME);
   COMMIT;

   -- ******************
   -- * Return Results *
   -- ******************
   OUTPUT_VALUE := success_value;

EXCEPTION
   WHEN OTHERS THEN
      OUTPUT_VALUE := failure_value;
END;

My question is how can I test this through SQL*Plus or SQL*Worksheet? I gather I am going to need some sort of PL SQL block, but everytime I try, it doesn't work.

DECLARE
   retval number(10,0);
BEGIN
   exec insert_call_record('0123456789',
                           '0123456789',
                           '0123456789',
                           '20050311141126',
                           'VAL',
                           'ENG',
                           'XFER',
                           'TX',
                           'ACR',
                           retval);
END;

How do I call that procedure and be able to see the value returned? I know it is something simple that I am missing...

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LKBrwnDBA]

Try this in SQL*Plus:

var retval number;
exec insert_call_record('0123456789',
                        '0123456789',
                        '0123456789',
                        '20050311141126',
                        'VAL',
                        'ENG',
                        'XFER',
                        'TX',
                        'ACR',
                        :retval);
print :retval

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[lewisp]

In SQLPlus use:

SET SERVEROUTPUT ON

DECLARE
   retval number(10,0);
BEGIN
  insert_call_record('0123456789',
                     '0123456789',
                     '0123456789',
                     '20050311141126',
                     'VAL',
                     'ENG',
                     'XFER',
                     'TX',
                     'ACR',
                     retval);
  DBMS_OUTPUT.PUT_LINE(retval);
END;
/

 

[THoey]

Thanks for the quick responses. I'll give them both a try...

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LKBrwnDBA]

Or this in PL/SQL Block:

set serverout on size 1000000;
DECLARE
   retval number(10,0);
BEGIN
   insert_call_record('0123456789',
                      '0123456789',
                      '0123456789',
                      '20050311141126',
                      'VAL',
                      'ENG',
                      'XFER',
                      'TX',
                      'ACR',
                       retval);
   dbms_output.put_line('Return value is: '||retval);
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[THoey]

They both worked well. Thanks again. Lead me to find the error in the procedure code listed in my first post. I added some data validation last week and the following typo cause an error:

   IF INPUT_DATEOFCALL IS NULL THEN
    current_DATEOFCALL := SYSDATE;
   ELSE
    current_DATEOFCALL := TO_DATE([b]current_DATEOFCALL[/b],
                                  'YYYYMMDDHH24MISS');
   END IF;

The code should have been:

   IF INPUT_DATEOFCALL IS NULL THEN
    current_DATEOFCALL := SYSDATE;
   ELSE
    current_DATEOFCALL := TO_DATE([b]INPUT_DATEOFCALL[/b],
                                  'YYYYMMDDHH24MISS');
   END IF;

This was filling an undefined variable with an undefined variable and then trying to save that to a NOT NULL COLUMN.

Thanks again...

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...