1 Reply Latest reply: Nov 21, 2012 5:29 AM by Ora RSS

    Ouput parameter does not accept formatted column value

    Mikail
      Here's my table structure:

      <h4><font color="Blue">CREATE TABLE "SSPFUSER05"."PERSONS"
      (     SSN NUMBER(13,0),
           PIN VARCHAR2(7 BYTE)
      );</font>
      </h4>

      And here's my procedure below:

      <h4><font color="Blue">
      CREATE OR REPLACE
      PROCEDURE SP_SSN_BY_PIN(
      V_REQUESTEDPIN IN VARCHAR2,
      V_SSN OUT CHAR)
      AS
      BEGIN
      SELECT
      LPAD(SSN,13,'0')
      INTO
      V_SSN
      FROM
      SSPFUSER05.PERSONS
      WHERE
      PIN=V_REQUESTEDPIN;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      V_SSN:=NULL;
      END;</font></h4>

      As you can see the type of the SSN column is NUMBER(13,0). But I leftpad it with 0 and assign it to my output paramter V_SSN, whose type is CHAR. But I get 111196100099 instead of 0111196100099. I've tried TO_CHAR(LPAD(SSN,13,'0')) but still doesn't help. However, if I return the left padded SSN inside a SYS_REFCURSOR I get what I want. What should I do to achieve this?
        • 1. Re: Ouput parameter does not accept formatted column value
          Ora
          Depends..
          SQL> CREATE TABLE PERSONS
            2  ( SSN NUMBER(13,0),
            3  PIN VARCHAR2(7 BYTE)
            4  );
          
          Table created.
          
          SQL> CREATE OR REPLACE
            2  PROCEDURE SP_SSN_BY_PIN(
            3  V_REQUESTEDPIN IN VARCHAR2,
            4  V_SSN OUT CHAR)
            5  AS
            6  BEGIN
            7  SELECT
            8  LPAD(SSN,13,'0')
            9  INTO
           10  V_SSN
           11  FROM
           12  PERSONS
           13  WHERE
           14  PIN=V_REQUESTEDPIN;
           15  EXCEPTION
           16  WHEN NO_DATA_FOUND THEN
           17  V_SSN:=NULL;
           18  END;
           19  /
          
          Procedure created.
          
          
          SQL> insert into PERSONS values(123,'abc');
          
          1 row created.
          
          SQL> commit;
          
          Commit complete.
          
          SQL> declare
            2  v_temp_1 char(100);
            3  v_temp_2 number;
            4  begin
            5  SP_SSN_BY_PIN('abc',v_temp_1);
            6  SP_SSN_BY_PIN('abc',v_temp_2);
            7  dbms_output.put_line(v_temp_1);
            8  dbms_output.put_line(v_temp_2);
            9  end;
           10  /
          0000000000123
          123
          
          PL/SQL procedure successfully completed.
          
          SQL>