>I have a function as below, which is working fine.
>is the string and obviously it does not execute
Please make up your/our mind.
>so is there any better approach
I don't have your tables.
I don't have your data.
I don't have your functions or procedures.
I don't understand your exact requirements.
using sqlplus along with CUT & PASTE post results showing whole session where code functions as desired utilizing a single hard coded value as a starting point.