I am new to oracle. How to test any function in oracle?
Do we need to create a table first and then apply function on columns?
There is a virtual table called "dual" available in oracle. To test any function we can use this.
For example to display date we can write a query like this.
Select sysdate from dual;
It depends on the function. If you can do a meaningful test with scalar inputs and it doesn't have any side effects then, no, you don't have to create a test tables, etc.
Suppose you had a function to decode a particular kind of date format that's unique to one of your other systems, where the year is offset from 1962 or something (yymmdd where the yy is the offset form 1962 and 2002 would be 40, so 2002-12-25 would be 401225). If this was called reformat_date and accepted your funky date field as the input and output a date type as the return value, you could just test it with different input values:
select reformat_date('401225') from dual;
and it would return 2002-12-25, if you had coded it right.
No one has followed this question yet.