PL/SQL stored proc from ksh just inserts thumb and does nothing

Greeting everyone. Ok, I have spent the past few days googling for this and I keep hitting a wall. Many results brought me here, but the solutions were not quite right for this.

Basically my script (ksh) is run with an arg for a csv. My script so far appears to be storing the values from my input file as variables.

These are then used in my stored proc with the intent of loading these into my aq. Problem is that it runs, but I am getting no errors or any feedback to indicate that its failing or where its failing. I can tell that its not working as there is no activity or records in the queue.

Previous iterations of this have been used using a wrapper script calling a .sql file. In those instances the values I needed were hard coded into the sql. Since I'm in QC and not dev I needed a method to test many various records, so this is what I came up with.

#!/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

Thanks to all in advance!

Hi,
put set serveroutput on in the first line of your here-document. This will enable your exception handler to show you the error message.
My first guess is that the assignment of the pl/sql-variables is the problem. After the substitution of your shell variables the are single quotes missing.
The exit; command is out of place and not needed when you feed a here document to sqlplus.
Finally you never execute the pl/sql-block you defined. A slash after the pl/sql-block executes it.

#!/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

Edit: depending on your data your code may fail. You'll have to handle characters like single quotes somehow, otherwise the substitution of the shell variables will produce illegal statements.
Edit2: I'd edit your original post - showing usernames and passwords in example code on the internet is a bad idea...

I would take the PL/SQL block out, and put it into a separate .sql file. I have found that the formatting commands work better when you run .sql files rather than having sql embedded as you do. Then make sure that you have the following and get rid of the exception section. It isn't helping anything.

SET ECHO ON
SET SERVEROUTPUT ON
SET FEEDBACK ON

Have you tested the rss_aq.rss_enqueue method from a sqlplus prompt and does it work. I created a proof of concept using advanced queueing in Oracle to rebuild indexed in parallel and I found cases where messages were lost when there was more than one process pulling the messages. It could be that the issue is with rss_aq.rss_enqueue, not with your ksh script.

First of all, thank you to all those that took time out to help on this.

I had fixed the quote problem as mentioned before, but still had problems. It ended up being that i left out one of the quotes. However there were still other problems. From there I went back to have a ksh wrapper calling the SP in a .sql. Ended up having problem with the variables from the ksh passing to the .sql.

I ended up caving and asked one of the cohorts at the office and this is the solution we crafted and was successful in test.

Now this works, but I'm curious as to what disadvantages there might be if any.

#!/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}

Your script may fail depending on the data in file.txt. If the content of the file is well known and can not contain single quotes this should not be an issue, otherwise read up on SQL-injection to get an idea what may happen. If the data contains spaces you will see wrong results because the positional parameters of myfunction are mixed up - better put the parameters in double quotes when you call the function.
Another disadvantage is, that you connect to the database every time you read a line from file.txt. Establishing a connection costs time and other resources, but the impact again depends on the file you read.