Forum Stats

  • 3,734,278 Users
  • 2,246,936 Discussions
  • 7,857,217 Comments

Discussions

Oracle Procedure unpivot data into destination using data splitting logic used in existing procedure

Vinipanda
Vinipanda Member Posts: 103 Red Ribbon
edited Nov 12, 2020 5:10PM in SQL & PL/SQL

I have a PL/SQL procedure which takes string at runtime which is comma separated,splits it and pushes into table.

Example of procedure and table is given here:

Procedure is push_data.

Current table where data is getting inserted is push_data_temp.


https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fae31c11a4ad6205ebcaa418a0bffffd


Now as per requirement, i need to write a new procedure where logic would remain same of splitting data, but in the new table push_data_pivot(structure inside pivot), should display data pivoted in this format.


Ex: if currently data in push_data_temp is:



The data should go in push_data_pivot in the new procedure as:


The logic should remain same, just that the data getting inserted would go into columns instead of rows, populating id and value columns.


Here column **id**, is the unique identifier for the string passed in both tables. Basically, it is same as the one used in original table.


Was unable to update fiddle for push_data_pivot structure so mentioning the latest structure and data here:

PUSH_DATA_PIVOT :


  create table PUSH_DATA_PIVOT

  (

  id_pk NUMBER,

   id NUMBER,

   label_id number,

   label varchar2(4000),

   value varchar2(4000)

  );


  insert into push_data_pivot values(1,null,'COL01',null);

  insert into push_data_pivot values(1,null,'COL02',null);

  insert into push_data_pivot values(1,null,'COL03',null);

  insert into push_data_pivot values(1,null,'COL04',null);

  insert into push_data_pivot values(1,null,'COL05',null);

  insert into push_data_pivot values(1,null,'COL06',null);

  insert into push_data_pivot values(1,null,'COL07',null);

  insert into push_data_pivot values(1,null,'COL08',null);

  insert into push_data_pivot values(1,null,'COL09',null);

  insert into push_data_pivot values(1,null,'COL10',null);

  insert into push_data_pivot values(1,null,'COL11',null);

  insert into push_data_pivot values(1,null,'COL12',null);

  insert into push_data_pivot values(1,null,'COL13',null);

  insert into push_data_pivot values(1,null,'COL14',null);


The sample data is single string, for single label_id and corresponding set of column label values.However actual table would have multiple such strings inserted on basis of id column, same as original requirement. This is already handled in existing procedure using cursor.


**The only requirement is, to create new procedure with new destination table as push_data_pivot and data in unpivoted format. Split logic would be same as in existing procedure.**

Tagged:

