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.
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
Is sql harder than python?
Explain the methods used to protect source code of pl/sql.
how to delete duplicate rows from a join tables(I have three tables on that join) how do you know which join is need to be used? The select statement I have is: SELECT gc_skill_type.skill_type, gc_area_tec.area, gc_technology.technology, gc_technology.id_technology, gc_area_tec.id_area_tec FROM gc_skill_type, gc_area_tec, gc_technology WHERE gc_area_tec.id_skill_type (+) = gc_skill_type.id_skill_type AND gc_technology.id_area_tec (+) = gc_area_tec.id_area_tec order by gc_skill_type.skill_type asc, gc_area_tec.area asc, gc_technology.technology asc
What is the use of <> sql?
What is error ora-01000: maximum open cursors exceeded
What do we need to check in database testing?
How many subqueries can be nested in a statement?
what are the differences between char and nchar? : Sql dba
Is a table valued function object?
Why is pl sql needed?
What is rank dense_rank and partition in sql?
What are the topics in pl sql?
What is embedded sql what are its advantages?
How delete all data from table in sql?
What is triggering circuit?