Shell Programming and Scripting

#!/bin/ksh

IFS=','

while read imsi msisdn segment country
do

sqlplus -s user/pass@sid << EOF > foo.log

declare

        v_qname VARCHAR2(20) := 'RS_AQ_AOTA';
        v_source VARCHAR2(10) := 'D1';
        v_sysid VARCHAR2(4) := 'RSS1';
        v_imsi VARCHAR2(20) := $imsi;
        v_msisdn VARCHAR2(10) := $msisdn;
        v_regid NUMBER := 100000000000000010;
        v_sedid NUMBER := 100000000228103870;
        v_tedid NUMBER := 100000000000000157;
        v_segment VARCHAR2(3) := $segment;
        v_dest VARCHAR2(3) := 'CTL';
        v_dlstatus VARCHAR2(1) := 'N';
        v_country VARCHAR2(3) := $country;
        v_carrier VARCHAR2(5) := 'USAWW';
        v_zone VARCHAR2(3) := ' ';
        v_evid NUMBER := 900000000000000004;
        v_timestamp date := sysdate;

     BEGIN
        rss_aq.rss_enqueue(v_qname,
                v_source, v_sysid, v_imsi, v_msisdn,
                v_regid, v_SEDid,v_TEDID, v_segment,
                v_dest, v_dlstatus, v_country,v_zone, v_timestamp, 0, 'F', v_carrier, v_evid);
        commit;

    EXCEPTION
      when others then
        dbms_output.put_line('Error code: '||sqlcode);
        dbms_output.put_line('Error msg: '||sqlerrm);
    END;
set serveroutput on

EOF

exit;

done < $1

#!/bin/ksh

IFS=','

while read imsi msisdn segment country
do

sqlplus -s username/password@ora_sid << EOF > foo.log
set serveroutput on
declare

        v_qname VARCHAR2(20) := 'RS_AQ_AOTA';
        v_source VARCHAR2(10) := 'D1';
        v_sysid VARCHAR2(4) := 'RSS1';
        v_imsi VARCHAR2(20) := '$imsi';
        v_msisdn VARCHAR2(10) := '$msisdn';
        v_regid NUMBER := 100000000000000010;
        v_sedid NUMBER := 100000000228103870;
        v_tedid NUMBER := 100000000000000157;
        v_segment VARCHAR2(3) := '$segment';
        v_dest VARCHAR2(3) := 'CTL';
        v_dlstatus VARCHAR2(1) := 'N';
        v_country VARCHAR2(3) := '$country';
        v_carrier VARCHAR2(5) := 'USAWW';
        v_zone VARCHAR2(3) := ' ';
        v_evid NUMBER := 900000000000000004;
        v_timestamp date := sysdate;

     BEGIN
        rss_aq.rss_enqueue(v_qname,
                v_source, v_sysid, v_imsi, v_msisdn,
                v_regid, v_SEDid,v_TEDID, v_segment,
                v_dest, v_dlstatus, v_country,v_zone, v_timestamp, 0, 'F', v_carrier, v_evid);
        commit;

    EXCEPTION
      when others then
        dbms_output.put_line('Error code: '||sqlcode);
        dbms_output.put_line('Error msg: '||sqlerrm);
    END;
/
EOF


done < $1

SET ECHO ON
SET SERVEROUTPUT ON
SET FEEDBACK ON

#!/bin/ksh


myfunction() {
cat << myeof

declare

        v_qname VARCHAR2(20) := 'RS_AQ_AOTA';
        v_source VARCHAR2(10) := 'D1';
        v_sysid VARCHAR2(4) := 'RSS1';
        v_imsi VARCHAR2(20) := '$1';
        v_msisdn VARCHAR2(10) := '$2';
        v_regid NUMBER := 100000000000000010;
        v_sedid NUMBER := 100000000228103870;
        v_tedid NUMBER := 100000000000000157;
        v_segment VARCHAR2(3) := 'STD';
        v_dest VARCHAR2(3) := 'CTL';
        v_dlstatus VARCHAR2(1) := 'N';
        v_country VARCHAR2(3) := 'USA';
        v_carrier VARCHAR2(5) := 'USAWW';
        v_zone VARCHAR2(3) := ' ';
        v_evid NUMBER := 900000000000000004;
        v_timestamp date := sysdate;

BEGIN

        rss_aq.rss_enqueue(v_qname,
                v_source, v_sysid, v_imsi, v_msisdn,
                v_regid, v_SEDid,v_TEDID, v_segment,
                v_dest, v_dlstatus, v_country,v_zone, v_timestamp, 0, 'F', v_carrier, v_evid);

END;
/


commit;
quit;
myeof
}

###################################
myfunction2() {
cat << myeof2
prompt Checking for invalid objects
SELECT count(*) FROM user_objects
where status != 'VALID';
quit;
myeof2
}

###################################


ORACLE_SID=sid
export ORACLE_SID

IFS=','
while read imsi msisdn
   do
        echo "imsi=$imsi msisdn=$msisdn"
        myfunction $imsi $msisdn | sqlplus -S user/pass@${ORACLE_SID}
   done < file.txt

myfunction2 | sqlplus -S user/pass@${ORACLE_SID}