access to function - ORA-00904: invalid identifier

I am new to oracle, please assist.

I have written a function similar to here in schemaB, with minor alterations.

When I run the query from above function, in schemaA, it runs fine and returns the sequence.

select schemaB.sequence_name_seq.nextVal from dual;

Running the function from schemaB ofcourse return the sequence as expected.

However, when I try to access the same function (containing the above query) from schemaA, it gives me an error: "ORA-00904: invalid identifier"

I have granted EXECUTE privileges to userA of schemaA (confirmed from 'DBA_TAB_PRIVS' table).

Function:

create or replace Function nextSeq
(
   tableName in VARCHAR2
)return NUMBER as
nextNum Number;

begin

EXECUTE IMMEDIATE 'select '||tableName||'_SEQ.nextval from dual' into nextNum;

return nextNum;
END nextSeq;

To Call:

select nextSeq('SCHEMAB.TABLENAME') from dual;

Solution

Here's how I'd do that: connected as SCOTT, I'm creating a sequence and a function; then I'll grant EXECUTE on the function to user MIKE:

SQL> show user
USER is "SCOTT"
SQL> create sequence dept_seq;

Sequence created.

SQL> create or replace function nextseq (tablename in varchar2)
  2    return number
  3  as
  4    nextnum number;
  5  begin
  6    execute immediate 'select ' || tablename||'_seq.nextval from dual' into nextnum;
  7    return nextnum;
  8  end;
  9  /

Function created.

SQL> select nextseq('dept') from dual;

NEXTSEQ('DEPT')
---------------
              1

SQL> grant execute on nextseq to mike;

Grant succeeded.

SQL>

Connect as MIKE and use SCOTT's function:

SQL> connect mike/lion@xe
Connected.
SQL> show user
USER is "MIKE"
SQL> select scott.nextseq('dept') from dual;

SCOTT.NEXTSEQ('DEPT')
---------------------
                    2

SQL>

As you can see it works. When compared to your code, the difference is here:

You: select nextSeq('SCHEMAB.TABLENAME') from dual;
Me : select scott.nextseq('dept') from dual;

You shouldn't precede table name with the owner, but the function name.