ename empno deptno
amar 1 10
akbar 2 20
anthonny 3 30
jonathan 4 40
write a procedure to dispaly the column values ina row
separated by a
deleimiter
eg - input - select ename from emp '|' output -
amar|akbar|anthony|jonathan
input - select empno from emp '@' o/p - 1@2@3@4
input - select deptno from emp '/' o/p -
10/20/30/40
Pls answer this questn.
Answers were Sorted based on User's Feedback
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
Is This Answer Correct ? | 3 Yes | 0 No |
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.
Is This Answer Correct ? | 2 Yes | 0 No |
Name the different types of indexes in sql and define them.
How do you use join?
What is the difference between a query and a report?
what is d diff between grant,commit,rollback n savepoint
package specification-3 procedures package body-2 procedures will is execute
Does pl sql work in mysql?
How do I create a memory optimized filegroup?
what happens when the column is set to auto increment and you reach the maximum value for that table? : Sql dba
What is trigger explain it?
if table named a is there and 4 records are there then how to swap (1 and 3) and (2 and 4) records at a time
Describe sql comments?
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