1 2 Previous Next 17 Replies Latest reply: Jan 9, 2013 5:07 AM by Purvesh K RSS

    big string issue.

    913578
      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
          --
          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
            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
              still getting the same error.


              SP2-0027: Input is too long (> 2499 characters) - line ignored
              • 4. Re: big string issue.
                983451
                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-Oracle
                  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
                    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
                      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
                        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
                          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
                            Guessed so and shared the same link before :)

                            Cheers,
                            Manik.
                            • 11. Re: big string issue.
                              Purvesh K
                              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
                                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
                                  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
                                    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