Thanks for the question, Davaasuren.

Asked: January 27, 2017 - 1:53 am UTC

Last updated: January 31, 2017 - 4:38 pm UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

I have this query.
select
count(*)
from TABLE (fn_report_tin_con( 'profession_cd' /**P*/, 'organization_type_cd' /**P*/, 'CMM0016' /**P*/, '20161201' /**P*/, '20170126' /**P*/, '' /**P*/, '' /**P*/));
Error is:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at "MLEX.SET_CONTEXT", line 6
ORA-06512: at "MLEX.FN_REPORT_TIN_CON", line 70
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges
I tried to grant privilege on SYS.DBMS_SESSION.
Thanks.

and Connor said...

That would suggest that the table function (fn_report_tin_con) is setting a context variable via a call to dbms_session.

With the right privs granted it should work fine

SQL> create or replace
  2  context MYCTX using MYFUNC;

Context created.

SQL>
SQL> create or replace
  2  function myfunc return int is
  3  begin
  4    dbms_session.set_context('MYCTX','ATTR','0');
  5    return 10;
  6  end;
  7  /

Function created.

SQL>
SQL> select myfunc from dual;

    MYFUNC
----------
        10

SQL> desc sys.odcivarchar2list
 sys.odcivarchar2list VARRAY(32767) OF VARCHAR2(4000)

SQL> create or replace
  2  function myfunc return sys.odcivarchar2list pipelined is
  3  begin
  4    dbms_session.set_context('MYCTX','ATTR','0');
  5    for i in 1 .. 5 loop
  6       pipe row ( to_char(i));
  7    end loop;
  8    return;
  9  end;
 10  /

Function created.

SQL>
SQL> select * from table(myfunc);

COLUMN_VALUE
----------------------------------------------------------------------------------------------------------------------------------
1
2
3
4
5

SQL>
SQL>

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

didn't understand

A reader, January 31, 2017 - 2:46 am UTC

How grant execute to sys.dbms_session on function?
Is there privilege of function to grant?

January 31, 2017 - 4:38 pm UTC

You just:

grant execute on sys.dbms_session to <your user>

Subash Sharda, February 03, 2021 - 12:14 pm UTC

In order to set any user defined context variable or namespace, you need to create the new namespace in (which is held in SYS).

The namespace can be created by a user who has appropriate privs.

CREATE [ OR REPLACE ] CONTEXT namespace
USING [ schema. ] package
[ INITIALIZED { EXTERNALLY | GLOBALLY }
| ACCESSED GLOBALLY
] ;

specifies which schema.package is trusted to use dbms_session. set_context

Immediately after creating the namespace in the above statement I was able to set context variable for the namespace.

Hope this helps.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

didn't understand

More to Explore

PL/SQL demos

PL/SQL docs

insufficient privileges on SYS.DBMS_SESSION

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

didn't understand

More to Explore

PL/SQL demos

PL/SQL docs