9 Replies Latest reply: Nov 18, 2008 3:23 AM by BluShadow RSS

    Maximum VARCHAR2 length in stored procedure parameter

    InoL
      DB version EE 9.2.0.6 on Win2K.

      I get error "character string buffer too small" when running procedure in a package. Simplified (there are actually more parameters in the procedure call):
      procedure x(p_var   in out varchar2)
        v_local   varchar2(4000)
      begin
        v_local := 'a very long string .....';
        p_var := v_local;    -- Line giving error
      end;
      It works OK if I limit the output to 1000 characters (1001 is already too long):
      procedure x(p_var   in out varchar2)
        v_local   varchar2(4000)
      begin
        v_local := 'a very long string .....';
        p_var := substr(v_local,1,1000);
      end;
      I never heard of a limit of 1000 characters for a varchar2 procedure parameter, though. Any ideas?
        • 1. Re: Maximum VARCHAR2 length in stored procedure parameter
          Nicolas.Gasparotto
          It's work fine for me :
          SQL> create or replace procedure x(p_var   in out varchar2) is
            2    v_local   varchar2(4000);
            3  begin
            4    for cur in (select object_name from user_objects) loop
            5        v_local := v_local||cur.object_name||' ';
            6        if length(v_local) > 2000 then
            7           exit;
            8        end if;
            9    end loop;
          10    p_var := v_local;    -- Line giving error
          11  end;
          12  /

          Procedure created.

          SQL> var p_var varchar2(4000);
          SQL> exec x(:p_var)

          PL/SQL procedure successfully completed.

          SQL> select p,length(p) from test2;

          ASYNCREPLYVERVW PS#SCC_SUSPNSE_NSI PS#SCC_TRIBE_LANG PS#SCC_TRIBE_TBL PS#SSR_ACD
          PLNAUS PS#SSR_ACD_PRG_AUS PS#SSR_ACD_PRG_NZL PS#SSR_AOU_CD_LANG PS#SSR_AOU_CD_
          TBL PS#SSR_CNTRY2_AUS PS#SSR_CNTRY2_LNG PS#SSR_CRSCLASF_LN PS#SSR_CRSE_CLASIF PS
          #SSR_CRS_OFR_AUS PS#SSR_DEST355_LNG PS#SSR_DEST358_LNG PS#SSR_DEST_355 PS#SSR_DE
          ST_358 PS#SSR_DSCP_CD_LNG PS#SSR_DSCP_CD_TBL PS#SSR_EFTSU_LANG PS#SSR_EFTSU_TBL
          PS#SSR_ELEARN_LANG PS#SSR_ELEARN_TBL PS#SSR_ETHNCTY_NZL PS#SSR_ETHNIC_MAP PS#SSR
          ETHNNZL_LN PS#SSR_FOE_LANG PS#SSR_FOE_TBL PS#SSR_FOS_LANG PS#SSR_FOS_TBL PS#SS
          R_FUNDING_CAT PS#SSR_FUND_CAT_LN PS#SSR_GRADE_FLAG PS#SSR_GRD_FLAG_LN PS#SSR_INS
          DEST_LNG PS#SSR_INSTIT_NZL PS#SSR_INST_DEST PS#SSR_LANG2_DEST PS#SSR_LNG2DEST_LN
          PS#SSR_MOE_SUBJECT PS#SSR_MOE_SUBJ_LN PS#SSR_NZSCDBRD_LN PS#SSR_NZSCDDET_LN PS#
          SSR_NZSCDNAR_LN PS#SSR_NZSCED_BRD PS#SSR_NZSCED_DET PS#SSR_NZSCED_NAR PS#SSR_NZV
          CCDGR_LN PS#SSR_NZVCCSBJ_LN PS#SSR_NZVCC_DEGR PS#SSR_NZVCC_SUBJ PS#SSR_PRG_CD_LN
          G PS#SSR_PRG_CD_TBL PS#SSR_PRG_TYP_LNG PS#SSR_PRG_TYP_TBL PS0AAP_ETHNIC_PMPT PS0
          AAP_TBL_LNG PS0AA_OVERRIDE PS0AA_OVERRIDE_LNG PS0ABSW_TMPL_TBL PS0ABSW_T_TBL_LAN
          G PS0ABS_CLASS_TBL PS0ABS_CL_TBL_LANG PS0ABS_TYPE_TBL PS0ABS_TY_TBL_LANG PS0ACAD
          CALLANG PS0ACAD_CAL_TABLE PS0ACAD_CAR_LANG PS0ACAD_CAR_TBL PS0ACAD_GROUP_LANG
          PS0ACAD_GROUP_TBL PS0ACAD_ORG_LANG PS0ACAD_ORG_TBL PS0ACAD_PLAN_LANG PS0ACAD_PLA
          N_TBL PS0ACAD_PROG_LANG PS0ACAD_PROG_TBL PS0ACAD_SUBPLN_LNG PS0ACAD_SUBPLN_TBL P
          S0ACCDNT_TYPE_LNG PS0ACCDNT_TYPE_TBL PS0ACCDT_INS_TBL PS0ACCOMP_TBL PS0ACCOMP_TB
          L_LANG PS0ACCOM_TYPE_LANG PS0ACCOM_TYPE_TBL PS0ACCT_CD_TBL PS0ACCT_CD_TBL_LNG PS
          0ACCT_CD_TBL_OLD PS0ACCT_FMLY_TBL PS0ACCT_TYP_TBL_SF PS0ACD_STAT_TP_LNG PS0ACD_S
          TAT_TP_TBL PS0ACTION_TBL PS0ACTN_REASON_TBL PS0ACTN_RSN_LANG PS0ACT_FML_TL_LNG P
          S0ADA_COUNTRY_TBL PS0ADDRESS_TYP_LNG PS0ADDRESS_TYP_TBL PS0ADDR_USAGE_LANG PS0AD
          DR_USAGE_TBL PS0ADJ_CODE_TBL PS0ADJ_CODE_TBLLNG PS0ADMIT_TYPE_LNG PS0ADMIT_TYPE_
          TBL PS0ADM_ACTION_TBL PS0ADM_APPLCTR_LNG PS0ADM_APPLCTR_TBL PS0ADM_APPL_DATA PS0
          ADM_APPL_PROG
                2014 <== More than 1000



          SQL> select * from v$version where rownum = 1;

          BANNER
          ----------------------------------------------------------------
          Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

          SQL>
          Nicolas.
          • 2. Re: Maximum VARCHAR2 length in stored procedure parameter
            27876
            Well, given the fact that your p_var is an OUT parameter, the caller will be responsible to allocate appropriate size for this parameter.
            How is this procedure called from the client? which client calls this procedure? how is this client declaring this parameter and of what size?

            Check the client code that calls this procedure to fix the problem.
            SQL> create or replace procedure x(p_var   in out varchar2) is
              2    v_local   varchar2(4000) ;
              3  begin
              4    v_local := RPAD('a very long string .....', 4000, '*');
              5    p_var := v_local;    -- Line giving error
              6  end;
              7  /

            Procedure created.

            SQL> show errors
            No errors.
            SQL> declare
              2    l_var varchar2(4000) ;
              3  begin
              4    x(l_var) ;
              5    dbms_output.put_line('length='||length(l_var)) ;
              6  end ;
              7  /
            length=4000

            PL/SQL procedure successfully completed.

            SQL>
            PS: Nicolas, your reply was not visible when I started to reply.

            Message was edited by:
            Kamal Kishore
            • 3. Re: Maximum VARCHAR2 length in stored procedure parameter
              383571
              A varchar2 parameter in PL/SQL code can go upto 32767

              If you define a column in a table with datatype varchar2, then the max size is 4000


              Shakti
              http://www.impact-sol.com
              Developers of Guggi Oracle - Tool for DBAs ande Developers
              • 4. Re: Maximum VARCHAR2 length in stored procedure parameter
                Nicolas.Gasparotto
                Hey Kamal, your demonstration is more elegant... ;-) ...RPAD usage...

                Nicolas.
                • 5. Re: Maximum VARCHAR2 length in stored procedure parameter
                  InoL
                  It was indeed the calling procedure (a Form) that was the problem. The error, however, was raised in the stored procedure, not the form. A bit confusing, as you can see here:
                  PROD@ONTW> create or replace procedure x(p_var   in out varchar2) is
                    2      v_local   varchar2(4000) ;
                    3  begin
                    4      v_local := RPAD('a very long string .....', 4000, '*');
                    5      p_var := v_local;    -- Line giving error
                    6  end;
                    7  /
                  
                  Procedure created.
                  
                  PROD@ONTW> declare
                    2      l_var varchar2(1000) ;
                    3  begin
                    4      x(l_var) ;
                    5      dbms_output.put_line('length='||length(l_var)) ;
                    6  end ;
                    7  /
                  declare
                  *
                  ERROR at line 1:
                  ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                  ORA-06512: at "PROD.X", line 5
                  ORA-06512: at line 4
                  Line 5 of procedure x produces the error, not line 4 of the anonymous procedure.
                  • 6. Re: Maximum VARCHAR2 length in stored procedure parameter
                    27876
                    Line 5 of procedure x produces the error, not line 4 of the anonymous procedure.
                    Well, the error message does say it correctly:
                    ORA-06512: at line 4
                    That is your line 4 of anonymous block that is included in the error message. That information is indeed there in the entire error stack.
                    The error sure happened at line 5 in procedure "X" since that is where the database found out that value you are putting is larger than the size specified by the caller. It could not have been done any sooner.
                    • 7. Re: Maximum VARCHAR2 length in stored procedure parameter
                      InoL
                      Kamal,

                      With your previous explanation of the behaviour of an IN OUT parameter, it all sounds very logical now.

                      Thx

                      Ino
                      • 8. Re: Maximum VARCHAR2 length in stored procedure parameter
                        671003
                        I have very similar problem.
                        I have declared procedure X with out varchar2 parameter, which i call from another procedure (Y) where varchar2 has row based declaration.
                        How can I determine length of that paremeter in X procedure? I want to use substr f.x.

                        Procedure X (par1 out varchar2) is
                        begin
                        select substr(col1,0,???) into par1 from some_table;
                        end;

                        Procedure Y ()
                        var1 some_another_table%rowtype;
                        begin
                        X(var1.some_field);
                        end;

                        Pawel
                        • 9. Re: Maximum VARCHAR2 length in stored procedure parameter
                          BluShadow
                          user2522317 wrote:
                          I have very similar problem.
                          I have declared procedure X with out varchar2 parameter, which i call from another procedure (Y) where varchar2 has row based declaration.
                          How can I determine length of that paremeter in X procedure? I want to use substr f.x.

                          Procedure X (par1 out varchar2) is
                          begin
                          select substr(col1,0,???) into par1 from some_table;
                          end;

                          Procedure Y ()
                          var1 some_another_table%rowtype;
                          begin
                          X(var1.some_field);
                          end;

                          Pawel
                          Well there's no simple way of determining what the maximum size "par1" will take at runtime as it's determined by whatever chooses to call it. The only ways I can think would be either

                          a) put an exception handler around the assignment to that variable/parameter so that you capture the error and handle it
                          b) pass in another parameter which specifies the name of the source table and column, so that the procedure can look up the maximum size from the data dictionary (e.g. user_tab_cols)

                          In reality your design should be tight enough that you don't actually have procedures trying to assign values that are too large for the passed in parameters. Either your procedure should know the smallest size required and work within that or the database design should cater for the largest values.