1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to increase string length from max size 32767

Discussion in 'SQL PL/SQL' started by sneha.bharti, Dec 17, 2014.

  1. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Hi All,

    I am writing a procedure in which string length is going beyond the maximum limit 32767,how can I increase them.

    please help
    Thanks in advance
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    HI,

    Instead of increasing, You can split that string and concat it again...

    Let see any other have solution to increase?
     
    sneha.bharti likes this.
  3. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Thanks mr. Jagadekara
    But the problem is my requirement is such that i can not split the string.
    please help
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    If possible,
    Can you post the script.. At least provide sample code, so that we can understand your issue better...
     
  5. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    I am using this script to send mail
    utl_smtp.Data(v_Mail_Conn,
    'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
    'From: ' ||v_sender || crlf ||
    'Subject: '|| v_Subject || crlf ||
    'To: ' || to_mail || crlf ||
    'CC: ' ||cc_mail || crlf ||
    'BCC: ' ||bcc_mail|| crlf||



    'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
    'Content-Type: multipart/mixed;'|| crlf ||
    ' boundary="-----SECBOUND"'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/html;'|| crlf ||
    'Content-Transfer_Encoding: 7bit'|| crlf ||
    crlf ||
    MSG||crlf||crlf||
    '-------SECBOUND--'
    );

    in this the "MSG" is exceeding the maximum limit.
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Which data type u used for MSG?

    And how it is populating data?
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to use a CLOB, not a VARCHAR2. There is no way to extend the maximum number of characters a VARCHAR2 can contain in PL/sQL.
     
    sneha.bharti likes this.
  8. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Thank you all for your replies..

    although i could not find the way to increase the characters but I used Clob data type and along with that I used dbms_lob.append(String1,String2) for concatenating strings and converting into one string.