Reputation: 1
I try to create a simple function in Oracle Database 11g, but it gives me the following error. It seems like it couldn't find the oe.orders table. Would you please help!
Thanks!
Code:
--Create following function as SYS
CREATE OR REPLACE FUNCTION get_bal(acc_no IN NUMBER)
RETURN NUMBER
IS acc_bal NUMBER(11,2);
BEGIN
SELECT oe.orders.order_total
INTO acc_bal
FROM oe.orders
WHERE oe.orders.customer_id = acc_no;
RETURN(acc_bal);
END;
/
SELECT get_bal(170) AS Balance FROM dual;
ERROR:
> ORA-06575: Package or function GET_BAL is in an invalid state
> 06575. 00000 - "Package or function %s is in an invalid state"
> *Cause: A SQL statement references a PL/SQL function that is in an
> invalid state. Oracle attempted to compile the function, but
> detected errors.
> *Action: Check the SQL statement and the PL/SQL function for syntax
> errors or incorrectly assigned, or missing, privileges for a
> referenced object. Error at Line: 28 Column: 7
Upvotes: 0
Views: 1634
Reputation: 231671
First, you should not be creating any objects as SYS
. SYS
is Oracle's schema-- only objects that Oracle creates should be placed in that schema. Though it is probably not the cause of your particular problem, various functionality behaves differently when you are SYS
than it does when you are any normal user-- another reason to avoid using the SYS
schema.
Second, does the table oe.orders
exist? The OE
schema is one of the sample schemas that ships with the Oracle database. It may or may not exist in any particular database. If the schema does not exist, we can walk you through installing it. Otherwise, the problem is likely one of permissions. If you are not sure whether the table exists, does this return any rows?
SELECT owner, table_name
FROM dba_tables
WHERE owner = 'OE'
AND table_name = 'ORDERS'
Third, if you want to query a table owned by user A
(in this case OE
) in a procedure or function owned by user B
(whatever user you decide to use rather than SYS
), the owner of the procedure has to have been granted access on the table directly not via a role. If the table exists, in all probability SYS
has access to the table only via a role DBA
rather than having direct access. That prevents SYS
from owning a function that references the table. To fix that, you need to log in as A
and grant SELECT
access on the table to B
. So, as OE
, you would need to
GRANT SELECT ON oe.orders TO <<user that will own the function>>
Upvotes: 2