Forum Stats

  • 3,769,695 Users
  • 2,253,009 Discussions
  • 7,875,155 Comments

Discussions

Question regarding breaking 3 lines into individual table rows

buggleboy007
buggleboy007 Member Posts: 285 Bronze Badge

I have created a Package with 1 procedure and 1 function. In the procedure, I have the following 11 input parameters which are supposed to be inserting a new row in IRI_POS_TRANSFERS table.

p_NbrInTransfer_id  IN  IRI_POS_TRANSFERS.transfer_id%TYPE,
p_NbrBusinessunit_id IN  IRI_POS_TRANSFERS.business_unit_id%TYPE,
p_NbrRequest_id    IN  IRI_POS_TRANSFERS.request_id%TYPE,
p_ChrTransfer_type  IN  IRI_POS_TRANSFERS.transfer_type%TYPE,
p_ChrFrom_site    IN IRI_POS_TRANSFERS.from_site%TYPE, 
p_ChrTo_site 	   IN  IRI_POS_TRANSFERS.to_site%TYPE, 
p_Chrsite_id     IN  IRI_POS_TRANSFERS.site_id%TYPE, 
p_NbrFrom_business_unit_id  IN  IRI_POS_TRANSFERS.from_business_unit_id%TYPE,
p_NbrTo_business_unit_id   IN  IRI_POS_TRANSFERS.to_business_unit_id%TYPE, 
p_ChrPOS_transfer_id     IN  IRI_POS_TRANSFERS.pos_transfer_id%TYPE,
p_Chrline_details      IN  VARCHAR2,

Everything is very straight forward when it comes to inserting data excepting with the parameter - p_Chrline_details. The data that would be sent to me with this parameter would be as follows:

<Lines>
  <line id="0" itemqty=" " skustyle=" "   skudimension=" " skusize=" " skucolor=" "/> 
  <line id="1" itemqty="2" skustyle="style1" skudimension="12" skusize="XL" skucolor="34"/>
  <line id="2" itemqty="5" skustyle="style2" skudimension="14" skusize="L" skucolor="40"/>
</Lines>

The 1st line is a header and the next 2 lines are details for that header.

My question is, how can this be broken so that it can be inserted into the IRI_POS_DETAILS table. Again since these are 3 rows of data it has to loop in 3 times to insert the data. How can this be achieved? I really have no idea about this.

So when the lines above are broken it should be something on these lines when the table is queried against a given transfer number:


Here is my Package spec and Package body:

