4 Replies Latest reply: Jan 29, 2013 1:14 AM by HamidHelal RSS

    send parameter from forms to database

    Maahjoor
      Dear all,

      i want to send a parameter to after-logon database trigger from oracle forms.
      actually i want to send the active directory username to after logon to database.

      how to do this?
      i am using oracle forms 10g with database 10g.
      Regards.
        • 1. Re: send parameter from forms to database
          HamidHelal
          Maahjoor wrote:
          Dear all,

          i want to send a parameter to after-logon database trigger from oracle forms.
          actually i want to send the active directory username to after logon to database.
          Hi Maahjoor,

          database trigger has no option to accept parameter from outside.
          You need to store data in a table and then select value from table inside you trigger. other way is using procedure.


          Hope this helps


          Hamid


          If someone's response is helpful or correct, please mark it accordingly.*
          • 2. Re: send parameter from forms to database
            Hani
            You can define parameter at package and when user login you can pass value for this parameter, but this solution must use same session
            • 3. Re: send parameter from forms to database
              Maahjoor
              thanks,
              i use the procedure technique.call that function in the forms, and it is done.
              below is the procedure for future visitors.
              note: i did'nt test it yet.
              create tablE SESSION_AUDIT(
              active_directory_user varchar2(30) references system_users(active_directory_user),
              logon_time date
              )
              /
              CREATE TABLE SYSTEM_users(active_directory_user VARCHAR2(30) primary key, password VARCHAR2(50), 
              dept VARCHAR2(20), room VARCHAR2(10), room_phone VARCHAR2(30),
              mobile# VARCHAR2(20),total_logins NUMBER,user_status VARCHAR2(20))
              /
              CREATE TABLE NOTIFIED_USERS(active_directory_user VARCHAR2(30) REFERENCES SYSTEM_USERS(active_directory_user),
              notification_date DATE)
              /
              connect sys as sysdba
              --
              CREATE OR REPLACE PROCEDURE audit_sessions(uname varchar2,logon_date date) is
              begin
              insert into admin.session_audit values(uname,logon_date);
              commit;
              end;
              /
              CREATE OR REPLACE function return_active_dirctory_user (uname varchar2) 
              return varchar2 is
              begin
              return(uname);
              end;
              /
              
              -- create the NOTIFY_2_days_NOLOGON  trigger in the admin schema which we created.
              CREATE OR REPLACE PROCEDURE NOTIFY_2_days_NOLOGON IS
              CURSOR AA IS SELECT active_directory_user FROM ADMIN.SYSTEM_USERS WHERE active_directory_user IN
              (SELECT DISTINCT active_directory_user FROM ADMIN.SESSION_AUDIT WHERE LOGON_TIME<SYSDATE-1);
              UNAME VARCHAR2(100);
              BEGIN
              OPEN AA;
              LOOP
              FETCH AA INTO UNAME;
              EXIT WHEN AA%NOTFOUND;
              INSERT INTO ADMIN.NOTIFIED_USERS VALUES(UNAME,SYSDATE);
              END LOOP;
              CLOSE AA;
              COMMIT;
              EXCEPTION
              WHEN NO_DATA_FOUND THEN NULL;
              END;
              /
              BEGIN
              DBMS_SCHEDULER.CREATE_JOB(JOB_NAME=>'NOTIFY_NO_LOGON',
              JOB_TYPE=>'STORED_PROCEDURE',
              JOB_ACTION=>'NOTIFY_2_days_NOLOGON',
              REPEAT_INTERVAL=>'FREQ=DAILY;byhour=15;byminute=00;bysecond=00',
              ENABLED=>TRUE);
              end;
              /
              thank you so much for both of you.

              Edited by: Maahjoor on 28-Jan-2013 23:13
              • 4. Re: send parameter from forms to database
                HamidHelal
                Maahjoor wrote:
                thanks,
                i use the procedure technique.call that procedure in the forms, and it is done.
                thank you so much.
                Praise be to Almighty Allah (God)