How to convert this proc into Merge command

[st2003]

create or replace procedure mtg_load as
v_osuser mtg_sessions.osuser%type;
v_username mtg_sessions.username%type;
v_program mtg_sessions.program%type;
v_machine mtg_sessions.machine%type;
V_N NUMBER;

cursor c1 is
select osuser, username, program, machine
from v$session;
--where rownum < 20;

begin
open c1;
loop
fetch c1 into v_osuser, v_username, v_program, v_machine;
exit when c1%notfound;
dbms_output.put_line(v_osuser||';'||v_machine);

begin
select 1 into v_n
from mtg_sessions
where osuser=v_osuser and machine=v_machine;

update mtg_sessions
set username = v_username,
program = v_program
where osuser = v_osuser
and machine = v_machine;

exception
when no_data_found then
insert into mtg_sessions values (v_osuser, v_username, v_program, v_machine);
commit;
when others then null;
end;

end loop;
close c1;

end;
/

show err

[rotem_fo]

MERGE INTO g5mtg_sessions g_ses
     USING v$session v_ses
        ON (    g_ses.osuser = v_ses.osuser 
            AND g_ses.machine = v_ses.machine)
   WHEN MATCHED THEN
      UPDATE
         SET username = v_ses.username, program = v_ses.program
   WHEN NOT MATCHED THEN
      INSERT (osuser, machine, program, username)
      VALUES (v_ses.osuser, v_ses.machine, v_ses.program, v_ses.username)
/

Hope this helps...

[DaPi]

Firstly, does this actually work? I don't think the UPDATE will give a NO_DATA_FOUND exception. Test on SQL%ROWCOUNT = 0, 1 and >1

Secondly, did you try a MERGE?


(P.S. rotem_fo must be having a better day than I am . . . )

[gandolf989]

Take your pick.

CREATE OR REPLACE PROCEDURE mtg_load
AS
   CURSOR c_v_session IS
      SELECT osuser, username, program, machine
        FROM v$session;
BEGIN
   FOR v_row IN c_v_session
   LOOP
      BEGIN
         INSERT INTO mtg_sessions
         VALUES ( v_row.osuser,
                  v_row.username,
                  v_row.program,
                  v_row.machine );
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX
         THEN
            UPDATE mtg_sessions
               SET username = v_row.username,
                   program  = v_row.program
             WHERE osuser   = v_row.osuser   AND
                   machine  = v_row.machine;
         WHEN OTHERS
         THEN
            RAISE;
      END;

   END LOOP;

   COMMIT;
END;
/

 
MERGE INTO mtg_sessions mtg
USING ( SELECT osuser, username, program, machine FROM v$session ) vsess
   ON ( mtg.osuser   = vsess.osuser   AND
        mtg.machine  = vsess.machine )
 WHEN MATCHED THEN UPDATE SET   mtg.username = vsess.username,
                                mtg.program  = vsess.program
 WHEN NOT MATCHED THEN INSERT ( mtg.osuser,
                                mtg.username,
                                mtg.program,
                                mtg.machine)
                       VALUES ( vsess.osuser,
                                vsess.username,
                                vsess.program,
                                vsess.machine);

[gandolf989]

Originally posted by DaPi
Firstly, does this actually work? I don't think the UPDATE will give a NO_DATA_FOUND exception. Test on SQL%ROWCOUNT = 0, 1 and >1

Secondly, did you try a MERGE?


(P.S. rotem_fo must be having a better day than I am . . . )

Sorry dude, the select into should generate a no data found. Second, he wanted someone to give him the syntax for the merge.

[DaPi]

Originally posted by gandolf989
Sorry dude, the select into should generate a no data found.