Oracle Forms 12c LOGON procedure throwing exception

Sivudu

I was upgrading my Forms 11g application to 12c.

Installed FMW and configured weblogic domain.

Compiled all my 11g forms in 12c builder including fmb, mmb and pll , though there are some issues i had all FMX, MMX and PLX files created.

Configured fmwweb.cfg, default.env files, TNS_ADMIN under $ORACLE_HOME\user_projects\domains\myappdomain\config\fmwconfig , started the weblogic server, application is launching in Edge browser under IE mode.

we use Oracle database Accounts for login (Oracle 19c). when providing the userName and password application throws exception alert message

did some debugging on ON-LOGON trigger and the control goes to the following procedure but fails with the above error. There are no hits to the database with these parameters. I could connect to the account from SQLPLUS using same credentials.

LOGON(

           LOWER(:logon_parms.emp_id_flag||:LOGON_PARMS.TNS_USERNAME)

           ,LOWER(:LOGON_PARMS.TNS_PASSWORD)||'@'||LOWER(:LOGON_PARMS.TNS_SERVICE)

           ,FALSE

          );

I see all the parameter values are populated correctly before calling LOGON function.

I am new to Forms and Report but not to programming. any help is highly appreciated.

Thank you

Frank Hoffmann

The Error Message seems to show a different problem. Your login-user seems not to have the necessary privledges or misses a public or private synonym to access a certain table or view. Press (SHIFT+F1) and if you are lucky you see the missing privledge.

Frank

Sivudu

Thank you Frank for the response, I had the DBA recreate all the Public synonyms but the issue persists, SHIFT+F1 on Edge browser did not work.

Holger.Lehmann

SHIFT+F1 is not for Edge, it is a shortcut in the forms application to show recent errors.

Hub Tijhuis

I don't expect the LOGON-build-in of oracle forms to access table/view, so maybe that access is in something that is triggered by the LOGON ? Does there exists an AFTER LOGON trigger in the database , and has that code in it that gets to another branch when using 12c instead of 11g? (I have some remembrance of restricting some activity for certain user and/or setting NLS-settings in the forms environment by checking the "program"-column of v$session).

user12056186

The best way to find this issue would be to debug the session with Forms. In Oracle 19 the passworts are not case insensitive as default. So the "Lower" might change the Passwort to an invalid value.

Can you ask the dba if the is a parameter changed for insensitive passwords in Oracle19?

But my problem is that I would expect 0RA-3113 rather ORA-0942. Can you check if before or after the logon code there are selects or execute queries on blocks that might raise this error?

Did you switch the database together with Forms at the same time?

It could be that you start the session with credentials and are logged in after you started the session. Can you please check the configuration in formsweb.cfg. Do you set userid / password in the configuration you use?

Frank

Sivudu

@Hub Tijhuis

I dont find any trigger AFTER LOGON

I am not able to follow NLS and v$session point, can you pls elaborate?

Sivudu

@user12056186

hi Frank, Thank you for the response!

To avoid any confusion on password case, i always used lowercase.

There are no SELECT commands before or after LOGON,

i have the following condition to check and logon procedure is called here, after

elsif   :LOGON_PARMS.TNS_USERNAME is not null and

         :LOGON_PARMS.TNS_PASSWORD is not null then

   LOGON(

            LOWER(:logon_parms.emp_id_flag||:LOGON_PARMS.TNS_USERNAME)

            ,LOWER(:LOGON_PARMS.TNS_PASSWORD)||'@'||LOWER(:LOGON_PARMS.TNS_SERVICE)

            ,FALSE

          );

    :LOGON_PARMS.LOGIN_ERROR_CODE := NULL; 

   IF FORM_FAILURE then

      Info_alert('FORM_FAILURE??');

       :LOGON_PARMS.CONNECTED_FLAG := 'FALSE';

       :LOGON_PARMS.LOGIN_ERROR_CODE := DBMS_ERROR_CODE; 

   else -- After successful LOGON, following supposed to run

      :LOGON_PARMS.CONNECTED_FLAG := 'TRUE';

      :PARAMETER.PARM4 := 'TRUE';

      Std_role_set;

      :GLOBAL.USER_ORACLE_USERNAME := LOWER(:logon_parms.emp_id_flag||:LOGON_PARMS.TNS_USERNAME);

       :GLOBAL.USER_ORACLE_PASSWORD := :LOGON_PARMS.TNS_PASSWORD;

       :GLOBAL.USER_ORACLE_SERVICE := :LOGON_PARMS.TNS_SERVICE;

   end if;

end if;

Yes we are switching Forms &Report 11g to 12c and Database 11g to 19c together

in formsweb.cfg i am not setting any configuration for UserId or Password.

Hub Tijhuis

https://community.oracle.com/tech/developers/discussion/comment/16853977#Comment_16853977

Don't mind, it was only mend to illustrate the use of the trigger. We wanted different behaviour in a session where the login was in forms or in another tool. To determine where the login had occurred we used the "program" information in the session. And we had to change that when we got a new forms-version.

If there is no such trigger I am out of ideas where to find that table/view access. If you add a debugging message after that logon call you really don't get there?

logon(....);

message('Logon succesfully completed');

Frank Hoffmann

Lets continue the interesting investigation ..

First .. whats behind "Std_role_set;" a database or Forms procedure? Whats in it?

The Login came from a button - can you send us the complete WHEN-BUTTON-PRESSED Trigger code behind your login button - or is this this already the code you sended us?

You might place the  

"IF FORM_FAILURE then Info_alert('FORM_FAILURE??');" directly behind the login and place the ":LOGON_PARMS.LOGIN_ERROR_CODE := NULL; " before the LOGON or after the "FORM_FAILURE"

Can you check if the exception raises if you enter a false password? Do you still get the "ORA-0942" then or the info_alert('FORM_FAILURE??');?

Frank