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 Go to next message
buggleboy007
Messages: 198
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 #629760 is a reply to message #629759] Mon, 15 December 2014 23:18 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>I am trying to concatenate a set of strings obtained via an Associate array.

What does above mean?
Why must Associate Array be used?
What does the desired results look like?
Re: Concatenating an array output into one single string [message #629761 is a reply to message #629760] Mon, 15 December 2014 23:22 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
BlackSwan:

you asked: What does above mean?

My reply: What I am trying to mean or do is, the values obtained via 3 subscripts(sandeep.tanjore@yahoo.com,stanjore@yahoo.ca and sandeep@yahoo.com) which are on 3 different lines, I want them to be next to each other i.e. something like this: sandeep.tanjore@yahoo.com, stanjore@yahoo.com, sandeep@yahoo.com

You asked: Why must Associate Array be used?

My reply: Then how else shall I retrieve these values? Associate Array was the one that came to my mind.


You asked: What does the desired results look like?

something like this: sandeep.tanjore@yahoo.com, stanjore@yahoo.com, sandeep@yahoo.com
so that I can trap the above in a variable and then pass it into a different array for further processing.
Re: Concatenating an array output into one single string [message #629769 is a reply to message #629761] Tue, 16 December 2014 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
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 #629826 is a reply to message #629769] Tue, 16 December 2014 05:34 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
I am not sure how you have arrived at 4 emails, but I will check the documentation on listagg function and go from there. Thanks Michel.

Re: Concatenating an array output into one single string [message #629827 is a reply to message #629826] Tue, 16 December 2014 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
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');

Wink
Re: Concatenating an array output into one single string [message #629829 is a reply to message #629827] Tue, 16 December 2014 05:40 Go to previous messageGo to next message
buggleboy007
Messages: 198
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 Go to previous messageGo to next message
buggleboy007
Messages: 198
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 #629862 is a reply to message #629858] Tue, 16 December 2014 15:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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)


I answered the question you posted:

Quote:
You asked: What does the desired results look like?

something like this: sandeep.tanjore@yahoo.com, stanjore@yahoo.com, sandeep@yahoo.com

Re: Concatenating an array output into one single string [message #629863 is a reply to message #629862] Tue, 16 December 2014 15:29 Go to previous messageGo to next message
buggleboy007
Messages: 198
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.
Re: Concatenating an array output into one single string [message #629876 is a reply to message #629863] Tue, 16 December 2014 16:48 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
I used an array and went ahead with my implementation. Certainly Thanks to all of you for recommending me LISTAGG function. I never knew of its existence and it helps to know how this can be done with LISTAGG function as well.
Re: Concatenating an array output into one single string [message #658460 is a reply to message #629876] Thu, 15 December 2016 01:44 Go to previous messageGo to next message
Mohammed Ayaz
Messages: 2
Registered: December 2016
Junior Member
please help me to solve this error
ERROR at line 1:
ORA-29279: SMTP permanent error: 530 authentication required
ORA-06512: at "SYS.UTL_MAIL", line 654
ORA-06512: at "SYS.UTL_MAIL", line 671
ORA-06512: at line 2
Re: Concatenating an array output into one single string [message #658461 is a reply to message #658460] Thu, 15 December 2016 01:47 Go to previous message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-29279: SMTP permanent error: %s
 *Cause:  A SMTP permanent error occurred.
 *Action: Correct the error and retry the SMTP operation.
https://www.google.com/?q=SMTP+permanent+error:+530+authentication+required

Previous Topic: Update Vs Delete&insertion
Next Topic: ORA-01722: invalid number ORA-02063: preceding line from
Goto Forum:
  


Current Time: Tue Oct 23 02:19:34 CDT 2018