Need Help: Shell script to call sql session with variables stored in .txt file Post: 302922899

Sponsored Content

Top Forums Shell Programming and Scripting Need Help: Shell script to call sql session with variables stored in .txt file Post 302922899 by RavinderSingh13 on Wednesday 29th of October 2014 03:08:14 AM

10-29-2014

Moderator

Quote:

Originally Posted by Khan28

Hi,
I need help in writing a shell script which can read data from a text file (Cancel_ID.txt) and then calls sqlplus session (Cancel.sql) with the first line parameter of the text file ("0322600453") till all rows are not completed.

The data in text file will be like following:

0322600453
CAN
5076

2867000260
CAN
3496

Code:

sqlplus -S <<EOF > /dev/null
user/password@universe
WHENEVER OSERROR EXIT 9;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
 
 
 
SET ECHO ON
SET SERVEROUTPUT ON
SPOOL $LOGFILE
 
-------------------------------------------------------------------------------------
---- Here I need to make a loop to read text file and pass on the first line as 
---- parameter to following .sql script till all the rows are not finished in text 
---- file
-------------------------------------------------------------------------------------
@cancel.sql 
 
SPOOL OFF
EOF
 
 
 
sql_return_code=$?
 
if [ $sql_return_code != 0 ]
then
echo "The Cancel.sql script failed. Please refer to the log results.txt for more information"
echo "Error code $sql_return_code"
exit 0;
fi

Hello Khan28,

Welcome to forum, you can use following command for same which will take input from a file(with sql queries) and put it's output to a output file.

Code:

sqlplus user/password@universe < QUERIES.sql > Output_file

As I am not that much clear on your requirement so just an idea. Then after saving the sql output to a file you can check it's data if any error or according to your need you can perform the operations.

Thanks,
R. Singh

RavinderSingh13

View Public Profile for RavinderSingh13

Find all posts by RavinderSingh13

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

how can a call shell script from pl/sql

I like to call a shell script from pl/sql proceduere and i have to use the shell script return value in that procedure. i am using oracle 9i and cygwin. can any one suggest me how can i do this

2. Shell Programming and Scripting

how can i call a shell script from pl/sql

I would like to call the shell script from pl/sql and i need to uses the value returned by the shell script in pl/sql procedure. can any one suggest me how can i do that?

3. Shell Programming and Scripting

how to call shell script from pl/sql loop

Hello, I am doing a shell script which contain a pl/sql loop to search for 3 values, i would like to call another shell script inside this sql loop each time it find the values. so how can i call shell script from pl/sql using its variables, any idea? Here is idea about the code: my...

4. Shell Programming and Scripting

call shell script from pl/sql block

Hi Experts, I want to call script_name.ksh as many time as id in customer table and also pass it as a parameter to script. someting Like below. for i in select id from customer do ./script_name.ksh $i & done I have figured out how to have ID from customer but now how to call...

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

6. UNIX for Advanced & Expert Users

call sql through shell script

Hi i am not able to connect sqlplus my script is as follows $ORACLE_HOME/bin/sqlplus << ! > /tmp/extract/DM.txt and output is SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jan 18 02:53:54 2012 Copyright (c) 1982, 2008, Oracle. All rights reserved. Enter user-name: t175481...

7. Shell Programming and Scripting

Need to write shell script for my .sql file call

Hi Guys, I need to write a simple shell script which will generate a .csv file/report by calling .sql file inside a shell script. Can somebody help me on this. Thanks in advance! Regards, LK

8. Shell Programming and Scripting

How to call SQL Loader in shell script?

HI Experts, I am pretty new to scripting and i need to create a perl or shell script which should fetch a file from local directory and insert the data into a table using sql loader. This will be later added to chron job to run daily and fetch all files and load them into the table. Also i...

9. Programming

Writing a UNIX shell script to call a C function and redirecting data to a .txt file

Hi, I am complete new to C programming and shell scripting. I just wrote a simple C code to calculate integral using trapezoid rule. I am prompting user to pass me No. of equally spaced points , N , upper and lower limit. My code looks as follows so far: #include<stdio.h> #include<string.h>...

10. Shell Programming and Scripting

Shell script to call sql file

hi , the below script contains sql query and after executed it sends the output of the query (output.txt) to an email body with conditional subject line based on the output of all_counts_match.txt. i want to make this script generic so that it can accept the sql file as parameter and can...

LEARN ABOUT DEBIAN

sb2-session

sb2-session(1)						       sb2-session man page						    sb2-session(1)

NAME

       sb2-session - manage scratchbox2 sessions

SYNOPSIS

       sb2-session [COMMAND [PARAMS]]

DESCRIPTION

       sb2-session  is	used  to  create attachable scratchbox2 sessions.  You can save session even when you haven't done '-S session' during sb2
       startup.

       Note that you can give parameters to attach command which are passed verbatim to sb2. If there  is  only  one  session  to  attach,  it	is
       selected automatically.

       If no command is given, help text is printed.

COMMANDS

       help   shows help text

       save   saves current session

       list   lists detached sb2 sessions

       delete SESSION
	      deletes given session

       attach [SESSION [PARAMS]]
	      attaches to a given session

       setenv VARIABLE VALUE
	      store a value for an environment variable

       showenv
	      show stored environment variables

EXAMPLES

       Basic usage is:

	   [SB2 .. ] $ sb2-session save  # save session
	   [SB2 .. ] $ ^D		 # exit

       Later we can then attach to this session:

	   % sb2-session attach

       If there are more than one saved session, then:

	   % sb2-session list
	   session.XXXX    TARGET_ARM	   My Comment
	   session.YYYY    TARGET_ARM	   My Comment 2
	   session.ZZZZ    TARGET_MIPS
	   % sb2-session attach session.YYYY

       Here we pass '-eR' to sb2 and we only have one saved session:

	    % sb2-session attach -- -eR

       '--' marks end of parameter list for sb2-session.

       setenv  VARIABLE  VALUE	can  be  used  to  add environment variables, that will become active when a saved session is later taken into use
       (either by sb2-session attach or by sb2 -J).

       setenv VARIABLE VALUE does not change environment of sessions that are already active.

       showenv lists environment variable settings which have been stored with this tool. It  does  not  show  environment  variables  from  other
       sources.

SEE ALSO

       sb2(1)

AUTHOR

       Mika Westerberg

2.2								 21 December 2010						    sb2-session(1)