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!

not by using PIPELINED

Discussion in 'SQL PL/SQL' started by sandip.senmajumder, Feb 7, 2011.

  1. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    can we do it using table valued function(not by using PIPELINED)

    CREATE TYPE TokenTables AS OBJECT
    (
    TokenPosition NUMBER(10),
    TokenValue NVARCHAR2(4000)
    )
    /
    -- ************************************************** ***************************************
    CREATE TYPE Tbl_TokenTypes AS TABLE OF TokenTables
    /
    -- ************************************************** ***************************************
    CREATE OR REPLACE FUNCTION ufSplitString
    (
    pTokenString NVARCHAR2 ,
    pSeparatorCharacter NCHAR := ',' , --default is the 'comma'
    pIsDebug NUMBER := 0
    )
    RETURN Tbl_TokenTypes PIPELINED
    AS
    pTokenPosition NUMBER(10) := 1;
    pTokenString1 NVARCHAR2(4000);
    pTokenString2 NVARCHAR2(4000);
    BEGIN

    IF LENGTH(pTokenString) > 0 THEN

    pTokenString1 := pTokenString || pSeparatorCharacter;

    WHILE(INSTR(pTokenString1,',') > 0)
    LOOP

    pTokenString2 := SUBSTR(pTokenString1,1,(INSTR(pTokenString1,pSepar atorCharacter) - 1));

    PIPE ROW(TokenTables(pTokenPosition,pTokenString2));

    pTokenPosition := pTokenPosition + 1;

    pTokenString1 := SUBSTR(pTokenString1,(INSTR(pTokenString1,',') + 1));

    END LOOP;

    END IF;

    RETURN;
    END;
    /
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
  3. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    If it is possible.....can you help me out.............i want to compare this 2 function....
    while implementing getting struck.....
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Have you checked the link I mentioned? Why are you so hesitating to check link or google? We are not here to spoonfeed you. we are here to give you links and directions to help you.

    Where are you stuck ? Feel free to come back if you really stuck after implementing the solution.
     
  5. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Hi Raj,
    If you have gone through with my code? i am sure then your answer would have been different.i am here to discuss with the thing that i have mentioned.........

    Thanks,
    sandip
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Still, you never mentioned what is the issue with existing code.

    Again, where did you stuck ?

    What error or performance issue you are facing?

    Why do you want to avoid pipelined function ?
     
  7. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Well thats a good question......
    Why do you want to avoid pipelined function.well if i create a function it takes less memory in the PGA(and suppose PGA is not a issue for me), correct me if i am wrong.But When i use pipe row it will by pass the result row by row........correct
    So it will take more processing time.So when i will compare this result set with other values in the calling program......will it take more time then table function or pipelined would be the faster way.....
    that is why my question was if we can process the entire thing at a time and can return with a table function.....will it save our time .......... ?
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    As long as your code is concerned (code to split the string into rows of table type), I don't think there will be big time difference.

    You can even try it using direct sql as shown below.

    This code is tried just now. you can customize it.

    Code (SQL):

    SQL> WITH EXAMPLE AS (SELECT 'ABCD,EFG,HIJK,LMN,OPQR,STU,VWXYZ' STR,
                            ',' DLMTR
      2    3                   FROM DUAL ),
      4  ATTRIBS AS ( SELECT STR,
      5                      DLMTR,LENGTH(STR||DLMTR) -LENGTH(REPLACE(STR||DLMTR,DLMTR)) DLMTCNT,
      6                            LENGTH(STR||DLMTR) STRLEN
      7               FROM EXAMPLE)
      8  SELECT LEVEL ,
      9         SUBSTR(STR,DECODE(LEVEL ,1,1 ,INSTR(STR,DLMTR,1,LEVEL-1)+1),
     10                    DECODE(INSTR(STR,DLMTR,1,LEVEL),0,STRLEN,INSTR(STR,DLMTR,1,LEVEL))
     11                      -DECODE(LEVEL ,1,1 ,INSTR(STR,DLMTR,1,LEVEL-1)+1) ) STRING
     12  FROM ATTRIBS
     13  CONNECT BY LEVEL <=DLMTCNT   ;

         LEVEL STRING
    ---------- --------------------------------
             1 ABCD
             2 EFG
             3 HIJK
             4 LMN
             5 OPQR
             6 STU
             7 VWXYZ

    7 ROWS selected.

    SQL>
    SQL>
    SQL> WITH EXAMPLE AS (SELECT 'ABCD!EFG!HIJK!LMN!OPQR!STU!VWXYZ' STR,
                            '!' DLMTR
      2    3                   FROM DUAL ),
      4  ATTRIBS AS ( SELECT STR,
      5                      DLMTR,LENGTH(STR||DLMTR) -LENGTH(REPLACE(STR||DLMTR,DLMTR)) DLMTCNT,
      6                            LENGTH(STR||DLMTR) STRLEN
      7               FROM EXAMPLE)
      8  SELECT LEVEL ,
      9         SUBSTR(STR,DECODE(LEVEL ,1,1 ,INSTR(STR,DLMTR,1,LEVEL-1)+1),
     10                    DECODE(INSTR(STR,DLMTR,1,LEVEL),0,STRLEN,INSTR(STR,DLMTR,1,LEVEL))
     11                      -DECODE(LEVEL ,1,1 ,INSTR(STR,DLMTR,1,LEVEL-1)+1) ) STRING
     12  FROM ATTRIBS
     13  CONNECT BY LEVEL <=DLMTCNT;

         LEVEL STRING
    ---------- --------------------------------
             1 ABCD
             2 EFG
             3 HIJK
             4 LMN
             5 OPQR
             6 STU
             7 VWXYZ

    7 ROWS selected.

    SQL>

     
    You can also do teh same SQL inside the function to bulk collect into table type. Lots of options. :)
     
  9. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    It's a lovely experiment you have shown.........But according to some people there it has big difference.

    so if we can return that function by table valued function we can get the actual picture.......

    If i am not wrong i can not use direct sql in this scenario because as i told you i am calling it from a procedure and that procedure has got some in parameter couple of then is , separated string.....

    Thanks,
    sandip
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    SQL> CREATE TYPE TokenTables AS OBJECT
    (
    TokenPosition NUMBER(10),
    TokenValue NVARCHAR2(4000)
    )  2    3    4    5  ;
      6  /

    TYPE created.

    SQL> CREATE TYPE Tbl_TokenTypes AS TABLE OF TokenTables;
      2  /

    TYPE created.

    SQL> CREATE OR REPLACE FUNCTION ufSplitString
      2  (
      3  pTokenString NVARCHAR2 ,
      4  pSeparatorCharacter NCHAR := ',' , --default is the 'comma'
      5  pIsDebug NUMBER := 0
      6  )
      7  RETURN Tbl_TokenTypes PIPELINED
      8  AS
      9  pTokenPosition NUMBER(10) := 1;
     10  pTokenString1 NVARCHAR2(4000);
     11  pTokenString2 NVARCHAR2(4000);
     12  BEGIN
     13
     14  IF LENGTH(pTokenString) > 0 THEN
     15
     16  pTokenString1 := pTokenString || pSeparatorCharacter;
     17
     18  WHILE(INSTR(pTokenString1,',') > 0)
     19  LOOP
     20
     21  pTokenString2 := SUBSTR(pTokenString1,1,(INSTR(pTokenString1,pSeparatorCharacter) - 1));
     22
     23  PIPE ROW(TokenTables(pTokenPosition,pTokenString2));
     24
     25  pTokenPosition := pTokenPosition + 1;
     26
     27  pTokenString1 := SUBSTR(pTokenString1,(INSTR(pTokenString1,',') + 1));
     28
     29  END LOOP;
     30
     31  END IF;
     32
     33  RETURN;
     34  END;
     35  /

    FUNCTION created.

    SQL> SELECT * FROM  TABLE(ufSplitString('ABCD,EFG,HIJK,LMN,OPQR,STU,VWXYZ'));

    TOKENPOSITION TOKENVALUE
    ------------- --------------------
                1 ABCD
                2 EFG
                3 HIJK
                4 LMN
                5 OPQR
                6 STU
                7 VWXYZ

    7 ROWS selected.

    SQL>
    SQL> CREATE OR REPLACE FUNCTION UFSPLITSTRINGDEMO
      2  (
      3  PTOKENSTRING NVARCHAR2 ,
      4  PSEPARATORCHARACTER NCHAR := ',' , --DEFAULT IS THE 'COMMA'
      5  PISDEBUG NUMBER := 0
      6  )
      7  RETURN TBL_TOKENTYPES
      8  AS
      9     V_TOKEN TBL_TOKENTYPES ;
     10  BEGIN
     11      V_TOKEN := TBL_TOKENTYPES ();
     12
     13      WITH EXAMPLE AS (SELECT PTOKENSTRING STR,
     14                              PSEPARATORCHARACTER DLMTR
     15                       FROM DUAL ),
     16      ATTRIBS AS ( SELECT STR,
     17                          DLMTR,LENGTH(STR||DLMTR) -LENGTH(REPLACE(STR||DLMTR,DLMTR)) DLMTCNT,
     18                                LENGTH(STR||DLMTR) STRLEN,
     19                                LENGTH(STR) -LENGTH(REPLACE(STR,DLMTR)) DLMTCNT1
     20                   FROM EXAMPLE)
     21      SELECT TOKENTABLES (LEVEL ,
     22                         SUBSTR(STR,DECODE(LEVEL ,1,1 ,INSTR(STR,DLMTR,1,LEVEL-1)+1),
     23                                    DECODE(INSTR(STR,DLMTR,1,LEVEL),0,STRLEN,INSTR(STR,DLMTR,1,LEVEL))
     24                                      -DECODE(LEVEL ,1,1 ,INSTR(STR,DLMTR,1,LEVEL-1)+1) ) )
     25      BULK COLLECT INTO V_TOKEN
     26      FROM ATTRIBS
     27      WHERE DLMTCNT1 >0
     28      CONNECT BY LEVEL <=DLMTCNT;
     29
     30      RETURN V_TOKEN ;
     31  END UFSPLITSTRINGDEMO;
     32  /

    FUNCTION created.

    SQL>
    SQL> SELECT * FROM  TABLE(ufSplitString('ABCD,EFG,HIJK,LMN,OPQR,STU,VWXYZ'));

    TOKENPOSITION TOKENVALUE
    ------------- --------------------
                1 ABCD
                2 EFG
                3 HIJK
                4 LMN
                5 OPQR
                6 STU
                7 VWXYZ

    7 ROWS selected.

    SQL> SELECT * FROM  TABLE(ufSplitStringdemo('ABCD,EFG,HIJK,LMN,OPQR,STU,VWXYZ'));

    TOKENPOSITION TOKENVALUE
    ------------- --------------------
                1 ABCD
                2 EFG
                3 HIJK
                4 LMN
                5 OPQR
                6 STU
                7 VWXYZ

    7 ROWS selected.

    SQL>

     
     
  11. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    it is really a lovely conversation..............PIPELINED function is more faster....
     
  12. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    SQL> SELECT * FROM TABLE(ufSplitStringdemo('1,2,3,4,5,6,7,8,9,44,55,66,77,88,99,
    33,2
    2 2,11,00,01,02,03,04,05,06,0,712,13,14,15,16,17,18,19,111,123,142,154,1235,1
    24,12
    3 5,1457,12,312,44,55,44,66,45788,55,7566,456,645,65,465,64,5,52,55,556,55,54
    ,456,
    4 5645566,55566,55666,4556666,45++555,4555,445566564,56665447,4445,1231,34565
    ,4556
    5 66,2566456,565565,556555,65566566,5566656,665666+5,666,d,ss,ss,d,ssdasdd,we
    qweqw
    6 ,qweqweq,weerwef,sfsf,ggrge,rertret,ergre,rerrgg,rertrete,err,rrer,hh,hhhhj
    ,sdss
    7 s,ffff,eeeee,gfgggg,rrrrwwwww,trtttt,ddddff,rererere,ererere,eretrtyet,tytr
    yrytr
    8 ytry,sdfsfsfsf,rtgggfgddd,sdfssffff,sfrerffff,sfdfsfsfsfsf,sfsffsdfsd,dsfds
    fdsfd
    9 s,sdfsdsfds,sfsdsddddddddddd,ddddddddddddddddffffffffff,fgggggg,ssssss,ssss
    sssss
    10 sddddss,ffffffff,d,ssddsfdrgfr,dsdsadasda,sdsgtfsfsdfd,dfgfdgdgf,sdfgdfsdfs
    ,sdfs
    11 fsfsdfsdf,ertrtertree,rtrere,egrgerge,gerhegerg,erhgfsfs,rgyertreeerfer,grt
    erter
    12 greter,ergerger,gergergerhsgdf,gdghtrt,egrtg,er,er,frg,egergergrege,gege,eg
    eg,eg
    13 ergerg,egerg,ergerg,egewgrdg,ghfsdg,dfghsraegrg,gd,ghdshdg,dg,shsd,gdf,hsg,
    ss,dh
    14 gg,gsdg,d,gd,sg,dsgh,h,,sfyhtrutr,,sh,du,yf,dg,s,hts,s,yt,y,erer,,eh,fghfhf
    g,hhh
    15 ,dsdddddddddddddddddddddddddddddddddddddddddddddddddddddddd,ddddddddddddddd
    ddddf
    16 ,dsfdfsdfsdfdfsdf,fsdfsd,fsf,sfsfsdfsdfdfsdfsd,fsdfsfsdfs,fsdfsdfsdf,sfdsf,
    sfsdf,
    17 dfsdf,sdf,fs,fdfsfsdf,fsdfs,fsd,fsdf,fd,,fdf,df,ddfdfdfdffdff,dfdf,dfdfsf,s
    fewrf,
    18 fd,fsdf,ff,fdsffsd,fsdfsfs,fererwewrweew,ewrew,erwrew,trt,wtre,tery,eyeyyer
    ,terye
    19 ,erewrewrwe,twet,wtwt,wtwe,rf,sfewtrt,erter,yt,yey,tr,yuu,uyur,ertre,ererty
    ,ryt,y
    20 dfdagdghadgdagdfgag,gfgafdga,gagag,daghdfhdf,gdfhh,gfugfhgf,hgfh,gfhgf,hgf,
    htr,t,
    21 ffsdfdsfdfdfdfdfdfdf,fdfsfdsssdfdsfdsfs,fsfsfdsfsfsfsfsfs,fsfsfsfsfsfsfsfsf
    sfsfsf
    22 ,dasdsadasdad,dddddddddddddddawwwwwwwwwwww,dada,fffffffffffffffffff,fffffff
    ,fffewr
    23 ,fwefew,fwefewf,wte,twe,fwe,twe,tweteeeeeeeeeeeeeeeeeeeeeeeeeee,ttttttttttt
    ,t,t,t
    24 twttwt,ttyyyyyyyyyyyyyyyyy,yyyyyyyyyyu,uuuuuuuuuuuuuuuuuuuuuiooooooooooiiii
    iiiiii,
    25 65,465,64,5,52,55,556,55,54,456,5645566,55566,55666,4556666,45++555,4555,44
    5566564,
    26 66,2566456,565565,556555,65566566,5566656,665666+5,666,d,ss,ss,d,ssdasdd,we
    qweqw
    27 ,qweqweq,weerwef,sfsf,ggrge,rertret,ergre,rerrgg,rertrete,err,rrer,hh,hhhhj
    ,sdss
    28 s,ffff,eeeee,gfgggg,rrrrwwwww,trtttt,ddddff,rererere,ererere,eretrtyet,tytr
    yrytr
    29 ytry,sdfsfsfsf,rtgggfgddd,sdfssffff,sfrerffff,sfdfsfsfsfsf,sfsffsdfsd,dsfds
    fdsfd
    30 s,sdfsdsfds,sfsdsddddddddddd,ddddddddddddddddffffffffff,fgggggg,ssssss,ssss
    sssss
    31 sddddss,ffffffff,d,ssddsfdrgfr,dsdsadasda,sdsgtfsfsdfd,dfgfdgdgf,sdfgdfsdfs
    ,sdfs
    32 fsfsdfsdf,ertrtertree,rtrere,egrgerge,gerhegerg,erhgfsfs,rgyertreeerfer,grt
    erter
    33 greter,ergerger,gergergerhsgdf,gdghtrt,egrtg,er,er,frg,egergergrege,gege,eg
    eg,eg
    34 ergerg,egerg,ergerg,egewgrdg,ghfsdg,dfghsraegrg,gd,ghdshdg,dg,shsd,gdf,hsg,
    ss,dh
    35 gg,gsdg,d,gd,sg,dsgh,h,,sfyhtrutr,,sh,du,yf,dg,s,hts,s,yt,y,erer,,eh,fghfhf
    g,hhh
    36 ,dsdddddddddddddddddddddddddddddddddddddddddddddddddddddddd,ddddddddddddddd
    ddddf
    37 ,dsfdfsdfsdfdfsdf,fsdfsd,fsf,sfsfsdfsdfdfsdfsd,fsdfsfsdfs,fsdfsdfsdf,sfdsf,
    sfsdf,
    38 dfsdf,sdf,fs,fdfsfsdf,fsdfs,fsd,fsdf,fd,,fdf,df,ddfdfdfdffdff,dfdf,dfdfsf,s
    fewrf,
    39 fd,fsdf,ff,fdsffsd,fsdfsfs,fererwewrweew,ewrew,erwrew,trt,wtre,tery,eyeyyer
    ,terye
    40 ,erewrewrwe,twet,wtwt,wtwe,rf,sfewtrt,erter,yt,yey,tr,yuu,uyur,ertre,ererty
    ,ryt,y
    41 dfdagdghadgdagdfgag,gfgafdga,gagag,daghdfhdf,gdfhh,gfugfhgf,hgfh,gfhgf,hgf,
    htr,t,
    42 ffsdfdsfdfdfdfdfdfdf,fdfsfdsssdfdsfdsfs,fsfsfdsfsfsfsfsfs,fsfsfsfsfsfsfsfsf
    sfsfsf
    43 ,dasdsadasdad,dddddddddddddddawwwwwwwwwwww,dada,fffffffffffffffffff,fffffff
    ,fffewr
    44 ,fwefew,fwefewf,wte,twe,fwe,twe,tweteeeeeeeeeeeeeeeeeeeeeeeeeee,ttttttttttt
    ,t,t,t
    45 twttwt,ttyyyyyyyyyyyyyyyyy,yyyyyyyyyyu,uuuuuuuuuuuuuuuuuuuuuiooooooooooiiii
    i',','));
    SELECT * FROM TABLE(ufSplitStringdemo('1,2,3,4,5,6,7,8,9,44,55,66,77,88,99,33,2
    *
    ERROR at line 1:
    ORA-01460: unimplemented or unreasonable conversion requested
    ORA-06512: at "KIITS.UFSPLITSTRINGDEMO", line 13
    ORA-06512: at line 1


    Elapsed: 00:00:00.04
    SQL>




    SELECT * FROM TABLE(ufSplitString('1,2,3,4,5,6,7,8,9,44,55,66,77,88,99,33,2
    2,11,00,01,02,03,04,05,06,0,712,13,14,15,16,17,18,19,111,123,142,154,1235,124,12
    5,1457,12,312,44,55,44,66,45788,55,7566,456,645,65,465,64,5,52,55,556,55,54,456,
    5645566,55566,55666,4556666,45++555,4555,445566564,56665447,4445,1231,34565,4556
    66,2566456,565565,556555,65566566,5566656,665666+5,666,d,ss,ss,d,ssdasdd,weqweqw
    ,qweqweq,weerwef,sfsf,ggrge,rertret,ergre,rerrgg,rertrete,err,rrer,hh,hhhhj,sdss
    s,ffff,eeeee,gfgggg,rrrrwwwww,trtttt,ddddff,rererere,ererere,eretrtyet,tytryrytr
    ytry,sdfsfsfsf,rtgggfgddd,sdfssffff,sfrerffff,sfdfsfsfsfsf,sfsffsdfsd,dsfdsfdsfd
    s,sdfsdsfds,sfsdsddddddddddd,ddddddddddddddddffffffffff,fgggggg,ssssss,sssssssss
    sddddss,ffffffff,d,ssddsfdrgfr,dsdsadasda,sdsgtfsfsdfd,dfgfdgdgf,sdfgdfsdfs,sdfs
    fsfsdfsdf,ertrtertree,rtrere,egrgerge,gerhegerg,erhgfsfs,rgyertreeerfer,grterter
    greter,ergerger,gergergerhsgdf,gdghtrt,egrtg,er,er,frg,egergergrege,gege,egeg,eg
    ergerg,egerg,ergerg,egewgrdg,ghfsdg,dfghsraegrg,gd,ghdshdg,dg,shsd,gdf,hsg,ss,dh
    gg,gsdg,d,gd,sg,dsgh,h,,sfyhtrutr,,sh,du,yf,dg,s,hts,s,yt,y,erer,,eh,fghfhfg,hhh
    ,dsdddddddddddddddddddddddddddddddddddddddddddddddddddddddd,dddddddddddddddddddf
    ,dsfdfsdfsdfdfsdf,fsdfsd,fsf,sfsfsdfsdfdfsdfsd,fsdfsfsdfs,fsdfsdfsdf,sfdsf,sfsdf,
    dfsdf,sdf,fs,fdfsfsdf,fsdfs,fsd,fsdf,fd,,fdf,df,ddfdfdfdffdff,dfdf,dfdfsf,sfewrf,
    fd,fsdf,ff,fdsffsd,fsdfsfs,fererwewrweew,ewrew,erwrew,trt,wtre,tery,eyeyyer,terye
    ,erewrewrwe,twet,wtwt,wtwe,rf,sfewtrt,erter,yt,yey,tr,yuu,uyur,ertre,ererty,ryt,y
    dfdagdghadgdagdfgag,gfgafdga,gagag,daghdfhdf,gdfhh,gfugfhgf,hgfh,gfhgf,hgf,htr,t,
    ffsdfdsfdfdfdfdfdfdf,fdfsfdsssdfdsfdsfs,fsfsfdsfsfsfsfsfs,fsfsfsfsfsfsfsfsfsfsfsf
    ,dasdsadasdad,dddddddddddddddawwwwwwwwwwww,dada,fffffffffffffffffff,fffffff,fffewr
    ,fwefew,fwefewf,wte,twe,fwe,twe,tweteeeeeeeeeeeeeeeeeeeeeeeeeee,ttttttttttt,t,t,t
    twttwt,ttyyyyyyyyyyyyyyyyy,yyyyyyyyyyu,uuuuuuuuuuuuuuuuuuuuuiooooooooooiiiiiiiiii,
    65,465,64,5,52,55,556,55,54,456,5645566,55566,55666,4556666,45++555,4555,445566564,
    66,2566456,565565,556555,65566566,5566656,665666+5,666,d,ss,ss,d,ssdasdd,weqweqw
    ,qweqweq,weerwef,sfsf,ggrge,rertret,ergre,rerrgg,rertrete,err,rrer,hh,hhhhj,sdss
    s,ffff,eeeee,gfgggg,rrrrwwwww,trtttt,ddddff,rererere,ererere,eretrtyet,tytryrytr
    ytry,sdfsfsfsf,rtgggfgddd,sdfssffff,sfrerffff,sfdfsfsfsfsf,sfsffsdfsd,dsfdsfdsfd
    s,sdfsdsfds,sfsdsddddddddddd,ddddddddddddddddffffffffff,fgggggg,ssssss,sssssssss
    sddddss,ffffffff,d,ssddsfdrgfr,dsdsadasda,sdsgtfsfsdfd,dfgfdgdgf,sdfgdfsdfs,sdfs
    fsfsdfsdf,ertrtertree,rtrere,egrgerge,gerhegerg,erhgfsfs,rgyertreeerfer,grterter
    greter,ergerger,gergergerhsgdf,gdghtrt,egrtg,er,er,frg,egergergrege,gege,egeg,eg
    ergerg,egerg,ergerg,egewgrdg,ghfsdg,dfghsraegrg,gd,ghdshdg,dg,shsd,gdf,hsg,ss,dh
    gg,gsdg,d,gd,sg,dsgh,h,,sfyhtrutr,,sh,du,yf,dg,s,hts,s,yt,y,erer,,eh,fghfhfg,hhh
    ,dsdddddddddddddddddddddddddddddddddddddddddddddddddddddddd,dddddddddddddddddddf
    ,dsfdfsdfsdfdfsdf,fsdfsd,fsf,sfsfsdfsdfdfsdfsd,fsdfsfsdfs,fsdfsdfsdf,sfdsf,sfsdf,
    dfsdf,sdf,fs,fdfsfsdf,fsdfs,fsd,fsdf,fd,,fdf,df,ddfdfdfdffdff,dfdf,dfdfsf,sfewrf,
    fd,fsdf,ff,fdsffsd,fsdfsfs,fererwewrweew,ewrew,erwrew,trt,wtre,tery,eyeyyer,terye
    ,erewrewrwe,twet,wtwt,wtwe,rf,sfewtrt,erter,yt,yey,tr,yuu,uyur,ertre,ererty,ryt,y
    dfdagdghadgdagdfgag,gfgafdga,gagag,daghdfhdf,gdfhh,gfugfhgf,hgfh,gfhgf,hgf,htr,t,
    ffsdfdsfdfdfdfdfdfdf,fdfsfdsssdfdsfdsfs,fsfsfdsfsfsfsfsfs,fsfsfsfsfsfsfsfsfsfsfsf
    ,dasdsadasdad,dddddddddddddddawwwwwwwwwwww,dada,fffffffffffffffffff,fffffff,fffewr
    ,fwefew,fwefewf,wte,twe,fwe,twe,tweteeeeeeeeeeeeeeeeeeeeeeeeeee,ttttttttttt,t,t,t
    twttwt,ttyyyyyyyyyyyyyyyyy,yyyyyyyyyyu,uuuuuuuuuuuuuuuuuuuuuiooooooooooiiiii',','));

    TOKENPOSITION
    -------------
    TOKENVALUE
    -----------------------------------------

    uuuuuuuuuuuuuuuuuuuuuiooooooooooiiiii


    491 rows selected.

    Elapsed: 00:00:03.50
    SQL>

    SEE this
     
  13. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    change the data type of input variables as follows.

    PTOKENSTRING VARCHAR2 ,
    PSEPARATORCHARACTER CHAR := ',' , --DEFAULT IS THE 'COMMA'
    PISDEBUG NUMBER := 0;

    It should work.


    Code (SQL):

    SQL> SELECT COUNT(*) FROM TABLE(UFSPLITSTRINGDEMO ('1,2,3,4,5,6,7,8,9,44,55,66,77 ,88,99,33,2
    2,11,00,01,02,03,04,05,06,0,712,13,14,15,16,17,18, 19,111,123,142,154,1235,124,12
      2    3  5,1457,12,312,44,55,44,66,45788,55,7566,456,645,65 ,465,64,5,52,55,556,55,54,456,
      4  5645566,55566,55666,4556666,45++555,4555,445566564 ,56665447,4445,1231,34565,4556
      5  66,2566456,565565,556555,65566566,5566656,665666+5 ,666,d,ss,ss,d,ssdasdd,weqweqw
      6  ,qweqweq,weerwef,sfsf,ggrge,rertret,ergre,rerrgg,r ertrete,err,rrer,hh,hhhhj,sdss
      7  s,ffff,eeeee,gfgggg,rrrrwwwww,trtttt,ddddff,rerere re,ererere,eretrtyet,tytryrytr
      8  ytry,sdfsfsfsf,rtgggfgddd,sdfssffff,sfrerffff,sfdf sfsfsfsf,sfsffsdfsd,dsfdsfdsfd
      9  s,sdfsdsfds,sfsdsddddddddddd,ddddddddddddddddfffff fffff,fgggggg,ssssss,sssssssss
     10  sddddss,ffffffff,d,ssddsfdrgfr,dsdsadasda,sdsgtfsf sdfd,dfgfdgdgf,sdfgdfsdfs,sdfs
     11  fsfsdfsdf,ertrtertree,rtrere,egrgerge,gerhegerg,er hgfsfs,rgyertreeerfer,grterter
     12  greter,ergerger,gergergerhsgdf,gdghtrt,egrtg,er,er ,frg,egergergrege,gege,egeg,eg
     13  ergerg,egerg,ergerg,egewgrdg,ghfsdg,dfghsraegrg,gd ,ghdshdg,dg,shsd,gdf,hsg,ss,dh
     14  gg,gsdg,d,gd,sg,dsgh,h,,sfyhtrutr,,sh,du,yf,dg,s,h ts,s,yt,y,erer,,eh,fghfhfg,hhh
     15  ,dsddddddddddddddddddddddddddddddddddddddddddddddd ddddddddd,dddddddddddddddddddf
     16  ,dsfdfsdfsdfdfsdf,fsdfsd,fsf,sfsfsdfsdfdfsdfsd,fsd fsfsdfs,fsdfsdfsdf,sfdsf,sfsdf,
     17  dfsdf,sdf,fs,fdfsfsdf,fsdfs,fsd,fsdf,fd,,fdf,df,dd fdfdfdffdff,dfdf,dfdfsf,sfewrf,
     18  fd,fsdf,ff,fdsffsd,fsdfsfs,fererwewrweew,ewrew,erw rew,trt,wtre,tery,eyeyyer,terye
     19  ,erewrewrwe,twet,wtwt,wtwe,rf,sfewtrt,erter,yt,yey ,tr,yuu,uyur,ertre,ererty,ryt,y
     20  dfdagdghadgdagdfgag,gfgafdga,gagag,daghdfhdf,gdfhh ,gfugfhgf,hgfh,gfhgf,hgf,htr,t,
     21  ffsdfdsfdfdfdfdfdfdf,fdfsfdsssdfdsfdsfs,fsfsfdsfsf sfsfsfs,fsfsfsfsfsfsfsfsfsfsfsf
     22  ,dasdsadasdad,dddddddddddddddawwwwwwwwwwww,dada,ff fffffffffffffffff,fffffff,fffewr
     23  ,fwefew,fwefewf,wte,twe,fwe,twe,tweteeeeeeeeeeeeee eeeeeeeeeeeee,ttttttttttt,t,t,t
     24  twttwt,ttyyyyyyyyyyyyyyyyy,yyyyyyyyyyu,uuuuuuuuuuu uuuuuuuuuuiooooooooooiiiiiiiiii,
     25  65,465,64,5,52,55,556,55,54,456,5645566,55566,5566 6,4556666,45++555,4555,445566564,
     26  66,2566456,565565,556555,65566566,5566656,665666+5 ,666,d,ss,ss,d,ssdasdd,weqweqw
     27  ,qweqweq,weerwef,sfsf,ggrge,rertret,ergre,rerrgg,r ertrete,err,rrer,hh,hhhhj,sdss
     28  s,ffff,eeeee,gfgggg,rrrrwwwww,trtttt,ddddff,rerere re,ererere,eretrtyet,tytryrytr
     29  ytry,sdfsfsfsf,rtgggfgddd,sdfssffff,sfrerffff,sfdf sfsfsfsf,sfsffsdfsd,dsfdsfdsfd
     30  s,sdfsdsfds,sfsdsddddddddddd,ddddddddddddddddfffff fffff,fgggggg,ssssss,sssssssss
     31  sddddss,ffffffff,d,ssddsfdrgfr,dsdsadasda,sdsgtfsf sdfd,dfgfdgdgf,sdfgdfsdfs,sdfs
     32  fsfsdfsdf,ertrtertree,rtrere,egrgerge,gerhegerg,er hgfsfs,rgyertreeerfer,grterter
     33  greter,ergerger,gergergerhsgdf,gdghtrt,egrtg,er,er ,frg,egergergrege,gege,egeg,eg
     34  ergerg,egerg,ergerg,egewgrdg,ghfsdg,dfghsraegrg,gd ,ghdshdg,dg,shsd,gdf,hsg,ss,dh
     35  gg,gsdg,d,gd,sg,dsgh,h,,sfyhtrutr,,sh,du,yf,dg,s,h ts,s,yt,y,erer,,eh,fghfhfg,hhh
     36  ,dsddddddddddddddddddddddddddddddddddddddddddddddd ddddddddd,dddddddddddddddddddf
     37  ,dsfdfsdfsdfdfsdf,fsdfsd,fsf,sfsfsdfsdfdfsdfsd,fsd fsfsdfs,fsdfsdfsdf,sfdsf,sfsdf,
     38  dfsdf,sdf,fs,fdfsfsdf,fsdfs,fsd,fsdf,fd,,fdf,df,dd fdfdfdffdff,dfdf,dfdfsf,sfewrf,
     39  fd,fsdf,ff,fdsffsd,fsdfsfs,fererwewrweew,ewrew,erw rew,trt,wtre,tery,eyeyyer,terye
     40  ,erewrewrwe,twet,wtwt,wtwe,rf,sfewtrt,erter,yt,yey ,tr,yuu,uyur,ertre,ererty,ryt,y
     41  dfdagdghadgdagdfgag,gfgafdga,gagag,daghdfhdf,gdfhh ,gfugfhgf,hgfh,gfhgf,hgf,htr,t,
     42  ffsdfdsfdfdfdfdfdfdf,fdfsfdsssdfdsfdsfs,fsfsfdsfsf sfsfsfs,fsfsfsfsfsfsfsfsfsfsfsf
     43  ,dasdsadasdad,dddddddddddddddawwwwwwwwwwww,dada,ff fffffffffffffffff,fffffff,fffewr
     44  ,fwefew,fwefewf,wte,twe,fwe,twe,tweteeeeeeeeeeeeee eeeeeeeeeeeee,ttttttttttt,t,t,t
     45  twttwt,ttyyyyyyyyyyyyyyyyy,yyyyyyyyyyu,uuuuuuuuuuu uuuuuuuuuuiooooooooooiiiii'
    ));

      COUNT(*)
    ----------
           491

    SQL>

     
     
  14. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    yes but here we can have Unicode data..........
     
  15. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    ok. Now,

    Another options are.

    1. To use NCLOB instead of NVARCHAR2 for Input string.
    2. To Populate the objrect table by using normal For loop in the Function (not pipeline function) and call it normally.

    But as long as there is no performance issue, I recommend to continue using current pipeline function.
     
  16. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for your advise.........