Page 1 of 1
HELP REQUIRED IN QUERY
Posted: Fri Jun 09, 2006 6:41 am
by imranhad
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
Posted: Fri Jun 09, 2006 9:29 am
by admin
You will have to write a script in pl/sql using cursor. thanks
Posted: Fri Jun 09, 2006 10:12 am
by imranhad
i could not make cursor logic for this query
Posted: Fri Jun 09, 2006 2:41 pm
by mirza_rehan
You create Function/Procedure for this task
take care
Allah Hafiz
Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807
When ever you pray
Please remember me and my family
Posted: Thu Apr 12, 2007 7:30 am
by shekhar_lonkar
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.
Posted: Thu Apr 12, 2007 8:17 am
by ahmadbilal
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
Posted: Fri Apr 20, 2007 12:55 am
by imranhad
thanks alot