ORA-02287: The avoidance and enforcement of an ordinal (sequence number not allowed here) is not allowed here

Problem Scenario One:

Select Id,name from (select seq_b_log_id. Nextval ID, ' elong_deo ' name from dual);

problem Scenario Two:

INSERT INTO B_authority  (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag) Select SEQ_B_ authority_id. nextval,1, ' admin:role:listRole ', ' role paging query ', '/admin/role/listrole.htm ', 1,210,4, 1 from Dualunionselect seq_b_ authority_id. nextval,1, ' admin:role:toEditAuthority ', ' Jump role permission edit ', '/admin/role/toeditauthority.htm ', 1,210,4, 1 from Dualunionselect seq_b_authority_id. nextval,1, ' admin:role:findAuthsByRoleId ', ' Get Role Permissions ', '/admin/role/findauthsbyroleid.htm ', 1,210,4, 1 from Dualunionselect seq_b_authority_id. nextval,1, ' admin:role:updateRoleAuths ', ' Update role Permissions ', '/admin/role/updateroleauths.htm ', 1,210,4, 1 from dual;

this prompt appears because Oracle does not allow this to be used, as described below:

Restrictions on Sequence Values You cannot use Currval and Nextval in the

following constructs:
a subquery in a DELETE, SELECT, or UPDATE statement
a query of a view or of a materialized view
A SELECT statement with the DISTINCT operator
a SELECT statement with a GROUP by clause or ORDER by clause
A SELECT statement that's combined with another SELECT statement with the
UNION, INTERSECT, or minus set operator
The WHERE clause of a SELECT statement
The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
The condition of a CHECK constrain

Problem-solving avoidance:

The so-called avoidance refers to a restricted area that does not go into the Oracle sequence, that is, try not to conform to the above-mentioned situations and achieve our goal by reasonably changing the SQL statement.

Scenario One solution:

SELECT seq_b_log_id. Nextval ID, name from (select  ' Elong_deo ' name from dual);

Scenario Two resolution:

INSERT INTO B_authority  (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag) Select SEQ_B_ authority_id. Nextval,t.c1,t.c2,t.c3,t.c4,t.c5,t.c6,t.c7 from (select 1 C1, ' admin:role:listRole ' C2, ' roles paged query ' C3, '/admin/role/ Listrole.htm ' C4, 1 c5,210 c6,4 C7, 1 C8 from Dualunion allselect 1, ' admin:role:toEditAuthority ', ' Jump role permission edit ', '/admin/rol E/toeditauthority.htm ', 1,210,4, 1 from dualunion allselect 1, ' admin:role:findAuthsByRoleId ', ' Get Role Permissions ', '/admin/role/ Findauthsbyroleid.htm ', 1,210,4, 1 from dualunion allselect 1, ' admin:role:updateRoleAuths ', ' Update role Permissions ', '/admin/role/ Updateroleauths.htm ', 1,210,4, 1 from dual) T;

Alternative enforcement of problem solving:

Many Oracle statements are limited when used, but function is not limited in most cases, and we can get nextval and currval through the program.

--Get sequence Next value Create or Replace function Get_seq_next (seq_name in varchar2) return Numberis  seq_val number; begin  EX Ecute immediate ' SELECT ' | | seq_name| | '. Nextval from dual ' into seq_val;  return seq_val; end Get_seq_next;

--Get the current value of the sequence (you can use it without first executing nextval) Create or Replace function Get_seq_curr (seq_name in varchar2) return Numberis  seq_val nu mber; begin  execute immediate ' select ' | | seq_name| | '. Currval from dual ' into seq_val;  return seq_val; end Get_seq_curr;

Scenario One solution:

Select Id,name from (the Select Get_seq_next (' seq_b_log_id ') ID, ' Elong_deo ' name from dual);

Scenario Two resolution:

INSERT INTO B_authority  (id,role_id,authority,remark,url,yn,parent_id,authority_type,log_flag) Select Get_seq_ Next (' seq_b_authority_id '), 1, ' admin:role:listRole ', ' Roles paged ', '/admin/role/listrole.htm ', 1,210,4, 1 from Dualunionselect get_seq_next (' seq_b_authority_id '), 1, ' admin:role:toEditAuthority ', ' Jump role Permissions edit ', '/admin/role/ Toeditauthority.htm ', 1,210,4, 1 from Dualunionselect get_seq_next (' seq_b_authority_id '), 1, ' Admin:role: Findauthsbyroleid ', ' Get Role Permissions ', '/admin/role/findauthsbyroleid.htm ', 1,210,4, 1 from Dualunionselect get_seq_next (' SEQ_ B_authority_id '), 1, ' admin:role:updateRoleAuths ', ' Update role Permissions ', '/admin/role/updateroleauths.htm ', 1,210,4, 1 from dual;

ORA-02287: The avoidance and enforcement of an ordinal (sequence number not allowed here) is not allowed here