This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jan 9, 2013 3:07 AM by Purvesh K RSS

big string issue.

913578 Newbie
Currently Being Moderated
Hi,

declare
v_temp varchar2(32000):='string of 32000 characters...';
v_temp1 varchar2(4000);
begin

v_temp1:=substr(v_temp,1,4000);
dbms_output.put_line(v_temp1);

end;



SP2-0027: Input is too long (> 2499 characters) - line ignored

can you please suggest i want to cut/limit the big 32000 chars string to 4000.

Please help me.

Thanks,
Vinod
  • 1. Re: big string issue.
    983451 Newbie
    Currently Being Moderated
    --
    Try this:

    DECLARE
    v_temp    CLOB := 'string of 32000 characters...';
    v_temp1   VARCHAR2 (4000);
    BEGIN
    v_temp1 := DBMS_LOB.SUBSTR (v_temp,1, 4000);
    DBMS_OUTPUT.put_line (v_temp1);
    END;
    SET SERVEROUTPUT ON SIZE 1000000
    
    DECLARE
       v_temp    CLOB
          DEFAULT '<saml2:Assertion xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion" Version="2.0" ID="_BABF2F22371D97B9F71357712527314" IssueInstant="2013-01-09T06:22:07.312Z"><saml2:Issuer>SAAS_GLOBAL</saml2:Issuer><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:CanonicalizationMethod><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"></ds:SignatureMethod><ds:Reference URI="#_BABF2F22371D97B9F71357712527314"><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"></ds:Transform><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"></ds:DigestMethod><ds:DigestValue>VBpMH54ImZuypTdMrGF2FmPMCJE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>O94fIestC14aa3+JKSiL4MRAnPuw4dOiCWQg2DrH9u740wRQJazz6JjSJiGvYS5Im7MBbUXNowudcbNIh950bNyVq3g3L9FFi/wPYhOmx6inyF69ROAxCanIRT9jDghzmYnuUzuKVUq+8+wBrRo0qMTBmdAuTdZytJhqZupti3zlkq6bVnIAMTKnvrlRr1PDLzvtat+evZD6QkWdMs+i6hgVa2UQSUdvsnuRpUIRhTckie4/IL+fvgnsPDa9bKYBbc+GpKiFCr7QdS6Nb+/kPc2t2DF19NbO1O0BU0Qa/LtiycdD572vE6yQpY6qSy4MJkyZGSPYRVIhQmV3gBr7sw==</ds:SignatureValue><ds:KeyInfo><ds:X509Data><ds:X509Certificate>MIIDuDCCAqCgAwIBAgIEUOvUpTANBgkqhkiG9w0BAQUFADCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwHhcNMTMwMTA4MDgxMTE3WhcNMjMwMTA2MDgxMTE3WjCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDHQecDk5If+jbTgOy2NL83CiRJtoLPjp70T1hIqS9peOe8bShAaTyfnwqpxmPdHEopSbSygx3IhFe/YXqZK6EK9LShkH5/oAQHPUKaESNBLvi7jvQaK4aoedYVzCu2Sk+tfVqIBYctZuPOYyf4TxQEkwbNNMlpaCTqKyNfdtrvkErDGdjyaOJyeNR9IonQ0B71dceHxUW9Y/BtrCTQUNqKvPG2TPKU+fJmzWmTUgKgkfb8p2s+FZcQ0EZ/W9eRUTcC8pekeMeiSCZhmrwAVIE9IFMZby7sSirpnsR0frnkkD6BAtpMNmAE99paWTs063YvwaHe29q06C/Kbq/g6ufdAgMBAAEwDQYJKoZIhvcNAQEFBQADggEBADU7oEdJOg/IMorekuXRK9Mb3xcoYOfIGpzvLFUljTnM3nGhgN1U7CvTahroyO2Plb6HIY4BLaU9v9QSaWdgoRik1C+UlSdTR/zXRbPQ3ZEZDC20dZqzNnDbyFLKNehnrk1P9Yv2OlwioJ8xJAx6coUq2K+pGm/8TxSxPHGaZz0WHBzU6kWpK2n1U9m6svqGWyLm5Dgkt3QfvIvhYPDATCd/UXXLrx5iGc0BxAR4Vh5yeWqG7E3FTN/2ZcbAnRxdWWF2D3ba7eayeqvLfnTpNA9fw384qq5kwGfJBAWhgIqU7I1PpDHJiGShP4gp1QE6t/GLNwZk6uy4nbGXKwIxZ+k=</ds:X509Certificate></ds:X509Data></ds:KeyInfo></ds:Signature><saml2:Subject><saml2:NameID>NAUTHENTICATED</saml2:NameID><saml2:SubjectConfirmation Method="urn:oasis:names:tc:SAML:2.0:cm:bearer"></saml2:SubjectConfirmation></saml2:Subject><saml2:Conditions NotBefore="2013-01-09T06:22:07.312Z" NotOnOrAfter="2013-01-09T07:22:07.312Z"></saml2:Conditions><saml2:AttributeStatement><saml2:Attribute Name="OriginalApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute><saml2:Attribute Name="DestinationApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute></saml2:AttributeStatement></saml2:Assertion>';
    
       v_temp1   VARCHAR2 (4000);
    BEGIN
       v_temp1 :=DBMS_LOB.SUBSTR (v_temp, 4000);
    
       DBMS_OUTPUT.put_line (v_temp1);
    EXCEPTION
       WHEN OTHERS THEN
          RAISE;
    END;
    Cheers,
    Manik.

    Edited by: corrected mistake in code
  • 2. Re: big string issue.
    Bawer Journeyer
    Currently Being Moderated
    910575 wrote:
    can you please suggest i want to cut/limit the big 32000 chars string to 4000.
    you do this already with:
    v_temp1:=substr(v_temp,1,4000);
    910575 wrote:SP2-0027: Input is too long (> 2499 characters) - line ignored
    this is not oracle Exception

    try to cut it to 2499
  • 3. Re: big string issue.
    913578 Newbie
    Currently Being Moderated
    still getting the same error.


    SP2-0027: Input is too long (> 2499 characters) - line ignored
  • 4. Re: big string issue.
    983451 Newbie
    Currently Being Moderated
    what is your input string.. does it have some special characters???

    Similar issue addressed here.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4789399600346029472

    Cheers,
    Manik.
  • 5. Re: big string issue.
    Nicosa Expert
    Currently Being Moderated
    Hi,

    Please stop multiposting, it's considered rude.

    Theses are but the same question :
    {message:id=10782925}
    {message:id=10782918}
    {message:id=10782863}
  • 6. Re: big string issue.
    913578 Newbie
    Currently Being Moderated
    DECLARE

    v_temp CLOB := '<saml2:Assertion xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion" Version="2.0" ID="_BABF2F22371D97B9F71357712527314" IssueInstant="2013-01-09T06:22:07.312Z"><saml2:Issuer>SAAS_GLOBAL</saml2:Issuer><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:CanonicalizationMethod><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"></ds:SignatureMethod><ds:Reference URI="#_BABF2F22371D97B9F71357712527314"><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"></ds:Transform><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"></ds:DigestMethod><ds:DigestValue>VBpMH54ImZuypTdMrGF2FmPMCJE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>O94fIestC14aa3+JKSiL4MRAnPuw4dOiCWQg2DrH9u740wRQJazz6JjSJiGvYS5Im7MBbUXNowudcbNIh950bNyVq3g3L9FFi/wPYhOmx6inyF69ROAxCanIRT9jDghzmYnuUzuKVUq+8+wBrRo0qMTBmdAuTdZytJhqZupti3zlkq6bVnIAMTKnvrlRr1PDLzvtat+evZD6QkWdMs+i6hgVa2UQSUdvsnuRpUIRhTckie4/IL+fvgnsPDa9bKYBbc+GpKiFCr7QdS6Nb+/kPc2t2DF19NbO1O0BU0Qa/LtiycdD572vE6yQpY6qSy4MJkyZGSPYRVIhQmV3gBr7sw==</ds:SignatureValue><ds:KeyInfo><ds:X509Data><ds:X509Certificate>MIIDuDCCAqCgAwIBAgIEUOvUpTANBgkqhkiG9w0BAQUFADCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwHhcNMTMwMTA4MDgxMTE3WhcNMjMwMTA2MDgxMTE3WjCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDHQecDk5If+jbTgOy2NL83CiRJtoLPjp70T1hIqS9peOe8bShAaTyfnwqpxmPdHEopSbSygx3IhFe/YXqZK6EK9LShkH5/oAQHPUKaESNBLvi7jvQaK4aoedYVzCu2Sk+tfVqIBYctZuPOYyf4TxQEkwbNNMlpaCTqKyNfdtrvkErDGdjyaOJyeNR9IonQ0B71dceHxUW9Y/BtrCTQUNqKvPG2TPKU+fJmzWmTUgKgkfb8p2s+FZcQ0EZ/W9eRUTcC8pekeMeiSCZhmrwAVIE9IFMZby7sSirpnsR0frnkkD6BAtpMNmAE99paWTs063YvwaHe29q06C/Kbq/g6ufdAgMBAAEwDQYJKoZIhvcNAQEFBQADggEBADU7oEdJOg/IMorekuXRK9Mb3xcoYOfIGpzvLFUljTnM3nGhgN1U7CvTahroyO2Plb6HIY4BLaU9v9QSaWdgoRik1C+UlSdTR/zXRbPQ3ZEZDC20dZqzNnDbyFLKNehnrk1P9Yv2OlwioJ8xJAx6coUq2K+pGm/8TxSxPHGaZz0WHBzU6kWpK2n1U9m6svqGWyLm5Dgkt3QfvIvhYPDATCd/UXXLrx5iGc0BxAR4Vh5yeWqG7E3FTN/2ZcbAnRxdWWF2D3ba7eayeqvLfnTpNA9fw384qq5kwGfJBAWhgIqU7I1PpDHJiGShP4gp1QE6t/GLNwZk6uy4nbGXKwIxZ+k=</ds:X509Certificate></ds:X509Data></ds:KeyInfo></ds:Signature><saml2:Subject><saml2:NameID>NAUTHENTICATED</saml2:NameID><saml2:SubjectConfirmation Method="urn:oasis:names:tc:SAML:2.0:cm:bearer"></saml2:SubjectConfirmation></saml2:Subject><saml2:Conditions NotBefore="2013-01-09T06:22:07.312Z" NotOnOrAfter="2013-01-09T07:22:07.312Z"></saml2:Conditions><saml2:AttributeStatement><saml2:Attribute Name="OriginalApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute><saml2:Attribute Name="DestinationApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute></saml2:AttributeStatement></saml2:Assertion>';

    v_temp1 VARCHAR2 (4000);
    BEGIN
    v_temp1 := DBMS_LOB.SUBSTR (v_temp, 1, 4000);
    DBMS_OUTPUT.put_line (v_temp1);
    END;
  • 7. Re: big string issue.
    Purvesh K Guru
    Currently Being Moderated
    The issue you are facing is SQL Plus limitation that prevents a Single String to contain more than 2499 characters.

    Alternative is to Break the string into two or more parts and then concatenating them or to Insert a New line character in between the string.
    Original Code:-
    declare
     v_var varchar2(4000) := 'Long String over 2500 Characters';
    begin
      ...
    end;
    
    Modified:
    declare
     v_var varchar2(4000) := 'Long String with 2500 Characters';
     v_var1 varchar2(4000) := 'String after 2500 characters';
     v_final_var varchar2(4000);
    begin
      v_final_var := v_var || v_var1
      ...
    end;
  • 8. Re: big string issue.
    983451 Newbie
    Currently Being Moderated
    What version of Oracle do you use??

    I executed the below script in 11g r2 : the original script which you have: And I see the o/p correctly

    ------------------

    SET SERVEROUTPUT ON SIZE 1000000
    DECLARE
       v_temp    VARCHAR2 (32000)
          := '<saml2:Assertion xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion" Version="2.0" ID="_BABF2F22371D97B9F71357712527314" IssueInstant="2013-01-09T06:22:07.312Z"><saml2:Issuer>SAAS_GLOBAL</saml2:Issuer><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:CanonicalizationMethod><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"></ds:SignatureMethod><ds:Reference URI="#_BABF2F22371D97B9F71357712527314"><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"></ds:Transform><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"></ds:DigestMethod><ds:DigestValue>VBpMH54ImZuypTdMrGF2FmPMCJE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>O94fIestC14aa3+JKSiL4MRAnPuw4dOiCWQg2DrH9u740wRQJazz6JjSJiGvYS5Im7MBbUXNowudcbNIh950bNyVq3g3L9FFi/wPYhOmx6inyF69ROAxCanIRT9jDghzmYnuUzuKVUq+8+wBrRo0qMTBmdAuTdZytJhqZupti3zlkq6bVnIAMTKnvrlRr1PDLzvtat+evZD6QkWdMs+i6hgVa2UQSUdvsnuRpUIRhTckie4/IL+fvgnsPDa9bKYBbc+GpKiFCr7QdS6Nb+/kPc2t2DF19NbO1O0BU0Qa/LtiycdD572vE6yQpY6qSy4MJkyZGSPYRVIhQmV3gBr7sw==</ds:SignatureValue><ds:KeyInfo><ds:X509Data><ds:X509Certificate>MIIDuDCCAqCgAwIBAgIEUOvUpTANBgkqhkiG9w0BAQUFADCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwHhcNMTMwMTA4MDgxMTE3WhcNMjMwMTA2MDgxMTE3WjCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDHQecDk5If+jbTgOy2NL83CiRJtoLPjp70T1hIqS9peOe8bShAaTyfnwqpxmPdHEopSbSygx3IhFe/YXqZK6EK9LShkH5/oAQHPUKaESNBLvi7jvQaK4aoedYVzCu2Sk+tfVqIBYctZuPOYyf4TxQEkwbNNMlpaCTqKyNfdtrvkErDGdjyaOJyeNR9IonQ0B71dceHxUW9Y/BtrCTQUNqKvPG2TPKU+fJmzWmTUgKgkfb8p2s+FZcQ0EZ/W9eRUTcC8pekeMeiSCZhmrwAVIE9IFMZby7sSirpnsR0frnkkD6BAtpMNmAE99paWTs063YvwaHe29q06C/Kbq/g6ufdAgMBAAEwDQYJKoZIhvcNAQEFBQADggEBADU7oEdJOg/IMorekuXRK9Mb3xcoYOfIGpzvLFUljTnM3nGhgN1U7CvTahroyO2Plb6HIY4BLaU9v9QSaWdgoRik1C+UlSdTR/zXRbPQ3ZEZDC20dZqzNnDbyFLKNehnrk1P9Yv2OlwioJ8xJAx6coUq2K+pGm/8TxSxPHGaZz0WHBzU6kWpK2n1U9m6svqGWyLm5Dgkt3QfvIvhYPDATCd/UXXLrx5iGc0BxAR4Vh5yeWqG7E3FTN/2ZcbAnRxdWWF2D3ba7eayeqvLfnTpNA9fw384qq5kwGfJBAWhgIqU7I1PpDHJiGShP4gp1QE6t/GLNwZk6uy4nbGXKwIxZ+k=</ds:X509Certificate></ds:X509Data></ds:KeyInfo></ds:Signature><saml2:Subject><saml2:NameID>NAUTHENTICATED</saml2:NameID><saml2:SubjectConfirmation Method="urn:oasis:names:tc:SAML:2.0:cm:bearer"></saml2:SubjectConfirmation></saml2:Subject><saml2:Conditions NotBefore="2013-01-09T06:22:07.312Z" NotOnOrAfter="2013-01-09T07:22:07.312Z"></saml2:Conditions><saml2:AttributeStatement><saml2:Attribute Name="OriginalApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute><saml2:Attribute Name="DestinationApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute></saml2:AttributeStatement></saml2:Assertion>';
    
       v_temp1   VARCHAR2 (4000);
    BEGIN
       v_temp1 := SUBSTR (v_temp, 1, 4000);
       DBMS_OUTPUT.put_line (v_temp1);
    END;
    output:
    <saml2:Assertion xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion" Version="2.0" ID="_BABF2F22371D97B9F71357712527314" IssueInstant="2013-01-09T06:22:07.312Z"><saml2:Issuer>SAAS_GLOBAL</saml2:Issuer><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:CanonicalizationMethod><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"></ds:SignatureMethod><ds:Reference URI="#_BABF2F22371D97B9F71357712527314"><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"></ds:Transform><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"></ds:DigestMethod><ds:DigestValue>VBpMH54ImZuypTdMrGF2FmPMCJE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>O94fIestC14aa3+JKSiL4MRAnPuw4dOiCWQg2DrH9u740wRQJazz6JjSJiGvYS5Im7MBbUXNowudcbNIh950bNyVq3g3L9FFi/wPYhOmx6inyF69ROAxCanIRT9jDghzmYnuUzuKVUq+8+wBrRo0qMTBmdAuTdZytJhqZupti3zlkq6bVnIAMTKnvrlRr1PDLzvtat+evZD6QkWdMs+i6hgVa2UQSUdvsnuRpUIRhTckie4/IL+fvgnsPDa9bKYBbc+GpKiFCr7QdS6Nb+/kPc2t2DF19NbO1O0BU0Qa/LtiycdD572vE6yQpY6qSy4MJkyZGSPYRVIhQmV3gBr7sw==</ds:SignatureValue><ds:KeyInfo><ds:X509Data><ds:X509Certificate>MIIDuDCCAqCgAwIBAgIEUOvUpTANBgkqhkiG9w0BAQUFADCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwHhcNMTMwMTA4MDgxMTE3WhcNMjMwMTA2MDgxMTE3WjCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDHQecDk5If+jbTgOy2NL83CiRJtoLPjp70T1hIqS9peOe8bShAaTyfnwqpxmPdHEopSbSygx3IhFe/YXqZK6EK9LShkH5/oAQHPUKaESNBLvi7jvQaK4aoedYVzCu2Sk+tfVqIBYctZuPOYyf4TxQEkwbNNMlpaCTqKyNfdtrvkErDGdjyaOJyeNR9IonQ0B71dceHxUW9Y/BtrCTQUNqKvPG2TPKU+fJmzWmTUgKgkfb8p2s+FZcQ0EZ/W9eRUTcC8pekeMeiSCZhmrwAVIE9IFMZby7sSirpnsR0frnkkD6BAtpMNmAE99paWTs063YvwaHe29q06C/Kbq/g6ufdAgMBAAEwDQYJKoZIhvcNAQEFBQADggEBADU7oEdJOg/IMorekuXRK9Mb3xcoYOfIGpzvLFUljTnM3nGhgN1U7CvTahroyO2Plb6HIY4BLaU9v9QSaWdgoRik1C+UlSdTR/zXRbPQ3ZEZDC20dZqzNnDbyFLKNehnrk1P9Yv2OlwioJ8xJAx6coUq2K+pGm/8TxSxPHGaZz0WHBzU6kWpK2n1U9m6svqGWyLm5Dgkt3QfvIvhYPDATCd/UXXLrx5iGc0BxAR4Vh5yeWqG7E3FTN/2ZcbAnRxdWWF2D3ba7eayeqvLfnTpNA9fw384qq5kwGfJBAWhgIqU7I1PpDHJiGShP4gp1QE6t/GLNwZk6uy4nbGXKwIxZ+k=</ds:X509Certificate></ds:X509Data></ds:KeyInfo></ds:Signature><saml2:Subject><saml2:NameID>NAUTHENTICATED</saml2:NameID><saml2:SubjectConfirmation Method="urn:oasis:names:tc:SAML:2.0:cm:bearer"></saml2:SubjectConfirmation></saml2:Subject><saml2:Conditions NotBefore="2013-01-09T06:22:07.312Z" NotOnOrAfter="2013-01-09T07:22:07.312Z"></saml2:Conditions><saml2:AttributeStatement><saml2:Attribute Name="OriginalApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute><saml2:Attribute Name="DestinationApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute></saml2:AttributeStatement></saml2:Assertion>
    PL/SQL procedure successfully completed.
    Cheers,
    Manik.
  • 9. Re: big string issue.
    chris227 Guru
    Currently Being Moderated
    It's a sql plus matter

    http://lmgtfy.com/?q=SP2-0027

    start with first hit
    =>http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4789399600346029472
  • 10. Re: big string issue.
    983451 Newbie
    Currently Being Moderated
    Guessed so and shared the same link before :)

    Cheers,
    Manik.
  • 11. Re: big string issue.
    Purvesh K Guru
    Currently Being Moderated
    Manik wrote:
    What version of Oracle do you use??

    I executed the below script in 11g r2 : the original script which you have: And I see the o/p correctly
    If the length of Input String exceeds 2500 characters, try running on SQL Plus please and share the results.
  • 12. Re: big string issue.
    983451 Newbie
    Currently Being Moderated
    True not working Purvesh... Thanks for pointing that.
    The output I posted was from my toad tool unfortunately.

    Cheers,
    Manik
  • 13. Re: big string issue.
    983451 Newbie
    Currently Being Moderated
    This one works in Sqlplus..
    SET SERVEROUTPUT ON SIZE 1000000
    
    DECLARE
       v_temp    VARCHAR2 (32000)
          := '<saml2:Assertion xmlns:saml2="urn:oasis:names:tc:SAML:2.0:assertion" Version="2.0" ID="_BABF2F22371D97B9F71357712527314" IssueInstant="2013-01-09T06:22:07.312Z"><saml2:Issuer>SAAS_GLOBAL</saml2:Issuer><ds:Signature xmlns:ds="http://www.w3.org/2000/09/xmldsig#"><ds:SignedInfo><ds:CanonicalizationMethod Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:CanonicalizationMethod><ds:SignatureMethod Algorithm="http://www.w3.org/2000/09/xmldsig#rsa-sha1"></ds:SignatureMethod><ds:Reference URI="#_BABF2F22371D97B9F71357712527314"><ds:Transforms><ds:Transform Algorithm="http://www.w3.org/2000/09/xmldsig#enveloped-signature"></ds:Transform><ds:Transform Algorithm="http://www.w3.org/2001/10/xml-exc-c14n#"></ds:Transform></ds:Transforms><ds:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1"></ds:DigestMethod><ds:DigestValue>VBpMH54ImZuypTdMrGF2FmPMCJE=</ds:DigestValue></ds:Reference></ds:SignedInfo><ds:SignatureValue>O94fIestC14aa3+JKSiL4MRAnPuw4dOiCWQg2DrH9u740wRQJazz6JjSJiGvYS5Im7MBbUXNowudcbNIh950bNyVq3g3L9FFi/wPYhOmx6inyF69ROAxCanIRT9jDghzmYnuUzuKVUq+8+wBrRo0qMT'||
          'BmdAuTdZytJhqZupti3zlkq6bVnIAMTKnvrlRr1PDLzvtat+evZD6QkWdMs+i6hgVa2UQSUdvsnuRpUIRhTckie4/IL+fvgnsPDa9bKYBbc+GpKiFCr7QdS6Nb+/kPc2t2DF19NbO1O0BU0Qa/LtiycdD572vE6yQpY6qSy4MJkyZGSPYRVIhQmV3gBr7sw==</ds:SignatureValue><ds:KeyInfo><ds:X509Data><ds:X509Certificate>MIIDuDCCAqCgAwIBAgIEUOvUpTANBgkqhkiG9w0BAQUFADCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwHhcNMTMwMTA4MDgxMTE3WhcNMjMwMTA2MDgxMTE3WjCBnTELMAkGA1UEBhMCQ04xDjAMBgNVBAgTBUhLU0FSMRIwEAYDVQQHEwlIb25nIEtvbmcxFDASBgNVBAsMC1NBQVNfR0xPQkFMMT4wPAYDVQQKEzVUaGUgSG9uZ2tvbmcgYW5kIFNoYW5naGFpIEJhbmtpbmcgQ29ycG9yYXRpb24gTGltaXRlZDEUMBIGA1UEAwwLU0FBU19HTE9CQUwwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQDHQecDk5If+jbTgOy2NL83CiRJtoLPjp70T1hIqS9peOe8bShAaTyfnwqpxmPdHEopSbSygx3IhFe/YXqZK6EK9LShkH5/oAQHPUKaESNBLvi7jvQaK4aoedYVzCu2Sk+tfVqIBYctZuPOYyf4TxQEkwbNNMlpaCTqKyNfdtrvkErDGdjyaOJyeNR9IonQ0B71dceHxUW9Y/BtrCTQUNqKvPG2TPKU+fJmzWmTUgKgkfb8p2s+FZcQ0EZ/W9eRUTcC8pekeMeiSCZhmrwAVIE9IFMZby7sSirpnsR0frnkkD6BAtpMNmAE99paWTs063YvwaHe29q06C/Kbq/g6ufdAgMBAAEwDQYJKoZIhvcNAQEFBQADggEBADU7oEdJOg/IMorekuXRK9Mb3xcoYOfIGpzvLFUljTnM3nGhgN1U7CvTahroyO2Plb6HIY4BLaU9v9QSaWdgoRik1C+UlSdTR/zXRbPQ3ZEZDC20dZqzNnDbyFLKNehnrk1P9Yv2OlwioJ8xJAx6coUq2K+pGm/8TxSxPHGaZz0WHBzU6kWpK2n1U9m6svqGWyLm5Dgkt3QfvIvhYPDATCd/UXXLrx5iGc0BxAR4Vh5yeWqG7E3FTN/2ZcbAnRxdWWF2D3ba7eayeqvLfnTpNA9fw384qq5kwGfJBAWhgIqU7I1PpDHJiGShP4gp1QE6t/GLNwZk6uy4nbGXKwIxZ+k=</ds:X509Certificate></ds:X509Data></ds:KeyInfo></ds:Signature><saml2:Subject><saml2:NameID>NAUTHENTICATED</saml2:NameID><saml2:SubjectConfirmation Method="urn:oasis:names:tc:SAML:2.0:cm:bearer"></saml2:SubjectConfirmation></saml2:Subject><saml2:Conditions NotBefore="2013-01-09T06:22:07.312Z" NotOnOrAfter="2013-01-09T07:22:07.312Z"></saml2:Conditions><saml2:AttributeStatement><saml2:Attribute Name="OriginalApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute><saml2:Attribute Name="DestinationApplicationName"><saml2:AttributeValue>PIB_CD362SG</saml2:AttributeValue></saml2:Attribute></saml2:AttributeStatement></saml2:Assertion>';
     
       v_temp1   VARCHAR2 (4000);
    BEGIN
       v_temp1 := SUBSTR (v_temp, 1, 4000);
       DBMS_OUTPUT.put_line (v_temp1);
    END;
    /
    Cheers,
    Manik.
  • 14. Re: big string issue.
    jeneesh Guru
    Currently Being Moderated
    Purvesh K wrote:
    Manik wrote:
    What version of Oracle do you use??

    I executed the below script in 11g r2 : the original script which you have: And I see the o/p correctly
    If the length of Input String exceeds 2500 characters, try running on SQL Plus please and share the results.
    ?
    SQL> set serveroutput on size 3000
    SQL> exec dbms_output.put_line(lpad('a',3000,'a'));
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
    aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa                                        
    
    PL/SQL procedure successfully completed.
    Edited by: jeneesh on Jan 9, 2013 2:47 PM
    Missed to read the previous post..
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points