create or replace package method5.m5_pkg authid definer is

--Copyright (C) 2018 Jon Heller, Ventech Solutions, and CMS. This program is licensed under the LGPLv3.

--See https://method5.github.io/ for more information.

C_VERSION constant varchar2(10) := '9.4.0';

g_debug boolean := false;

/******************************************************************************

RUN

Purpose:

Run SQL, PL/SQL, or shell scripts on multiple databases or hosts.

The results, metadata, and errors are stored in tables on the user's schema

and in these three views: M5_RESULTS, M5_METADATA, and M5_ERRORS.

Inputs:

p_code - The SQL, PL/SQL, or Unix shell script to run.

p_targets (OPTIONAL) - Either a query or a comma-separated list of values.

The query must return one column with the database name, you may want to use

the table M5_DATABASE for that query.

The comma-separted list of values can match any of the database names, host

names, lifecycle statuses, or lines of business configured in M5_DATABASE.

Leave empty to use the (configurable) default targets.

p_table_name - (OPTIONAL) A table name, without quotation marks. If null,

a sequence is used to generate the table name.

p_table_exists_action (OPTIONAL) - One of these values:

ERROR - (DEFAULT) Only creates a new table. Raises exception -20017 if the table already exists.

APPEND - Adds data to the table. May raise an exception if the column types or names are different.

DELETE - Deletes current data before inserting new data. May raise an exception if the column types or names are different.

DROP - Drops the table if it exists and create a new one.

p_asynchronous (OPTIONAL) - Does the process return immediately (TRUE, the default)

or wait for all jobs to finish (FALSE).

p_run_as_sys (OPTIONAL) - Does the code run as SYS (TRUE) or as METHOD5 (FALSE, the default).

Outputs:

SELECT returns the query output, other SQL return the relevant SQL*Plus

feedback message, PL/SQL returns DBMS_OUTPUT, and shell scripts return stdout and stderr.

Side-Effects:

This procedure commits and creates tables, views, and jobs.

Example: SQL query to find invalid components on all DEV and QA databases.

begin

method5.m5_pkg.run(

p_code => q'< select * from dba_registry where status not in ('VALID', 'REMOVED') >',

p_targets => 'dev,qa'

);

end;

select * from m5_results order by 1,2;

select * from m5_metadata;

select * from m5_errors order by 1;

Notes:

- See https://github.com/method5/method5/blob/master/user_guide.md for details.

*******************************************************************************/

procedure run(

p_code varchar2,

p_targets varchar2 default null,

p_table_name varchar2 default null,

p_table_exists_action varchar2 default 'ERROR',

p_asynchronous boolean default true,

p_run_as_sys boolean default false

);

/******************************************************************************

GET_ENCRYPTED_RAW

Purpose:

Encrypt a command before sending it be executed remotely as SYS.

Inputs:

p_database_name - The name of the database as used in the database link.

p_command - The command to be encrypted.

Returns:

The encrypted command, as a RAW type.

(Encryption is performed using AES 256, CBC, and PKCS5 padding.

Keys are generated using a cryptographic pseudo-random number

generator, and are unique for each database. The keys are stored

remotely in SYS.LINK$, a special table that only SYS can read.

The command is padded with a GUID, to prevent replay attacks.)

Notes:

This function should only be called from a Method5 temporary procedure.

It doesn't make sense to call this function in any other context.

*******************************************************************************/

function get_encrypted_raw(

p_database_name varchar2,

p_command varchar2

) return raw;

/******************************************************************************

GET_AND_REMOVE_PIPE_DATA

Purpose:

Get and remove the pipe data generates and runs the Method5 commands on

remote databases. This is necessary beceause the jobs may be created as

the calling user, but the pipes are private to Method5. The definer-rights

functions allows the user to get the specific pipe data for their run.

Inputs:

p_target_name - The target for the pipe.

p_sequence - The unique sequence number for the pipe.

p_pipe_count - The number of pipes.

Returns:

The data stored in the pipe.

Notes:

This function should only be called from a Method5 job.

It doesn't make sense to call this function in any other context.

*******************************************************************************/

function get_and_remove_pipe_data(

p_target_name varchar2,

p_sequence varchar2,

p_pipe_count number

) return varchar2;

