12 Replies Latest reply: Dec 17, 2012 6:21 AM by 978086 RSS

    Problem with login after onlogin

    978086
      After I create Triger onlogin like this
      CREATE OR REPLACE
      TRIGGER abc20.abc20_nls_tr
      AFTER logon ON abc20.SCHEMA
      BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_SORT=''BINARY_CI''';
      EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_COMP=''LINGUISTIC''';
      END;
      This triger work just fine, but after no work login with Oracle SQL Developer Version 3.2.20.09.

      I get this error http://prntscr.com/ldjsq


      Please help

      Edited by: 975083 on 2012.12.05 01:55
        • 1. Re: Problem with login after onlogin
          978086
          Is it possible to exclude changes in alter session?

          the only thing I found is Tool -> Preferences -> Database -> NLS -> Skip NLS Settings, but this not help me?
          • 2. Re: Problem with login after onlogin
            Gary Graham-Oracle
            Hi,

            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 script
            I cannot recall which between the logon script and NLS preferences gets executed first, but it may be worth a try.

            Regards,
            Gary
            SQL Developer Team
            • 3. Re: Problem with login after onlogin
              978086
              Hi Gary

              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 :)


              Regards,
              Filip
              • 4. Re: Problem with login after onlogin
                Gary Graham-Oracle
                Hi Filip,

                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,
                Gary

                Edited by: Gary Graham on Dec 6, 2012 7:41 AM
                • 5. Re: Problem with login after onlogin
                  978086
                  Hi

                  1. Microsoft Windows XP [Version 5.1.2600] 32bit
                  2. On XP was install jre7 32bit, but in SQL Developer

                  Component     Version
                  =========     =======
                  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
                  and http://prntscr.com/lhk13

                  An error was encountered performing the requested operation:
                  Closed Connection
                  Vendor code 17008


                  Do you need something else I will be happy to help?

                  Regards,
                  Filip
                  • 6. Re: Problem with login after onlogin
                    Gary Graham-Oracle
                    Hi Filip,

                    So far I am unable to reproduce the connection reset. I only have the 10.2.0.1, 11.2.0.1, and 11.2.0.2 database releases readily available, but I did try two different ojdbc6.jar files (the one that ships with 11.2.0.1 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?

                    -Gary
                    • 7. Re: Problem with login after onlogin
                      978086
                      Hi Gary,

                      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 :(

                      -Filip
                      • 8. Re: Problem with login after onlogin
                        Turloch O'Tierney-Oracle
                        Hi Filip,

                        -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.

                        -Turloch
                        SQLDeveloper team
                        975083 wrote:
                        Hi Gary,

                        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 :(

                        -Filip
                        • 9. Re: Problem with login after onlogin
                          978086
                          Hi Turloch

                          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;
                          • 10. Re: Problem with login after onlogin
                            978086
                            Hi Turloch,

                            1. Is it possible to log to a file via SQL Developer using some command flag to get all errors in connection on database?
                            2. If I install wireshark and sniff network packet whether can help you with it?

                            Filip
                            • 11. Re: Problem with login after onlogin
                              Turloch O'Tierney-Oracle
                              Hi Filip,

                              -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.

                              -Turloch
                              SQLDeveloper team
                              • 12. Re: Problem with login after onlogin
                                978086
                                Hi,

                                I add Insert but I can not catch any error :(.


                                I make images for every step

                                First I have trigger logon and I can't connection on database
                                after that I drop triger with MyOra and I reconnect on database successfully

                                please see images
                                http://zakrpa.com/oracle.html

                                Filip