This discussion is archived
4 Replies Latest reply: Jan 28, 2013 11:14 PM by HamidHelal RSS

send parameter from forms to database

Maahjoor Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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)

Legend

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