/******************************************************************************

STOP_JOBS

Purpose:

Stop Method5 jobs that are collecting data from databases. There can be a

large number of jobs, especially if some databases are not responding well.

This only stops jobs called FROM Method5, not jobs calling Method5.

Side-Affects:

Stops Method5 jobs.

Inputs:

p_owner - The user running the jobs. The default, NULL, means all users.

p_table_name - The name of the table being inserted into. This matches the

comments of the jobs. The default, NULL, means any table name.

p_elapsed_minutes - Only drop jobs that have been running for at least this

many minutes. The default, NULL, means any number of minutes.

Example: Stop jobs for the current user for a specific run:

begin

m5_pkg.stop_jobs(p_owner => user, p_table_name = 'M5_TEMP_12345');

end;

*******************************************************************************/

procedure stop_jobs

(

p_owner varchar2 default null,

p_table_name varchar2 default null,

p_elapsed_minutes number default null

);

/******************************************************************************

GET_TARGET_TAB_FROM_TARGET_STR

Purpose:

Get a nested table of target names from a target string.

Side-Affects:

None

Inputs:

p_target_string - Same syntax as the P_TARGETS parameter for RUN.

p_database_or_host - Return either the database names or the host names.

Example: View all databases in the lifecycle DEV or with a database name like ACME%:

select * from table(method5.m5_pkg.get_target_tab_from_target_str('dev,acme%'));

COLUMN_VALUE

------------

devdb1

devdb2

devdb3

...

*******************************************************************************/

function get_target_tab_from_target_str(

p_target_string in varchar2,

p_database_or_host in varchar2 default 'database'

) return method5.string_table result_cache;

end;

/

create or replace package body method5.m5_pkg is

/******************************************************************************/

--Private type used by multiple procedures.

type config_data_rec is record(

admin_email_sender_address varchar2(4000),

admin_email_recipients varchar2(4000),

access_control_locked varchar2(4000),

global_default_targets varchar2(4000),

has_valid_db_username varchar2(3),

has_valid_os_username varchar2(3),

user_default_targets varchar2(4000),

can_use_sql_for_targets varchar2(3),

can_drop_tab_in_other_schema varchar2(3),

db_domain_suffix varchar2(4000)

);

/******************************************************************************/

--(See specification for description.)

procedure stop_jobs

(

p_owner varchar2 default null,

p_table_name varchar2 default null,

p_elapsed_minutes number default null

) is

v_must_be_a_job exception;

pragma exception_init(v_must_be_a_job, -27475);

begin

for jobs_to_kill in

(

select dba_scheduler_running_jobs.owner, dba_scheduler_running_jobs.job_name, comments, elapsed_time

from sys.dba_scheduler_running_jobs

join sys.dba_scheduler_jobs

on dba_scheduler_running_jobs.job_name = dba_scheduler_jobs.job_name

and dba_scheduler_running_jobs.owner = dba_scheduler_jobs.owner

where dba_scheduler_jobs.auto_drop = 'TRUE'

and dba_scheduler_running_jobs.job_name like 'M5%'

and (regexp_replace(dba_scheduler_jobs.comments, 'TABLE:(.*)"CALLER:.*', '\1') = upper(p_table_name) or p_table_name is null)

and (regexp_replace(dba_scheduler_jobs.comments, 'TABLE:.*"CALLER:(.*)', '\1') = upper(trim(p_owner)) or p_owner is null)

and (dba_scheduler_running_jobs.elapsed_time > p_elapsed_minutes * interval '1' minute or p_elapsed_minutes is null)

order by dba_scheduler_jobs.owner, dba_scheduler_jobs.job_name

) loop

begin

sys.dbms_scheduler.stop_job(

job_name => jobs_to_kill.owner||'.'||jobs_to_kill.job_name,

force => true

);

exception when v_must_be_a_job then

--Ignore errors caused when a job finishes between the query and the STOP_JOB.

null;

end;

end loop;

end stop_jobs;

/******************************************************************************/

--(See specification for description.)

function get_encrypted_raw(p_database_name varchar2, p_command varchar2) return raw is

v_clean_db_link varchar2(128) := 'M5_'||trim(upper(p_database_name));

v_sys_key raw(32);

begin

--Get the SYS key.

select max(sys_key)

into v_sys_key

from method5.m5_sys_key

where db_link = v_clean_db_link;

--Throw error if the SYS key does not exist.

if v_sys_key is null then

