I would like to make dynamic pl/sql using type variables in parameter values.
parameter value > type = {'name + place', 'resno', 'hpno', 'telno'};
for example if i got parameter values('resno', 'hpno') Two combine queries're required(2 union all) so, it means that The number of queries depends on the parameter value.
select * from (
--repeatation
select * from
(select a.custnm
, 'name + place' as vtype
, a.custnm || '-' || c.pjtcd || '-' || c.dong || '-' || c.ho as con
, count(a.custid) as nodup
from custtable a
, thng c
where a.custid = c.custid(+)
group by a.custnm, c.pjtcd, c.dong, c.ho
having count(a.custid) > 1) x
--/repeatation
union all
--repeatation
select * from
(select a.custnm
, 'resno' as vtype
, a.resno as condup
, count(a.custid) as nodup
from custtable a
group by a.custnm, a.resno
having count(a.custid) > 1) x2
--repeatation
union all
--repeatation
select * from
(select a.custnm
, 'hpno' as vtype
, a.hpno as condup
, count(a.custid) as nodup
from custtable a
group by a.custnm, a.hpno
having count(a.custid) > 1) x3
--repeatation
union all
--repeatation
select * from
(select a.custnm
, 'telno' as vtype
, a.telno as condup
, count(a.custid) as nodup
from custtable a
group by a.custnm, a.telno
having count(a.custid) > 1) x4
--repeatation
)
order by decode(vtype, 'name +`enter code here` place', 1 ,'resno', 2 ,'hpno', 3, 'telno', 4), nodup desc
plz let me know how to make plsql with parameter value
This is a SELECT
statement. The way it is written now, it is SQL, not PL/SQL, and my suggestion is to leave it that way. Instead of writing a horrible PL/SQL (dynamic SQL usually isn't nice), why wouldn't you create a view, based on that statement?
create or replace view v_my_view as
select * from (
--repeatation
select * from
(select a.custnm
, 'name + place' as vtype
<snip>
Once you do that, use it anywhere you want (PL/SQL included).