Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Run stored procedure from shell script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Run stored procedure from shell script
# 1  
Old 01-03-2012
Run stored procedure from shell script
Hello all,

I am trying to run stored procrdure from shell script which takes one argument. And also I want to verify in the script whether the script executed successfully. However the Stored procedure is not running from shell script. Manually if I run it update the data in the table.
Can any one help me to resolve the issue. Below is my script -

Code:
        AREA_CODE=11
 
        RETURN_OUTPUT1=`isql -Usa -Ppassword -DPP_DB << EOF
        set heading off feedback off verify off
        set serveroutput on
        exec SL_migratePortedNumbers('$AREA_CODE')
        go
        EOF`
        #echo output $RETURN_OUTPUT1
        if [ "$RETURN_OUTPUT1" != "0" ]
        then
            echo "error in running SL_migratePortedNumberMapping"
            exit
        fi

Also the RETURN_OUTPUT1 variable is always blank.

Thanks in advance.
Last edited by PriyaSri; 01-04-2012 at 01:54 AM.. Reason: please user code tags
# 2  
Old 01-03-2012
After putting the code into "code" tags it looks indented to me.
The line "EOF" should be on its own and with no leading spaces:

Code:
EOF
`

# 3  
Old 01-04-2012
Thank for quick response methyl however I tried putting EOF as suggested by you but of no use. The Stored Procedure is still not running and the output variable is blank Smilie
# 4  
Old 01-04-2012
Run that script ksh -x scriptname and paste that content here ..
# 5  
Old 01-04-2012
Kindly find the output after running the script as suggested

Code:
[ctp-1-zone564:tecnomen] /export/home/tecnomen/> ksh -x migrationBNumFrom10To11Digits.sh -m
+ migrate=false
+ remove=false
+ echo Script running
Script running
+ export SCRIPT_PATH=/in_service/db/pp/procs
+ echo /in_service/db/pp/procs
/in_service/db/pp/procs
+ AREA_CODE=11
+ echo default area_code 11
default area_code 11
+ SAO_PALO_AREA_CODE=11
+ echo sao_palo_area_code 11
sao_palo_area_code 11
+ getopts hmra: opt
+ echo opt = m
opt = m
+ migrate=true
+ getopts hmra: opt
+ echo AREA_CODE=11
AREA_CODE=11
+ [ 11 = 11 ]
+ [ true = true ]
+ echo Migrating ported numbers
Migrating ported numbers
+ + isql -Usa -Ppassword -DPP_DB
+ 0<<
RETURN_OUTPUT1=
+ echo output
output
+ [  != 0 ]
+ echo error in running SL_migratePortedNumberMapping
error in running SL_migratePortedNumberMapping
+ exit
[ctp-1-zone564:tecnomen] /export/home/tecnomen/>

Moderator's Comments:
Mod Comment How to use code tags
Last edited by Franklin52; 01-04-2012 at 03:35 AM.. Reason: Please use code tags for code and data samples, thank you
# 6  
Old 01-04-2012
Just check whether the AREA_CODE is passing inside your sql block..
Code:
set serveroutput on
dbms_output.put_line('AREA_CODE',$AREA_CODE);
exec SL_migratePortedNumbers('$AREA_CODE')
go

# 7  
Old 01-04-2012
I added the line to check the Area Code value and it is getting passed successfully.
Code:
[ctp-1-zone564:tecnomen] /export/home/tecnomen/> ksh -x migrationBNumFrom10To11Digits.sh -m
+ migrate=false
+ remove=false
+ echo Script running
Script running
+ export SCRIPT_PATH=/in_service/db/pp/procs
+ echo /in_service/db/pp/procs
/in_service/db/pp/procs
+ AREA_CODE=11
+ echo default area_code 11
default area_code 11
+ SAO_PALO_AREA_CODE=11
+ echo sao_palo_area_code 11
sao_palo_area_code 11
+ getopts hmra: opt
+ echo opt = m
opt = m
+ migrate=true
+ getopts hmra: opt
+ echo AREA_CODE=11
AREA_CODE=11
+ [ 11 = 11 ]
+ [ true = true ]
+ echo Migrating ported numbers
Migrating ported numbers
+ + isql -Usa -Ppassword -DNP_DB
+ 0<<
                set heading off feedback off verify off
                set serveroutput on
                #exec SL_getBlackList
                dbms_output.put_line('AREA_CODE -',11);
                exec SL_migratePortedNumbers('11')
                go
                EOFRETURN_OUTPUT1=
+ echo output
output
+ [  != 0 ]
+ echo error in running SL_migratePortedNumberMapping
error in running SL_migratePortedNumberMapping
+ exit
[ctp-1-zone564:tecnomen] /export/home/tecnomen/>

Last edited by Franklin52; 01-04-2012 at 03:36 AM.. Reason: Please use code tags for code and data samples, thank you
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to get OUT parameter of a stored procedure in shell script?

I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible? (6 Replies)
Discussion started by: vel4ever
6 Replies

2. Shell Programming and Scripting

How to call stored procedure with CLOB out parameter from shell script?

I have written a stored procedure in oracle database, which is having a CLOB OUT parameter. How can i call this stored procedure from shell script and get the CLOB object in shell script variable? (0 Replies)
Discussion started by: vel4ever
0 Replies

3. Shell Programming and Scripting

How to execute the stored procedure from shell script

How to execute the stored procedure from shell script and is there any possibility to print the dbms output in a log file. (2 Replies)
Discussion started by: dineshmurs
2 Replies

4. Shell Programming and Scripting

Passing a value to stored procedure from unix shell script

Hi Dudes :) I want a unix shell script to pass value to SQL stored procedure. Below is the procedure declare res varchar2(10); begin odm_load_check('PRE_SANITY',res); dbms_output.put_line(res); end; select * from error_log; truncate table error_log; select * from test; (1 Reply)
Discussion started by: shirdi
1 Replies

5. Shell Programming and Scripting

how to store the return values of stored procedure in unix shell script.

hi i am calling a oracle stored procedure(in the database) from unix shell scripting (a.sh). the called stored procedure returns some values through OUT variables i want to assign the return values of stored procedure in to unix shell script variable. can you provide me the code. ... (1 Reply)
Discussion started by: barani75
1 Replies

6. Shell Programming and Scripting

Invoking Oracle stored procedure in unix shell script

Here's a shell script snippet..... cd $ORACLE_HOME/bin Retval=`sqlplus -s <<eof $TPDB_USER/april@$TPD_DBCONN whenever SQLERROR exit 2 rollback whenever OSERROR exit 3 rollback set serveroutput on set pages 999 var status_desc char(200) var status_code... (1 Reply)
Discussion started by: hidnana
1 Replies

