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

Sponsored Content

Top Forums Shell Programming and Scripting PL/SQL stored proc from ksh just inserts thumb and does nothing Post 302972810 by cero on Wednesday 11th of May 2016 02:52:01 AM

05-11-2016

Registered User

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 04:26 AM..

cero

View Public Profile for cero

Find all posts by cero

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)