Search code examples
oracle-databaseplsqlprocedure

How to access the HR schema objects through a user defined procedure in Oracle 18c?


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

Solution

  • You will need to grant access to HR objects to user user1.

    Connected as user HR:

    grant select on employees to user1;