7. Shell Programming and Scripting

passing parameter 4m shell script to a DB stored procedure

hi all please tell me how to pass parameters 4m shell script to a DataBase stored procedure. To be specific i have sybase DB. i mean i want the syntax of the command.. how to connect to DB, pass user id and password, pass the required parameter to SP.. .. need ur help frnds.. hema (0 Replies)
Discussion started by: hema2026
0 Replies

8. Shell Programming and Scripting

Calling an Oracle Stored Procedure from Unix shell script

hai, can anybody say how to call or to execute an oracle stored procedure in oracle from unix... thanks in advance.... for ur reply.... by, leo (2 Replies)
Discussion started by: Leojhose
2 Replies

9. Shell Programming and Scripting

Calling stored procedure from shell script

HI, I have a similar problem to thread 18264, only I couldn't get it to work. https://www.unix.com/showthread.php?t=18264 I have a stored procedure which is called by a shell script program. When I run the stored procedure alone or through the shell script, it works fine with output text... (3 Replies)
Discussion started by: dorisw
3 Replies

10. Shell Programming and Scripting

calling stored procedure from shell script.

Hi All, This is a very starnge problem I am having. I have a shell script that calls a stored procedure. Here's my code in shell script: sqlplus "userid/pwd" @file.sql and file.sql has the following statement: exec my_storedProc; Now, when I execute my shell script, nothing... (2 Replies)
Discussion started by: priyamurthy2005
2 Replies
Login or Register to Ask a Question