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

Sponsored Content

Top Forums Shell Programming and Scripting PL/SQL stored proc from ksh just inserts thumb and does nothing Post 302972774 by dezdiggler on Tuesday 10th of May 2016 06:01:34 PM

05-10-2016

Registered User

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 01:28 PM..

dezdiggler

View Public Profile for dezdiggler

Find all posts by dezdiggler

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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.

4. 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.

5. 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

6. 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...

7. 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'...

8. 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...

9. 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...

10. 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...

LEARN ABOUT X11R4

times

times(1)							   User Commands							  times(1)

NAME

       times - shell built-in function to report time usages of the current shell

SYNOPSIS

   sh
       times

   ksh
       times

DESCRIPTION

   sh
       Print the accumulated user and system times for processes run from the shell.

   ksh
       Print the accumulated user and system times for the shell and for processes run from the shell.

       On this man page, ksh(1) commands that are preceded by one or two * (asterisks) are treated specially in the following ways:

       1.  Variable assignment lists preceding the command remain in effect when the command completes.

       2.  I/O redirections are processed after variable assignments.

       3.  Errors cause a script that contains them to abort.

       4.  Words,  following a command preceded by ** that are in the format of a variable assignment, are expanded with the same rules as a vari-
	   able assignment. This means that tilde substitution is performed after the = sign and word splitting and file name generation  are  not
	   performed.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE	     |	    ATTRIBUTE VALUE	   |
       +-----------------------------+-----------------------------+
       |Availability		     |SUNWcsu			   |
       +-----------------------------+-----------------------------+

SEE ALSO

       ksh(1), sh(1), time(1), attributes(5)

SunOS 5.10							    15 Apr 1994 							  times(1)