Procedure gives ORA-00942: table or view does not exist, when table exists

1
votes

I don't know where is the error inside my procedure.

I tried using it in different SQL worksheet, different connection

CREATE OR REPLACE PROCEDURE SERVICEINVOICE(v_name IN VARCHAR2)
IS
BEGIN
    SELECT * FROM S8.SERVWORK WHERE SERVINVNO = '&v_name';
END;
/

Error(4,1): PL/SQL: SQL Statement ignored Error(4,18): PL/SQL: ORA-00942: table or view does not exist

2
sqloracleplsql
You need to check the role that was created, the problem is that the grant was done via a role. Privileges granted to a user are not available in a definer's rights stored procedure (the default). try running this command SET Role none – Hasan Patel

2 Answers

1
votes

That error suggests that the user who owns the serviceinvoice procedure either doesn't have any privileges to the s8.servwork table, or it only has privileges granted by a role.

If you have a definers right program (which is the default), the program's owner needs to have specific privileges on tables, etc that it needs to use.

So, you need to run something like the following as the S8 user for your procedure to work:

grant select on servwork to <owner of your serviceinvoice procedure>;

If your procedure(s) will also be inserting, updating, etc on that table, you will need to grant those privileges too. (e.g. grant insert, select, ... to ...;)

Secondly, your select needs to return values into a variable, which it does not currently do. Depending on whether you're expecting at most 1 row or multiple rows affects the kind of variable you need to return into. E.g. if you're getting multiple rows back, you need to bulk collect into an array of the necessary rowtype, or maybe it's a record, or individual variables.

Finally, it's bad practice to use select * ... inside code - you should explicitly specify the columns you want to use. This avoids errors with your code if someone adds a column to the table.

0
votes

The error obviously tells your schema has not been granted to select from SERVWORK table of S8 schema. But even if the needed privilege is granted, the SELECT Statement is missing an INTO Clause to return the desired columns. Indeed there's a practical way to return all columns by using rowtype pseudocolumn within S8.SERVWORK%rowtype as in your case. So, try the below code block :

SQL>SET SERVEROUTPUT ON
SQL>CREATE OR REPLACE PROCEDURE SERVICEINVOICE( v_name s8.servwork.servivvno%type ) IS
  v_servwork s8.servwork%rowtype;
BEGIN
  SELECT * INTO v_servwork FROM s8.servwork WHERE servivvno = v_name;
  DBMS_OUTPUT.PUT_LINE( v_servwork.col1||' - '||v_servwork.col2||' - '||v_servwork.col3);
END;
/

To individually check the returning columns by printing them out. (col1,2,3 are assumed columns of s8.servwork table )