SELECT EMAIL FROM EMPLOYEE;
SQL> email
-------------------------------------
atif@abc.com
altaf@yahoo.com
nasir@abc.com
najam@hotmail.com
Can i get result in this form
SQL> email
-------------------------------------
atif@abc.com,altaf@yahoo.com,nasir@abc.com,najam@hotmail.com
HELP REQUIRED IN QUERY
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
-
- Posts: 12
- Joined: Mon Mar 26, 2007 4:21 am
- Location: India
Hi..Friend....
May be following code usefull for u..
1 declare
2 type nest_tab is table of varchar2(25);
3 nt1 nest_tab :=nest_tab('qqh@gmail.com','she.yahoo.com','rams@comp.com');
4 answer nest_tab;
5 procedure my_nest_data (xx_data nest_tab) is
6 output varchar2(100);
7 begin
8 IF xx_data IS NULL THEN
9 DBMS_OUTPUT.PUT_LINE('Results: <NULL>');
10 RETURN;
11 END IF;
12 IF xx_data.COUNT = 0 THEN
13 DBMS_OUTPUT.PUT_LINE('Results: empty set');
14 RETURN;
15 END IF;
16 for i in xx_data.first..xx_data.last loop
17 output := output ||xx_data(i) || ',';
18 end loop;
19 DBMS_OUTPUT.PUT_LINE('Results:'||output );
20 end;
21 BEGIN
22 answer := nt1 ;--UNION nt2; -- (1,2,3,1,2,4)
23 my_nest_data(answer);
24* end;
SQL> /
Results:qqh@gmail.com,she.yahoo.com,rams@comp.com,
PL/SQL procedure successfully completed.
May be following code usefull for u..
1 declare
2 type nest_tab is table of varchar2(25);
3 nt1 nest_tab :=nest_tab('qqh@gmail.com','she.yahoo.com','rams@comp.com');
4 answer nest_tab;
5 procedure my_nest_data (xx_data nest_tab) is
6 output varchar2(100);
7 begin
8 IF xx_data IS NULL THEN
9 DBMS_OUTPUT.PUT_LINE('Results: <NULL>');
10 RETURN;
11 END IF;
12 IF xx_data.COUNT = 0 THEN
13 DBMS_OUTPUT.PUT_LINE('Results: empty set');
14 RETURN;
15 END IF;
16 for i in xx_data.first..xx_data.last loop
17 output := output ||xx_data(i) || ',';
18 end loop;
19 DBMS_OUTPUT.PUT_LINE('Results:'||output );
20 end;
21 BEGIN
22 answer := nt1 ;--UNION nt2; -- (1,2,3,1,2,4)
23 my_nest_data(answer);
24* end;
SQL> /
Results:qqh@gmail.com,she.yahoo.com,rams@comp.com,
PL/SQL procedure successfully completed.
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
another option
CREATE OR REPLACE FUNCTION F_email
RETURN VARCHAR2
IS
CURSOR C IS
SELECT
unique emailaddress
FROM
tablename;
V_DESC VARCHAR2(4000);
BEGIN
FOR W_REC IN C LOOP
IF V_DESC IS NULL THEN
V_DESC := w_rec.emailaddress;
ELSIF V_DESC IS NOT NULL THEN
V_DESC := V_DESC ||'-'|| w_rec.emailaddress;
END IF;
END LOOP;
RETURN V_DESC;
END;
/
[quote]SELECT EMAIL FROM EMPLOYEE;
SQL> email
-------------------------------------
atif@abc.com
altaf@yahoo.com
nasir@abc.com
najam@hotmail.com
Can i get result in this form
SQL> email
-------------------------------------
atif@abc.com,altaf@yahoo.com,nasir@abc.com,najam@hotmail.com
<i><div align="right">Originally posted by imranhad
CREATE OR REPLACE FUNCTION F_email
RETURN VARCHAR2
IS
CURSOR C IS
SELECT
unique emailaddress
FROM
tablename;
V_DESC VARCHAR2(4000);
BEGIN
FOR W_REC IN C LOOP
IF V_DESC IS NULL THEN
V_DESC := w_rec.emailaddress;
ELSIF V_DESC IS NOT NULL THEN
V_DESC := V_DESC ||'-'|| w_rec.emailaddress;
END IF;
END LOOP;
RETURN V_DESC;
END;
/
[quote]SELECT EMAIL FROM EMPLOYEE;
SQL> email
-------------------------------------
atif@abc.com
altaf@yahoo.com
nasir@abc.com
najam@hotmail.com
Can i get result in this form
SQL> email
-------------------------------------
atif@abc.com,altaf@yahoo.com,nasir@abc.com,najam@hotmail.com
<i><div align="right">Originally posted by imranhad
Who is online
Users browsing this forum: No registered users and 8 guests