I am trying to concatenate a set of strings obtained via an Associate array. How can I do that? The situation is as follows:
I have a table called dummy which has numerous email ids. DDL for table is as follows:
CREATE TABLE DUMMY
( EMPNO NUMBER(2),
ENAME VARCHAR2(20),
EMAILID VARCHAR2(100)
);
Table gets created.
DML for inserting values are:
INSERT INTO dummy(empno, ename, emailid) VALUES (10,'Sandeep', 'sandeep.tanjore@yahoo.com');
INSERT INTO dummy(empno, ename, emailid) VALUES (11,'Soumya', 'soumya@yahoo.com');
INSERT INTO dummy(empno, ename, emailid) VALUES (12,'Soni', 'soni@yahoo.com');
INSERT INTO dummy(empno, ename, emailid) VALUES (10,'Sandeep', 'stanjore@yahoo.com');
INSERT INTO dummy(empno, ename, emailid) VALUES (10,'Sandeep', 'sandeep@yahoo.com');
COMMIT;
/
Data is committed.
Now I want the 3 email ids pertaining to emp id 10 which are: sandeep.tanjore@yahoo.com,stanjore@yahoo.ca,sandeep@yahoo.com
to be retrieved from the database. So I use an Associate Array. The code for that is given below:
SET SERVEROUTPUT ON
SET ECHO ON
DECLARE
TYPE emailid_T IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
lv_these_emails emailid_T;
lv_counter INTEGER:=0;
lv_counter1 INTEGER:=1;
lv_complete_email_id VARCHAR2(32000);
CURSOR c_emailid IS
SELECT emailid FROM dummy
WHERE empno = 10;
BEGIN
FOR emailid_rec IN c_emailid LOOP
lv_counter:=lv_counter+1;
lv_these_emails(lv_counter):=emailid_rec.emailid;
--DBMS_OUTPUT.put_line(lv_these_emails(lv_counter));
END LOOP;
DBMS_OUTPUT.put_line('Total number of email ids are:'||' '||lv_these_emails.COUNT);
FOR i IN 1..lv_counter LOOP
--FOR j IN 1..lv_counter1 LOOP
DBMS_OUTPUT.put_line('The('||lv_counter1||') '|| 'email id is:'||' '||lv_these_emails(i));
-- DBMS_OUTPUT.put_line('The next email id is:'||' '||lv_these_emails.NEXT);
--DBMS_OUTPUT.put_line('The last email id is:'||' '||lv_these_emails.LAST);
lv_counter1:=lv_counter1+1;
-- END LOOP;
END LOOP;
END;
output is:
anonymous block completed
Total number of email ids are: 3
The(1) email id is: sandeep.tanjore@yahoo.com
The(2) email id is: stanjore@yahoo.ca
The(3) email id is: sandeep@yahoo.com
However from here, how can I show all the 3 email id's in a single variable besides each other seperated by comma and quotes ie ('sandeep.tanjore@yahoo.com','stanjore@yahoo.com','sandeep@yahoo.com'), so that I can pass this value into a new array for further processing?
I am running out of ideas. If you can help me out that would be great.
Many Thanks in Advance.
PS: Yesterday, I had posed this question and today I am unable to locate it. Hence posting it again today. I was also told to try out listagg function. I tried it but does not serve my purpose.