CREATE OR REPLACE PACKAGE POStoMerchDirectTransfer
IS 
 --Global public variables
 TYPE iripostransferdetails_rt IS RECORD
		       (Businessunitid       IRI_POS_TRANSFERS.business_unit_id%TYPE,
		       Transfer_id        IRI_POS_TRANSFERS.transfer_id%TYPE,  
		       transfer_occurence_type  IRI_POS_TRANSFERS.transfer_occurence_type%TYPE,       
		       line_no          IRI_POS_TRANSFERS.line_no%TYPE,               
		       prgid           IRI_POS_TRANSFERS.prgid%TYPE,                
		       transfer_type       IRI_POS_TRANSFERS.transfer_type%TYPE,            
		       request_id         IRI_POS_TRANSFERS.request_id%TYPE,             
		       from_site         IRI_POS_TRANSFERS.from_site%TYPE,              
		       to_site          IRI_POS_TRANSFERS.to_site%TYPE,               
		       bar_code_bus_unit_id    IRI_POS_TRANSFERS.bar_code_bus_unit_id%TYPE,        
		       bar_code_sub_type     IRI_POS_TRANSFERS.bar_code_sub_type%TYPE,          
		       bar_code_id        IRI_POS_TRANSFERS.bar_code_id%TYPE,             
		       style_id          IRI_POS_TRANSFERS.style_id%TYPE,              
		       color_id          IRI_POS_TRANSFERS.color_id%TYPE,              
		       dimension_id        IRI_POS_TRANSFERS.dimension_id%TYPE,            
		       valid_dimension_id     IRI_POS_TRANSFERS.valid_dimension_id%TYPE,         
		       size_id          IRI_POS_TRANSFERS.size_id%TYPE,               
		       valid_size_id       IRI_POS_TRANSFERS.valid_size_id%TYPE,            
		       item_qty          IRI_POS_TRANSFERS.item_qty%TYPE,              
		       valid_ind         IRI_POS_TRANSFERS.valid_ind%TYPE,              
		       status           IRI_POS_TRANSFERS.status%TYPE,               
		       site_id          IRI_POS_TRANSFERS.site_id%TYPE,               
		       valid_header        IRI_POS_TRANSFERS.valid_header%TYPE,            
		       valid_detail        IRI_POS_TRANSFERS.valid_detail%TYPE,            
		       reason_id         IRI_POS_TRANSFERS.reason_id%TYPE,              
		       transfer_origin_name    IRI_POS_TRANSFERS.transfer_origin_name%TYPE,        
		       from_site_retail_value   IRI_POS_TRANSFERS.from_site_retail_value%TYPE,       
		       to_site_retail_value    IRI_POS_TRANSFERS.to_site_retail_value%TYPE,        
		       unit_cost         IRI_POS_TRANSFERS.unit_cost%TYPE,              
		       expense_transfer_id    IRI_POS_TRANSFERS.expense_transfer_id%TYPE,         
		       rej_rpt_printed_ind    IRI_POS_TRANSFERS.rej_rpt_printed_ind%TYPE,         
		       rejected_id        IRI_POS_TRANSFERS.rejected_id%TYPE,             
		       reason_sub_type      IRI_POS_TRANSFERS.reason_sub_type%TYPE,           
		       process_status       IRI_POS_TRANSFERS.process_status%TYPE,           
		       process_date_time     IRI_POS_TRANSFERS.process_date_time%TYPE,          
		       richter_version_id     IRI_POS_TRANSFERS.richter_version_id%TYPE,         
		       pos_version_id       IRI_POS_TRANSFERS.pos_version_id%TYPE,           
		       control_no         IRI_POS_TRANSFERS.control_no%TYPE,             
		       user_trace_id       IRI_POS_TRANSFERS.user_trace_id%TYPE,            
		       user_id          IRI_POS_TRANSFERS.user_id%TYPE,               
		       ride_error_code      IRI_POS_TRANSFERS.ride_error_code%TYPE,           
		       severity_code       IRI_POS_TRANSFERS.severity_code%TYPE,            
		       inventory_adjust_id    IRI_POS_TRANSFERS.inventory_adjust_id%TYPE,         
		       from_business_unit_id   IRI_POS_TRANSFERS.from_business_unit_id%TYPE,        
		       to_business_unit_id    IRI_POS_TRANSFERS.to_business_unit_id%TYPE,         
		       cancel_ind         IRI_POS_TRANSFERS.cancel_ind%TYPE,             
		       pos_transfer_id      IRI_POS_TRANSFERS.pos_transfer_id%TYPE,           
		       date_created        IRI_POS_TRANSFERS.date_created%TYPE,            
		       process_date_created    IRI_POS_TRANSFERS.process_date_created%TYPE,        
		       ride_out_date       IRI_POS_TRANSFERS.ride_out_date%TYPE,            
		       transfer_date       IRI_POS_TRANSFERS.transfer_date%TYPE,            
		       user_in_date        IRI_POS_TRANSFERS.user_in_date%TYPE,            
		       --fkdomshipmentid     IRI_POS_TRANSFERS.fkdomshipmentid%TYPE,           
		       rma_code          IRI_POS_TRANSFERS.rma_code%TYPE,              
		       ref_dom_order_id      IRI_POS_TRANSFERS.web_order_id%TYPE  
         
		     );
		      
		     TYPE lv_iripostransferdetails_aa  IS TABLE OF iripostransferdetails_rt INDEX BY PLS_INTEGER;

	      
 procedure accept_transfer_transferdetails(p_NbrInTransfer_id  IN  IRI_POS_TRANSFERS.transfer_id%TYPE,
                      p_NbrBusinessunit_id IN  IRI_POS_TRANSFERS.business_unit_id%TYPE,
                      p_NbrRequest_id    IN  IRI_POS_TRANSFERS.request_id%TYPE,  
                      p_ChrTransfer_type  IN  IRI_POS_TRANSFERS.transfer_type%TYPE,
                      p_ChrFrom_site    IN IRI_POS_TRANSFERS.from_site%TYPE, 
                      p_ChrTo_site 					IN  IRI_POS_TRANSFERS.to_site%TYPE, 
                      p_Chrsite_id     IN  IRI_POS_TRANSFERS.site_id%TYPE, 
                      p_NbrFrom_business_unit_id  IN  IRI_POS_TRANSFERS.from_business_unit_id%TYPE,
                      p_NbrTo_business_unit_id   IN  IRI_POS_TRANSFERS.to_business_unit_id%TYPE, 
                      p_ChrPOS_transfer_id     IN  IRI_POS_TRANSFERS.pos_transfer_id%TYPE,
                      p_Chrline_details      IN  VARCHAR2,
 																				 p_aaoutiriposdetails OUT lv_iripostransferdetails_aa);
 																			  	  
