Hi,
I have Created a SEQUENCE and wanted to use within CUSTOM MENU with PL/SQL Block, but it’s raise Error:
“Illegal SQL rejected: ifs.fnd.services.plsqlserver.service.security.RejectedSqlException: Illegal column IFSAPP.MY_SEQUENCE_ID.NEXTVAL”
when i execute command using Custom Menu.
Kindly help me to solve this issue.
IFS Application 8
Thnaks
Best answer by durette
Yeah, Apps 8 wants to only see application objects in the dictionary cache. Sorry, I haven’t worked in Apps 8 in two years.
Here’s another work-around to try:
CREATE SEQUENCE c_sequence_id_seq;
CREATE OR REPLACE PACKAGE c_sequence_id_api AUTHID DEFINER IS
module_ CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'CSequenceId';
FUNCTION get_nextval RETURN NUMBER;
PROCEDURE init;
END c_sequence_id_api;
/
CREATE OR REPLACE PACKAGE BODY c_sequence_id_api IS
FUNCTION get_nextval RETURN NUMBER
IS
BEGIN
general_sys.init_method(lu_name_, 'C_SEQUENCE_ID_API', 'GET_NEXTVAL');
RETURN c_sequence_id_seq.nextval;
END get_nextval;
PROCEDURE init IS BEGIN NULL; END init;
END c_sequence_id_api;
/
EXEC dictionary_sys.rebuild_dictionary_storage_(0);
EXEC security_sys.grant_package('C_SEQUENCE_ID_API', 'FND_ENDUSER');
EXEC security_sys.refresh_active_list__(0);
-- Event action code:
DECLARE
n_ NUMBER := 0;
BEGIN
n_ := ifsapp.c_sequence_id_api.get_nextval;
ifsapp.trace_sys.message('######## n_ = ' || n_);
END;
/
+19Did you grant it?
GRANT SELECT ON ifsapp.my_sequence_id TO ifssys WITH GRANT OPTION;
GRANT SELECT ON ifsapp.my_sequence_id TO fnd_enduser; // or your preferred permission set
Edit:
Actually, this is coming from the application:
ifs.fnd.services.plsqlserver.service.security.RejectedSqlException
I’ve never seen this error with a custom PL/SQL block. It typically lets you do pretty much anything. Is your block surrounded by BEGIN and END? Can you provide more details?
Already Grant this all permissions.
This code is running fine with Application 9 but not with Applicaion 8
Error :
+19Try removing the app owner, or try replacing it with &AO.
OMS_E_INV_SEQ_NO.NEXTVAL
&AO.OMS_E_INV_SEQ_NO.NEXTVAL
I thought custom code execution in V8 was much more restricted than in V9, is it not just a consequence of the full range of custom functions are not available in V8 that is blocking the execution?
+19Yeah, Apps 8 wants to only see application objects in the dictionary cache. Sorry, I haven’t worked in Apps 8 in two years.
Here’s another work-around to try:
CREATE SEQUENCE c_sequence_id_seq;
CREATE OR REPLACE PACKAGE c_sequence_id_api AUTHID DEFINER IS
module_ CONSTANT VARCHAR2(25) := 'FNDBAS';
lu_name_ CONSTANT VARCHAR2(25) := 'CSequenceId';
FUNCTION get_nextval RETURN NUMBER;
PROCEDURE init;
END c_sequence_id_api;
/
CREATE OR REPLACE PACKAGE BODY c_sequence_id_api IS
FUNCTION get_nextval RETURN NUMBER
IS
BEGIN
general_sys.init_method(lu_name_, 'C_SEQUENCE_ID_API', 'GET_NEXTVAL');
RETURN c_sequence_id_seq.nextval;
END get_nextval;
PROCEDURE init IS BEGIN NULL; END init;
END c_sequence_id_api;
/
EXEC dictionary_sys.rebuild_dictionary_storage_(0);
EXEC security_sys.grant_package('C_SEQUENCE_ID_API', 'FND_ENDUSER');
EXEC security_sys.refresh_active_list__(0);
-- Event action code:
DECLARE
n_ NUMBER := 0;
BEGIN
n_ := ifsapp.c_sequence_id_api.get_nextval;
ifsapp.trace_sys.message('######## n_ = ' || n_);
END;
/
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.