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.
- 2 Answers
- 3972 Views
- I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What is varchar sql?
What are the different types of tables in sql?
Can a commit statement be executed as part of a trigger?
What is out parameter used for eventhough return statement can also be used in pl/sql?
what are the properties and different types of sub-queries? : Sql dba
How do I view an execution plan in sql?
How to create an array in pl/sql?
What are the types pl/sql code blocks?
Explain the steps needed to create the scheduled job?
How does index help in query performance?
What is sql engine in oracle?
Can you have more than one trigger on a table?
Can one improve the performance of sql*loader? : aql loader
What do we need to check in database testing?
What is data type in sql?
