12 Replies Latest reply: Mar 7, 2012 3:44 AM by CGMS RSS

    Strange thing in SQL and PL/SQL

    CGMS
      Hi All,

      I am encountering a strange thing of length of the same string in SQL and PL/SQL.
      I have executed the the length of the string in SQL and PL/SQL. The output values are not matching.
      Can you please help me on how this is happening?

      SQL
      select LENGTH ( '!w0n5hn:  0.1 qlpgz-P0h1txtn-tk68-..1tk81-!A_kh0nlw_P0hphn0ln kh0nlw P0hphn0ln
      zyntwm qlpgz – P0h1txtn 0n1 B00n1n – T00xk-..2 pwqt50wmwntn zt0ttn Pwn15nl pwlw0nw
      tk pwfw0wnxw1 Extwnn5hn hf: T00xk ..1 qlpgz-P0h1txtn-tk77-!A_P0hphn0ln_B0hwnw0
      Annhx50tw1 tkpn 
      P05m00y Axth0n 
      zwxhn100y Axth0n 
      gwnx05pt5hn  zx0wwn 1
       
       
       
       
      
      zx0wwn 2
       
       
       
       
       
       
       
       
      
      zx0wwn .
       
       
       
       
       
       
       
       
      
      zx0wwn !
       
       
       
       
       
       
       
       
      
      zx0wwn 5
       
      
      P0w-khn15t5hnn ed Thw tnw0 m0y l0tnxh thw kh0nlw P0hphn0l f0x5l5t5wn hy nwlwxt5nl thw kh0nlw !0ltw ftnxt5hn hn thw p0hphn0ln h0hwnw0
      Phnt khn15t5hnn ed Fhllhw5nl thw ntxxwnnftl kh0nlw hf 0 15xt5hn00y xh0nnwl p0hphn0l, thw nyntwm x0005wn htt fhllhw5nl 0xt5hnn:
      eded Thw xh0nlw1 !0ltw 5n nh lhnlw0 0!05l0hlw fh0 nwlwxt5hn 1t05nl 5twm m05ntwn0nxw; thw nyntwm thw0why p0w!wntn thw !0ltw hw5nl 0nn5lnw1 th 0115t5hn0l 5twmn
      edmq Thw xh0nlw1 !0ltw 5n 0wmh!w1 f0hm 0ll 5twmn hn wh5xh 5t 5n p0wnwnt 0n1 0wpl0xw1 hy thw tnw0 npwx5f5w1 0wpl0xwmwnt; whw0w th5n 5mp0xtn 1w05!w1, 5twm-lw!wl fl0ln, ntxh 0n “pwn15nl 0tthh05n0t5hn”, thw nyntwm 0lnh 0mwn1n thwnw fl0ln 
      ed.. Thw xh0nlw1 !0ltw p0hphn0l 5n 011w1 th thw pwjwxtw1 P0hphn0ln l5h000y, 0lhnl w5th thw xh0nlw x0twlh0y 0n1 0w0nhn th0t 00w npwx5f5w1 hy thw tnw0 5n thw xh0nlw !0ltw 150lhl
      ed!. An 0 0wntlt hf 0wmh!5nl thw xh0nlw1 !0ltw f0hm 0ll 5twmn, 0ll tn0lwn hf thw xh0nlw1 !0ltw 00w, hf xht0nw, 0wmh!wed  Th5n 5nw!5t0hly 0wntltn 5n thw 0wmh!0l hf 0ll p0hphn0ln 5n!hl!5nl thw xh0nlw1 !0ltw 5n 0ll hthw0 xh0nnwln.  Thw 0115t5hn hf 0 nww !0ltw th thwnw 5twmn m0y, hf xht0nw, x0w0tw 0 nww p0hphn0l.  Thwnw p0hphn0ln 00w nht 0tthm0t5x0lly 0tthh05nw1 0n 0 0wntlt hf thw xh0nlw !0ltw 0xt5hn; thwy mtnt hw 01m5n5ntw0w1 hy thw 0pp0hp050tw 0tthh05nw0 5n 0 nwp000tw 0xt5hn
      ed5. An w-m05l nht5f5x0t5hn 5n 1wl5!w0w1 th thw tnw0 whh x0w0tw1 thw p0hphn0l 0n1 th 0ny 0tthh05nw0n whh h0!w 0l0w01y 0tthh05nw1 thw p0hphn0l 5n thw wh0kflhw
      mq Fhllhw5nl thw ntxxwnnftl kh0nlw hf 0 mh1tlw, lhx0l Pk h0 !0ltw-p050 xh0nnwl p0hphn0l, thw nyntwm x0005wn htt fhllhw5nl 0xt5hnn:
      mqed Thw xh0nlw1 !0ltw 5tnwlf 5n 0wt05nw1 5n thw 15xt5hn00y
      mqmq Thw !0ltw 5n 0wmh!w1 f0hm 0ll 5twmn hn wh5xh thw p0hphn0l (NB. thw p0hphn0l, nht thw !0ltw) 5n p0wnwnt 0n1 0wpl0xw1 hy thw tnw0 npwx5f5w1 0wpl0xwmwnt; whw0w th5n 5mp0xtn 1w05!w1, 5twm-lw!wl fl0ln, ntxh 0n “pwn15nl 0tthh05n0t5hn”, thw nyntwm 0lnh 0mwn1n thwnw fl0ln.  In thw x0nw hf !0ltw-p050 p0hphn0ln, hnly thw p0hphn0l !0ltw th0t w0n nwlwxtw1 5n thw p0hphn0ln h0hwnw0 m0y hw xh0nlw1 (5f thw tnw0 w5nhwn th xh0nlw thw hthw0 !0ltw 5n thw p050, thwy mtnt 0xxwnn thw n0mw p050 p0hphn0l !50 th0t !0ltw 5n thw p0hphn0ln h0hwnw0)
      mq.. Thw tn0lw p0hphn0l 5n 011w1 th thw pwjwxtw1 P0hphn0ln l5h000y, 0lhnl w5th thw 0wjwxt5hn x0twlh0y 0n1 0w0nhn th0t 00w npwx5f5w1 hy thw tnw0 5n thw 0wjwxt5hn 150lhl.  Thw 0wpl0xwmwnt !0ltw th0t w0n 0ppl5w1 5n thw xh0nlw 5n 0lnh 0wxh01w1 pw0m0nwntly, nh th0t th5n x0n hw 15npl0yw1 th tnw0n whw0w 0pp0hp050tw 1t05nl 5twm xh15nl 0xt5!5ty
      mq!. An 0 0wntlt hf 0wmh!5nl thw xh0nlw1 !0ltw f0hm nhmw 5twmn, hthw0 tn0lwn hf thw !0ltw m0y 0lnh hw 0wmh!wed  If th5n 5n thw x0nw, 0n1 5f thwnw tn0lwn 00w 0lnh p0hphn0ln, th5n 0lnh 0wntltn 5n thw 0wmh!0l hf p0hphn0ln 5n!hl!5nl thw !0ltw 5n hthw0 tn0lw xh0nnwln (5t 5n w!wn 0 0wmhtw phnn5h5l5ty th0t xh0nl5nl 0 lhx0l Pk p0hphn0l m0y 0wmh!w 0 tn0lw 0n1 p0hphn0l fh0 thw n0mw !0ltw 5n 0nhthw0 lhx0l Pk; n5m5l00ly xh0nl5nl 0 !0ltw p050 m0y 0wmh!w hthw0 !0ltw-p050 tn0lwn/p0hphn0ln).  z5m5l00ly, thw 0115t5hn hf 0 nww !0ltw th 5twmn 0n 0 0wntlt hf thw xh0nlw 0xt5hn, m0y lwnw00tw nww p0hphn0ln 5n 0ny tn0lw xh0nnwl
      mq5. An w-m05l nht5f5x0t5hn 5n 1wl5!w0w1 th thw tnw0 whh x0w0tw1 thw p0hphn0l 0n1 th 0ny 0tthh05nw0n whh h0!w 0l0w01y 0tthh05nw1 thw p0hphn0l 5n thw wh0kflhw
      .. zww 5mplwmwnt0t5hn nhtwn hwlhw, tn1w0 “pwl0tw1 Infh0m0t5hn” 
      B0n5x Flhw 
       fl1l1fl1fl;lj0n1khfh0njkfhzgJKkf xhy0w0ttj 0nwhtj5y0w0hlwfklj0w0lklt5h5hn1fhlj1fhljn1fhlj1fhljwj50w0ytlt5w0j0w5t5
      ztfl!n1fllw0t!qww0wh00wwjh0f0n1jfhn1jkkwjhqw5t! tjt5w0yttwn1fln1ln1fn1') from dual;
      Here, I got an error like
      ORA-01704: string literal too long
      01704. 00000 -  "string literal too long"
      *Cause:    The string literal is longer than 4000 characters.
      *Action:   Use a string literal of at most 4000 characters.
                 Longer values may only be entered using bind variables.
      Error at Line: 1 Column: 16
      But in PL/SQL,
      BEGIN
           dbms_output.put_line(LENGTH ( '!w0n5hn:  0.1 qlpgz-P0h1txtn-tk68-..1tk81-!A_kh0nlw_P0hphn0ln kh0nlw P0hphn0ln
      zyntwm qlpgz – P0h1txtn 0n1 B00n1n – T00xk-..2 pwqt50wmwntn zt0ttn Pwn15nl pwlw0nw
      tk pwfw0wnxw1 Extwnn5hn hf: T00xk ..1 qlpgz-P0h1txtn-tk77-!A_P0hphn0ln_B0hwnw0
      Annhx50tw1 tkpn 
      P05m00y Axth0n 
      zwxhn100y Axth0n 
      gwnx05pt5hn  zx0wwn 1
       
       
       
       
      
      zx0wwn 2
       
       
       
       
       
       
       
       
      
      zx0wwn .
       
       
       
       
       
       
       
       
      
      zx0wwn !
       
       
       
       
       
       
       
       
      
      zx0wwn 5
       
      
      P0w-khn15t5hnn ed Thw tnw0 m0y l0tnxh thw kh0nlw P0hphn0l f0x5l5t5wn hy nwlwxt5nl thw kh0nlw !0ltw ftnxt5hn hn thw p0hphn0ln h0hwnw0
      Phnt khn15t5hnn ed Fhllhw5nl thw ntxxwnnftl kh0nlw hf 0 15xt5hn00y xh0nnwl p0hphn0l, thw nyntwm x0005wn htt fhllhw5nl 0xt5hnn:
      eded Thw xh0nlw1 !0ltw 5n nh lhnlw0 0!05l0hlw fh0 nwlwxt5hn 1t05nl 5twm m05ntwn0nxw; thw nyntwm thw0why p0w!wntn thw !0ltw hw5nl 0nn5lnw1 th 0115t5hn0l 5twmn
      edmq Thw xh0nlw1 !0ltw 5n 0wmh!w1 f0hm 0ll 5twmn hn wh5xh 5t 5n p0wnwnt 0n1 0wpl0xw1 hy thw tnw0 npwx5f5w1 0wpl0xwmwnt; whw0w th5n 5mp0xtn 1w05!w1, 5twm-lw!wl fl0ln, ntxh 0n “pwn15nl 0tthh05n0t5hn”, thw nyntwm 0lnh 0mwn1n thwnw fl0ln 
      ed.. Thw xh0nlw1 !0ltw p0hphn0l 5n 011w1 th thw pwjwxtw1 P0hphn0ln l5h000y, 0lhnl w5th thw xh0nlw x0twlh0y 0n1 0w0nhn th0t 00w npwx5f5w1 hy thw tnw0 5n thw xh0nlw !0ltw 150lhl
      ed!. An 0 0wntlt hf 0wmh!5nl thw xh0nlw1 !0ltw f0hm 0ll 5twmn, 0ll tn0lwn hf thw xh0nlw1 !0ltw 00w, hf xht0nw, 0wmh!wed  Th5n 5nw!5t0hly 0wntltn 5n thw 0wmh!0l hf 0ll p0hphn0ln 5n!hl!5nl thw xh0nlw1 !0ltw 5n 0ll hthw0 xh0nnwln.  Thw 0115t5hn hf 0 nww !0ltw th thwnw 5twmn m0y, hf xht0nw, x0w0tw 0 nww p0hphn0l.  Thwnw p0hphn0ln 00w nht 0tthm0t5x0lly 0tthh05nw1 0n 0 0wntlt hf thw xh0nlw !0ltw 0xt5hn; thwy mtnt hw 01m5n5ntw0w1 hy thw 0pp0hp050tw 0tthh05nw0 5n 0 nwp000tw 0xt5hn
      ed5. An w-m05l nht5f5x0t5hn 5n 1wl5!w0w1 th thw tnw0 whh x0w0tw1 thw p0hphn0l 0n1 th 0ny 0tthh05nw0n whh h0!w 0l0w01y 0tthh05nw1 thw p0hphn0l 5n thw wh0kflhw
      mq Fhllhw5nl thw ntxxwnnftl kh0nlw hf 0 mh1tlw, lhx0l Pk h0 !0ltw-p050 xh0nnwl p0hphn0l, thw nyntwm x0005wn htt fhllhw5nl 0xt5hnn:
      mqed Thw xh0nlw1 !0ltw 5tnwlf 5n 0wt05nw1 5n thw 15xt5hn00y
      mqmq Thw !0ltw 5n 0wmh!w1 f0hm 0ll 5twmn hn wh5xh thw p0hphn0l (NB. thw p0hphn0l, nht thw !0ltw) 5n p0wnwnt 0n1 0wpl0xw1 hy thw tnw0 npwx5f5w1 0wpl0xwmwnt; whw0w th5n 5mp0xtn 1w05!w1, 5twm-lw!wl fl0ln, ntxh 0n “pwn15nl 0tthh05n0t5hn”, thw nyntwm 0lnh 0mwn1n thwnw fl0ln.  In thw x0nw hf !0ltw-p050 p0hphn0ln, hnly thw p0hphn0l !0ltw th0t w0n nwlwxtw1 5n thw p0hphn0ln h0hwnw0 m0y hw xh0nlw1 (5f thw tnw0 w5nhwn th xh0nlw thw hthw0 !0ltw 5n thw p050, thwy mtnt 0xxwnn thw n0mw p050 p0hphn0l !50 th0t !0ltw 5n thw p0hphn0ln h0hwnw0)
      mq.. Thw tn0lw p0hphn0l 5n 011w1 th thw pwjwxtw1 P0hphn0ln l5h000y, 0lhnl w5th thw 0wjwxt5hn x0twlh0y 0n1 0w0nhn th0t 00w npwx5f5w1 hy thw tnw0 5n thw 0wjwxt5hn 150lhl.  Thw 0wpl0xwmwnt !0ltw th0t w0n 0ppl5w1 5n thw xh0nlw 5n 0lnh 0wxh01w1 pw0m0nwntly, nh th0t th5n x0n hw 15npl0yw1 th tnw0n whw0w 0pp0hp050tw 1t05nl 5twm xh15nl 0xt5!5ty
      mq!. An 0 0wntlt hf 0wmh!5nl thw xh0nlw1 !0ltw f0hm nhmw 5twmn, hthw0 tn0lwn hf thw !0ltw m0y 0lnh hw 0wmh!wed  If th5n 5n thw x0nw, 0n1 5f thwnw tn0lwn 00w 0lnh p0hphn0ln, th5n 0lnh 0wntltn 5n thw 0wmh!0l hf p0hphn0ln 5n!hl!5nl thw !0ltw 5n hthw0 tn0lw xh0nnwln (5t 5n w!wn 0 0wmhtw phnn5h5l5ty th0t xh0nl5nl 0 lhx0l Pk p0hphn0l m0y 0wmh!w 0 tn0lw 0n1 p0hphn0l fh0 thw n0mw !0ltw 5n 0nhthw0 lhx0l Pk; n5m5l00ly xh0nl5nl 0 !0ltw p050 m0y 0wmh!w hthw0 !0ltw-p050 tn0lwn/p0hphn0ln).  z5m5l00ly, thw 0115t5hn hf 0 nww !0ltw th 5twmn 0n 0 0wntlt hf thw xh0nlw 0xt5hn, m0y lwnw00tw nww p0hphn0ln 5n 0ny tn0lw xh0nnwl
      mq5. An w-m05l nht5f5x0t5hn 5n 1wl5!w0w1 th thw tnw0 whh x0w0tw1 thw p0hphn0l 0n1 th 0ny 0tthh05nw0n whh h0!w 0l0w01y 0tthh05nw1 thw p0hphn0l 5n thw wh0kflhw
      .. zww 5mplwmwnt0t5hn nhtwn hwlhw, tn1w0 “pwl0tw1 Infh0m0t5hn” 
      B0n5x Flhw 
       fl1l1fl1fl;lj0n1khfh0njkfhzgJKkf xhy0w0ttj 0nwhtj5y0w0hlwfklj0w0lklt5h5hn1fhlj1fhljn1fhlj1fhljwj50w0ytlt5w0j0w5t5
      ztfl!n1fllw0t!qww0wh00wwjh0f0n1jfhn1jkkwjhqw5t! tjt5w0yttwn1fln1ln1fn1') );
      END;
      I got the value as 3969.

      I have tried with the same String. I know the maximum length of string in SQL is 4000 and in PL/SQL is 32767. but the thing is when got an length of the string in PL/SQL as below 4000, then iwhy it should not be executed in SQL side?

      Can you help me to clear on this issue?

      DB: Oracle 11g Release 2

      Thank and Regards,
      Suresh.
        • 1. Re: Strange thing in SQL and PL/SQL
          Billy~Verreynne
          Do not see a problem.
          SQL> var s varchar2(4000);
          SQL> 
          SQL> begin
            2   :s := '!w0n5hn:  0.1 qlpgz-P0h1txtn-tk68-..1tk81-!A_kh0nlw_P0hphn0ln kh0nlw P0hphn0ln
            3  zyntwm qlpgz – P0h1txtn 0n1 B00n1n – T00xk-..2 pwqt50wmwntn zt0ttn Pwn15nl pwlw0nw
            4  tk pwfw0wnxw1 Extwnn5hn hf: T00xk ..1 qlpgz-P0h1txtn-tk77-!A_P0hphn0ln_B0hwnw0
            5  Annhx50tw1 tkpn
            6  P05m00y Axth0n
            7  zwxhn100y Axth0n
            8  gwnx05pt5hn  zx0wwn 1
            9  
           10  
           11  
           12  
           13  
           14  zx0wwn 2
           15  
           16  
           17  
           18  
           19  
           20  
           21  
           22  
           23  
           24  zx0wwn .
           25  
           26  
           27  
           28  
           29  
           30  
           31  
           32  
           33  
           34  zx0wwn !
           35  
           36  
           37  
           38  
           39  
           40  
           41  
           42  
           43  
           44  zx0wwn 5
           45  
           46  
           47  P0w-khn15t5hnn ed Thw tnw0 m0y l0tnxh thw kh0nlw P0hphn0l f0x5l5t5wn hy nwlwxt5nl thw kh0nlw !0ltw ftnxt5hn hn thw p0hphn0ln h0hwnw0
           48  Phnt khn15t5hnn ed Fhllhw5nl thw ntxxwnnftl kh0nlw hf 0 15xt5hn00y xh0nnwl p0hphn0l, thw nyntwm x0005wn htt fhllhw5nl 0xt5hnn:
           49  eded Thw xh0nlw1 !0ltw 5n nh lhnlw0 0!05l0hlw fh0 nwlwxt5hn 1t05nl 5twm m05ntwn0nxw; thw nyntwm thw0why p0w!wntn thw !0ltw hw5nl 0nn5lnw1 th 0115t5hn0l 5twmn
           50  edmq Thw xh0nlw1 !0ltw 5n 0wmh!w1 f0hm 0ll 5twmn hn wh5xh 5t 5n p0wnwnt 0n1 0wpl0xw1 hy thw tnw0 npwx5f5w1 0wpl0xwmwnt; whw0w th5n 5mp0xtn 1w05!w1, 5twm-lw!wl fl0ln, ntxh 0n “pwn15nl 0tthh05n0t5hn”, thw nyntwm 0lnh 0mwn1n thwnw fl0ln
           51  ed.. Thw xh0nlw1 !0ltw p0hphn0l 5n 011w1 th thw pwjwxtw1 P0hphn0ln l5h000y, 0lhnl w5th thw xh0nlw x0twlh0y 0n1 0w0nhn th0t 00w npwx5f5w1 hy thw tnw0 5n thw xh0nlw !0ltw 150lhl
           52  ed!. An 0 0wntlt hf 0wmh!5nl thw xh0nlw1 !0ltw f0hm 0ll 5twmn, 0ll tn0lwn hf thw xh0nlw1 !0ltw 00w, hf xht0nw, 0wmh!wed  Th5n 5nw!5t0hly 0wntltn 5n thw 0wmh!0l hf 0ll p0hphn0ln 5n!hl!5nl thw xh0nlw1 !0ltw 5n 0ll hthw0 xh0nnwln.  Thw 0115t5hn hf 0 nww !0ltw th thwnw 5twmn m0y, hf xht0nw, x0w0tw 0 nww p0hphn0l.     Thwnw p0hphn0ln 00w nht 0tthm0t5x0lly 0tthh05nw1 0n 0 0wntlt hf thw xh0nlw !0ltw 0xt5hn; thwy mtnt hw 01m5n5ntw0w1 hy thw 0pp0hp050tw 0tthh05nw0 5n 0 nwp000tw 0xt5hn
           53  ed5. An w-m05l nht5f5x0t5hn 5n 1wl5!w0w1 th thw tnw0 whh x0w0tw1 thw p0hphn0l 0n1 th 0ny 0tthh05nw0n whh h0!w 0l0w01y 0tthh05nw1 thw p0hphn0l 5n thw wh0kflhw
           54  mq Fhllhw5nl thw ntxxwnnftl kh0nlw hf 0 mh1tlw, lhx0l Pk h0 !0ltw-p050 xh0nnwl p0hphn0l, thw nyntwm x0005wn htt fhllhw5nl 0xt5hnn:
           55  mqed Thw xh0nlw1 !0ltw 5tnwlf 5n 0wt05nw1 5n thw 15xt5hn00y
           56  mqmq Thw !0ltw 5n 0wmh!w1 f0hm 0ll 5twmn hn wh5xh thw p0hphn0l (NB. thw p0hphn0l, nht thw !0ltw) 5n p0wnwnt 0n1 0wpl0xw1 hy thw tnw0 npwx5f5w1 0wpl0xwmwnt; whw0w th5n 5mp0xtn 1w05!w1, 5twm-lw!wl fl0ln, ntxh 0n “pwn15nl 0tthh05n0t5hn”, thw nyntwm 0lnh 0mwn1n thwnw fl0ln.  In thw x0nw hf !0ltw-p050 p0hphn0ln, hnly thw p0hphn0l !0ltw th0t w0n nwlwxtw1 5n thw p0hphn0ln h0hwnw0 m0y hw xh0nlw1 (5f thw tnw0 w5nhwn th xh0nlw thw hthw0 !0ltw 5n thw p050, thwy mtnt 0xxwnn thw n0mw p050 p0hphn0l !50 th0t !0ltw 5n thw p0hphn0ln h0hwnw0)
           57  mq.. Thw tn0lw p0hphn0l 5n 011w1 th thw pwjwxtw1 P0hphn0ln l5h000y, 0lhnl w5th thw 0wjwxt5hn x0twlh0y 0n1 0w0nhn th0t 00w npwx5f5w1 hy thw tnw0 5n thw 0wjwxt5hn 150lhl.  Thw 0wpl0xwmwnt !0ltw th0t w0n 0ppl5w1 5n thw xh0nlw 5n 0lnh 0wxh01w1 pw0m0nwntly, nh th0t th5n x0n hw 15npl0yw1 th tnw0n whw0w 0pp0hp050tw 1t05nl 5twm xh15nl 0xt5!5ty
           58  mq!. An 0 0wntlt hf 0wmh!5nl thw xh0nlw1 !0ltw f0hm nhmw 5twmn, hthw0 tn0lwn hf thw !0ltw m0y 0lnh hw 0wmh!wed     If th5n 5n thw x0nw, 0n1 5f thwnw tn0lwn 00w 0lnh p0hphn0ln, th5n 0lnh 0wntltn 5n thw 0wmh!0l hf p0hphn0ln 5n!hl!5nl thw !0ltw 5n hthw0 tn0lw xh0nnwln (5t 5n w!wn 0 0wmhtw phnn5h5l5ty th0t xh0nl5nl 0 lhx0l Pk p0hphn0l m0y 0wmh!w 0 tn0lw 0n1 p0hphn0l fh0 thw n0mw !0ltw 5n 0nhthw0 lhx0l Pk; n5m5l00ly xh0nl5nl 0 !0ltw p050 m0y 0wmh!w hthw0 !0ltw-p050 tn0lwn/p0hphn0ln).  z5m5l00ly, thw 0115t5hn hf 0 nww !0ltw th 5twmn 0n 0 0wntlt hf thw xh0nlw 0xt5hn, m0y lwnw00tw nww p0hphn0ln 5n 0ny tn0lw xh0nnwl
           59  mq5. An w-m05l nht5f5x0t5hn 5n 1wl5!w0w1 th thw tnw0 whh x0w0tw1 thw p0hphn0l 0n1 th 0ny 0tthh05nw0n whh h0!w 0l0w01y 0tthh05nw1 thw p0hphn0l 5n thw wh0kflhw
           60  .. zww 5mplwmwnt0t5hn nhtwn hwlhw, tn1w0 “pwl0tw1 Infh0m0t5hn”
           61  B0n5x Flhw
           62   fl1l1fl1fl;lj0n1khfh0njkfhzgJKkf xhy0w0ttj 0nwhtj5y0w0hlwfklj0w0lklt5h5hn1fhlj1fhljn1fhlj1fhljwj50w0ytlt5w0j0w5t5
           63  ztfl!n1fllw0t!qww0wh00wwjh0f0n1jfhn1jkkwjhqw5t! tjt5w0yttwn1fln1ln1fn1';
           64  end;
           65  /
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> select length(:s) from dual;
          
          LENGTH(:S)
          ----------
                3934
          
          SQL>  --// last 10 characters are fln1ln1fn1 - confirm via SQL
          SQL> select substr(:s,-10) from dual;
          
          SUBSTR(:S,-10)
          ------------------------------
          fln1ln1fn1
          
          SQL> 
          When doing this type of thing (string with line feeds), make sure you know whether single byte or double byte line feeds are used by the client and send to Oracle.

          For example. Windows uses #10#13 as control line feed. So 20 line feeds mean 40 bytes needed for storage. If from a Unix/Linux client, that would only require 20 bytes.

           
          PS. See that you got a larger char count - that would likely be to whitespaces in your data that fail to copy-and-paste from the HTML page and are now missing in the string that I've tested.

          Edited by: Billy Verreynne on Feb 28, 2012 7:43 AM
          • 2. Re: Strange thing in SQL and PL/SQL
            CGMS
            Hi,

            Thanks for your kind reply.

            In our application, i am getting this input string from java code.
            My goal is to insert the string into a table / update it to the table.
            By trying the direct insert/update statement, i am getting the error, char length exceeds to 4000.
            so, i try to do in pl/sql.

            I am getting the length of the same string in PL/sql. so i have written the procedure which one has one varchar2 input parameter.

            Now, the problem continues. this procedure throwing the same error.
            For debugging, i just print the test message by dbms_output.put_line in the first line after BEGIN. It did not print.
            The same input string is cannot be passed in PL/SQL procedure.

            Can you please help me in this issue?

            I cannot use bind variables in our applicaition as you suggested.

            How do i pass the same string value into the procedure for doing DML operation within the procedure?

            Regards,
            Suresh.
            • 3. Re: Strange thing in SQL and PL/SQL
              Billy~Verreynne
              Suresh Mohan wrote:

              In our application, i am getting this input string from java code.
              My goal is to insert the string into a table / update it to the table.
              How do you pass the string from Java to Oracle?
              By trying the direct insert/update statement, i am getting the error, char length exceeds to 4000.
              so, i try to do in pl/sql.
              In that case, you should be using a bind variable in the call to Oracle (be that a PL/SQL or SQL call). And that is pretty much mandatory. You would use something like the following (am not a Java programmer so I cannot guarantee syntactical correctness):
              // prepare the SQL cursor (with bind variables)
              PreparedStatement insertSQL = conn.prepareStatement( "insert into my_test_table values( seq_id.NextVal, ?)" );
              ..
              // bind the variable with a Java var value
              insertSQL.setString( 1, myJavaString );
              ..
              How do i pass the same string value into the procedure for doing DML operation within the procedure?
              The simple answer to this is:
              - use bind variables
              - bind host (e.g. Java) variables correctly (types need to be valid)

              The problem you have does not seem to me to be on the Oracle side. It seems to be how your Java code passes that string, incorrectly, to Oracle.

              The issues to note are:
              - Oracle supports 4000 byte string sizes
              - larger strings need to be passed as CLOBs (that can be TB's in size)
              - Oracle multi-byte character sets require 2+ bytes for storage (e.g. when the database uses a 2-byte charset then the largest string is 2000 characters as that will need 4000 bytes storage)
              • 4. Re: Strange thing in SQL and PL/SQL
                CGMS
                Hi,

                Thanks for your reply.

                From the front application side, my oracle code may get whitespace characters.
                Is there any way to restrict / trim the whitespace characters in between the input string in oracle?

                Regards,

                Suresh.
                • 5. Re: Strange thing in SQL and PL/SQL
                  Billy~Verreynne
                  Suresh Mohan wrote:

                  From the front application side, my oracle code may get whitespace characters.
                  Is there any way to restrict / trim the whitespace characters in between the input string in oracle?
                  You can do this with SQL statement. E.g.
                  // prepare the SQL cursor (with bind variables)
                  PreparedStatement insertSQL = conn.prepareStatement( "insert into my_test_table values( seq_id.NextVal, trim(?) )" );
                  You can update your GUI code so that the on text entry event that reads the keyboard input, trims whitespaces.

                  You can create a trigger on the table that trims whitespaces from the value supplied for that column.

                  You can create a PL/SQL procedure that alone is used for inserting rows into that table - single interface for adding data to the table. This procedure can then not only apply the required business logic, but also apply data cleaning such as trimming whitespaces.

                  It depends on just what the requirements are - though I would tend to select the last, the PL/SQL interface, as the solution.
                  • 6. Re: Strange thing in SQL and PL/SQL
                    CGMS
                    Hi,

                    Thanks for kind reply.

                    As you told the last solution by PL/SQL, how do we trim the whitespaces without trimiming space, enter and tab keys.
                    Because, the user needs the data as he typed. How to remove the unwanted whitespaces within the string?

                    In the first question i have posted in this thread, I had mentioned the length of the string differs in SQL and PL/SQL.
                    I had done this exercise in Oracle SQL Developer 3.1.

                    When i try this same exercise in TOAD, the values are matching by using SQL and PL/SQL. Why the same thing is mismatched in Oracle SQL Developer and Toad.

                    As you mentioned, there are some whitespaces are there in the text. Why is it not showing in TOAD - SQL query? But it was showing in Oracle SQL Developer 3.1 - SQL Query.

                    Regards,
                    Suresh.
                    • 7. Re: Strange thing in SQL and PL/SQL
                      Billy~Verreynne
                      Suresh Mohan wrote:
                      Thanks for kind reply.

                      As you told the last solution by PL/SQL, how do we trim the whitespaces without trimiming space, enter and tab keys.
                      Because, the user needs the data as he typed. How to remove the unwanted whitespaces within the string?
                      The REPLACE() function (SQL and PL/SQL) can be used to replace white spaces. E.g.
                      SQL> declare
                        2          WHITESPACE      constant varchar2(1) := chr(256);
                        3          line            varchar2(100);
                        4  begin
                        5          line := WHITESPACE||'Hello '||WHITESPACE||' World.';
                        6  
                        7          DBMS_OUTPUT.put_line( 'Original text: "'||line||'"' );
                        8  
                        9          line := replace( line, WHITESPACE, '' );
                       10          DBMS_OUTPUT.put_line( 'Formatted text: "'||line||'"' );
                       11  end;
                       12  /
                      Original text: " Hello   World."
                      Formatted text: "Hello  World."
                      
                      PL/SQL procedure successfully completed.
                      
                      SQL>
                      As you mentioned, there are some whitespaces are there in the text. Why is it not showing in TOAD - SQL query? But it was showing in Oracle SQL Developer 3.1 - SQL Query.
                      Clients do not all behave the same. Different programming language runtimes also differ.
                      • 8. Re: Strange thing in SQL and PL/SQL
                        CGMS
                        Hi,

                        Thanks for your kind reply.
                        I have tried with your code, i had encountered an error in my db.
                         DECLARE
                          WHITESPACE CONSTANT VARCHAR2(1) := chr(256);
                          line       VARCHAR2(100);
                        BEGIN
                          line := WHITESPACE||'Hello '||WHITESPACE||' World.';
                          DBMS_OUTPUT.put_line( 'Original text: "'||line||'"' );
                          line := REPLACE( line, WHITESPACE, '' );
                          DBMS_OUTPUT.put_line( 'Formatted text: "'||line||'"' );
                        END;
                        
                        Error starting at line 2 in command:
                        Error report:
                        ORA-06502: PL/SQL: numeric or value error: character string buffer too small
                        ORA-06512: at line 2
                        06502. 00000 -  "PL/SQL: numeric or value error%s"
                        *Cause:    
                        *Action:
                        I have tried this code with varchar2(2).
                         DECLARE
                          WHITESPACE CONSTANT VARCHAR2(2) := chr(256);
                          line       VARCHAR2(100);
                        BEGIN
                          line := WHITESPACE||'Hello '||WHITESPACE||' World.';
                          DBMS_OUTPUT.put_line( 'Original text: "'||line||'"' );
                          line := REPLACE( line, WHITESPACE, '' );
                          DBMS_OUTPUT.put_line( 'Formatted text: "'||line||'"' );
                        END;
                        anonymous block completed
                        
                        Original text: " Hello   World."
                        Formatted text: "Hello  World."
                        Is there something wrong in my db settings?
                        Why didn't i assign the chr(256) in to a varchar2(1) variable as you did?.

                        Apart from chr(256), are there any other white space characters available?

                        Regards,
                        Suresh.
                        • 9. Re: Strange thing in SQL and PL/SQL
                          Billy~Verreynne
                          Seems like your database is configured for using a multiple byte character set. This means that a single character requires 2 bytes (or more) storage.

                          I'm using a single byte character set. One byte storage required per character.
                          • 10. Re: Strange thing in SQL and PL/SQL
                            Billy~Verreynne
                            Suresh Mohan wrote:

                            Apart from chr(256), are there any other white space characters available?
                            White space characters are listed on http://en.wikipedia.org/wiki/Whitespace_character.

                            From an old MS-DOS/ASCII character perspective, a whitespace char was #256 as the normal space char was #32. It was also called a soft space versus a hard space.

                            You need to look at the character set you're using - ASCII is a single byte charset and not the same.

                            You can use the DUMP() SQL function to look at the characters in a string. E.g.
                            select dump( my_string_column ) from my_table where ...
                            • 11. Re: Strange thing in SQL and PL/SQL
                              CGMS
                              Hi,

                              Thanks for your reply.

                              I have tried a little workout using DUMP function.
                              SELECT
                                dump('Hi'
                                ||chr(256)
                                ||'Test',1010)
                              FROM
                                dual
                              DUMP('HI'||CHR(256)||'TEST',1010)                            
                              ------------------------------------------------------------ 
                              Typ=1 Len=8 CharacterSet=AL32UTF8: 72,105,1,0,84,101,115,116 
                              
                              SELECT
                                dump(REPLACE(REPLACE('Hi'
                                ||chr(256)
                                ||'Test',chr(0),''),chr(1),''),1017)
                              FROM
                                dual
                              DUMP(REPLACE(REPLACE('HI'||CHR(256)||'TEST',CHR(0),''),CHR(1),''),1017) 
                              ----------------------------------------------------------------------- 
                              Typ=1 Len=6 CharacterSet=AL32UTF8: H,i,T,e,s,t                          
                              Below are the Character setting in My DB.
                              SELECT
                                *
                              FROM
                                v$nls_parameters where parameter like '%CHARACTERSET%'
                              PARAMETER                                                        VALUE                                                            
                              ---------------------------------------------------------------- ---------------------------------------------------------------- 
                              NLS_CHARACTERSET                                                 AL32UTF8                                                         
                              NLS_NCHAR_CHARACTERSET                                           AL16UTF16    
                              From the Java side, i am trying the pass the string to one procedure.
                              But the mismatch of the length, the call to the procedure did not executed.

                              I can do the DUMP function exercise only the control comes to procedure.
                              But the calling the procedure is not successful.

                              Regards,
                              Suresh.
                              • 12. Re: Strange thing in SQL and PL/SQL
                                CGMS
                                Hi,

                                The Characterset in Java program is ISO8589-1.

                                I can not pass the value from Java program to Oracle procedure as input.
                                When calling it shows the Character length exceeds error.

                                Regards,
                                Suresh.