Reputation: 11
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
Upvotes: 1
Views: 2054
Reputation: 65408
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 )
Upvotes: 0
Reputation: 23588
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.
Upvotes: 1