title: Capture error using OUT parameter in Oracle's procedure
body: <p>I have to create procedure to insert row into table, also I need to include am OUT parameter that would capture any errors. However when I test using anonymous block it would not work, but if I use exception in procedure it is working. It means if I add an OUT parameter and I pass values from the block it won't work. </p>
  
  <p>This code works but is not what I want:</p>
  
  <pre><code>    create or replace 
      PROCEDURE EXAM_SP 
  (P_FIRSTNAME IN BB_SHOPPER.FIRSTNAME%TYPE,
   P_LASTNAME  IN BB_SHOPPER.LASTNAME%TYPE,
   P_ADDRESS IN BB_SHOPPER.ADDRESS%TYPE,
   P_CITY IN BB_SHOPPER.CITY%TYPE,
   P_STATE IN BB_SHOPPER.STATE%TYPE,
   P_ZIP IN BB_SHOPPER.ZIPCODE%TYPE)
  
  
  IS  
  
  BEGIN
  INSERT INTO BB_SHOPPER (IDSHOPPER, FIRSTNAME, LASTNAME, ADDRESS, CITY,   
  STATE, ZIPCODE)
  VALUES                   
  (BB_SHOPPER_IDSHOPPER_SEQ.NEXTVAL,P_FIRSTNAME,P_LASTNAME,P_ADDRESS, 
                          P_CITY, P_STATE,P_ZIP);
  
  EXCEPTION
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error Code = '||SQLCODE); 
  DBMS_OUTPUT.PUT_LINE('Error Message = Please check input');     
  END EXAM_SP;.   
  </code></pre>
  
  <p>But in code above if I name out parameter it won't work, not sure how to do this, if I add OUT parameter to procedure and pass values:</p>
  
  <pre><code>    create or replace 
      PROCEDURE EXAM_SP 
  (P_FIRSTNAME IN VARCHAR2,
   P_LASTNAME  IN VARCHAR2,
   P_ADDRESS IN VARCHAR2,
   P_CITY IN VARCHAR2,
   P_STATE IN CHAR,
   P_ZIP IN VARCHAR2,
   P_ERROR OUT VARCHAR2)......
  </code></pre>
  
  <p>I will receive this error</p>
  
  <pre><code>ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  </code></pre>
  
  <p>If I remove parameter and use original code on the beginning it works.</p>
  
  <p>Anonymous block that gets that error:</p>
  
  <pre><code>DECLARE
    LV_FIRSTNAME_TXT BB_SHOPPER.FIRSTNAME%TYPE := 'FIRST';
    LV_LASTNAME_TXT BB_SHOPPER.LASTNAME%TYPE := 'LAST';
    LV_ADDRESS_TXT BB_SHOPPER.ADDRESS%TYPE := '8899 TAPE PARK';
    LV_CITY_TXT BB_SHOPPER.CITY%TYPE := 'JACKSONVILLE';
    LV_STATE_TXT BB_SHOPPER.STATE%TYPE := 'FLd';
    LV_ZIP_NUMBER BB_SHOPPER.ZIPCODE%TYPE := '34567';
    LV_ERROR varchar2(100);
  
  BEGIN 
    EXAM_SP(LV_FIRSTNAME_TXT, LV_LASTNAME_TXT, LV_ADDRESS_TXT,      
      LV_CITY_TXT,LV_STATE_TXT,LV_ZIP_NUMBER);
  
      DBMS_OUTPUT.PUT_LINE(LV_ERROR);
  END;
  </code></pre>
  
  <p>Procedure:</p>
  
  <pre><code>  create or replace 
    PROCEDURE EXAM_SP 
  (P_FIRSTNAME IN VARCHAR2,
   P_LASTNAME  IN VARCHAR2,
   P_ADDRESS IN VARCHAR2,
   P_CITY IN VARCHAR2,
   P_STATE IN CHAR,
   P_ZIP IN VARCHAR2,
   P_ERROR OUT VARCHAR2)
  
  
  
  IS  
  
  BEGIN
    INSERT INTO BB_SHOPPER (IDSHOPPER, FIRSTNAME, LASTNAME, ADDRESS, CITY, 
                          STATE, ZIPCODE)
    VALUES                  
           (BB_SHOPPER_IDSHOPPER_SEQ.NEXTVAL,P_FIRSTNAME,P_LASTNAME,P_ADDRESS, 
                          P_CITY, P_STATE,P_ZIP);
  
  EXCEPTION
    WHEN OTHERS THEN
    P_ERROR := SQLCODE;
  
  /*DBMS_OUTPUT.PUT_LINE('Error Code = '||SQLCODE); 
  DBMS_OUTPUT.PUT_LINE('Error Message = Please check input'); 
  P_ERROR := SQLCODE;*/
  
  END EXAM_SP;
  </code></pre>
  
  <p>Error message that I am receiving:</p>
  
  <pre><code>Error report:
  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
  ORA-06512: at line 6
  06502. 00000 -  "PL/SQL: numeric or value error%s"
  </code></pre>
  
  <p>I have passed variable <code>'FLd'</code> to procedure parameter in order to test error capture.  </p>
  
  <p>Table structure:</p>
  
  <pre><code>IDSHOPPER   NUMBER(4,0)
  FIRSTNAME   VARCHAR2(15 BYTE)
  LASTNAME    VARCHAR2(20 BYTE)
  ADDRESS VARCHAR2(40 BYTE)
  CITY    VARCHAR2(20 BYTE)
  STATE   CHAR(2 BYTE)
  ZIPCODE VARCHAR2(15 BYTE)
  PHONE   VARCHAR2(10 BYTE)
  FAX VARCHAR2(10 BYTE)
  EMAIL   VARCHAR2(25 BYTE)
  USERNAME    VARCHAR2(8 BYTE)
  PASSWORD    VARCHAR2(8 BYTE)
  COOKIE  NUMBER(4,0)
  DTENTERED   DATE
  PROVINCE    VARCHAR2(15 BYTE)
  COUNTRY VARCHAR2(15 BYTE)
  ...
  </code></pre>