Home » SQL & PL/SQL » SQL & PL/SQL » Concatenating an array output into one single string (O/S:Windows 8; Product: Oracle; version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Concatenating an array output into one single string [message #629759] |
Mon, 15 December 2014 23:06  |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
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 (10,'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 of the above anonymous block when run is as follows:
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. I am running out of ideas. If you can help me out that would be great.
Many Thanks in Advance.
Regards,
Sandeep
|
|
|
|
|
|
|
|
| Re: Concatenating an array output into one single string [message #629769 is a reply to message #629761] |
Tue, 16 December 2014 00:27  |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:Now I want the 3 email ids pertaining to emp id 10 which are
For what I see there are 4 mails for emp id 10.
SQL> select deptno, listagg(ename,',') within group (order by ename) emplist
2 from emp
3 group by deptno
4 /
DEPTNO EMPLIST
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
|
|
|
|
|
|
| Re: Concatenating an array output into one single string [message #629827 is a reply to message #629826] |
Tue, 16 December 2014 05:36 |
|
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Your script:
Quote:INSERT INTO dummy(empno, ename, emailid) VALUES (10,'Sandeep', 'sandeep.tanjore@yahoo.com');
INSERT INTO dummy(empno, ename, emailid) VALUES (10,'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');
|
|
|
|
| Re: Concatenating an array output into one single string [message #629829 is a reply to message #629827] |
Tue, 16 December 2014 05:40 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Got it. That was a typo from my side. I am not sure how I added 10 for Soni instead of 11 in the INSERT INTO dummy(empno, ename, emailid) VALUES (10,'Soni', 'soni@yahoo.com');
The attachment in the 1st thread of mine shows it correctly. Sorry for the confusion Michel and Thanks for pointing it out.
|
|
|
|
| Re: Concatenating an array output into one single string [message #629858 is a reply to message #629759] |
Tue, 16 December 2014 14:06 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Michel - The query you have provided with LISTAGG is good too. However it does not serve my purpose as when I pass it into an array (the values that I am passing are email id's ) , it takes as one full string (even though the email ids are separated with a comma)
So basically it passes as
array(stanjore@yahoo.com, sandeep.tanjore@yahoo.com)
and this bombs out with and it generates a run time error "ORA-29279: SMTP permanent error: 553 5.1.3"
In case I am able to break it into:
array('stanjore@yahoo.com', 'sandeep.tanjore@yahoo.com')
Then it will work else it won't
So my choices are limited in that :
a) I have to have a single quote against each email id so that the server treats it as a single email id else it treats it as one complete string resulting in the error
or
b)I will have to use the array that I had shown in the early part of my question and then based on the values from the array, I need to store them in a variable and pass it to the second array. Is there any way you can tell me how to store values from the array into a variable?
Code for that I have tried out is:
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;
Out put of the above 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
Is there any way around this?
|
|
|
|
|
|
| Re: Concatenating an array output into one single string [message #629863 is a reply to message #629862] |
Tue, 16 December 2014 15:29 |
buggleboy007
Messages: 282
Registered: November 2010
Location: Canada
|
Senior Member |
|
|
Correct. I even tried SELECT LISTAGG(''''||EMAILID||'''',',') WITHIN GROUP(ORDER BY EMAILID) FROM DUMMY WHERE EMPNO=10;
where in the output is email ids with quotes and commas between emails. However it still does not help as the variable being passed is an array and it treats it as one string and then bombs with an error.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 03:12:43 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
