-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathIPC.pkb
More file actions
63 lines (43 loc) · 2.09 KB
/
IPC.pkb
File metadata and controls
63 lines (43 loc) · 2.09 KB
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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
create or replace package body ipc as
function exec_plsql_in_other_session(plsql in varchar2, maxwait_seconds in number := 1) return varchar2 is
v_pipe_name varchar2(37) := 'pipe-' || sys_guid;
v_status number;
v_proc varchar2(32000);
v_result varchar2(32000);
pragma autonomous_transaction;
begin
v_status := dbms_pipe.create_pipe(v_pipe_name);
v_proc := 'declare c number; dummy number; status number; res varchar2(4000);' ||
'begin c:= dbms_sql.open_cursor;' ||
'dbms_sql.parse(c, q''@' || plsql || '@'', dbms_sql.native);' ||
'dbms_sql.bind_variable(c, ''result'', lpad('' '', 32000));' ||
'dummy := dbms_sql.execute(c);' ||
'dbms_sql.variable_value(c, ''result'', res);' ||
'dbms_sql.close_cursor(c);' ||
'dbms_pipe.pack_message(res);' ||
'status := dbms_pipe.send_message(''' || v_pipe_name || ''');' ||
'exception when others then' ||
' dbms_pipe.pack_message(sqlerrm); ' ||
' status := dbms_pipe.send_message(''' || v_pipe_name || ''');' ||
' raise_application_error(-20800, sqlerrm);' ||
'end;';
dbms_scheduler.create_job (
job_name => 'tools_process_memory',
job_type => 'PLSQL_BLOCK',
job_action => v_proc,
start_date => null,
repeat_interval => null,
enabled => true
);
v_status := dbms_pipe.receive_message(v_pipe_name, maxwait_seconds);
dbms_pipe.unpack_message(v_result);
v_status := dbms_pipe.remove_pipe(v_pipe_name);
commit;
return v_result;
exception when others then
v_status := dbms_pipe.remove_pipe(v_pipe_name);
rollback;
return 'IPC.exec_plsql_in_other_session: ' || sqlerrm;
end exec_plsql_in_other_session;
end ipc;
/