Issue with SYS_CONTEXT in Toad

Toad for Oracle
1

Hi all,

I have an issue with Toad’s (Version 9.7.2.5) handling of SYS_CONTEXT. When I do the following in SQL*PLUS I get:

SQL> exec DBMS_SESSION.SET_IDENTIFIER ( ‘Mark Testing’ );

PL/SQL procedure successfully completed.

SQL> SELECT SYS_CONTEXT(‘USERENV’,‘CLIENT_IDENTIFIER’) CLIENT_IDENTIFIER FROM DUAL;

CLIENT_IDENTIFIER

Mark Testing

SQL> SELECT SYS_CONTEXT(‘USERENV’,‘CLIENT_IDENTIFIER’) CLIENT_IDENTIFIER FROM DUAL;

CLIENT_IDENTIFIER

Mark Testing

SQL> SELECT SYS_CONTEXT(‘USERENV’,‘CLIENT_IDENTIFIER’) CLIENT_IDENTIFIER FROM DUAL;

CLIENT_IDENTIFIER

Mark Testing

CLIENT_IDENTIFIER

1 row selected.

CLIENT_IDENTIFIER

1 row selected.

As you can see, in Toad, the select statement returns the intended result only once. That is, immediately following

exec DBMS_SESSION.SET_IDENTIFIER ( ‘Mark Testing’ );

All subsequent selects do not return the expected result in Toad, it returns and empty row. What could be the explanation for this?

Thanks in advance for responding.

Regards,
Mark

CLIENT_IDENTIFIER

Mark Testing

That is, the session identifier set with DBMS_SESSION.SET_IDENTIFIER ( ‘Mark Testing’ ); is returned whenever the select statement is issued.

In Toad, however, I get;

exec DBMS_SESSION.SET_IDENTIFIER ( ‘Mark Testing’ ); PL/SQL procedure successfully completed.

SELECT SYS_CONTEXT(‘USERENV’,‘CLIENT_IDENTIFIER’) CLIENT_IDENTIFIER FROM DUAL;

1 row selected.
SELECT SYS_CONTEXT(‘USERENV’,‘CLIENT_IDENTIFIER’) CLIENT_IDENTIFIER FROM DUAL;

SELECT SYS_CONTEXT(‘USERENV’,‘CLIENT_IDENTIFIER’) CLIENT_IDENTIFIER FROM DUAL;

2

Mark,

Do you have Options -> Oracle -> Transactions -> Execute scripts in Toad session
checked? If not, each execution of the script will execute in a new session and
will give the results you have shown.

Greg

3

Hi Greg,

Yes, Options -> Oracle -> Transactions -> Execute scripts in Toad session is checked. The other three are not.

–
Mark

4

Hi Greg, any leads to solving this problem?

–
Mark

5

Mark,
The only way I can reproduce the issue is if that option is not checked. Otherwise, it works as expected for me in both 9.7.2.5 and 10. If you send me your toad.ini file off line, I’ll see if I can reproduce it with your settings.

Greg

6

I have sent the file.

7

Mark,

Uncheck Options -> Execute/Compile -> Login scripts -> Restore SET defaults
prior to script execution.

Greg

8

Thanks Greg. That solved my problem.

Cheers!

Mark