1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
|
FUNCTION start_test ( p1 IN VARCHAR2, p2 IN VARCHAR2, p3 IN VARCHAR2 )
RETURN INTEGER
AS
current_sql CLOB;
BEGIN
current_sql := EMPTY_CLOB ( );
/*
... avant toute chose, tentons de récupérer ce le SQL ...
*/
SELECT d.sql_fulltext
INTO current_sql
FROM (SELECT address, hash_value
FROM v$open_cursor
WHERE user_name = USER AND sid = SYS_CONTEXT ( 'USERENV', 'SID' )) a
, (SELECT to_name, from_address
FROM v$object_dependency
WHERE to_owner = USER) b
, (SELECT address, hash_value, sql_fulltext
FROM v$sql
WHERE users_executing > 0
AND parsing_schema_name = USER
AND TRUNC ( last_active_time, 'MI' ) = TRUNC ( SYSDATE, 'MI' ) -- <-tentative peut être naive de réduire le nombre d'enregistrements remontés par l'appel a cette vue
) d
, (SELECT object, TYPE
FROM v$access
WHERE owner = USER AND TYPE = 'PACKAGE' AND sid = SYS_CONTEXT ( 'USERENV', 'SID' )) e
WHERE a.address = d.address
AND a.hash_value = d.hash_value
AND a.address = b.from_address
AND b.to_name = e.object;
DBMS_OUTPUT.put_line ( current_sql );
/*
...maintenant on fait pleins de trucs intéressants ici ...
*/
RETURN 1;
END start_test; |
Partager