user1623650
user1623650

Reputation: 1

Oracle Database 11g: oe.orders table doesn't exist

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

Answers (1)

Justin Cave
Justin Cave

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

Related Questions