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
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.
create table cust_src_tbl
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
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
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.
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
TYPE Type1 IS TABLE OF cust_src_tbl.cust_att%TYPE PLS_INETGER;
CURSOR C IS
select cust_att from cust_src_tbl ;
FETCH C BULK COLLECT INTO VC LIMIT 1000;
FORALL i IN 1..VC.count save exceptions
INSERT INTO cust_target_tbl()
WHEN bulk_exception THEN
FOR i iN 1..v_error_count
v_error_msg:=Error' ||SQL%BULK_EXCEPTIONS(i).error_index() || sqlerrm(SQL%BULK_EXCEPTIONS(i).error_code());
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., 18.104.22.168.0).
You don't need a procedure for what you described; you can do the whole job in a single INSERT statement.
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
CONNECT BY LEVEL<= 9
, got_pos AS
SELECT s.cid, s.cust_att
WHEN c.n = 1
THEN INSTR (cust_att, '>')
ELSE INSTR (cust_att, '-', 1, c.n - 1)
END + 1 AS pos1
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
FROM got_pos p
JOIN cust_map m ON m.mpid = CAST ( SUBSTR (cust_att, pos1, pos2 - pos1)
AS VARCHAR2 (20)
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.
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.
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.