OraFAQ Forum: SQL & PL/SQL » How to Get the Program Name in 11g version

Home » SQL & PL/SQL » SQL & PL/SQL » How to Get the Program Name in 11g version (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Get the Program Name in 11g version [message #690160]

Fri, 08 November 2024 13:15

wtolentino
Messages: 421
Registered: March 2005

Senior Member

In 12c and later version of the oracle database I can use this built-in function to get the program name:

sys_context('USERENV','CLIENT_PROGRAM_NAME')

In 11g what is the similar parameters or function that I can use? Please help.

Thank you,
Warren

Report message to a moderator

Re: How to Get the Program Name in 11g version [message #690161 is a reply to message #690160]

Fri, 08 November 2024 13:42

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select program from v$session where sid=(select sid from v$mystat where rownum=1);
PROGRAM
----------------------------------------------------------------
sqlplus.exe

Report message to a moderator

Re: How to Get the Program Name in 11g version [message #690162 is a reply to message #690161]

Fri, 08 November 2024 13:46

wtolentino
Messages: 421
Registered: March 2005

Senior Member

can't use the v$...views because account does not have access

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02003: invalid USERENV parameter
ORA-06512: at line 11
ORA-00942: table or view does not exist

/forum/fa/14824/0/

Attachment: sys_context.jpg
(Size: 31.92KB, Downloaded 128 times)

[Updated on: Fri, 08 November 2024 13:55]

Report message to a moderator

Re: How to Get the Program Name in 11g version [message #690163 is a reply to message #690162]

Fri, 08 November 2024 14:16

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Create a procedure from an authorized account and grant the execute privilege to the unprivileged user:

SQL> Create or replace function my_program return varchar2
  2  is
  3    prog v$session.program%type;
  4  begin
  5    select program into prog from v$session where sid=(select sid from v$mystat where rownum=1);
  6    return prog;
  7  end;
  8  /

Function created.

SQL> grant execute on my_program to scott;

Grant succeeded.

SQL> conn scott/TIGER
Connected.
SCOTT> select michel.my_program from dual;
MY_PROGRAM
---------------------------------------------
sqlplus.exe

Report message to a moderator

Re: How to Get the Program Name in 11g version [message #690164 is a reply to message #690163]

Fri, 08 November 2024 16:24

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

This may or may not be related to your problem. V$mystat is a synonym for the v_$mystat table.
Privileges must be granted on the table, not the synonym. Please see the example below that demonstrates that.

-- reproduction of your error:

SYS@orcl_12.1.0.2.0> connect test/test
Connected.
TEST@orcl_12.1.0.2.0> select program
  2  from   v$session
  3  where   sid = (select sid from v$mystat where rownum=1)
  4  /
where   sid = (select sid from v$mystat where rownum=1)
                               *
ERROR at line 3:
ORA-00942: table or view does not exist

-- unable to grant select on v$mystat:

TEST@orcl_12.1.0.2.0> connect sys as sysdba
Connected.
SYS@orcl_12.1.0.2.0> grant select on v$mystat to test
  2  /
grant select on v$mystat to test
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

-- identification of v$mystat as synonym and identification of v_$mystat as underlying table:

SYS@orcl_12.1.0.2.0> select object_type
  2  from   dba_objects
  3  where  object_name = 'V$MYSTAT'
  4  /

OBJECT_TYPE
-----------------------
SYNONYM

1 row selected.

SYS@orcl_12.1.0.2.0> select table_name
  2  from   dba_synonyms
  3  where  synonym_name = 'V$MYSTAT'
  4  /

TABLE_NAME
--------------------------------------------------------------------------------
V_$MYSTAT

1 row selected.

-- granting of select on underlying table v_$mystat:

SYS@orcl_12.1.0.2.0> grant select on v_$mystat to test
  2  /

Grant succeeded.

-- original select statement now runs without error:

SYS@orcl_12.1.0.2.0> connect test/test
Connected.
TEST@orcl_12.1.0.2.0> select program
  2  from   v$session
  3  where   sid = (select sid from v$mystat where rownum=1)
  4  /

PROGRAM
----------------------------------------------------------------
sqlplus.exe

1 row selected.

Report message to a moderator

Re: How to Get the Program Name in 11g version [message #690165 is a reply to message #690164]

Sat, 09 November 2024 00:49

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The problem is not with V$MYSTAT (or SYS.V_$MYSTAT) which is granted SELECT to PUBLIC (default settings in 11g) but with V$SESSION:

SQL> select grantee from dba_tab_privs where owner='SYS' and table_name='V_$MYSTAT' and privilege='SELECT' order by 1;
GRANTEE
------------------------------
APEX_030200
PLUSTRACE
PUBLIC
SELECT_CATALOG_ROLE

4 rows selected.

SQL> select grantee from dba_tab_privs where owner='SYS' and table_name='V_$SESSION' and privilege='SELECT' order by 1;
GRANTEE
------------------------------
APEX_030200
APPQOSSYS
CTXSYS
ORACLE_OCM
PERFSTAT
SELECT_CATALOG_ROLE

6 rows selected.

SQL> @v

Oracle version: 11.2.0.4.181016 EE - JVM v1.6.0_43 - timezone files v11

[Updated on: Sat, 09 November 2024 00:49]

Report message to a moderator

Re: How to Get the Program Name in 11g version [message #690166 is a reply to message #690165]

Sat, 09 November 2024 06:06

Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA

Senior Member

We can use own context:

create or replace
  context ctx
    using ctx_pkg
/
create or replace
  package ctx_pkg
    is
      procedure set_program;
end;
/
create or replace
  package body ctx_pkg
    is
      procedure set_program
        is
            v_program varchar2(64);
        begin
            select  program
              into  v_program
              from  v$session
              where sid = sys_context('userenv','sid');
            dbms_session.set_context('ctx','program',v_program);
      end;
end;
/
create or replace
  trigger after_login_trg
  after logon
  on database
  begin
      ctx_pkg.set_program;
end;
/

Now:

SQL> connect u1/u1@orcl11
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select sys_context('ctx','program') program from dual;

PROGRAM
--------------------------------------------------------------------------------
sqlplus.exe

SQL>

SY.

Report message to a moderator

Re: How to Get the Program Name in 11g version [message #690167 is a reply to message #690163]

Sat, 09 November 2024 08:53

wtolentino
Messages: 421
Registered: March 2005

Senior Member

I forgot to think about that workaround. That works thanks.

Report message to a moderator

Previous Topic:	SQL count with comma delimit
Next Topic:	Find out the column values based on primary key from one table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 17:45:25 CST 2025