asked on

ORA-38818: illegal reference to editioned object creating PL/SQL Package

Hi all,
I'm trying to create a simple PL/SQL package, to insert data into one of the eBusiness suite interface tables wip_job_dtls_interface. If I run this insert statement as an anonymous block it works fine, but as part of a package, it fails compilation with the error ORA-38818: illegal reference to editioned object

I've run: alter user appsplan enable editions but this hasn't helped

CREATE OR REPLACE PACKAGE ASG_WIP_INTERFACE AS

PROCEDURE wip_job_dtls_interface
 (p_operation_seq_num in NUMBER,
  p_load_type in NUMBER,
  p_substitution_type in NUMBER,
  p_process_phase in NUMBER,
  p_process_status IN NUMBER);
  
END ASG_WIP_INTERFACE;

CREATE OR REPLACE PACKAGE BODY ASG_WIP_INTERFACE AS

PROCEDURE wip_job_dtls_interface
 (p_operation_seq_num in NUMBER,
  p_load_type in NUMBER,
  p_substitution_type in NUMBER,
  p_process_phase in NUMBER,
  p_process_status IN NUMBER)
  
IS BEGIN

insert into apps.wip_job_dtls_interface(
OPERATION_SEQ_NUM,
LOAD_TYPE,
SUBSTITUTION_TYPE,
PROCESS_PHASE,
PROCESS_STATUS,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
values(10,1,2,2,1,sysdate,1,sysdate,1);

commit;


END wip_job_dtls_interface;
  
END ASG_WIP_INTERFACE;

Open in new window

Any ideas?

Thanks,

Mike

schwertner

https://www.experts-exchange.com/questions/27790448/11g-ORA-38818-illegal-reference-to-editioned-object.html

Additionally figure out the user who runs the procedure. If he has INSERT granted to that table if he isn't the owner.

schwertner

http://oracleblues.blogspot.bg/2010/11/user-enable-editions-troubles-and.html

this explains the relations.

hchunky

ASKER

Hi,
The user running the procedure is APPSPLAN, and has INSERT granted to that table, which is owned by APPS. I can run the insert block as an anonymous block, I just can't create the block as a stored package?

Thanks,

Mike

slightwv (䄆 Netminder)

I'm not familiar with the specific error message but if it is permission related, make sure the user has been explicitly granted insert to the table. The grant cannot be through a ROLE.

hchunky

ASKER

The user has been explicitly granted insert on the table

schwertner

Investigate the users (as SYS):

SELECT username, EDITIONS_ENABLED FROM dba_users;

Also the table:

SELECT EDITIONABLE FROM dba_objects WHERE owner ='APPS' AND object_name= UPPER('wip_job_dtls_interface');

Mark Geerlings

In Oracle database systems that use the editioning option (as e-Business Suite does) any schemas that are not set up to be editioned (like: SYS and SYSTEM) are not allowed to refer directly in PL\SQL at least to objects in schemas that are editioned (this includes most of the e-Business Suite schemas).

You need to either compile your custom package in a schema that already supports editioning (like: the APPS schema) or change your custom schema to also support editioning. Be aware that you cannot change a schema back to be non-editioned. So, if you choose this option, make sure that you are prepared to live with editioning in your custom schema.

And yes, Oracle does more and different checking of privileges and permissions for SQL statements inside PL\SQL procedures, than for those same SQL statements if you run them outside of a stored procedure.

hchunky

ASKER

Hi, turns out the ENABLE EDITIONS hadn't been granted to my custom schema, despite it previously being requested. Granting this enabled me to create the package. Thanks for the assistance.

Mike

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.