END POStoMerchDirectTransfer;  
 																			  /	
 				
CREATE OR REPLACE PACKAGE BODY POStoMerchDirectTransfer
IS          

FUNCTION generate_job_id RETURN NUMBER;

procedure accept_transfer_transferdetails(p_NbrInTransfer_id  IN  IRI_POS_TRANSFERS.transfer_id%TYPE,
                     p_NbrBusinessunit_id IN  IRI_POS_TRANSFERS.business_unit_id%TYPE,
                     p_NbrRequest_id    IN  IRI_POS_TRANSFERS.request_id%TYPE,
                     p_ChrTransfer_type  IN  IRI_POS_TRANSFERS.transfer_type%TYPE,
                     p_ChrFrom_site    IN IRI_POS_TRANSFERS.from_site%TYPE, 
                     p_ChrTo_site 					IN  IRI_POS_TRANSFERS.to_site%TYPE, 
                     p_Chrsite_id     IN  IRI_POS_TRANSFERS.site_id%TYPE, 
                     p_NbrFrom_business_unit_id  IN  IRI_POS_TRANSFERS.from_business_unit_id%TYPE,
                     p_NbrTo_business_unit_id   IN  IRI_POS_TRANSFERS.to_business_unit_id%TYPE, 
                     p_ChrPOS_transfer_id     IN  IRI_POS_TRANSFERS.pos_transfer_id%TYPE,
                     p_Chrline_details      IN  VARCHAR2,
 																				--p_aaoutiriposdetails OUT lv_iripostransferdetails_aa
 																				)
IS
 		
 		 lv_transfer_occurence_type  IRI_POS_TRANSFERS.transfer_occurence_type%TYPE;
 		 lv_prgid           IRI_POS_TRANSFERS.prgid%TYPE;
 		 lv_Nbrjob_id         NUMBER;
 																			  	  
