===========
General:
===========
Example for Calling stored procedure from bash.
In this case, the stored procedure is using input parameters to run EXECUTE IMMEDIATE statement using bind variables.
===========
Code
===========
cre_gwch.sh
cre_ga_w_counters_history.sql
cre_gwch.sh
#!/bin/bash
USER_NAME=MY_USER
USER_PASS=MY_PASS
ORA_INST=orainst
WORK_DIR=`pwd`
HOSTNAME=`hostname`
FILE_NAME=`basename $0`
v_caller="'${WORK_DIR}/${FILE_NAME}@${HOSTNAME}'"
v_static_id='1'
v_counter_sum=2
v_counter_delta=3
v_dyn1='0'
v_dyn2='1'
v_dyn3='0'
v_dyn4='0'
v_dyn5='0'
v_is_processed=0
v_is_counter_restarted=0
v_ts_last_modified="'20220711 12:22:00'"
v_day=06
v_node_id=2131251
v_src_network_id='NULL'
v_ts_last_updated='NULL'
v_dyn_s1='NULL'
v_dyn_s2='NULL'
v_dyn_s3='NULL'
v_dyn_s4='NULL'
v_dyn_s5='NULL'
v_affiliate_id='NULL'
v_site_id=1
# echo "Running SQL exec cre_ga_w_counters_history($v_caller, $v_static_id, $v_counter_sum, $v_counter_delta, $v_dyn1, $v_dyn2, $v_dyn3, $v_dyn4, $v_dyn5, $v_is_processed, $v_is_counter_restarted, $v_ts_last_modified, $v_day, $v_node_id, $v_src_network_id, $v_ts_last_updated, $v_dyn_s1, $v_dyn_s2, $v_dyn_s3, $v_dyn_s4, $v_dyn_s5, $v_affiliate_id, $v_site_id);"
sqlplus -s ${USER_NAME}/${USER_PASS}@${ORA_INST} <<EOF
set verify off define off feedback off
BEGIN
cre_ga_w_counters_history($v_caller, $v_static_id, $v_counter_sum, $v_counter_delta, $v_dyn1, $v_dyn2, $v_dyn3, $v_dyn4, $v_dyn5, $v_is_processed, $v_is_counter_restarted, $v_ts_last_modified, $v_day, $v_node_id, $v_src_network_id, $v_ts_last_updated, $v_dyn_s1, $v_dyn_s2, $v_dyn_s3, $v_dyn_s4, $v_dyn_s5, $v_affiliate_id, $v_site_id);
END;
/
commit;
exit;
EOF
cre_ga_w_counters_history.sql
CREATE OR REPLACE PROCEDURE CRE_GA_W_COUNTERS_HISTORY (
p_caller IN VARCHAR2,
p_static_id IN GA_W_COUNTERS_HISTORY.static_id%TYPE,
p_counter_sum IN GA_W_COUNTERS_HISTORY.counter_sum%TYPE,
p_counter_delta IN GA_W_COUNTERS_HISTORY.counter_delta%TYPE,
p_dyn1 IN GA_W_COUNTERS_HISTORY.dyn1%TYPE,
p_dyn2 IN GA_W_COUNTERS_HISTORY.dyn2%TYPE,
p_dyn3 IN GA_W_COUNTERS_HISTORY.dyn3%TYPE,
p_dyn4 IN GA_W_COUNTERS_HISTORY.dyn4%TYPE,
p_dyn5 IN GA_W_COUNTERS_HISTORY.dyn5%TYPE,
p_is_processed IN GA_W_COUNTERS_HISTORY.is_processed%TYPE,
p_is_counter_restarted IN GA_W_COUNTERS_HISTORY.is_counter_restarted%TYPE,
p_ts_last_modified IN VARCHAR2,
p_day IN GA_W_COUNTERS_HISTORY.day%TYPE,
p_node_id IN GA_W_COUNTERS_HISTORY.node_id%TYPE,
p_src_network_id IN GA_W_COUNTERS_HISTORY.src_network_id%TYPE,
p_ts_last_updated IN GA_W_COUNTERS_HISTORY.ts_last_updated%TYPE,
p_dyn_s1 IN GA_W_COUNTERS_HISTORY.dyn_s1%TYPE,
p_dyn_s2 IN GA_W_COUNTERS_HISTORY.dyn_s2%TYPE,
p_dyn_s3 IN GA_W_COUNTERS_HISTORY.dyn_s3%TYPE,
p_dyn_s4 IN GA_W_COUNTERS_HISTORY.dyn_s4%TYPE,
p_dyn_s5 IN GA_W_COUNTERS_HISTORY.dyn_s5%TYPE,
p_affiliate_id IN GA_W_COUNTERS_HISTORY.affiliate_id%TYPE,
p_site_id IN GA_W_COUNTERS_HISTORY.site_id%TYPE) IS
v_sql_str VARCHAR2(2000);
v_module_name SGA_W_LOG.procedure_name%TYPE;
v_msg_str SGA_W_LOG.data%TYPE;
BEGIN
v_module_name := 'CRE_GA_W_COUNTERS_HISTORY';
v_msg_str := 'Starting from caller: '||p_caller;
SUPPORT_UTIL_PKG.write_sga_w_log(v_module_name, v_msg_str);
v_sql_str:='INSERT INTO GA_W_COUNTERS_HISTORY (static_id, counter_sum, counter_delta, dyn1, dyn2, dyn3, dyn4, dyn5, is_processed, is_counter_restarted, ts_last_modified, day, node_id, src_network_id, ts_last_updated, dyn_s1, dyn_s2, dyn_s3, dyn_s4, dyn_s5, affiliate_id, site_id) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22) ';
EXECUTE IMMEDIATE v_sql_str USING p_static_id, p_counter_sum, p_counter_delta, p_dyn1, p_dyn2, p_dyn3, p_dyn4, p_dyn5, p_is_processed, p_is_counter_restarted, TO_DATE(p_ts_last_modified,'YYYYMMDD hh24:mi:ss'), p_day, p_node_id, p_src_network_id, p_ts_last_updated, p_dyn_s1, p_dyn_s2, p_dyn_s3, p_dyn_s4, p_dyn_s5, p_affiliate_id, p_site_id;
EXCEPTION
WHEN OTHERS THEN
v_msg_str := 'Unexpected Error from caller: '||p_caller;
SUPPORT_UTIL_PKG.write_sga_w_log(v_module_name, v_msg_str);
v_msg_str := 'Error : '||SQLERRM;
SUPPORT_UTIL_PKG.write_sga_w_log(v_module_name, v_msg_str);
RAISE;
END CRE_GA_W_COUNTERS_HISTORY;
/
No comments:
Post a Comment