This content has been marked as final. Show 12 replies
Actually the documentation link was intended to help. It explains how to write functions and solve the problem yourself which I guess would be the better option than spoonfeeding you. After all it is a discussion form and not a give me the solution forum. If you'd have had questions regarding how to write functions I'd have helped you, but - as you requested - I am off this page.
Sorry i have no forms to test but i could guide u how to formalize ur select statment into a function u want to return a date so ur function should also return a date
- the input u want to work on is either a date or numbet then ur function needs a parameter to receive a value input from the user & process into the function body or select statment as urs.
- u will also need to declare a variable where the select statment process to return that variable by the function.
Now the following is how we should write or transfer these words into a language where the oracle forms compiler language correctly accept & recognize.
This language should have rules or syntax as any other language.
Pls notice & compare our language in words previously explained with the rules or syntax we will use..
u can trace the function by displaying messages to see the values of parameter and variables & modify the function according to this messages output. :)
CREATE FUNCTION GET_DATE_FUNCTION (P_RENT_DATE DATE) RETURN DATE v_date DATE; --this where we will return by funvtion BEGIN BEGIN SELECT TO_CHAR (SYSDATE + P_RENT_DATE,'DD/MM/YYYY HH24:MI:SS') INTO V_DATE FROM dual; EXCEPTION WHEN NO_DATA_FOUND THEN MESSAGE....; WHEN OTHER THEN ... Display message error to fix the problem ; END; RETURN v_date; -- this value is the output of the function END GET_DATE_FUNCTION;
Now the calling part..
We created the function & tested it i supposed let's use or call it.
In WHEN-VALIDATE-ITEM Trigger of the item where u input the period or date of rent period create it.
Put in mind that that item input data is used for the parameter function ; we will pass the value of that item to the function to process and calc the date expected to return from the function.
Pls follow ...
Hope it helps...
DECLARE v_result DATE; BEGIN v_result :=GET_DATE_FUNCTION (:name of item due rent input by the user); :output := v_result; -- out put is where u display the value result of the function END;
Your example contains several errors and or bad practices:
1. There is no need to do a SELECT to calculate anything with a date.
2. Adding two dates will never return a date, it will result in an error.
3. Converting a date using TO_CHAR and returning the result into a DATE-value will only work under certain conditions.
4. A WHEN OTHERS exception should only be used under certin condition.
So the function can be reduced to a simple
CREATE FUNCTION GET_DATE_FUNCTION (P_RENT_DATE DATE) RETURN DATE BEGIN RETURN SYSDATE + 7; END;
U r the Master's King :) Andreas u , Craig ,Micheal & Hamid & all others...
always read their point of view to learn from& all the experts here.
But i think that the parameter of the function has be used in the return statment instead of constant number 7, this also depends on the datatype of the user input in rent date form item.
Happy with the optimization & code correction :)