I'm writing a stored procedure to copy data from one user's table to another schema. Basically, it is a series of INSERT .. SELECT statements such as this:
INSERT INTO GESCHAEFTE
SELECT *
FROM TURAT03.GESCHAEFTE
WHERE kong_nr = 1234;
This works fine when issueing from sqlplus (or TOAD for me ;-)) so I know that I have sufficient privileges, but when this is part of stored procedure like this:
CREATE OR REPLACE FUNCTION COPY_KONG
(pKongNr IN NUMBER)
RETURN NUMBER
AUTHID CURRENT_USER
IS
BEGIN
INSERT INTO GESCHAEFTE
SELECT *
FROM TURAT03.GESCHAEFTE
WHERE kong_nr = pKongNr;
END;
I get an Oracle error:
[Error] ORA-00942 (11: 22): PL/SQL: ORA-00942: table or view does not exist
As you can see, I've already inserted an AUTHID
, but to no avail.
What else can I do? I'm pretty much at the end of my ideas here.
A user with the EXECUTE object privilege for a package can execute any public procedure or function in the package and access or modify the value of any public package variable. Specific EXECUTE privileges cannot be granted for a package's constructs.
select * from dba_role_privs where grantee = 'SCHEMA_NAME'; All the role granted to the schema will be listed. Thanks Szilagyi Donat for the answer.
CALL test_sp_1(); An anonymous PL/SQL block is PL/SQL that is not inside a named procedure, function, trigger, etc. It can be used to call your procedure.
The owner of a procedure must be granted privilege to access the underlying objects directly, not through a role. To have the same level of access as your procedures, use the following commands:
SET ROLE NONE;
To access another table from a procedure, you need to be granted SELECT directly, not through a role:
GRANT SELECT ON TURAT03.GESCHAEFTE TO <your_user>;
This article by Tom Kyte contains additional info.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With