BEGIN


    IF p_ChrTransfer_type = 'TRANSFER OUT' THEN
     lv_transfer_occurence_type:='S';
     lv_prgid:='TOUT';
   ELSIF p_ChrTransfer_type= 'TRANSFER REQ' THEN
     lv_transfer_occurence_type:='S';
     lv_prgid:='TREQ'; 
   ELSIF p_ChrTransfer_type = 'TRANSFER IN' THEN
     lv_transfer_occurence_type:='D';
     lv_prgid:='TRIN';
   END IF;  
    
   
 
    INSERT INTO iri_pos_transfers
      ( job_id,
							business_unit_id,
							transfer_id,
							transfer_occurence_type,
							line_no,
							prgid,
							transfer_type,
							transfer_date,
							request_id,
							from_site,
							to_site,
							bar_code_bus_unit_id,
							bar_code_sub_type,
							bar_code_id,
							style_id,
							color_id,
							dimension_id,
							valid_dimension_id,
							size_id,
							valid_size_id,
							item_qty,
							valid_ind,
							status,
							site_id,
							valid_header,
							valid_detail,
							date_created,
							reason_id,
							transfer_origin_name,
							from_site_retail_value,
							to_site_retail_value,
							unit_cost,
							expense_transfer_id,
							rej_rpt_printed_ind,
							rejected_id,
							reason_sub_type,
							process_date_created,
							process_status,
							process_date_time,
							richter_version_id,
							pos_version_id,
							control_no,
							user_in_date,
							user_trace_id,
							user_id,
							ride_out_date,
							ride_error_code,
							severity_code,
							inventory_adjust_id,
							from_business_unit_id,
							to_business_unit_id,
							cancel_ind,
							pos_transfer_id,
							web_tracking_number,
							transaction_no,
							transfer_complete_ind,
							activation_date,                    
							comments,         
							carton_id ,        
							rma_code,         
							web_order_id
						)
				 
     VALUES
      (999999999999,
      p_NbrBusinessunit_id,
      p_NbrInTransfer_id,
      lv_transfer_occurence_type,
      p_Chrline_details, --line number needs to be split XML
      lv_prgid, 
      p_ChrTransfer_type, 
      TRUNC(sysdate),
      p_NbrRequest_id,
      p_ChrFrom_site,
      p_ChrTo_site,
      NULL,--bar code bus id 
      NULL, -- barcode subtype 
      NULL,-- bar code id can be null
      p_Chrline_details, -- style id meeds to be split xml
      p_Chrline_details,--color id needs to be split xml
      p_Chrline_details, -- dimension id needs to be split xml
      'Y',
      p_Chrline_details, -- size id needs to be split 
      'Y',
      p_Chrline_details, -- item qty needs to be split
      NULL, -- Valid Ind 
      'Y',
      p_Chrsite_id,
      NULL, --Valid Header
      NULL, -- Valid Detail
      SYSDATE, -- Date Created
      1, -- Reason id
      'POS', -- Transfer Origin Name
      NULL, -- From Site Retail Value
      NULL, -- To Site Retail Value
      NULL, -- Unit Cost
      NULL, -- Expense Transfer Id
      NULL, -- Reject Report Ind
      NULL, -- Rejected Ind
      'TRX', -- Reason Sub Type
      SYSDATE, -- Process date created
      'P', --Process status
      NULL, --Process date time
      'v60', -- Richter version
      'Generic', -- POS Version Id
       1, -- Control Number
       NULL, -- User In Date
       NULL, -- User Trace Id
       USER, -- User Id
       NULL, -- Ride Out Date
       NULL, -- Ride Error Code
       NULL, -- Severity Code
       NULL, -- Inventory Adust Id
       p_NbrFrom_business_unit_id,
       p_NbrTo_business_unit_id,
       NULL, --Cancel Ind
       p_ChrPOS_transfer_id,
       NULL, --Web Tracking number
       1, --Transaction Number default 1
						 'N', -- Transfer Complete Ind default N
						 NULL, --Activation Date
						 NULL, --Comments
						 NULL, --Carton Id
						 NULL, --RMA Code Id
						 NULL -- Web Order Id
						 );
       

       lv_Nbrjob_id:=generate_job_id;  
    
       IF lv_Nbrjob_id IS NOT NULL THEN
       
         INSERT INTO job_control 
			       (job_id,
			       application_id,
			       username,
			       object_type,
			       object_name,
			       description,
			       execution_node,
			       status,
			       time_created,
			       scheduled_start_time,
			       time_started,
			       time_completed,
			       comments,
			       parameter_selection_id
			       )
					     VALUES
					     (lv_Nbrjob_id,
					      'MERCH',
					      user,
					      'PROCEDURE',
					      'MERCHtoPOSTransferFlow1',
					       NULL,
					       NULL,
					      'PEND',
					       SYSDATE,
					       NULL,
					       NULL,
					       NULL,
					       NULL,
					       NULL
					      );
         
         POS_DAILY_UPLOAD(lv_Nbrjob_id);
       END IF;    


END accept_transfer_transferdetails;

FUNCTION generate_job_id RETURN NUMBER IS
   p_NbrOutJobId NUMBER;

  BEGIN
    SELECT job_id.nextval
    INTO p_NbrOutJobId
    FROM DUAL;

    RETURN p_NbrOutJobId;

    EXCEPTION WHEN NO_DATA_FOUND THEN
     p_NbrOutJobId:=0;

  END generate_job_id;