raise_application_error(-20031, 'The SYS key for this database does not exist. '||

'Try calling this procedure to generate the key:'||chr(10)||

'begin'||chr(10)||

' method5.method5_admin.set_local_and_remote_sys_key(''m5_'||p_database_name||''');'||chr(10)||

'end;');

end if;

--Return the encrypted command.

return sys.dbms_crypto.encrypt

(

--Add SYS_GUID as a session ID, to prevent replay attakcs.

src => utl_i18n.string_to_raw (sys_guid() || p_command, 'AL32UTF8'),

typ => sys.dbms_crypto.encrypt_aes256 + sys.dbms_crypto.chain_cbc + sys.dbms_crypto.pad_pkcs5,

key => v_sys_key

);

end get_encrypted_raw;

/******************************************************************************/

--(See specification for description.)

function get_and_remove_pipe_data(

p_target_name varchar2,

p_sequence varchar2,

p_pipe_count number

) return varchar2 is

v_result integer;

v_code varchar2(32767);

v_item varchar2(4000);

v_pipename varchar2(128);

begin

--Reconstruct procedure DDL.

for pipe_index in 1 .. p_pipe_count loop

v_pipename := 'M5_'||p_target_name||'_'||p_sequence||'_'||pipe_index;

--Receive message; timeout=> ensures procedure will not wait.

v_result := sys.dbms_pipe.receive_message(v_pipename, timeout => 0);

if v_result <> 0 then

raise_application_error(-20023, 'Pipe error. Result = '||v_result||'.');

end if;

--Unpack, put together the string.

sys.dbms_pipe.unpack_message(v_item);

v_code := v_code||v_item;

--Remove the pipe.

v_result := sys.dbms_pipe.remove_pipe(v_pipename);

if v_result <> 0 then

raise_application_error(-20023, 'Pipe error. Result = '||v_result||'.');

end if;

end loop;

--Return code.

return v_code;

end get_and_remove_pipe_data;

/******************************************************************************/

--Get configuration data from M5_CONFIG, M5_USER, and V$PARAMETER.

--(Private function that's called by both get_target_tab_from_target_str and run.

function get_config_data return config_data_rec is

v_config_data config_data_rec;

v_db_domain varchar2(4000);

begin

--User configuration for admin email addresses.

select

listagg(email_address, ';') within group (order by lower(email_address)) admin_email_recipients,

min(email_address) admin_email_sender_address

into

v_config_data.admin_email_sender_address,

v_config_data.admin_email_recipients

from method5.m5_user

where is_m5_admin = 'Yes'

and email_address is not null;

--M5_CONFIG data.

select

max(case when config_name = 'Access Control - User is not locked' then string_value else null end) user_not_locked,

max(case when config_name = 'Default Targets' then string_value else null end) gloal_default_targets

into

v_config_data.access_control_locked,

v_config_data.global_default_targets

from method5.m5_config;

--User configuration data, with empty and 'No' for missing data.

select

nvl(max(has_valid_db_username), 'No') has_valid_db_username,

max(has_valid_os_username) has_valid_os_username,

max(default_targets) default_targets,

max(can_use_sql_for_targets) can_use_sql_for_targets,

max(can_drop_tab_in_other_schema) can_drop_tab_in_other_schema

into

v_config_data.has_valid_db_username ,

v_config_data.has_valid_os_username ,

v_config_data.user_default_targets ,

v_config_data.can_use_sql_for_targets ,

v_config_data.can_drop_tab_in_other_schema

from

(

--Configuration data for current user.

select m5_user.oracle_username, os_username, default_targets, can_use_sql_for_targets, can_drop_tab_in_other_schema

,'Yes' has_valid_db_username

,case

when

os_username is null or

lower(os_username) = lower(sys_context('userenv', 'os_user')) or

sys_context('userenv', 'module') = 'DBMS_SCHEDULER'

then 'Yes'

else 'No'

end has_valid_os_username

from method5.m5_user

where lower(m5_user.oracle_username) = lower(sys_context('userenv', 'session_user'))

);

--DB_DOMAIN from V$PARAMETER.

--The DB_DOMAIN changes all the database links if it exists.

select value

into v_db_domain

from v$parameter

where name = 'db_domain';

v_config_data.db_domain_suffix := case when v_db_domain is null then null else '.' || upper(v_db_domain) end;

return v_config_data;

end get_config_data;

/******************************************************************************/

--(See specification for description.)

function get_target_tab_from_target_str(

p_target_string in varchar2,

p_database_or_host in varchar2 default 'database'

) return method5.string_table result_cache is

v_config_data config_data_rec := get_config_data();

--SQL statements:

v_clean_select_sql varchar2(32767);

v_configured_target_query varchar2(32767);

--Types and variables to hold database configuration attributes.

type string_table_table is table of string_table;

v_config_type string_table;

v_config_key string_table;

v_config_values string_table_table;

type string_table_aat is table of string_table index by varchar2(32767);

v_config_key_values string_table_aat;

--Holds split list of items.

v_target_items string_table := string_table();

v_item varchar2(32767);

--Final value with databases:

v_target_tab string_table := string_table();

--If the input is a SELECT statement, return that statement without a terminator (if any).

--For example: '/* asdf*/ with asdf as (select 1 a from dual) select * from asdf;' would return

-- the same string but without the final semicolon. But 'asdf' would return null.

function get_unterminated_select(p_sql varchar2) return varchar2 is

v_category varchar2(32767);

v_statement_type varchar2(32767);

v_command_name varchar2(32767);

v_command_type number;

v_lex_sqlcode number;

v_lex_sqlerrm varchar2(32767);

v_tokens token_table;

begin

--Tokenize and remove semicolon if necessary.

v_tokens := plsql_lexer.lex(p_sql);

v_tokens := statement_terminator.remove_semicolon(v_tokens);

--Classify statement.

statement_classifier.classify(

p_tokens => v_tokens,

p_category => v_category,

p_statement_type => v_statement_type,

p_command_name => v_command_name,

p_command_type => v_command_type,

p_lex_sqlcode => v_lex_sqlcode,

p_lex_sqlerrm => v_lex_sqlerrm

);

--Return SQL if it's a SELECT>

if v_command_name = 'SELECT' then

return plsql_lexer.concatenate(v_tokens);

--Return NULL if it's not a SELECT.

else

return null;

end if;

end get_unterminated_select;

procedure add_target_group(p_item varchar2, p_target_items in out string_table) is

v_query clob;

v_targets method5.string_table := method5.string_table();

begin

--Get query for target group.

begin

select string_value query

into v_query

from method5.m5_config

where replace(trim(lower(config_name)), '$') like 'target group -%' || replace(trim(lower(p_item)), '$');

exception when no_data_found then

raise_application_error(-20025, 'Could not find the target group "'||p_item||'" in METHOD5.M5_CONFIG.'||

' Either fix the target group name or add the target group to the configuration.');

end;

--Get the targets

begin

execute immediate v_query bulk collect into v_targets;

exception when others then raise_application_error(-20026,

'There was an error retrieving the targets for the target group "'||p_item||'".'||

' Check the query in METHOD5.M5_CONFIG for valid syntax and sure it only '||

' returns one column.'||chr(10)||

sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

end;

--Add them to the existing list of targets.

for i in 1 .. v_targets.count loop

p_target_items.extend;

p_target_items(p_target_items.count) := lower(trim(v_targets(i)));

end loop;

end add_target_group;

begin

--Validate input.

if trim(p_database_or_host) is null or lower(trim(p_database_or_host)) not in ('host', 'database') then

raise_application_error(-20034, 'P_DATABASE_OR_HOST must be either HOST or DATABASE.');

end if;

--Get SQL to run (without a semicolon), if it's a SELECT.

v_clean_select_sql := get_unterminated_select(p_target_string);

--Only allow authorized users to run a SQL statement.

--Since this is a definer's right procedure the SQL statement could potentially query any table.

if v_clean_select_sql is not null and v_config_data.can_use_sql_for_targets = 'No' then

raise_application_error(-20036, 'You are not authorized to run SQL statements in P_TARGETS.'||chr(10)||

'Contact your Method5 administrator to change your access.');

end if;

--Execute P_TARGET_STRING as a SELECT statement if it looks like one.

if v_clean_select_sql is not null then

--Try to run query, raise helpful error message if it doesn't work.

begin

--Add an "intersect" to ensure that only valid rows are returned.

if lower(trim(p_database_or_host)) = 'database' then

execute immediate v_clean_select_sql || ' intersect select database_name from method5.m5_database'

bulk collect into v_target_tab;

else

execute immediate v_clean_select_sql || ' intersect select host_name from method5.m5_database'

bulk collect into v_target_tab;

end if;

exception when others then

sys.dbms_output.put_line('Target Name Query: '||chr(10)||p_target_string);

raise_application_error(-20006, 'Error executing P_TARGETS.'||chr(10)||

'Please check that the query is valid and only returns one VARCHAR2 column.'||chr(10)||

'Check the query stored in M5_CONFIG or check the DBMS_OUTPUT for the query.'||

sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

end;

--Remove duplicates.

v_target_tab := set(v_target_tab);

--Force lower-case to simplify comparisons.

for i in 1 .. v_target_tab.count loop

v_target_tab(i) := lower(v_target_tab(i));

end loop;

--Split P_TARGET_STRING into attributes if it's not a SELECT statement.

--Else treat P_TARGET_STRING as comma-separated-values that may identify database,

--host, lifecycle, line of business, or cluster name.

else

--Build query to retrieve database attributes as collections of strings.

v_configured_target_query :=

q'[

with config as

(

select database_name, instance_name, m5_default_connect_string connect_string, host_name, lifecycle_status, line_of_business, cluster_name

from method5.m5_database

where is_active='Yes'

)

select 'database_name' row_type, lower(database_name) row_value, cast(collect(distinct lower(database_name)) as method5.string_table)

from config

group by database_name

union all

select 'instance_name' row_type, lower(instance_name) row_value, cast(collect(distinct lower(database_name)) as method5.string_table)

from config

where instance_name <> database_name

group by instance_name

union all

select 'host_name' row_type, lower(host_name) row_value, cast(collect(distinct lower(database_name)) as method5.string_table)

from config

group by host_name

union all

select 'lifecycle_status' row_type, lower(lifecycle_status) row_value, cast(collect(distinct lower(database_name)) as method5.string_table)

from config

group by lifecycle_status

union all

select 'line_of_business' row_type, lower(line_of_business) row_value, cast(collect(distinct lower(database_name)) as method5.string_table)

from config

group by line_of_business

union all

select 'cluster_name' row_type, lower(cluster_name) row_value, cast(collect(distinct lower(database_name)) as method5.string_table)

from config

group by cluster_name

]';

--Convert the string to retreive hosts instead of databases for shell scripts.

if lower(trim(p_database_or_host)) = 'host' then

v_configured_target_query := replace(

v_configured_target_query,

'collect(distinct lower(database_name)',

'collect(distinct lower(host_name)'

);

end if;

--Gather configuration data.

begin

execute immediate v_configured_target_query

bulk collect into v_config_type, v_config_key, v_config_values;

exception when others then

sys.dbms_output.put_line('Configuration query that generated an error: '||v_configured_target_query);

raise_application_error(-20008, 'Error retrieving database configuration.'||

' Check the query stored in M5_CONFIG. Or check the DBMS_OUTPUT for the query.'||

chr(10)||sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

end;

--Convert configuration data into an associative array.

for i in 1 .. v_config_key.count loop

if v_config_key(i) is not null then

v_config_key_values(v_config_key(i)) := v_config_values(i);

end if;

end loop;

--Convert comma-separated list of targets into nested table.

declare

v_target_index number := 0;

begin

loop

v_target_index := v_target_index + 1;

v_item := regexp_substr(p_target_string, '[^,]+', 1, v_target_index);

exit when v_item is null;

--Replace target groups if necessary.

if trim(v_item) like '$%' then

add_target_group(v_item, v_target_items);

--Else use regular name.

else

v_target_items.extend();

v_target_items(v_target_items.count) := lower(trim(v_item));

end if;

end loop;

end;

--Map target items to configuration items, create a nested table with all data.

for i in 1 .. v_target_items.count loop

for j in 1 .. v_config_key.count loop

if v_config_key(j) like v_target_items(i) then

v_target_tab := v_target_tab multiset union distinct v_config_values(j);

end if;

end loop;

end loop;

end if;

return v_target_tab;

end get_target_tab_from_target_str;

/******************************************************************************/

--(See specification for description.)

procedure run(

p_code varchar2,

p_targets varchar2 default null,

p_table_name varchar2 default null,

p_table_exists_action varchar2 default 'ERROR',

p_asynchronous boolean default true,

p_run_as_sys boolean default false

) is

--All printable ASCII characters, excluding ones that would look confusing (',",@),

--and ones that match, such as [], <>, (), {}.

--This is a global constant to avoid being executed with each function call.

c_delimiter_candidates constant sys.odcivarchar2list := sys.odcivarchar2list(

'!','#','$','%','*','+',',','-','.','0','1','2','3','4','5','6','7','8','9',

':',';','=','?','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O',

'P','Q','R','S','T','U','V','W','X','Y','Z','^','_','`','a','b','c','d','e',

'f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x',

'y','z','|','~'

);

--Constants.

c_max_database_attempts constant number := 100;

--Collections.

type allowed_privs_rec is record(

os_username varchar2(128),

target varchar2(4000),

db_link_name varchar2(4000),

default_targets varchar2(4000),

install_links_in_schema varchar2(3),

run_as_m5_or_sandbox varchar2(7),

job_owner varchar2(128),

sandbox_default_ts varchar2(30),

sandbox_temporary_ts varchar2(30),

sandbox_quota number,

sandbox_profile varchar2(128),

privileges method5.string_table,

has_any_install_links varchar2(3)

);

type allowed_privs_nt is table of allowed_privs_rec;

--Code templates.

c_select_template constant varchar2(32767) := q'<

create procedure m5_temp_proc_##SEQUENCE## authid current_user is

v_dummy varchar2(1);

begin

--Ping database with simple select to create simple error message if link fails.

execute immediate 'select dummy from sys.dual@##DB_LINK_NAME##' into v_dummy;

execute immediate q'##QUOTE_DELIMITER2##

declare

v_rowcount number;

begin

--Create remote temporary table with results.

##DBA_OR_SYS_RUN_CTAS##

--Insert data into local tble using database link.

--Use dynamic SQL - PL/SQL must compile in order to catch exceptions.

execute immediate q'##QUOTE_DELIMITER1##

insert into ##TABLE_OWNER##.##TABLE_NAME##

select '##DATABASE_NAME##', m5_temp_table_##SEQUENCE##.*

from m5_temp_table_##SEQUENCE##@##DB_LINK_NAME##

##QUOTE_DELIMITER1##';

v_rowcount := sql%rowcount;

--Update _META table.

update ##TABLE_OWNER##.##TABLE_NAME##_meta

set targets_completed = targets_completed + 1,

date_updated = sysdate,

is_complete = decode(targets_expected, targets_completed+targets_with_errors+1, 'Yes', 'No'),

num_rows = num_rows + v_rowcount

where date_started = (select max(date_started) from ##TABLE_OWNER##.##TABLE_NAME##_meta);

--Drop remote temporary table.

sys.dbms_utility.exec_ddl_statement@##DB_LINK_NAME##(q'##QUOTE_DELIMITER1##

drop table m5_temp_table_##SEQUENCE## purge

##QUOTE_DELIMITER1##');

end;

##QUOTE_DELIMITER2##';

--Exception block must be outside of dynamic PL/SQL.

--Exceptions like "ORA-00257: archiver error. Connect internal only, until freed."

--will make the whole block fail and must be caught by higher level block.

exception when others then

update ##TABLE_OWNER##.##TABLE_NAME##_meta

set targets_with_errors = targets_with_errors + 1,

date_updated = sysdate,

is_complete = decode(targets_expected, targets_completed+targets_with_errors+1, 'Yes', 'No')

where date_started = (select max(date_started) from ##TABLE_OWNER##.##TABLE_NAME##_meta);

insert into ##TABLE_OWNER##.##TABLE_NAME##_err

values ('##DATABASE_NAME##', '##DB_LINK_NAME##'

, sysdate, sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

commit;

raise;

end;

>';

c_select_limit_privs_template constant varchar2(32767) := q'<

create procedure m5_temp_proc_##SEQUENCE## authid current_user is

v_dummy varchar2(1);

begin

--Ping database with simple select to create simple error message if link fails.

execute immediate 'select dummy from sys.dual@##DB_LINK_NAME##' into v_dummy;

execute immediate q'##QUOTE_DELIMITER3##

declare

v_rowcount number;

v_default_permanent_tablespace varchar2(128);

v_default_temporary_tablespace varchar2(128);

v_quota varchar2(128);

v_profile varchar2(128);

begin

--Find the temporary user properties if they exist, else use defaults.

select

nvl(requested_and_avail_ts, default_ts) tablespace,

nvl(requested_and_avail_temp_ts, default_temp_ts) temp_tablespace,

nvl(to_char('##QUOTA##'), 'UNLIMITED') quota,

nvl(requested_and_avail_profile, 'DEFAULT') profile

into v_default_permanent_tablespace, v_default_temporary_tablespace, v_quota, v_profile

from

(

--Requested and available values

select

(

select max(tablespace_name)

from dba_tablespaces@##DB_LINK_NAME##

where contents = 'PERMANENT'

and tablespace_name = trim(upper('##DEFAULT_PERMANENT_TABLESPACE##'))

) requested_and_avail_ts,

(

select max(tablespace_name)

from dba_tablespaces@##DB_LINK_NAME##

where contents = 'TEMPORARY'

and tablespace_name = trim(upper('##DEFAULT_TEMPORARY_TABLESPACE##'))

) requested_and_avail_temp_ts,

(

select distinct profile

from dba_profiles@##DB_LINK_NAME##

where profile = trim(upper('##PROFILE##'))

) requested_and_avail_profile

from dual

)

cross join

(

--Default values.

select

max(case when property_name = 'DEFAULT_PERMANENT_TABLESPACE' then property_value else null end) default_ts,

max(case when property_name = 'DEFAULT_TEMP_TABLESPACE' then property_value else null end) default_temp_ts

from database_properties@##DB_LINK_NAME##

);

--Create temporary user to run function.

sys.dbms_utility.exec_ddl_statement@##DB_LINK_NAME##('

create user m5_temp_sandbox_##SEQUENCE##

identified by "'||replace(replace(sys.dbms_random.string(opt=> 'p', len=> 26), '''', null), '"', null) || 'aA#1'||'"

account lock password expire

default tablespace '||v_default_permanent_tablespace||'

temporary tablespace '||v_default_temporary_tablespace||'

quota '||v_quota||' on '||v_default_permanent_tablespace||'

profile '||v_profile||'

');

--Grant the user privileges.

declare

v_privs sys.odcivarchar2list := sys.odcivarchar2list('create table'##ALLOWED_PRIVS##);

begin

for i in 1 .. v_privs.count loop

begin

sys.dbms_utility.exec_ddl_statement@##DB_LINK_NAME##(

'grant '||v_privs(i)||' to m5_temp_sandbox_##SEQUENCE##'

);

exception when others then null;

end;

end loop;

end;

--Create remote temporary procedure with CTAS.

##CREATE_CTAS_PROC##

--Create remote scheduler job to run the procedure as the sandbox user.

dbms_scheduler.create_job@##DB_LINK_NAME##(

job_name => 'm5_temp_sandbox_##SEQUENCE##.m5_temp_job_##SEQUENCE##',

job_type => 'stored_procedure',

job_action => 'm5_temp_sandbox_##SEQUENCE##.m5_temp_proc_##SEQUENCE##',

comments => 'Temporary Method5 job for a temporary Method5 user. Both will be dropped after the job finishes.'

);

