Archive for the ‘ORACLE’ Category

Calculate Year, Month & Days in Oracle

Month_Between function in oracle is used for calculating Year, Month & Days

select    trunc (months_between (end_date, start_date) / 12)
|| ‘ Years ‘ 

       || mod (trunc (months_between (end_date, start_date)), 12)
       || ‘ Months ‘
       || (  to_date (end_date)
           – add_months (start_date,
                         trunc (months_between (end_date, start_date))
                        )
          )
       || ‘ Days’
  from dual;

How to pass comma separated values in IN Clause query to oracle

How to pass comma separated values in IN Clause query to oracle

Comma separated value = ‘O’,’P’,’I’

Generally we use below query in oracle and try to use same query in asp.net using procedure.

select * from tablename where column in (‘O’,’P’,’I’);

In procedure, we use p_parameter varible to pass comma separated value like,
select * from tablename where column in (p_parameter);

if you get output text query, it will work in oracle but not works in asp.net.
Solution

Use below mention query to resolve this issue.
So query should be
Select * from tablename where column in
(
SELECT substr(str, instr(str, ‘,’, 1, LEVEL) + 1, instr(str, ‘,’, 1, LEVEL + 1) –
instr(str, ‘,’, 1, LEVEL) – 1) column_value
FROM (SELECT ‘,’ || p_parameter || ‘,’ str FROM dual)
CONNECT BY LEVEL <= length(str) – length(REPLACE(str, ‘,’)) – 1
)