END POStoMerchDirectTransfer;
/
«1

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond

    Hi, @buggleboy007

    So, you need to parse a string. Using string manipulation functionS, like SUBSTR or REGEXP_SUBSTR comes to mind immediately.

    Regular expressions are great for ease of coding and debugging. You could do something like this:

    ...
    -- Set values that will be the same on all three rows
    sku_transfer_id := ...;
    
    FOR j IN 1 .. 3
    LOOP
    	-- Get a single line
    	p_chrline_line := REGEXP_SUBSTR ( p_chrline_details
    		    	 		, '<line [^>]*>'
    					, 1
    					, j
    					);
    
    	-- Now parse that line
    	sku_line_no := TO_NUMBER ( REGEXP_SUBSTR ( p_chrline_line
    	   	 	   		  	 , 'id="([^"]+)"'
    					  	 , 1
    					  	 , 1
    					  	 , NULL
    					  	 , 1
    					  	 )
    	   	 	  			 );
    	sku_style_id := RTRIM ( REGEXP_SUBSTR ( p_chrline_line
    		  	  		      , 'skustyle="([^"]+)"'
    			 		      , 1
    			 		      , 1
    			 		      , NULL
    			 		      , 1
    		  	 		      );
    
    ...
    	INSERT INTO iris_pos_details (transfer_id,    line_no,     style_id, ...)
    	    	  	 VALUES (sku_transfer_id, sku_line_no, sku_style_id, ...);
    	
    END LOOP;
    

    Notice that all the REGEXP_SUBSTR calls to get the individual attributes are identical except for the attribute name ('id', 'skustyle', etc.) You can simplify the coding by making a function that calls REGEXP_SUBSTR, like this:

    RETURN VARCHAR2 IS
    BEGIN
    	RETURN REGEXP_SUBSTR ( str
    	   	 	   , LOWER (attr) || '="([^"]+)"'
    			   , 1
    			   , 1
    			   , NULL
    			   , 1
    	   	 	   );
    END get_attr;
    

    Then, inside the LOOP, you can set the individual variables like this:

    LOOP
    	p_chrline_line := REGEXP_SUBSTR ( p_chrline_details
    		    	 		, '<line [^>]*>'
    					, 1
    					, j
    					);
    	skuid := TO_NUMBER (get_attr (p_chrline_line, 'id'));
    	skustyle := RTRIM (get_attr (p_chrline_line, 'skustyle'));
    ...
    

    This is not the fastest way to do it. You could do this more efficiently with INSTR and SUBSTR, but the milliseconds you save in execution time justify the additional hours of coding and debugging?

  • buggleboy007
    buggleboy007 Member Posts: 285 Bronze Badge

    @Frank Kulash : How did you come up with the loop counter of 1..3?

    What I am trying to say is that the Header will always have only 1 row where as the Transfer Detail can have 1 or 100 or n number of rows, then in that case how do we identify the max number using a REGEXP?

    I will try the solution given but the loop part is the one that I am not clear as it can keep changing (max limit)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,229 Red Diamond
    edited Jul 16, 2021 1:58AM

    Hi,

    How did you come up with the loop counter of 1..3?

    From the subject line:

    Question regarding breaking 3 lines into individual table rows

    and from the original message body:

    My question is, how can this be broken so that it can be inserted into the IRI_POS_DETAILS table. Again since these are 3 rows of data it has to loop in 3 times to insert the data. How can this be achieved? I really have no idea about this.

    If there could be any number of line elements (0, 1, 2, 3, 4, ...) then you can use a LOOP like this:;j := 1;

    LOOP
    	p_chrline_line := REGEXP_SUBSTR ( p_chrline_details
    		    	 		, '<line [^>]*>'
    					, 1
    					, j
    					);
    	EXIT WHEN p_chrline_line IS NULL;
    
    	j := j + 1;
    	skuid := TO_NUMBER (get_attr (p_chrline_line, 'id'));
    	skustyle :=  RTRIM (get_attr (p_chrline_line, 'skustyle'));
    ...
    END LOOP;
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond
    edited Jul 16, 2021 9:38AM

    Ok, what I don't get is you're talking of receiving a payload of data and trying to split it as if it were a string, but then the data you're showing us is actually XML. Oracle provides XML functionality for dealing with that.

    e.g. if you know the XML structure you're receiving then it's fairly straightforward to extract the details you want..

    SQL> with t(dta) as (
      2    select '<Lines>
      3    <line id="0" itemqty="" skustyle="" skudimension="" skusize="" skucolor=""/>
      4    <line id="1" itemqty="2" skustyle="style1" skudimension="12" skusize="XL" skucolor="34"/>
      5    <line id="2" itemqty="5" skustyle="style2" skudimension="14" skusize="L" skucolor="40"/>
      6  </Lines>' from dual
      7    )
      8  -- end of test data
      9  select lns.*
     10  from   t
     11         cross join
     12         xmltable('Lines/line'
     13                  passing xmltype(t.dta)
     14                  columns rn for ordinality
     15                         ,id number path './@id'
     16                         ,itemqty number path './@itemqty'
     17                         ,skustyle varchar2(10) path './@skustyle'
     18                         ,skudimension number path './@skudimension'
     19                         ,skusize varchar2(4) path './@skusize'
     20                         ,skucolor number path './@skucolor'
     21                 ) lns
     22  /
    
            RN         ID    ITEMQTY SKUSTYLE   SKUDIMENSION SKUS   SKUCOLOR
    ---------- ---------- ---------- ---------- ------------ ---- ----------
             1          0
             2          1          2 style1               12 XL           34
             3          2          5 style2               14 L            40
    

    Or if it's an unknown structure (usually indicative of bad design - question why you don't know the structure of the data you're receiving), you can still process it to get the information out e.g..

    SQL> with t(dta) as (
      2    select '<Lines>
      3    <line id="0" itemqty="" skustyle="" skudimension="" skusize="" skucolor=""/>
      4    <line id="1" itemqty="2" skustyle="style1" skudimension="12" skusize="XL" skucolor="34"/>
      5    <line id="2" itemqty="5" skustyle="style2" skudimension="14" skusize="L" skucolor="40"/>
      6  </Lines>' from dual
      7    )
      8  -- end of test data
      9  select hdr.hdr
     10        ,chld.chldno
     11        ,chld.chld
     12        ,attr.attrno
     13        ,attr.attr
     14        ,attr.attrval
     15  from   t
     16         cross join
     17         xmltable('*'
     18                  passing xmltype(t.dta)
     19                  columns hdr varchar2(30) path './local-name()'
     20                         ,chld xmltype path '.'
     21                 ) hdr
     22         left outer join
     23         xmltable('*/*'
     24                  passing hdr.chld
     25                  columns chldno for ordinality
     26                         ,chld varchar2(10) path './local-name()'
     27                         ,attr xmltype path '.'
     28                 ) chld on (1=1)
     29         left outer join
     30         xmltable('*/@*'
     31                  passing chld.attr
     32                  columns attrno for ordinality
     33                         ,attr varchar2(20) path './name()'
     34                         ,attrval varchar2(20) path '.'
     35                 ) attr on (1=1)
     36  /
    
    HDR                                CHLDNO CHLD           ATTRNO ATTR                 ATTRVAL
    ------------------------------ ---------- ---------- ---------- -------------------- --------------------
    Lines                                   1 line                1 id                   0
    Lines                                   1 line                2 itemqty
    Lines                                   1 line                3 skustyle
    Lines                                   1 line                4 skudimension
    Lines                                   1 line                5 skusize
    Lines                                   1 line                6 skucolor
    Lines                                   2 line                1 id                   1
    Lines                                   2 line                2 itemqty              2
    Lines                                   2 line                3 skustyle             style1
    Lines                                   2 line                4 skudimension         12
    Lines                                   2 line                5 skusize              XL
    Lines                                   2 line                6 skucolor             34
    Lines                                   3 line                1 id                   2
    Lines                                   3 line                2 itemqty              5
    Lines                                   3 line                3 skustyle             style2
    Lines                                   3 line                4 skudimension         14
    Lines                                   3 line                5 skusize              L
    Lines                                   3 line                6 skucolor             40
    
    18 rows selected.
    


    Edited: to include missing "@" (thanks comaco)

  • cormaco
    cormaco Member Posts: 1,723 Bronze Crown

    Here is an @ missing:

    ,skucolor number path './skucolor'
    
    BluShadow
  • buggleboy007
    buggleboy007 Member Posts: 285 Bronze Badge

    @BluShadow : Yes, your approach is very straight forward. The only hitch is that I would not know what the data inside the structure would be. For example: the itemqty in line id= 1 can be 10 or 2 or 25 and same with line id =2 can be anything. Based on that would your approach work?

    Also once you retrieve the data, how can we loop the number of rows retrieved and then insert them into another table?

  • buggleboy007
    buggleboy007 Member Posts: 285 Bronze Badge

    @Frank Kulash : Thanks for the note Frank. I should have been clear. My bad - yesterday's data presented was just a sample visualization. It can be 3 or 5 or 10 lines of data. So based on the number of rows retrieved, I will need to loop in that many number of times and insert data into the other table.

    I will try your approach and see if that works.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,491 Red Diamond


    Erm... yes it would work. That's the whole point isn't it... to extract the data regardless of what it is.

    If you know the Element and attribute names of the XML then the first query I provided will extract the data for each "line" in there.

    If you don't know the element and attributes names but need to extract those names along with the data, then the second query will do that.


    Try it and see.

    Frank Kulash
  • buggleboy007
    buggleboy007 Member Posts: 285 Bronze Badge

    @Frank Kulash : What is the Function signature or parameters that you are passing or suggest passing based on the code given by you below?

    RETURN VARCHAR2 IS
    BEGIN
    	RETURN REGEXP_SUBSTR ( str
    	   	 	   , LOWER (attr) || '="([^"]+)"'
    			   , 1
    			   , 1
    			   , NULL
    			   , 1
    	   	 	   );
    END get_attr;
    


    Sorry, I know this is spoon feeding but really unable to proceed. Will the first parameter be: the string that I am interested in? and what will be the second parameter?

  • buggleboy007
    buggleboy007 Member Posts: 285 Bronze Badge
    edited Jul 16, 2021 3:56PM

    @BluShadow :

    Your query works wonderfully at the SQL prompt. However when I convert that to PL/SQL I am getting hit with errors and here is where the issue is:

    I have created a variable called l_string which captures the string under question (this string will be passed as a parameter to the procedure that I am building) and when I pass I get an error saying "not too many values". I have spent some time trying to debug with no avail.

    declare 
      -- Local variables here
      l_string VARCHAR2(32767);
      L_STRING_RESULT VARCHAR2(32767); 
      L_ROWCOUNT NUMBER(3);
      i integer;
    begin
      -- Test statements here
      l_string:= '<Lines>
        <line id="0" itemqty=" " skustyle=" "   skudimension=" " skusize=" " skucolor=" "/> 
         <line id="1" itemqty="2" skustyle="style1" skudimension="12" skusize="XL" skucolor="34"/>
         <line id="2" itemqty="5" skustyle="style2" skudimension="14" skusize="L" skucolor="40"/>
         </Lines>'; 
         
         dbms_output.PUT_LINE(L_STRING);
         
         select l_string   --=> this works well. it retrives the count of records
         into L_STRING_RESULT
         from dual;
         
    --the below returns the error
    
            with t(dta) as (
                  select L_string
                  from dual
                 )
                select lns.*
                into L_STRING_RESULT
                 from t
                       cross join
                       xmltable('Lines/line'
                                 passing xmltype(t.dta)
                                 columns rn for ordinality
                                      ,id number path './@id'
                                      ,itemqty number path './@itemqty'
                                       ,skustyle varchar2(10) path './@skustyle'
                                       ,skudimension number path './@skudimension'
                                      ,skusize varchar2(4) path './@skusize'
                                       ,skucolor number path './@skucolor'
                              ) lns;
                              
                             
    end;