When running the FIX_NEXT_SEQ stored procedure to identify and fix out-of-whack sequences, you might encounter this error:
ERROR at line 1:
ORA-00909: invalid number of arguments
ORA-06512: at "BIOTICS_USER.FIX_NEXT_SEQ", line 22
ORA-06512: at line 1
It's not the procedure, but something slightly amiss in the database. You've either got
a) an entry in the HDMS_TABLE that refers to a table that doesn't exist, or
b) a table that doesn't have a PK constraint defined for it.
The procedure queries HDMS_TABLE to get table names, then it queries the USER_CONSTRAINTS table to find out the name of the primary key column. When it queries the USER_CONSTRAINTS table, it comes up empty and tries to query using max() with no argument. Naturally, Oracle will take an error.
Find the table by submitting a ticket, requesting that NatureServe run the following query, as all tables are owned by the biotics_user, to which you do not have access:
SELECT * from hdms_table where table_name not in (select t.table_name from user_tables t);
Results from the above query indicate that there are entry(ies) in the HDMS_TABLE that refer to tables that have since been deleted (typically an extensible table). The entries should removed from the HDMS_TABLE with the following statement, replacing TABLE_NAME1, TABLE_NAME2,... with any tables identified in the query above. Log in as the biotics_dlink user to do so.
delete hdms_table where table_name in (TABLE_NAME1, TABLE_NAME2,...);
If this doesn't result in anything, reply to the same ticket, requesting that NatureServe run the following query, as all tables are owned by the biotics_user, to which you do not have access.
This query will identify any business or domain tables that don't have a primary key constraint defined, a less likely scenario, but could still happen.
select b.* from hdms_table b
where b.table_type in ('B', 'D') AND
b.table_name not in (select a.table_name
from user_constraints a
where a.constraint_type = 'P'
If the table doesn't have a primary key (PK) constraint on it, you can add it with this statement,
ALTER TABLE name of table here
ADD CONSTRAINT name_of_table_pk PRIMARY KEY (primary key column name here);
Once you've done one of the above, try running the FIX_NEXT_SEQ stored procedure again.