Dividing the text data into rows 2

[rk68]

Hi,

[highlight #FCE94F]98765,AB123,3;[/highlight][highlight #8AE234]98765,AD243,2;[/highlight][highlight #EF2929]98765,BG153,5;[/highlight]

The above text data would have the following fields ID, SKU, QTY followed by ;
There could be multiple records with all the 3 fields in a line with ; separator.
I want to push the data the into a table in the following manner. i.e. row wise with # of quantity broken into 1.
So therefore the 1st record has 3 Qty, it will be broken into 3 records/rows with ID & SKU repeated.

ID SKU QTY
[highlight #FCE94F]98765 AB123 1
98765 AB123 1
98765 AB123 1[/highlight]
98765 AD243 1
98765 AD243 1
[highlight #EF2929]98765 BG153 1
98765 BG153 1
98765 BG153 1
98765 BG153 1
98765 BG153 1[/highlight]

How can I identify the separator, divide # of quantity to multiple rows to be used in stored procedure?
I'm using Oracle 11g.

TIA,
RAJ

 

[carp]

Here's a quick and dirty crack at it that should get you pretty close:

CREATE OR REPLACE
PROCEDURE breaking_and_entering(p_string IN VARCHAR2) IS
   l_id   NUMBER;
   l_idx1 INTEGER;
   l_idx2 INTEGER;
   l_qty  INTEGER;
   l_sku VARCHAR2(100);
   l_substr VARCHAR2(1000);   
BEGIN
   l_idx1 := 1;
   l_idx2 := INSTR(p_string, ';',l_idx1);
   WHILE (l_idx2 != 0) LOOP
      -- BREAK OUT THE FIRST SUBRECORD
      l_substr := SUBSTR(p_string,l_idx1, l_idx2 - l_idx1);
      -- BREAK OUT THE RECORD ELEMENTS
      l_id := SUBSTR(l_substr, 1,INSTR(l_substr,',')-1);
      l_sku := SUBSTR(l_substr,INSTR(l_substr,',')+1, INSTR(l_substr,',',-1)- INSTR(l_substr,',')-1);
      l_qty := SUBSTR(l_substr,INSTR(l_substr,',',-1)+1,LENGTH(l_substr));
      -- CREATE THE INSERT RECORDS
      FOR i IN 1..l_qty LOOP
         INSERT INTO my_table VALUES (l_id,l_sku,1);
      END LOOP;
      -- GET READY FOR THE NEXT RECORD
      l_idx1 := l_idx2 + 1;
      l_idx2 := INSTR(p_string, ';',l_idx1);
    END LOOP;
EXCEPTION
   WHEN OTHERS THEN 
      -- REPLACE THE FOLLOWING WITH HOWEVER YOU WANT TO HANDLE AN EXCEPTION
      NULL;
END breaking_and_entering;

 

[rk68]

Thanks Carp. Let me try it out & will update you.

 

[rk68]

Hi Carp,
I did try the code, works fine. Need to add some more of checks before inserting into the table.
Wondering why this simple code didn't strike me.
Thanks once again.

Thanks,
RAJ

 

[Andrzejek]

rk68,
The best way to say: "Thank you" here at TT is to award somebody a star for their help, which you only did twice in last 16 years. Don't you find the help here useful


---- Andy

There is a great need for a sarcasm font.

 

[rk68]

Sorry Andy, I didn't note that. Hence forth I will definitely do.
Thanks