This discussion is archived
12 Replies Latest reply: Dec 17, 2012 4:21 AM by 978086 RSS

Problem with login after onlogin

978086 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    TurlochO'Tierney Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points