0

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;
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>
| improve this answer | |
  • 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? – AZT Mar 28 '18 at 8:27
  • So you can use "GRANT ON ... TO <user>". HR inside is not neccessary. – user56816 Mar 28 '18 at 8:32
  • It doesn't work.I got this Error(4,1): PLS-00428: an INTO clause is expected in this SELECT statement. – AZT Mar 28 '18 at 9:30
  • What do you what to do. Show some user some data. So maybe CREATE VIEW is the right way to do so. – user56816 Mar 28 '18 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.