I have given the code and the error messages I got when I try to create a procedure being user1. I try to access employees table in the sample hr schema in Oracle XE 18c. Can I know how to access hr schema objects via a user defined procedures
CREATE OR REPLACE PROCEDURE proc_1 IS
v_name hr.employees.first_name%TYPE;
v_num NUMBER;
BEGIN
SELECT first_name INTO v_name FROM hr.employees
WHERE employee_id=100;
v_num:=5;
DBMS_OUTPUT.PUT_LINE('test'||v_name);
DBMS_OUTPUT.PUT_LINE(v_num);
END;
/
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE PROC_1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/8 PL/SQL: Item ignored
2/8 PLS-00201: identifier 'HR.EMPLOYEES' must be declared
5/1 PL/SQL: SQL Statement ignored
5/39 PL/SQL: ORA-00942: table or view does not exist
7/1 PL/SQL: Statement ignored
7/38 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
You will need to grant access to HR objects to user user1.
Connected as user HR:
grant select on employees to user1;