Separating a string [message #7822] |
Thu, 10 July 2003 12:07 |
MM
Messages: 27 Registered: July 2002
|
Junior Member |
|
|
I have a error code string 'OM-1001;TG-1990;SM-1001', i need to separate the error code and populate approrpriate error mesg. I have a function which does the separation based on the ';' and return the error mesg. I have cut pasted the function for your reference.
FUNCTION EXTRACT_NEXT(p_string IN VARCHAR2, p_error_out OUT g_error_code_tab_typ)
RETURN BOOLEAN IS
v_string VARCHAR2(256);
v_pos NUMBER;
v_errmsg VARCHAR2(200);
v_cnt NUMBER := 0;
BEGIN
v_string := p_string;
LOOP
v_pos := instr(v_string, ';');
EXIT WHEN (nvl(v_pos, 0) = 0);
v_cnt := v_cnt + 1;
p_error_out(v_cnt) := (trim(substr(v_string, 1, v_pos - 1)));
v_string := substr(v_string, v_pos + 1);
END LOOP;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
v_errmsg := SUBSTR(SQLERRM, 1,200);
RETURN FALSE;
END EXTRACT_NEXT;
This function is executing fine when i have a string of error codes like this 'OM-1001;TG-1990;SM-1001'.
When io have a single error code OM-1001 it does not come with a ';' at the end of it and in case of multiple error codes the last one comes without a ';', I am missing out the single ones and the last one in case of multiple ones.
WHat do i need to modify the current function to catch the single ones and the last ones in case of multiple codes.
Thanks in advance
MM
|
|
|
|
|
Re: Separating a string [message #7825 is a reply to message #7822] |
Thu, 10 July 2003 12:59 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
I think you just need to add the delimiter to the end of the string (since it is never at the end whether there is one entry or multiple entries):
v_string := p_string || ';';
|
|
|
Re: Separating a string [message #7826 is a reply to message #7824] |
Thu, 10 July 2003 13:08 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
Really? I have no problems:SQL> CREATE OR REPLACE FUNCTION extract_next (
2 p_string IN VARCHAR2
3 , p_error_out OUT DBMS_SQL.VARCHAR2S
4 )
5 RETURN BOOLEAN IS
6 v_string VARCHAR2(256);
7 v_pos NUMBER;
8 v_errmsg VARCHAR2(200);
9 v_cnt NUMBER := 0;
10 BEGIN
11 v_string := p_string <font color=blue>|| ';'</font>;
12 LOOP
13 v_pos := instr(v_string, ';');
14 EXIT WHEN (nvl(v_pos, 0) = 0);
15 v_cnt := v_cnt + 1;
16 p_error_out(v_cnt) := (trim(substr(v_string, 1, v_pos - 1)));
17 v_string := substr(v_string, v_pos + 1);
18 END LOOP;
19 RETURN TRUE;
20 EXCEPTION
21 WHEN OTHERS THEN
22 v_errmsg := SUBSTR(SQLERRM, 1,200);
23 RETURN FALSE;
24 END extract_next;
25 /
Function created.
SQL> DECLARE
2 b BOOLEAN;
3 x DBMS_SQL.VARCHAR2S;
4 BEGIN
5 b := extract_next ('&p_string',x);
6 IF (b) THEN
7 DBMS_OUTPUT.PUT_LINE('b = TRUE');
8 ELSE
9 DBMS_OUTPUT.PUT_LINE('b = FALSE');
10 END IF;
11 FOR i IN 1..x.COUNT LOOP
12 DBMS_OUTPUT.PUT_LINE('x(' || i || ') = ' || x(i));
13 END LOOP;
14 END;
15 /
Enter value for p_string: OM-1001
old 5: b := extract_next ('&p_string',x);
new 5: b := extract_next ('OM-1001',x);
b = TRUE
x(1) = OM-1001
PL/SQL procedure successfully completed.
SQL> /
Enter value for p_string: OM-1001;TG-1990;SM-1001
old 5: b := extract_next ('&p_string',x);
new 5: b := extract_next ('OM-1001;TG-1990;SM-1001',x);
b = TRUE
x(1) = OM-1001
x(2) = TG-1990
x(3) = SM-1001
PL/SQL procedure successfully completed.
SQL> The only difference is that I used DBMS_SQL.VARCHAR2S as my array of VARCHAR2s instead of your custom g_error_code_tab_typ.
Maybe try adding some DBMS_OUTPUT.PUT_LINEs to the function, and see where it's getting hung up.
How are you calling your function?
A.
|
|
|
Re: Separating a string [message #7827 is a reply to message #7825] |
Thu, 10 July 2003 13:23 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Sorry - didn't see Art's reply before posting. I also don't see any reason why it should go into an infinite loop. I use this same template all the time with no problems.
|
|
|
Re: Separating a string [message #7828 is a reply to message #7826] |
Thu, 10 July 2003 13:46 |
MM
Messages: 27 Registered: July 2002
|
Junior Member |
|
|
Art,
I dont know what happened when i ran first time, the process would not come out and i had to kill the session. It works fine now after trying out the same way you mentioned.
Thanks for all the help.
MM
|
|
|