Comments

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond
    edited Nov 12, 2020 5:16PM

    Hi,

    I suspect the results you posted aren't what you really want from this sample data. For example, you say:

    Here column **id**, is the unique identifier for the string passed in both tables. Basically, it is same as the one used in original table.

    so, with this sample data, I'd expect the id column to be 110 or 111, not 1. Also, in the results you have

    "The values are, "exactly" up to the ..."

    but the sample data doesn't have any nested double-quotes. The only double-quotes in the sample data are the ones that enclose the values.


    Anyhoo, I think the problem is this:

    Your existing procedure does these things:

    1. Split data_string into parts (sub-query rcte)
    2. "clean" the data (e.g, for parts 2 and 3, you want to replace '\n' with a newline character (CHR(10)), but in other parts you don't want to)
    3. PIVOT those cleaned parts into 14 columns
    4. INSERT the the row into push_data_temp

    Now you want a procedure that is similar, except that the last step is to INSERT into push_data_pivot, which, despite the name, is in an un-pivoted entity-attribute-value format. So you want to skip what was step 3 above, and INSERT 14 rows into push_data_pivot.

    If you wanted to INSERT directly from a table like disp_data, you wouldn't need a PL/SQL procedure; you could do the whole job in SQL, like this:

    INSERT INTO push_data_pivot (id_pk, id, label, label_id, value) 
    WITH  input  AS
    (
      SELECT id
      ,    data  
      FROM  disp_data
    -- WHERE  id = 110  -- or whatever you want
    )
    ,  rcte (id, data, lvl, result)  AS
    (
      SELECT id
      ,    data
      ,    1
      ,    REGEXP_SUBSTR (data, '(".*?")(,|\])', 1, 1, 'n', 1)
      FROM  input
    UNION ALL
      SELECT id
      ,    data
      ,    lvl + 1
      ,    REGEXP_SUBSTR ( data, '(".*?")(,|\])', 1, lvl + 1, 'n', 1)
      FROM  rcte
      WHERE  lvl < 14
    )
    SELECT id
    ,    1 -- or whatever
    ,    'COL' || TO_CHAR (lvl, 'FM00')
    ,    1 -- or whatever
    ,    REPLACE ( CASE
               WHEN lvl IN (2, 3)
    	       THEN REPLACE (result, '\n', CHR (10))
    		   ELSE result
        	 END
    	    , '""'
    	    )
    FROM  rcte;
    

    If you really need a PL/SQL procedure, then you could put essentially the same INSERT statement into a procedure, but instead of the input sub-query you would simply use the arguments to the procedure. Alternatively, you could do 14 single-row INPUT statements like this:

    CREATE OR REPLACE PROCEDURE PUSH_DATA
    ( p_id     NUMBER
    , data_string  VARCHAR2
    )
    IS
      sb_str   VARCHAR2 (4000);  -- sub-string returned by REGEXP_SUBSTR
    BEGIN
      FOR lvl IN 1 .. 14
      LOOP
    	sb_str := REGEXP_SUBSTR (data_string, '(".*?")(,|\])', 1, lvl, 'n', 1);
    	sb_str := REPLACE (sb_str, '""');
    
    	IF lvl IN (2, 3)
    	THEN
    	  sb_str := REPLACE (sb_str, '\n', CHR (10));
    	END IF;
    	
        INSERT INTO push_data_pivot (id_pk, id, label, label_id, value)
    	VALUES ( p_id
      	    , 1 -- or whatever
      	    , 'COL' || TO_CHAR (lvl, 'FM00')
      	    , 1 -- or whatever
      	    , sb_str
    	    );
      END LOOP;
    END push_data;
    /
    SHOW ERRORS
    
    

    Whenever you are tempted to put data into an entity-attribute-value format, think carefully. Whatever the problem is, that's usually not the solution.

    Vinipanda
  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Thanks a lot! I will check and confirm to you.!

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon
    edited Nov 12, 2020 6:02PM

    Ok, i verified and its working fine. however if you take a look at the original procedure, It is inserting values if new, and updating if already present. So how can i add that logic in the procedure?

    If you check the fiddle, i ran the procedure twice for id 111 and it made duplicate entries.

    Can we have logic similar to the original procedure push data which is inserting new records and updating existing entries if id is already present?


    Also disp_data is old requirement, new requirement is to pass string at runtime and split. This is working fine. i just need to include update logic but am new and less confident so if you can help, it would be great

    I have changed new procedure nameto push_data_pivot_1.

    Please take a look .

    https://dbfiddle.uk/?rdbms=oracle_18&fiddle=7bc1e34dfc89146e279d99640d90c62a

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond

    Hi,

    If you want to UPDATE when there is already a row, and INSERT when there isn't, the most efficient way is to use MERGE. For example:

    CREATE OR REPLACE PROCEDURE PUSH_DATA_PIVOT_1
    ( p_id   NUMBER
    , data_string VARCHAR2
    )
    IS
     sb_str  VARCHAR2 (4000); -- sub-string returned by REGEXP_SUBSTR
    BEGIN
     FOR lvl IN 1 .. 14
     LOOP
    	sb_str := REGEXP_SUBSTR (data_string, '(".*?")(,|\])', 1, lvl, 'n', 1);
    	sb_str := REPLACE (sb_str, '""');
    
    	IF lvl IN (2, 3)
    	THEN
    	 sb_str := REPLACE (sb_str, '\n', CHR (10));
    	END IF;
    	
      MERGE INTO push_data_pivot dst
      USING (
    	   SELECT p_id AS id_pk
     	   ,   1   AS id  -- or whatever
     	   ,   'COL' || TO_CHAR (lvl, 'FM00') AS label
     	   ,   1   AS label_id -- or whatever
     	   ,   sb_str AS value
    	   FROM  dual
    	 )  src
       ON  (  dst.id_pk = src.id_pk
         AND dst.label = src.label
    	 )
       WHEN MATCHED THEN UPDATE
         SET  dst.value = src.value
         WHERE dst.value || '?' <> src.value || '?' 
       WHEN NOT MATCHED THEN INSERT (dst.id_pk, dst.id, dst.label, dst.label_id, dst.value)
                                                             VALUES (src.id_pk, src.id, src.label, src.label_id, src.value)
    	 ;
     END LOOP;
    END push_data_pivot_1;
    /
    


    Vinipanda
  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Thanks a ton, however i use oracle 11g and merge inside pl/sql procedure doesn't work. Can you help with something other than merge, like cursor and insert/update?

    If its not too much to ask?

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond
    edited Nov 12, 2020 7:09PM

    Hi,

    however i use oracle 11g and merge inside pl/sql procedure doesn't work.

    Are you sure? I don't have an Oracle 11 database, so I can't check, but it's hard to believe that MERGE (which worked in version 9 SQL) didn't work in version 11 PL/SQL. I don't see anything in the Oracle 11 documentation that explicitly says MERGE does or doesn't Work in PL/SQL. I did see a thread on this Forum that said MERGE worked in Oracle 10 PL/SQL. (I suppose it's possible that MERGE did work in version 10 and there was a bug in version 11 PL/SQL, but that would be a very serious bug, and it's hard to believe they wouldn't have caught it in testing.) Post the exact code you're trying (even if you thought you had copied it exactly from my message), and also post the complete error messages you get, including line numbers

    If MERGE really doesn't work then you could use the technique in your original procedure. You could also DELETE any possible duplicates then do a simple INSERT.

    Do you really need to use PL/SQL?

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Yes, and sorry i meant 12 c. It requires a patch upgrade but it is not allowed where i work. So i need to do this in 12 c only.

    So i guess i need to use the technique in original procedure.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon
    edited Nov 12, 2020 7:12PM

    Yes i need to use pl/sql only. That's the requirement hence all the trouble.


    P.S. The issue with merge not working is the oracle version which is currently Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Product however if we upgrade it to 12.2 or above , this will work.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond
    edited Nov 12, 2020 7:16PM

    Hi,

    .. sorry i meant 12 c. ...

    MERGE definitely works in Oracle 12 PL/SQL. I wrote and tested the procedure (using MERGE) in version 12.2.0.1.0, without any patches. Again, post the exact code you tried and the exact error messages you got.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    The issue is in version 12.1.0.2.0. This has been fixed in 12.2 . I will share example and details tomorrow, i.e. my morning, as its midnight here.

    I will try the original technique and will let you know if i am successful or not. In case not i'll bother you one last time for help.


    Thanks a lot for your help till now.

  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond

    By the way, part of your regex is pretty inefficient and will cause a massive amount of backtracking

    This is pretty bad....

    (".*?")

    This is MUCH better...

    ("[^"]*")

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,512 Red Diamond
    edited Nov 12, 2020 11:46PM

    Hi,

    This is pretty bad....

    (".*?")

    This is MUCH better...

    ("[^"]*")

    They're not equivalent. OP needs to handle nested double-quotes, as in the example below.

    WITH  sample_data (r_id, str)  AS
    (
      SELECT 1, '"A "B" C","D"' FROM dual
    )
    SELECT  s.*
    ,     REGEXP_SUBSTR (str, '(".*?")(,|\])', 1, 1, 'n', 1) AS good
    ,     REGEXP_SUBSTR (str, '("[^"]*")',   1, 1, 'n', 1) AS bad
    FROM	 sample_data s
    ORDER BY r_id
    ;
    

    Output:

      R_ID STR       GOOD       BAD

    -------- ------------- --------------- ---------------

        1 "A "B" C","D" "A "B" C"     "A "

    Paulzip
  • Paulzip
    Paulzip Member Posts: 8,322 Blue Diamond

    Ah OK, I didn't notice that, so I stand corrected.

  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Hi,

    There has been a change in requirement and now the column headers, i.e. COL01..14 would be changed as per a new column app_id. SO if app_1id=1, the labels would be say 12,13 or any number with changing names. The names would change as per app_id passed.

    I have created a new thread for it so that there is clarity and hope you can take a look as it gets slightly complicated now!



  • Vinipanda
    Vinipanda Member Posts: 103 Red Ribbon

    Not sure if they fixed, but this is working fine now. So merge is not an issue for me now.

Sign In or Register to comment.