4 Replies Latest reply on Aug 17, 2020 2:59 PM by jaramill

    Need help on the requirement

    Ram_A

      Hi Experts,

       

       

       

       

      CREATE OR REPLACE PROCEDURE proc20

      IS

      TYPE Type1 IS TABLE OF cust_src_tbl.cust_att%TYPE PLS_INETGER;

      vc TYPE1;

      bulk_exception EXCEPTION;

      v_error_count NUMBER;

      v_error_msg VARCHAR2(100);

      PRAGMA EXCEPTION_INIT(bulk_exception,-24381);

      CURSOR C IS

      select cust_att from cust_src_tbl ;

      BEGIN

      OPEN c;

      FETCH C BULK COLLECT INTO VC LIMIT 1000;

      CLOSE C;

       

       

      FORALL i IN 1..VC.count save exceptions

      INSERT INTO cust_target_tbl()

      SELECT

      where

      commit;

       

       

      EXCEPTION

      WHEN bulk_exception THEN

      v_error_count:= SQL%BULK_EXCEPTIONS.count();

      FOR i iN 1..v_error_count

      LOOP

      v_error_msg:=Error' ||SQL%BULK_EXCEPTIONS(i).error_index() || sqlerrm(SQL%BULK_EXCEPTIONS(i).error_code());

              dbms_output.put_line (v_err_msg);

            end loop;

      end proc20;

       

      Please help me.

      Thanks in advance.

        • 1. Re: Need help on the requirement
          Paulzip

          Your cust_src_tbl doesn't even pass first normal form, because of cust_att :

           

          A column value should be atomic

           

          In other words, it shouldn't be a value that can be broken down into other values.  It's a hack, it'll give you problems in future.  Simply don't do it.  Fix your model and problems miraculously disappear.  That's the nature of relational DBs

           

          My advice, change it to a parent child combo :

           

          create table cust_src_tbl

          (

          cid varchar2(20),

          cname varchar2(20),

          cust_descprition varchar(40),

          );

           

          create table cust_src_tbl_mapid

          (

          cid varchar2(20), -- PK, FK to cust_src_tbl

          mpid archar2(20) -- PK, FK to cust_map

          );

           

          Then work from that.

           

          Here's an article I wrote that might help you

          • 2. Re: Need help on the requirement
            Frank Kulash

            Hi,

            Ram_A wrote:

             

            Hi Experts,

             

             

            create table cust_src_tbl

            (

            cid varchar2(20),

            cname varchar2(20),

            cust_descprition varchar(40),

            cust_att clob

            );

             

             

            INSERT INTO cust_src_tbl values(1,'SHYAM','sales','<tag>A1-A2-A3-A4-A5-A6-A7-A8-A9-A10</tag>');

             

             

            create table cust_map

            (

            mpid varchar2(20),

            mp_value NUMBER

            );

             

             

            INSERT INTO cust_map values('A1',20);

            INSERT INTO cust_map values('A2',30);

            INSERT INTO cust_map values('A3',40);

            INSERT INTO cust_map values('A4',50);

            INSERT INTO cust_map values('A5',60);

            INSERT INTO cust_map values('A6',70);

            INSERT INTO cust_map values('A7',80);

            INSERT INTO cust_map values('A8',90);

            INSERT INTO cust_map values('A9',100);

             

             

            create table cust_target_tbl

            (

            ID number,

            CUST_ATT1 VARCHAR2(30),

            CUST_ATT2 VARCHAR2(30),

            CUST_ATT3 VARCHAR2(30),

            CUST_ATT4 VARCHAR2(30),

            CUST_ATT5 VARCHAR2(30),

            CUST_ATT6 VARCHAR2(30),

            CUST_ATT7 VARCHAR2(30),

            CUST_ATT8 VARCHAR2(30),

            CUST_ATR9 VARCHAR2(30),

            CUST_ATT10 VARCHAR2(20),

            CUST_ATT11 VARCHAR2(20)

            );

             

             

            The source table "cust_src_tbl" is having 250K records.

             

             

            The requirement is

            1) All records to be extracted and loaded to target table which has mappings available in "cust_map" table.

            2) Extract these clob values and store in a a variable and pass that value into target table to respective attribute fields in target.

             

             

            EXPECTED O/P:

             

             

            ID CUST_ATT1 CUST_ATT2 CUST_ATT3 CUST_ATT4 CUST_ATT5 CUST_ATT6 CUST_ATT7 CUST_ATT8 CUST_ATT9 CUST_ATT10 CUST_ATT11

            1 20 30 40 50 60 70 80 90 100

             

             

            I have tried the below procedure. But I have stuck up how to extract the values from the CLOB field.

             

             

            CREATE OR REPLACE PROCEDURE proc20

            IS

            TYPE Type1 IS TABLE OF cust_src_tbl.cust_att%TYPE PLS_INETGER;

            vc TYPE1;

            bulk_exception EXCEPTION;

            v_error_count NUMBER;

            v_error_msg VARCHAR2(100);

            PRAGMA EXCEPTION_INIT(bulk_exception,-24381);

            CURSOR C IS

            select cust_att from cust_src_tbl ;

            BEGIN

            OPEN c;

            FETCH C BULK COLLECT INTO VC LIMIT 1000;

            CLOSE C;

             

             

            FORALL i IN 1..VC.count save exceptions

            INSERT INTO cust_target_tbl()

            SELECT

            where

            commit;

             

             

            EXCEPTION

            WHEN bulk_exception THEN

            v_error_count:= SQL%BULK_EXCEPTIONS.count();

            FOR i iN 1..v_error_count

            LOOP

            v_error_msg:=Error' ||SQL%BULK_EXCEPTIONS(i).error_index() || sqlerrm(SQL%BULK_EXCEPTIONS(i).error_code());

            dbms_output.put_line (v_err_msg);

            end loop;

            end proc20;

             

            Please help me.

            Thanks in advance.

            Thanks for posting the CREATE TABLE and INSERT statements.

            Don't forget to post your Oracle version (e.g., 12.2.0.1.0).

             

            You don't need a procedure for what you described; you can do the whole job in a single INSERT statement.

            For example:

            INSERT INTO  cust_target_tbl ( id

                                         , cust_att1, cust_att2, cust_att3, cust_att4, cust_att5

                                         , cust_att6, cust_att7, cust_att8, cust_atr9

                                         )

            WITH    cntr    AS

            (

                SELECT  LEVEL  AS n

                FROM    dual

                CONNECT BY  LEVEL<= 9

            )

            ,    got_pos    AS

            (

                SELECT  s.cid, s.cust_att

                ,       c.n

                ,       CASE

                            WHEN  c.n = 1

                            THEN  INSTR (cust_att, '>')

                            ELSE  INSTR (cust_att, '-', 1, c.n - 1)

                        END + 1  AS pos1

                ,       CASE

                            WHEN  c.n = 9

                            THEN  INSTR (cust_att, '<', 1, 2)

                            ELSE  INSTR (cust_att, '-', 1, c.n)

                        END      AS pos2

                FROM    cust_src_tbl  s

                CROSS JOIN  cntr      c

            )

            ,    got_mp_value    AS

            (

                SELECT  p.cid, p.n

                ,       m.mp_value

                FROM    got_pos   p

                JOIN    cust_map  m  ON m.mpid = CAST ( SUBSTR (cust_att, pos1, pos2 - pos1)

                                                      AS  VARCHAR2 (20)

                                                      )

            )

            SELECT  *

            FROM    got_mp_value

            PIVOT   (    MIN (mp_value)

                    FOR  n  IN (1, 2, 3, 4, 5, 6, 7, 8, 9)

                    )

            ;

            This assumes cust_src_tbl.cust_att is well-formed, that '-' never occurs in the tag (which doesn't have to be '<tag>; it can be somehting like '<fubar>'), that '<' and '>' don't occur in the '-'''delimited list, and that there are always exactly 9 (that is, 8 '-'s) items in the list.

            Items in the list can be NULL.  For example '<x>A1---A4-----A9</x>'

            If any of these assumptions are wrong, the approach above will still work; it may just be a little messier.

             

            You could use regular expressions to split the list into parts, but INSTR and SUBSTR is more efficient.

            • 3. Re: Need help on the requirement
              jaramill

              From the link on --> Re: 2. How do I ask a question on the forums?

              READ that....and read #2.  It helps the community if you put a "relevant" subject title.  So change your title of your thread.

               

              2) Thread Subject line

              Give your thread a meaningful subject, not just "help please", "Query help" or "SQL". This is the SQL and PL/SQL forum. We know your question is going to be about those things, make it meaningful to the type of question so that people with the right sort of knowledge can pick it up and those without can ignore it. Never, EVER, mark your subject as "URGENT" or "ASAP".  This forum is manned by volunteers giving their own time to help and your question is never urgent or more important than their own work or than other people's questions. It may be urgent to you, but that's not forum members issue.  By marking your question as urgent you are actually less likely to get a good response, as members will choose to ignore such questions or respond unfavorably.

              • 4. Re: Need help on the requirement
                jaramill

                WHY would you even MARK my post as the answer??  It has NOTHING to do with an answer but a comment to change the title of your meaningless subject.  Again you truly do not understand how to read and follow guidelines.