Home » SQL & PL/SQL » SQL & PL/SQL » Split Function for Input Blob (Oracle 10g)
Split Function for Input Blob [message #356128] Wed, 29 October 2008 12:51 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

How to rewrite the same function for input string as Clob.


CREATE OR REPLACE FUNCTION str2tbl(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
    RETURN vcarray PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index    := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start    := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start    := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END str2tbl;



 

instead of p_string IN VARCHAR2
p_string IN Clob




Thanks
Re: Split Function for Input Blob [message #356132 is a reply to message #356128] Wed, 29 October 2008 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did you try so far?
Or are you expecting we do all the job for you?

Regards
Michel
Re: Split Function for Input Blob [message #356135 is a reply to message #356128] Wed, 29 October 2008 13:10 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#BABEEAAH

Hope this helps.

Regards

Raj
Re: Split Function for Input Blob [message #356136 is a reply to message #356132] Wed, 29 October 2008 13:16 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
I am not expecting any one to do my job michael. I just asked how to rewrite the same proc with clob.
Re: Split Function for Input Blob [message #356138 is a reply to message #356132] Wed, 29 October 2008 13:17 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Its an Pl/Sql Stored Procedure try to understand.
Re: Split Function for Input Blob [message #356142 is a reply to message #356138] Wed, 29 October 2008 13:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I know it's a PL/SQL procedure and I know it comes from T. Kyte and I ask you to post what you already tried (or just searched) to make it work with CLOB for instance with the help of the link Raj posted.

Regards
Michel
Re: Split Function for Input Blob [message #356143 is a reply to message #356142] Wed, 29 October 2008 13:38 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
As I don't have any idea about clob datatypes. So asked to give some help on rewriting the above procedure with same functionality but input as clob.
Re: Split Function for Input Blob [message #356145 is a reply to message #356143] Wed, 29 October 2008 13:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Application Developer's Guide - Large Objects

Regards
Michel
Re: Split Function for Input Blob [message #356381 is a reply to message #356128] Thu, 30 October 2008 10:01 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

Below is the procedure written for input string clob.

create or replace FUNCTION Split
(
iv_String IN CLOB,
v_Delimiter IN VARCHAR2
)
RETURN sys_refcursor
AS
v_length NUMBER:= LENGTH(iv_String);
v_start NUMBER(10,0):=1;
V_temp2 NUMBER(10,0):=0;
v_index NUMBER;
v_temp SYS_REFCURSOR;
BEGIN
WHILE(v_start <= v_length)
LOOP
BEGIN
v_index:= INSTR(iv_String, v_delimiter, v_start);
IF v_index = 0 THEN
V_temp2:=V_temp2+1;
INSERT INTO tt_v_SplittedValues
VALUES (V_temp2 ,SUBSTR(iv_String, v_start) );
v_start := v_length + 1;
ELSE
V_temp2:=V_temp2+1;
INSERT INTO tt_v_SplittedValues
VALUES (V_temp2 ,SUBSTR(iv_String, v_start, v_index - v_start) );
v_start := v_index + 1;
END IF;
END;
END LOOP;
OPEN v_temp FOR
SELECT * FROM tt_v_SplittedValues;
return v_temp;


END Split;




Function got created successfully

sql> variable x sys_refcursor
sql>exec :x:=split('asdf|dsdsd|fdfdf|kkhh','|');
sql> print :x


Output
1 asdf
2 dsdsd
3 fdfdf
4 kkhh

Its giving expected result when the string is small.

While execution of a long string i am encountered with following error

sql> variable x sys_refcursor
sql>exec :x:=split('1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.
2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPo
int$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$';
,'|');


I am getting an below error

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






Any Help really appreciated
Re: Split Function for Input Blob [message #356384 is a reply to message #356381] Thu, 30 October 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Split your lines (not only in the post but also in SQL*Plus Wink ).

Regards
Michel
Re: Split Function for Input Blob [message #356419 is a reply to message #356384] Thu, 30 October 2008 12:28 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi Michel,

Could you please let me know the sql*plus command to split the lines. Is there any other way to this in pl/sql function with any new features of oracle 10g or existing features of 10g.

Thanks
Re: Split Function for Input Blob [message #356427 is a reply to message #356419] Thu, 30 October 2008 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't use "exec" but "begin end;"
2/ Concatenate substrings each one on a line

Regards
Michel
Re: Split Function for Input Blob [message #356433 is a reply to message #356427] Thu, 30 October 2008 13:14 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi Michel,

Tried with pl/sql block but no lock

DECLARE
IV_STRING clob;
V_DELIMITER VARCHAR2(200);
v_Return sys_refcursor;
BEGIN
IV_STRING :='1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.
2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPo
int$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$';
V_DELIMITER :='$';

v_Return := SPLIT(
IV_STRING => IV_STRING,
V_DELIMITER => V_DELIMITER
);

END;



Encountered the below error

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


Thanks
Re: Split Function for Input Blob [message #356452 is a reply to message #356433] Thu, 30 October 2008 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
2/ Concatenate substrings each one on a line

Regards
Michel
Re: Split Function for Input Blob [message #358487 is a reply to message #356452] Tue, 11 November 2008 09:47 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member


Hi,

Here I have rewritten the same proc using lob's. But no luck.

This particular proc gets i/p from other procs. so I cannot concatenate or break the lines.


create or replace FUNCTION Split(
p_string IN clob,
p_delimiter IN VARCHAR2
) return split_rec pipelined is
v_length NUMBER := dbms_lob.getlength(p_string);
v_start NUMBER := 1;
v_index NUMBER;
V_temp NUMBER(10,0):=0;
begin
WHILE(v_start <= v_length)
LOOP
v_index := dbms_lob.INSTR(p_string, p_delimiter, v_start);

IF v_index = 0 THEN
V_temp:=V_temp+1;
PIPE ROW(split_obj(v_temp,dbms_lob.SUBSTR(p_string, v_length,v_start)));
v_start := v_length + 1;
ELSE
v_temp:=v_temp+1;
PIPE ROW(split_obj(v_temp,dbms_lob.SUBSTR(p_string, v_index - v_start,v_start)));
v_start := v_index + 1;
END IF;
END LOOP;
return;
end Split;


select * from table(cast(Split('1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.
2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPoint$3.2,3.1,2.2$$$$$$$$$1$$1$-1$489$53$ProcessTest$Title$$2$$$$$$$$$$|2$-1$490$133$Quesion1$Question$1$2.2,2.1$$$$$$$$$$|1$2$-1$-50$70$Yes$$3$$|2$2$-1$50$70$No$$4$$|3$-1$440$233$Question2$Question$2.1$3.2,3.1$$$$$$$$$$|1$3$-1$-50$70$Yes$$4$$|2$3$-1$50$70$No$$4$$|4$-1$594$341$ExitPoint1$ExitPo','|')
 as split_rec)); 

Input is too long string cannot be allowed more than 4000 characters.



Request to let me know. If we have any alternative sol to achieve the same result.

Thanks in advance
Re: Split Function for Input Blob [message #358490 is a reply to message #358487] Tue, 11 November 2008 09:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 30 October 2008 20:36
Quote:
2/ Concatenate substrings each one on a line

Regards
Michel


Re: Split Function for Input Blob [message #358502 is a reply to message #358490] Tue, 11 November 2008 11:17 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi Michel,

This parameter gets input parameter from front end dynamically. So its not possible to break into 2 strings and concatenate.


It gets more than 400kb of string as a input parameter from front end application.

Thanks in advance


Re: Split Function for Input Blob [message #358503 is a reply to message #356128] Tue, 11 November 2008 11:22 Go to previous messageGo to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi,

It gets an input string of size more than 400kb from front end application. Its an dynamically generated string based on options selected by user.

It collects all selected options by user and sends as a input string to procedure.

Is there any alternative way to achieve the same.

Thanks
Re: Split Function for Input Blob [message #358514 is a reply to message #358503] Tue, 11 November 2008 12:26 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it comes from a front end you have it in a variable you have not the problem you post.

Regards
Michel
Previous Topic: Deviation of monthly totals?
Next Topic: Function and ref cursor questions
Goto Forum:
  


Current Time: Mon Dec 05 20:51:00 CST 2016

Total time taken to generate the page: 0.12152 seconds