How Can I create my stored procedure?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How Can I create my stored procedure?

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    How Can I create my stored procedure?

    Hi,
    I've this table AFM_USERS.

    CREATE TABLE AFM_USERS
    (
    USER_PWD VARCHAR2(64 BYTE),
    USER_NAME VARCHAR2(64 BYTE),
    BAD_LOGIN NUMBER DEFAULT 0
    )

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$............0
    JOHN..............JOHN$.............0
    SMITH.............SMITH$............0
    KOSTER............KOSTER$...........0
    ........................................
    ........................................

    I've my application that connect Oracle via ODBC.

    When I connect, for example, with USER_NAME=ADAMS PASSWORD=ADAMS$ into sys.fga_log$ table I get 2 records:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.......TIMESTAMP.............. LSQLTEXT...............................................................LSQLBIND
    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34.....AFM........AFM_MODS....AFM_MODS.....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................


    When I connect, for example, with USER_NAME=SMITH PASSWORD=SMITH$ into sys.fga_log$ table I get others 2 records:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT...............................................................LSQLBIND
    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    When I connect, for example, with USER_NAME=SMITH PASSWORD=AAAA(Bad Password) into sys.fga_log$ table I get just one record:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT...............................................................LSQLBIND
    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:16:01,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH

    When I connect, for example, with USER_NAME=KOSTER PASSWORD=BBBBB(Bad Password) into sys.fga_log$ table I get just one record:

    OSUID.................OSHST......OBJ$SCHEMA....OBJ$NAME.....POLICYNAME.....TIMESTAMP..............LS QLTEXT...............................................................LSQLBIND
    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:15:23,7030..SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):ADAMS
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS.......AFM_MODS....01/12/2008 7:15:26,7044....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34...AFM_SECURE....AFM_USERS....AFM_LOGIN....01/12/2008 7:15:35,7070...SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH
    191.164.2.34\Sam...191.164.2.34....AFM........AFM_MODS......AFM_MODS....01/12/2008 7:15:38,7067.....SELECT afm_module,button,afm_module_it FROM afm_mods.................................

    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN..01/12/2008 7:16:01,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):SMITH

    191.164.2.34\Sam...191.164.2.34....AFM_SECURE....AFM_USERS....AFM_LOGIN...01/12/2008 7:17:04,7067.......SELECT USER_NAME,USER_PSWD, COUNTER FROM afm_users WHERE user_name=:1....#1(5):KOSTER

    Now I'd like to create a stored procedure on sys.fga_log$ table that increase or decrease of 1 the COUNTER column of table AFM_USERS with these conditions:

    if I connect not correctly (user o password incorrect) I increase COUNTER of 1 (+1)
    if I connect correctly (user and password correct) I decrease COUNTER of 1 (-1)

    In my case I'd like to get like this:

    When I connect with USER_NAME=ADAMS PASSWORD=ADAMS$ I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$............-1 -- (-1)
    JOHN..............JOHN$.............0
    SMITH.............SMITH$............0
    KOSTER............KOSTER$...........0
    ........................................
    ........................................

    When I connect with USER_NAME=SMITH PASSWORD=SMITH$ I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$............-1
    JOHN..............JOHN$.............0
    SMITH.............SMITH$............-1 -- (-1)
    KOSTER............KOSTER$...........0

    When I connect with USER_NAME=SMITH PASSWORD=AAAA(Bad Password) I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$............-1
    JOHN..............JOHN$.............0
    SMITH.............SMITH$............0 -- (+1)
    KOSTER............KOSTER$...........0


    When I connect with USER_NAME=SMITH PASSWORD=BBBBB(Bad Password) I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$............-1
    JOHN..............JOHN$.............0
    SMITH.............SMITH$............1 -- (+1)
    KOSTER............KOSTER$...........0

    When I connect with USER_NAME=KOSTER PASSWORD=BBBBB(Bad Password) I'd like to get:

    execute my_stored_procedure;

    select *
    from AFM_USERS;

    USER_NAME.......USER_PSWD........COUNTER
    ADAMS.............ADAMS$............-1
    JOHN..............JOHN$.............0
    SMITH.............SMITH$............1
    KOSTER............KOSTER$...........1 -- (+1)


    How can I create my stored procedure?


    Thanks in advance.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    What do you mean by "how can I create my stored procedure"?

    Could you please elaborate?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    Quote Originally Posted by PAVB
    What do you mean by "how can I create my stored procedure"?

    Could you please elaborate?
    I'd like to know what steps must follow to create my stored procedure.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you write the code - but you havent written anything useful here about what you actually want to do

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by raf
    I'd like to know what steps must follow to create my stored procedure.
    1- Write your storedproc in a file like yourfilename.sql
    2- Compile your storedproc

    Follows the basic syntax for your storedproc...
    Code:
    CREATE OR REPLACE PROCEDURE procedure_name IS
    BEGIN
      put your code logic here
    END procedure_name;
    /
    ...then log into sqlplus and do @yourfilename.sql
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jul 2002
    Posts
    228
    Quote Originally Posted by PAVB
    1- Write your storedproc in a file like yourfilename.sql
    2- Compile your storedproc

    Follows the basic syntax for your storedproc...
    Code:
    CREATE OR REPLACE PROCEDURE procedure_name IS
    BEGIN
      put your code logic here
    END procedure_name;
    /
    ...then log into sqlplus and do @yourfilename.sql
    Thanks but I don't Know write the code to update the column COUNTER with my conditions.

    Have someone any idea?

  7. #7
    Join Date
    Jul 2002
    Posts
    228
    In attach I posted my 2 tables: AFM_USERS and FGA_LOG$

    I'd like to create a procedure like this:

    Create or replace procedure update_counter
    IS

    cursor c1 is
    select SESSIONID,DBUID,OSUID,OSHST,OBJ$SCHEMA,OBJ$NAME,POLICYNAME,SCN,NTIMESTAMP#,PROCESS#,STATEMENT,ENTRYI D,LSQLBIND
    from FGA_LOG$;

    PROG NUMBER := 0;

    begin
    for a in c1 loop

    IF I connect not correctly then

    PROG := PROG + 1;

    UPDATE AFM_SECURE.AFM_USERS
    SET COUNTER = PROG
    WHERE USER_NAME = a.substr(LSQLBIND,instr(LSQLBIND,':')+1);

    END IF;

    IF I connect correctly then

    PROG := PROG - 1;

    UPDATE AFM_SECURE.AFM_USERS
    SET COUNTER = PROG
    WHERE USER_NAME = a.substr(LSQLBIND,instr(LSQLBIND,':')+1);

    END IF;

    end loop;
    commit;

    EXCEPTION
    WHEN OTHERS THEN
    raise;
    END;

    But I don't know how can I found the correct connection.

    When I connect correctly I get 2 records with OBJ$NAME = (AFM_USERS and AFM_MODS)
    and 2 records with POLICY_NAME = (AFM_LOGIN and AFM_MODS)

    When I connect not correctly I get ust one record with OBJ$NAME = AFM_USERS and POLICY_NAME = AFM_LOGIN

    Have someone any idea??
    Attached Files Attached Files

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Cool Exercise in futility

    raf,

    The main issue is that we see no purpose or need or stated requirement provided by you to justify querying and/or recording information from a SYStem table.

    Maybe it's just an exercise in futility. Could't you just query the Oracle supplied DBA_FGA_AUDIT_TRAIL view?
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width