Makker
Member²
I find that the Command window is remarkebly compatible with SQLPlus. But I have created a template script to do logging/spooling and let the operator validate the database the script is run on. It works fine with SQLPlus (so I use SQLPlus as external tools to run it) but it would be nice to get it worked in Command Window (because that's my development window for the scripts based on the template. Example of the script:
In Command window the variables are not propagated correctly. It prompts for the variables where they should either be filled by the defines or by the selects and COL-settings.
Thanks in advance.
Regards,
Martien
Code:
define date_format='DD-MM-YYYY HH24:MI:SS'
define script_name='setup_strmadmin_at_src'
set feedback off;
set verify off;
set lines 120;
col "Database" format a30 new_value database
col "schema_name" format a30 new_value schema_name
col "Schema" format a30
col "Start Date" Format a30 new_value start_date
col "End Date" Format a30 new_value End_date
select lower(decode( instr(gn.global_name, '.WORLD')
, 0, gn.global_name
, substr(gn.global_name, 1, instr(gn.global_name, '.WORLD') -1)
)
) database
, lower(user) schema_name
from global_name gn
/
define spool_file=&script_name._for_&schema_name._at_&database..log
Prompt
Prompt Press Enter To Start &script_name.
prompt ____________________
pause;
spool &spool_file.
prompt
prompt Start Script
prompt ____________
select global_name "Database"
, user "Schema"
, to_char(sysdate, '&date_format.' ) "Start Date"
from global_name
/
Prompt
Prompt Actual Script
Prompt _____________
set feedback on;
define strmuser='strmuser'
define strmadmin='strmadmin'
Prompt Setup Streams Administrator
prompt create view stm_v_parameter
@@stm_v_parameter.sql
Prompt Streams Init.ora parameter values
col name format a30
col value format a20
col recommended_value format a20
select * from stm_v_parameter;
Prompt Create package xxx_streams_adm
@@xxx_streams_adm.pck
Prompt Create Database link to Destination
@@create_database_link_to_dest.sql
Prompt Grants And Synonyms
@@grants_and_synonyms.sql
Prompt Remove existing Streams Configuration
exec xxx_streams_adm.remove_configuration;
Prompt Setup Local Queues
begin
xxx_streams_adm.set_up_queue;
end;
/
Prompt Add supplemental logging to table 'STRMUSER.STM_TEST'
exec xxx_streams_adm.add_sup_logging(p_table_name => 'STRMUSER.STM_TEST');
Prompt Setup Schema Rule and Capture Process for '&strmuser.'
begin
dbms_streams_adm.add_schema_rules(schema_name => '&strmuser.'
, streams_type => xxx_streams_adm.c_streams_type_capture
, streams_name => xxx_streams_adm.c_streams_name_capture
, queue_name => xxx_streams_adm.g_dft_streams_queue
, include_dml => true
, include_ddl => true
, inclusion_rule => true);
end;
/
Prompt Add Schema Propagation Rule for '&strmuser.'
begin
xxx_streams_adm.add_schema_propagation('&strmuser.');
end;
/
set feedback off;
prompt
prompt End Script
prompt __________
select global_name "Database"
, user "Schema"
, to_char(sysdate, '&date_format.') "End Date"
from global_name
/
prompt
prompt Statistics
prompt ____________
select start_date "Start Script"
, end_date "End Script"
, lpad(trunc(duration*24), 2, '0')||':'
|| lpad(trunc((duration*24 - trunc(duration * 24))*60), 2, '0')||':'
|| lpad(round((duration*24*60 - trunc(duration * 24 * 60))*60), 2, '0') "Duration Script"
from
( select '&start_date.' start_date
, '&end_date.' end_date
, to_date('&end_date.', '&date_format.') - to_date('&start_date.', '&date_format.') duration
from dual
);
spool off;
edit &spool_file.
exit;Thanks in advance.
Regards,
Martien
