Pages

Tuesday, July 26, 2022

Code Example - Run PL/SQL from bash.

===========
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