1
I am studying PL/SQL in college and the teacher passed some questions based on the Human Resources model of Oraclexe itself. The question I’m asking asks a Rigger to prevent an employee from earning more than his boss. And for this Rigger I need a package with some procedures so as not to give Rigger mutant error.
That’s the package I wrote:
CREATE OR REPLACE PACKAGE BODY CONSULT_EMPLOYEE_SALARY IS
PROCEDURE LIMPA_VETOR IS
BEGIN
idx := 0;
v_tab.DELETE;
END;
PROCEDURE ALIMENTAR_VETOR (p_id EMPLOYEES.EMPLOYEE_ID%TYPE, p_salary EMPLOYEES.SALARY%TYPE, p_managerID EMPLOYEES.MANAGER_ID%TYPE) IS
BEGIN
idx := idx + 1;
v_tab(idx).EMPLOYEE_ID := p_id;
v_tab(idx).SALARY := p_salary;
v_tab(idx).MANAGER_ID := p_managerID;
END;
PROCEDURE OBTER_VETOR (p_idx PLS_INTEGER, p_employeeID OUT EMPLOYEES.EMPLOYEE_ID%TYPE, p_salary OUT EMPLOYEES.SALARY%TYPE, p_managerID OUT EMPLOYEES.MANAGER_ID%TYPE) IS
BEGIN
IF v_tab.EXISTS(p_idx) THEN
p_employeeID := v_tab(idx).EMPLOYEE_ID;
p_salary := v_tab(idx).SALARY;
p_managerID := v_tab(idx).MANAGER_ID;
END IF;
END;
END;
And this is the Trigger that runs the Procedure ALIMENTAR_VETOR:
CREATE OR REPLACE TRIGGER EMPLOYEE_AU AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
IF UPDATING THEN
CONSULT_EMPLOYEE_SALARY.ALIMENTAR_VETOR(:OLD.EMPLOYEE_ID, :NEW.SALARY, :OLD.MANAGER_ID) ;
END IF;
END;
When I test the update the following error occurs:
Erro a partir da linha : 249 no comando -
update employees set salary = 9500 where employee_id = 104
Relatório de erros -
ORA-06502: PL/SQL: erro: valor-chave de tabela de índice NULL numérico ou de
valor
ORA-06512: em "HR.CONSULT_EMPLOYEE_SALARY", line 12
ORA-06512: em "HR.EMPLOYEE_AU", line 3
ORA-04088: erro durante a execução do gatilho 'HR.EMPLOYEE_AU'
I researched this error, but the message I found related to this ORA code is different. Is this saying I’m passing a null parameter? And I am also in doubt whether it is possible to have access to all columns in a line Trigger after using :old. [column-name].
Thank you.
The vector would have to be fed before the use of Trigger , I do not know this technique with package but I believe it will give error in the load if executed during Trigger. The test itself (earn more than the boss) was not done.... off topic joke , is Rigger does not hold for the PSG ... :)
– Motta
Yes, there is a Trigger before sentence that clears the vector. And at each updated line it is for these values to be included in the vector. And there is another Rigger after set where the salary test occurs, but it does not even execute this because the error occurs in the one of feeding the vector
– Leandro Souza
You can see the "old" in the Rigger of after yes .
– Motta