Create a stored function that adds an employee to the employees table. The function must accept enough values to add a row to the employees table and return a character string. the return character string should be "Success" of the employee was added as a result of an error(analyze the types of errors that can be raised when the function is executed and create the code to handle those errors). Write an sql statement that will invoke the procedure and display the result
-
-
It seems to be an assignment or a homework question. I will give you an hint to start with:
[code=oracle]
CREATE OR REPLACE FUNCTION insert_employee (empno NUMBER,ename VARCHAR2,age NUMBER,dept NUMBER) RETURN VARCHAR2 IS
BEGIN
--<insert the values in to emp table>>
--commit your transaction
--return 'success';
--handle exception using below block
EXCEPTION
WHEN OTHERS THEN -- to handle all kind of exceptions
--return failure
END;
[/code]
Also note, if any DML statement is executed within a function, you cannot invoke that function using a SQL. Though this is possible to do using "precompile r derivatives" PRAGMA, research on oracle precompiler derivative and check how you can make your function work from sql statement.
Comment