PL/SQL: Statement ignored in procedure

Question

I try to create one test procedure but it gets Error(4,1): PL/SQL: SQL Statement ignored ** and ** Error(12,20): PL/SQL: ORA-00942: table or view does not exist when I try to compile. Then I test select statement in next session, it run successfully. Could you please help me.

create or replace PROCEDURE TEST_1
AS
BEGIN
SELECT
A.EMPLOYEE_ID AS "Employee ID",
A.FIRST_NAME||' '||A.LAST_NAME AS "Name",
B.DEPARTMENT_NAME AS "Department Name",
A.EMAIL AS "Email Address",
A.PHONE_NUMBER AS "Contact Number",
C.STREET_ADDRESS||', '||C.CITY||', '||NVL(C.STATE_PROVINCE,C.CITY) AS "Address"
FROM HR.EMPLOYEES A 
LEFT OUTER JOIN HR.DEPARTMENTS B
ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
LEFT OUTER JOIN HR.LOCATIONS C
ON B.LOCATION_ID = C.LOCATION_ID;
END;

Answer 1

Looks like a right problem. Do you realy need "HR." ?

Try

create PROCEDURE TEST_1 AS BEGIN 
CURRENT_USER
SELECT A.EMPLOYEE_ID AS "Employee ID",
 A.FIRST_NAME || ' ' || A.LAST_NAME AS "Name",
 B.DEPARTMENT_NAME AS "Department Name",
 A.EMAIL AS "Email Address",
 A.PHONE_NUMBER AS "Contact Number",
 C.STREET_ADDRESS || ', ' || C.CITY || ', ' ||
 NVL(C.STATE_PROVINCE, C.CITY) AS "Address"
 FROM EMPLOYEES A
 LEFT OUTER JOIN DEPARTMENTS B
 ON A.DEPARTMENT_ID = B.DEPARTMENT_ID
 LEFT OUTER JOIN LOCATIONS C
 ON B.LOCATION_ID = C.LOCATION_ID;
END;

And have a look at GRANT (for example)

GRANT EXECUTE ON PROCEDURE <user>.test_1 TO <role>

• AZT: Thanks for answer but it doesn't work. Yes, I need HR because I create procedure from another users, it has got DBA role. Do I need to add another GRANT on it?
• user56816: So you can use "GRANT ON ... TO <user>". HR inside is not neccessary.
• AZT: It doesn't work.I got this Error(4,1): PLS-00428: an INTO clause is expected in this SELECT statement.
• user56816: What do you what to do. Show some user some data. So maybe CREATE VIEW is the right way to do so.
• Tulains Córdova: @AZT In PL/SQL context you cannot use a query like that, you have to either run a query that returns exactly one row, and use "INTO" clause to save the row or value to a variable, or you use it as a cursor and iterate it.