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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting PL/SQL stored proc from ksh just inserts thumb and does nothing
# 1  
Old 05-10-2016
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.

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

Last edited by dezdiggler; 05-11-2016 at 02:28 PM..
# 2  
Old 05-11-2016
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.
Code:
#!/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...

Last edited by cero; 05-11-2016 at 05:26 AM..
# 3  
Old 05-11-2016
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.

Code:
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.
# 4  
Old 05-11-2016
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.

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

# 5  
Old 05-12-2016
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.

Last edited by cero; 05-12-2016 at 07:27 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sybase Stored Proc call from UNIX script.

Hi, I am new to shell scripting and Sybase database i need a help that i try to execute a SYBASE stored procedure from a Unix shell script and wanna write the output of the SP into a Text File.somehow i try to find a solution but whwn i try to run the script i am not getting the output file with... (1 Reply)
Discussion started by: Arun619
1 Replies

2. Post Here to Contact Site Administrators and Moderators

Calling Sybase Stored proc from UNIX Shellscript.

Hi, I am new to shell scripting and Sybase database i need a help that i try to execute a SYBASE stored procedure from a Unix shell script and wanna write the output of the SP into a Text File, somehow i tried to find a solution but when i try to run the script i am not getting the output file with... (1 Reply)
Discussion started by: Arun619
1 Replies

3. UNIX and Linux Applications

How to capture the value returned by a stored proc while executing it from SQSH connection

I have a very simple set up I am connecting to a MS SQL db using SQSH statement from a shell script In this sqsh connection i am trying to execute a stored proc However I want to capture the value returned by the stored proc. I haven't really come across anything useful so far which would... (0 Replies)
Discussion started by: shishirkotkar
0 Replies

4. Shell Programming and Scripting

Error when calling sybase stored proc from shell script

Hi, I am writing a script that needs to call a stored proc which would update a column in a table based on a condition. I need to also capture the number of rows updated. However, When I execute the script I keep getting this error: ./test_isql.sh: syntax error at line 33: `end of file'... (3 Replies)
Discussion started by: karthikk
3 Replies

5. UNIX for Advanced & Expert Users

Sql dynamic table / dynamic inserts

I have a file that reads File (X.txt) Contents of record 1: rdrDESTINATION_ADDRESS (String) "91 971502573813" rdrDESTINATION_IMSI (String) "000000000000000" rdrORIGINATING_ADDRESS (String) "d0 movies" rdrORIGINATING_IMSI (String) "000000000000000" rdrTRAFFIC_EVENT_TIME... (0 Replies)
Discussion started by: magedfawzy
0 Replies

6. Shell Programming and Scripting

Executing Shell Script from Within a Sybase Stored Proc

Greetings, I need to make an open server call to a shell script from inside a Sybase Stored procedure. Coul any one please provide a sample code? TIA (0 Replies)
Discussion started by: rajpreetsidhu
0 Replies

7. UNIX for Advanced & Expert Users

Executing Stored Proc from unix prompt.

Hi All, I want to run/execute a stored procedure (sybase) from unix command prompt not by login in isql utility which is provided my Sybase guys. Is there way ..? Thanks in advance for your help !!! Regards, Arvind S. (0 Replies)
Discussion started by: arvindcgi
0 Replies

8. UNIX for Dummies Questions & Answers

Executing Stored Proc from unic prompt.

Hi All, Want to know if is it possible to run / execute any stored procedures (sybase) from unix command prompt.? Thanks for your help in Advance. Regards, Arvind S. (0 Replies)
Discussion started by: Arvind_temp
0 Replies

9. Shell Programming and Scripting

calling a PL/SQL stored procedure from KSH

Hi I have a stored procedure which should be called from KSH. Could ayone please help me with this. Thanks (1 Reply)
Discussion started by: BlAhEr
1 Replies

10. Programming

Need help ! SQL and Proc *C

:) hi all ! Please help me When I select data from oracle with proc * C prog. I count the number of rows For example the total rows is 1000000 but the number of result return is a limit number 5000 for ex So How can I know this limit (5 Replies)
Discussion started by: iwbasts
5 Replies
Login or Register to Ask a Question

Featured Tech Videos