Answer / tina

create a function


--concat column values

create or replace function conactfunc (query_1 in varchar2)
return varchar2
is
v_return varchar2(4000);
v_query_concat varchar2(4000);
cur sys_refcursor;
begin
open cur for query_1;

loop
fetch cur into v_query_concat;
EXIT WHEN cur%NOTFOUND;
if v_return is null then
v_return := v_query_concat;
else
v_return := v_return || '@' || v_query_concat;
end if;
end loop;
return v_return;
end;


show_err;

and then call it


select hr.conactfunc('select region_name from regions') from dual;

O/P

Europe@Americas@Asia@Middle East and Africa

Answer / sujatha nulu

Hi,
We have well advanced concepts in Oracle to achieve this.
LISTAGG analytical function can be used for this.

If its ename to be concatenated yet separated by delimiter,
use this way;

SELECT LISTAGG(ename,'|')
WITH IN GROUP (ORDER BY DEPTNO)
FROM EMPLOYEES
GROUP BY DEPTNO
ORDER BY DEPTNO;

You can write a procedure and pass column, delimiter as
parameters.

What do you understand by pl/sql packages?

0 Answers  

---

take one table is t1 and in that column name is f1 f1 column values are 200 5000 3000 7000 300 600 100 400 800 400 i want display the values asc and desc in a single output. sample output is f1.a 100 200 300 400 500 600 etc...... and f1.d is 5000 4000 3000 2000 1000 etc...

9 Answers   Zensar,

---

Where are my tempfiles, I don't see them in v$datafile or dba_data_file?

0 Answers  

---

i hv 30 rows with date.ex:1month hav 4 weeks i want 1st day of the every week.write the qry for that.example jan has 4 weeks i need 1st dd for evry wk

1 Answers   Aspire,

---

Which sql statement is used to delete data from a database?

0 Answers  

---

Can you create a table with Primary Key not as the clustered index.

2 Answers   CGI,

---

What are the types of dbms?

0 Answers  

---

When we can declare a column as Unique and Not Null both at the same time. What is the use pf Primary Key then?

2 Answers   Accenture, Unisoft Infotech,

---

what are the advantages a stored procedure? : Sql dba

0 Answers  

---

what is sp_pkeys? : Transact sql

0 Answers  

---

Define implicit and explicit cursors.

0 Answers  

---

A table was given with 3 columns like Manager id,Manager Name and Employee name. Question was to create hierarchy.

2 Answers   TCS,

---