execution de script oracle avec parametres inclus
bonjour,
j'ai un fichier de trace sql d'une application Documentum. la trace SQL est comme suit:
Citation:
Fri Sep 4 12:55:09 2009 298336: 23535[1] SessionID:0100ef6080787125 DBID:502 UPDATE geode.DM_SYSOBJECT_S dm_dbalias_B SET I_VSTAMP = 0 WHERE dm_dbalias_B.R_OBJECT_ID=:handle
Fri Sep 4 12:55:09 2009 298505: 23535[1] SessionID:0100ef6080787125 DBID:502 :handle = 0800ef6080614173
Fri Sep 4 12:55:09 2009 300133: 23535[1] SessionID:0100ef6080787125 DBID:502 UPDATE /*+ USE_NL(dmi_queue_item_s) */ dmi_queue_item_s SET sign_off_user = 'cbxgidv1.pharma.ave_IndexAgent1', task_state = 'acquired', dequeued_by = 'B#34293', i_vstamp = i_vstamp + 1 WHERE name = 'dm_fulltext_index_user' AND sign_off_user = ' ' AND task_state = ' ' AND dequeued_by = ' ' AND item_id = ANY (SELECT /*+ CARDINALITY(1) */ DISTINCT item_id FROM (SELECT item_id FROM (SELECT /*+ CARDINALITY(1) */qis.item_id FROM dmi_queue_item_s qis WHERE qis.name = 'dm_fulltext_index_user' AND qis.sign_off_user = ' ' AND qis.task_state = ' ' AND qis.dequeued_by = ' ') WHERE rownum <= 1000 ) qis2 WHERE 0 = ANY (SELECT count(qis1.r_object_id) FROM dmi_queue_item_s qis1 WHERE qis1.item_id = qis2.item_id AND qis1.task_state = 'acquired' AND qis1.name = 'dm_fulltext_index_user' AND qis1.sign_off_user != ' ' AND qis1.sign_off_user != 'cbxgidv1.pharma.ave_IndexAgent1' ))
Fri Sep 4 12:55:09 2009 324303: 23535[1] SessionID:0100ef6080787125 DBID:502 commit
Fri Sep 4 12:55:09 2009 327101: 23535[1] SessionID:0100ef6080787125 DBID:357 select all dmi_queue_item.r_object_id, dmi_queue_item.item_id, dmi_queue_item.event, dmi_queue_item.i_event_flags, dmi_queue_item.date_sent, dmi_queue_item.message, dmi_queue_item.task_subject from dmi_queue_item_sp dmi_queue_item where ((dmi_queue_item.name='dm_fulltext_index_user') and (dmi_queue_item.sign_off_user='cbxgidv1.pharma.ave_IndexAgent1') and (dmi_queue_item.task_state='acquired') and (dmi_queue_item.dequeued_by='B#34293'))
Fri Sep 4 12:55:09 2009 333031: 23535[1] SessionID:0100ef6080787125 DBID:357 commit
Fri Sep 4 12:55:10 2009 009519: 22345[1] SessionID:0100ef6080787112 DBID:505 select all stamp, priority, date_sent from geode.dm_queue where ((name='dm_mediaserver') and sign_off_user = ' ') order by priority desc, date_sent asc
Fri Sep 4 12:55:10 2009 010589: 22345[1] SessionID:0100ef6080787112 DBID:505 commit
Fri Sep 4 12:55:12 2009 863243: 23535[1] SessionID:0100ef6080787125 DBID:502 select distinct dmi_queue_item.r_object_id from dmi_queue_item_sp dmi_queue_item where ((dmi_queue_item.name='dm_fulltext_index_user') and (dmi_queue_item.task_state='acquired') and (dmi_queue_item.sign_off_user='cbxgidv1.pharma.ave_IndexAgent1') and (dmi_queue_item.dequeued_by='B#34292'))
Fri Sep 4 12:55:12 2009 868281: 23535[1] SessionID:0100ef6080787125 DBID:502 SELECT * FROM DMI_QUEUE_ITEM_SV dm_dbalias_B WHERE dm_dbalias_B.R_OBJECT_ID=:handle
Fri Sep 4 12:55:12 2009 868478: 23535[1] SessionID:0100ef6080787125 DBID:502 :handle = 1b00ef60802fd113
Fri Sep 4 12:55:12 2009 870121: 23535[1] SessionID:0100ef6080787125 DBID:502 select r_object_id from dm_relation_s where (child_id = :p0 or parent_id = :p1)
Fri Sep 4 12:55:12 2009 870291: 23535[1] SessionID:0100ef6080787125 DBID:502 :p0 = 1b00ef60802fd113
Fri Sep 4 12:55:12 2009 870443: 23535[1] SessionID:0100ef6080787125 DBID:502 :p1 = 1b00ef60802fd113
Fri Sep 4 12:55:12 2009 871526: 23535[1] SessionID:0100ef6080787125 DBID:502 DELETE FROM geode.DMI_QUEUE_ITEM_S DL_ WHERE (DL_.R_OBJECT_ID=:handle AND DL_.I_VSTAMP=:version)
Fri Sep 4 12:55:12 2009 871700: 23535[1] SessionID:0100ef6080787125 DBID:502 :handle = 1b00ef60802fd113
Fri Sep 4 12:55:12 2009 871858: 23535[1] SessionID:0100ef6080787125 DBID:502 :version = 1
Fri Sep 4 12:55:12 2009 873110: 23535[1] SessionID:0100ef6080787125 DBID:502 commit
Fri Sep 4 12:55:18 2009 667068: 22345[1] SessionID:0100ef6080787112 DBID:505 select all stamp, priority, date_sent from geode.dm_queue where ((name='dm_autorender_win31') and sign_off_user = ' ') order by priority desc, date_sent asc
Fri Sep 4 12:55:18 2009 668484: 22345[1] SessionID:0100ef6080787112 DBID:505 commit
Fri Sep 4 12:55:20 2009 048272: 22345[1] SessionID:0100ef6080787112 DBID:505 select all stamp, priority, date_sent from geode.dm_queue where ((name='dm_mediaserver') and sign_off_user = ' ') order by priority desc, date_sent asc
Fri Sep 4 12:55:20 2009 049329: 22345[1] SessionID:0100ef6080787112 DBID:505 commit
Fri Sep 4 12:55:28 2009 722767: 22345[1] SessionID:0100ef6080787112 DBID:505 select all stamp, priority, date_sent from geode.dm_queue where ((name='dm_autorender_win31') and sign_off_user = ' ') order by priority desc, date_sent asc
Fri Sep 4 12:55:28 2009 723965: 22345[1] SessionID:0100ef6080787112 DBID:505 commit
Fri Sep 4 12:55:30 2009 124835: 22345[1] SessionID:0100ef6080787112 DBID:505 select all stamp, priority, date_sent from geode.dm_queue where ((name='dm_mediaserver') and sign_off_user = ' ') order by priority desc, date_sent asc
Fri Sep 4 12:55:30 2009 126010: 22345[1] SessionID:0100ef6080787112 DBID:505 commit
Fri Sep 4 12:55:38 2009 780804: 22345[1] SessionID:0100ef6080787112 DBID:505 select all stamp, priority, date_sent from geode.dm_queue where ((name='dm_autorender_win31') and sign_off_user = ' ') order by priority desc, date_sent asc
Fri Sep 4 12:55:38 2009 782036: 22345[1] SessionID:0100ef6080787112 DBID:505 commit
Fri Sep 4 12:55:40 2009 156741: 22345[1] SessionID:0100ef6080787112 DBID:505 select all stamp, priority, date_sent from geode.dm_queue where ((name='dm_mediaserver') and sign_off_user = ' ') order by priority desc, date_sent asc
Fri Sep 4 12:55:40 2009 157777: 22345[1] SessionID:0100ef6080787112 DBID:505 commit
Fri Sep 4 12:55:44 2009 355713: 23542[1] SessionID:0100ef6080787126 DBID:320 SELECT dm_dbalias_B.R_OBJECT_ID FROM geode.DM_USER_S dm_dbalias_B WHERE (dm_dbalias_B.R_OBJECT_ID=:objectp AND dm_dbalias_B.I_VSTAMP=:versionp)
Fri Sep 4 12:55:44 2009 356086: 23542[1] SessionID:0100ef6080787126 DBID:320 :objectp = 1100ef608010c124
Fri Sep 4 12:55:44 2009 356221: 23542[1] SessionID:0100ef6080787126 DBID:320 :versionp = 53
Fri Sep 4 12:55:44 2009 358470: 23542[1] SessionID:0100ef6080787126 DBID:320 SELECT dm_dbalias_B.R_OBJECT_ID FROM geode.DM_SYSOBJECT_S dm_dbalias_B WHERE (dm_dbalias_B.R_OBJECT_ID=:objectp AND dm_dbalias_B.I_VSTAMP=:versionp)
Fri Sep 4 12:55:44 2009 358667: 23542[1] SessionID:0100ef6080787126 DBID:320 :objectp = 4b00ef6080408535
Fri Sep 4 12:55:44 2009 358799: 23542[1] SessionID:0100ef6080787126 DBID:320 :versionp = 36
dans cette trace il y a les requetes SQL et les parametres qui suivent:
Code:
1
2
3
| SELECT dm_dbalias_B.R_OBJECT_ID FROM geode.DM_SYSOBJECT_S dm_dbalias_B WHERE (dm_dbalias_B.R_OBJECT_ID=:objectp AND dm_dbalias_B.I_VSTAMP=:versionp)
:objectp = 4b00ef6080408535
:versionp = 36 |
ma question est; est ce qu'il y a un outil ou un moyen qui permet d'executer les requetes en leur passant les parametres directement dans le meme fichier SQL.
Merci par avance