--Workaround to PLS-00960: RPCs cannot use parameters with schema-level object types in this release.

dbms_utility.exec_ddl_statement@##DB_LINK_NAME##('

create procedure m5_temp_sandbox_##SEQUENCE##.run_job is

begin

dbms_scheduler.run_job(''m5_temp_sandbox_##SEQUENCE##.m5_temp_job_##SEQUENCE##'');

end;

');

--Run the procedure, that runs the job, that runs the proc, that runs the code.

execute immediate 'begin m5_temp_sandbox_##SEQUENCE##.run_job@##DB_LINK_NAME##; end;';

--Insert data into local tble using database link.

--Use dynamic SQL - PL/SQL must compile in order to catch exceptions.

execute immediate q'##QUOTE_DELIMITER2##

insert into ##TABLE_OWNER##.##TABLE_NAME##

select '##DATABASE_NAME##', m5_temp_table_##SEQUENCE##.*

from m5_temp_sandbox_##SEQUENCE##.m5_temp_table_##SEQUENCE##@##DB_LINK_NAME##

##QUOTE_DELIMITER2##';

v_rowcount := sql%rowcount;

--Update _META table.

update ##TABLE_OWNER##.##TABLE_NAME##_meta

set targets_completed = targets_completed + 1,

date_updated = sysdate,

is_complete = decode(targets_expected, targets_completed+targets_with_errors+1, 'Yes', 'No'),

num_rows = num_rows + v_rowcount

where date_started = (select max(date_started) from ##TABLE_OWNER##.##TABLE_NAME##_meta);

--Drop temporary user.

sys.dbms_utility.exec_ddl_statement@##DB_LINK_NAME##('drop user m5_temp_sandbox_##SEQUENCE## cascade');

end;

##QUOTE_DELIMITER3##';

--Exception block must be outside of dynamic PL/SQL.

--Exceptions like "ORA-00257: archiver error. Connect internal only, until freed."

--will make the whole block fail and must be caught by higher level block.

exception when others then

update ##TABLE_OWNER##.##TABLE_NAME##_meta

set targets_with_errors = targets_with_errors + 1,

date_updated = sysdate,

is_complete = decode(targets_expected, targets_completed+targets_with_errors+1, 'Yes', 'No')

where date_started = (select max(date_started) from ##TABLE_OWNER##.##TABLE_NAME##_meta);

insert into ##TABLE_OWNER##.##TABLE_NAME##_err

values ('##DATABASE_NAME##', '##DB_LINK_NAME##'

, sysdate, sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

commit;

--Cleanup by dropping the temporary user.

execute immediate q'##QUOTE_DELIMITER3##

begin

sys.dbms_utility.exec_ddl_statement@##DB_LINK_NAME##('drop user m5_temp_sandbox_##SEQUENCE## cascade');

end;

##QUOTE_DELIMITER3##';

raise;

end;

>';

c_shell_script_template constant varchar2(32767) := q'<

create procedure m5_temp_proc_##SEQUENCE## authid current_user is

v_database_name varchar2(128);

v_platform_name varchar2(4000);

begin

--Ping database with simple select to create simple error message if link fails.

--Also find out which database is used in the host link, and the platform name.

execute immediate 'select database_name, platform_name from method5.db_name_or_con_name_vw@##HOST_LINK_NAME##'

into v_database_name, v_platform_name;

--Windows shell commands are not yet supported.

if lower(v_platform_name) like '%windows%' then

raise_application_error(-20033, 'The shell command option does not yet support Windows platforms.');

end if;

execute immediate replace(q'##QUOTE_DELIMITER3##

declare

v_rowcount number;

begin

--Create remote temporary table with results.

sys.m5_runner.run_as_sys@##HOST_LINK_NAME##

(

method5.m5_pkg.get_encrypted_raw(

'##DATABASE_NAME##',

q'##QUOTE_DELIMITER2##

begin

sys.m5_run_shell_script(

q'##QUOTE_DELIMITER1####CODE####QUOTE_DELIMITER1##'

,'M5_TEMP_TABLE_##SEQUENCE##');

commit;

end;

##QUOTE_DELIMITER2##'

)

);

--Insert data using database link.

--Use dynamic SQL - PL/SQL must compile in order to catch exceptions.

execute immediate q'##QUOTE_DELIMITER1##

insert into ##TABLE_OWNER##.##TABLE_NAME##

select '##HOST_NAME##', m5_temp_table_##SEQUENCE##.*

from m5_temp_table_##SEQUENCE##@##HOST_LINK_NAME##

##QUOTE_DELIMITER1##';

v_rowcount := sql%rowcount;

--Update _META table.

update ##TABLE_OWNER##.##TABLE_NAME##_meta

set targets_completed = targets_completed + 1,

date_updated = sysdate,

is_complete = decode(targets_expected, targets_completed+targets_with_errors+1, 'Yes', 'No'),

num_rows = num_rows + v_rowcount

where date_started = (select max(date_started) from ##TABLE_OWNER##.##TABLE_NAME##_meta);

--Drop remote temporary table.

sys.dbms_utility.exec_ddl_statement@##HOST_LINK_NAME##(q'##QUOTE_DELIMITER1##

drop table m5_temp_table_##SEQUENCE## purge

##QUOTE_DELIMITER1##');

end;

##QUOTE_DELIMITER3##', '##DATABASE_NAME##', v_database_name);

--Exception block must be outside of dynamic PL/SQL.

--Exceptions like "ORA-00257: archiver error. Connect internal only, until freed."

--will make the whole block fail and must be caught by higher level block.

exception when others then

update ##TABLE_OWNER##.##TABLE_NAME##_meta

set targets_with_errors = targets_with_errors + 1,

date_updated = sysdate,

is_complete = decode(targets_expected, targets_completed+targets_with_errors+1, 'Yes', 'No')

where date_started = (select max(date_started) from ##TABLE_OWNER##.##TABLE_NAME##_meta);

insert into ##TABLE_OWNER##.##TABLE_NAME##_err

values ('##HOST_NAME##', '##HOST_LINK_NAME##'

, sysdate, sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

commit;

raise;

end;

>';

c_plsql_template constant varchar2(32767) := q'<

create procedure m5_temp_proc_##SEQUENCE## authid current_user is

begin

execute immediate q'##QUOTE_DELIMITER4##

declare

v_dummy varchar2(1);

v_return_value varchar2(32767);

--Exception handling is the same, except it will print a different message if

--the error was in compiling or running.

procedure handle_exception(p_compile_or_run varchar2) is

begin

update ##TABLE_OWNER##.##TABLE_NAME##_meta

set targets_with_errors = targets_with_errors + 1,

date_updated = sysdate,

is_complete = decode(targets_expected, targets_completed+targets_with_errors+1, 'Yes', 'No')

where date_started = (select max(date_started) from ##TABLE_OWNER##.##TABLE_NAME##_meta);

insert into ##TABLE_OWNER##.##TABLE_NAME##_err

values ('##DATABASE_NAME##', '##DB_LINK_NAME##'

, sysdate, p_compile_or_run||' error: '||sys.dbms_utility.format_error_stack||sys.dbms_utility.format_error_backtrace);

commit;

--Drop the temporary function.

declare

v_does_not_exist exception;

pragma exception_init(v_does_not_exist, -4043);

begin

execute immediate '

begin

sys.dbms_utility.exec_ddl_statement@##DB_LINK_NAME##(''drop function m5_temp_function_##SEQUENCE##'');

sys.dbms_utility.exec_ddl_statement@##DB_LINK_NAME##(''drop table m5_temp_table_##SEQUENCE## purge'');

end;

';

exception when v_does_not_exist then null;