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

Sponsored Content

Top Forums Shell Programming and Scripting PL/SQL stored proc from ksh just inserts thumb and does nothing Post 302972927 by dezdiggler on Wednesday 11th of May 2016 08:33:32 PM

05-11-2016

Registered User

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}

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 OPENSOLARIS

let

let(1)								   User Commands							    let(1)

NAME

       let - shell built-in function to evaluate one or more arithmetic expressions

SYNOPSIS

   ksh
       let arg...

   ksh93
       let [expr...]

DESCRIPTION

   ksh
       Each arg is a separate arithmetic expression to be evaluated.

   ksh93
       let  evaluates  each expr in the current shell environment as an arithmetic expression using ANSI C syntax. Variables names are shell vari-
       ables and they are recursively evaluated as arithmetic expressions to get numerical values. let has been made obsolete by the ((...))  syn-
       tax of ksh93(1) which does not require quoting of the operators to pass them as command arguments.

EXIT STATUS

   ksh
       ksh returns the following exit values:

       0    The value of the last expression is non-zero.

       1    The value of the last expression is zero.

   ksh93
       ksh93 returns the following exit values:

       0     The last expr evaluates to a non-zero value.

       >0    The last expr evaluates to 0 or an error occurred.

ATTRIBUTES

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

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

SEE ALSO

       ksh(1), ksh93(1), set(1), typeset(1), attributes(5)

SunOS 5.11							    2 Nov 2007								    let(1)