This content has been marked as final. Show 12 replies
So you want these NLS settings just for one specific user login and cannot change the Sort and Comparison settings in
Tool > Preferences > Database > NLS ?Perhaps you might try creating a logon script and substituting it for your logon trigger:
Tool > Preferences > Database > Filename for connection startup scriptI cannot recall which between the logon script and NLS preferences gets executed first, but it may be worth a try.
SQL Developer Team
I use php to communicate with oracle database. This triger work just find and search is now non-sensitive :)
But after I add triger, Oracle SQL Developer tool won't normal login in database (session restart and close connection).
I tried to enable empty logon script that you've recommended it (I know I can delete triger with this script), but it did not help to prevent onlogin tringer.
This is bug is in SQL Developer Tool, now I try MyOra and work fine.
Please correct this error in the next version :)
Actually I had not yet got to the point of considering your database connection reset a bug, so I did not try to reproduce the behavior with the test case you supplied. I was just offering advice on possible workarounds.
To complete the test case, and possibly get a bug logged, I will need some additional information:
1. OS, version, 32-bit or 64-bit
2. Java version (from Help > About > Properties > java.version)
3. JDBC driver version (from Help > About > Properties > jdbc.library)
4. Oracle DB server version, local or remote?
5. Oracle client version (if any, installed on your PC/latop)
6. If Oracle client installed, do you use the OCI/Thick driver (Tools > Preferences > Database > Advance > Use OCI/Thick?)
7. In your SQL Developer connection details, which connection type do you use: Basic, TNS, or some other?
8. Is the reset condition a regression? That is, did your test case work fine on some earlier SQL Developer version? If so, which?
Thanks in advance,
Edited by: Gary Graham on Dec 6, 2012 7:41 AM
1. Microsoft Windows XP [Version 5.1.2600] 32bit
2. On XP was install jre7 32bit, but in SQL Developer
Java(TM) Platform 1.6.0_35
Oracle IDE 3.2.20.09.87
Versioning Support 3.2.20.09.87
3. jdbc.library /C:/sqldeveloper-3.2.20.09.87/jdbc/lib/ojdbc6.jar
4. remote server Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
5. not install Oracle client version (I download instantclient_11_2 and unzip on c root folder no installation find in folder)
6. i do not use the OCI/Thick driver
7. I use BASIC connection
8. First I have Oracle SQL Developer 3.0.x did't work with onlogin triger so I upgrade to 3.2.x
If I test connection I get Success like this http://prntscr.com/lhjrh
after this i try connect and get this http://prntscr.com/lhjzq
An error was encountered performing the requested operation:
Vendor code 17008
Do you need something else I will be happy to help?
So far I am unable to reproduce the connection reset. I only have the 10.2.0.1, 220.127.116.11, and 18.104.22.168 database releases readily available, but I did try two different ojdbc6.jar files (the one that ships with 22.214.171.124 DB plus the one with the latest SQL Developer 3.2.20.09.87) for both the Thin and OCI/Thick driver cases.
The user only has very basic privileges (also including the 'ALTER SESSION' system privilege) and the test case trigger (created by the SYS user), but after connecting can display that trigger plus other public objects like pubic synonyms without problems.
If the problem is somehow specific to 10.2.0.5, keep in mind that Premier support for 10.2.0.x ended July 2010 and Extended support ends July 2013, so getting a fix may be unlikely anyway. Finding an acceptable workaround is most practical.
Your comments imply the connection reset is immediate upon login. Is that true, or might there be some delay before it occurs, or it occurs after taking some other post-login action?
-Confirm the other products work with the same schema/login and the onlogin trigger active.
-Try catching any exception in the onlogin trigger to put (and commit) information into a log table for later analysis http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/errors.htm#i3372 Retrieving the Error Code and Error Message: SQLCODE and SQLERRM.
Connection immediate reset after login.
Please try connect without OCI/Thick (Tools > Preferences > Database > Advance > Use OCI/Thick?)
Today I try and use MyORA, SQL+ and work fine.
Only Oracle SQL Developer not working :(
1. Shema and login is same
2. how I create exception on error login and insert error log in table?
CREATE TABLE errors (code NUMBER, message VARCHAR2(64), happened TIMESTAMP); DECLARE v_code NUMBER; v_errm VARCHAR2(64); BEGIN /* some missing code to record any error after login */ EXCEPTION WHEN OTHERS THEN v_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1 , 64); DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm); INSERT INTO errors VALUES (v_code, v_errm, SYSTIMESTAMP); END;
-You should commit the insert, so it is persistent and visible in other sessions.
-It is up to you whether to raise the error or not, after logging it in the table.
-You can get more info than SQLERRM and SQLCODE in an exception - for example a stack trace which would be more than varchar2(64).
-I often use wireshark. I have not used wireshark for onlogon triggers. Report back on the exception (if any) first.