Home » SQL & PL/SQL » SQL & PL/SQL » Separating a string
Separating a string [message #7822] Thu, 10 July 2003 12:07 Go to next message
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 #7823 is a reply to message #7822] Thu, 10 July 2003 12:23 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Try
....
BEGIN
    v_string := p_string<font color=blue> || ';'</font>;
LOOP
....
A.
Re: Separating a string [message #7824 is a reply to message #7823] Thu, 10 July 2003 12:36 Go to previous messageGo to next message
MM
Messages: 27
Registered: July 2002
Junior Member
Art,

It goes into a indefinite loop, the program gets hung.
Any other thoughts.

MM
Re: Separating a string [message #7825 is a reply to message #7822] Thu, 10 July 2003 12:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: help with identifying duplicate rows and delete them
Next Topic: Connect by
Goto Forum:
  


Current Time: Fri Apr 26 20:22:34 CDT 2024