Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Unable to pass value from .Shell script to .SQL file
Top Forums Shell Programming and Scripting Unable to pass value from .Shell script to .SQL file Post 302942466 by reddy298599 on Wednesday 29th of April 2015 04:41:14 AM
Old 04-29-2015
Unable to pass value from .Shell script to .SQL file
Hi All,
I am new to shell script. I am trying to pass value from .sh file to .sql file .
But I am able to run the .sql file from .sh file with values in sql file.
But I am unable to pass the values from .sh file. can some one please help to resolve this.

here is my .sh file
s1.sh

Code:
#!/bin/ksh[/FONT][/SIZE][/COLOR]
echo "please enter case:"
read v_case_nbr
echo "the case number entered is $v_case_nbr"
sqlplus wmtst/wmtst@wmtest <<EOF
@ test.sql $ v_case_nbr
exit;
EOF

here is .sql file


Code:
 SET SERVERoutput on;
declare 
v_case_nbr varchar(400):='&Enter_Case_nbr';
c_stat_code case_hdr.stat_code%type;

 cursor c1 is select ch.stat_code into c_stat_code from case_hdr ch  where ch.case_nbr=v_case_nbr;
  
 
begin
open c1;
LOOP
FETCH c1 into c_stat_code;
EXIT when c1%notfound;
If (c_stat_code = '50')
then
update case_hdr ch
set ch.stat_code='30',
ch.user_id = 'PILLAREM',
ch.mod_date_time = sysdate 
where ch.case_nbr = v_case_nbr;
dbms_output.put_line('Case_hdr:Case updated To putaway status ');
end if;
 end loop;
 close c1;
 end;
 /

Here I need to pass v_case_nbr='123456' from .sh file to .sql file

Please help me to resolve this.

Thanks a bunch.

Regards,
reddy298599
Last edited by Don Cragun; 04-29-2015 at 01:31 PM.. Reason: Remove FONT, COLOR, and FONT tags; add CODE and ICODE tags.
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

how to pass values from oracle sql plus to unix shell script

how to pass values from oracle sql plus to unix shell script (2 Replies)
Discussion started by: trichyselva
2 Replies

2. Shell Programming and Scripting

How to pass pl/sql table values to shell script

Hello, i am using '#!/bin/bash', i want to make a loop in pl/sql, this loop takes values from a table according to some conditions, each time the loop choose 3 different variables. What i am not able to do is that during the loop i want my shell script to read this 3 variables and run a shell... (1 Reply)
Discussion started by: rosalinda
1 Replies

3. Shell Programming and Scripting

How to pass arguments to SQL file passed in shell script?

Hi, I am using SYBASE database. in my script i am connecting to DB via using isql. isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b0 -w3000 -h0 -s"|" -i${MYDIR}/ABC.sql -oXYZ.txt << FINSQL i am taking a ABC.sql file to use the queries written in it and storing the output in... (3 Replies)
Discussion started by: dazdseg
3 Replies

4. Shell Programming and Scripting

How to Pass the Output Values from the PL/SQL Procedure to Shell Script?

hi, Could anyone tell me how to pass the output values of the PL/SQL procedure to Shell script and how to store that values in a shell script variable... Thanks in advance... (5 Replies)
Discussion started by: funonnet
5 Replies

5. Shell Programming and Scripting

pass null value to sql script from korn shell script

There are 4 parameters that I have to pass from korn shell to sql script. 1) I have to check if $1 , $2 , $3 and $4 are null values or not . How can I do that ? 2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
Discussion started by: megha2525
11 Replies

6. Shell Programming and Scripting

unable to pass value to user prompt from calling shell script

This is my script structure main script calls configure script which needs to be run as a different user and the configure script calls my application installation script. the application instruction script prompts the user for a directory which I need to pass from my main or configure script. ... (4 Replies)
Discussion started by: cmastays
4 Replies

7. Post Here to Contact Site Administrators and Moderators

Unable to pass shell script parameter value to awk command in side the same script

Variable I have in my shell script diff=$1$2.diff id=$2 new=new_$diff echo "My id is $1" echo "I want to sync for user account $id" ##awk command I am using is as below cat $diff | awk -F'~' ''$2 == "$id"' {print $0}' > $new I could see value of $id is not passing to the awk... (0 Replies)
Discussion started by: Ashunayak
0 Replies

8. Shell Programming and Scripting

Unable to pass shell script variable to awk command in same shell script

I have a shell script (.sh) and I want to pass a parameter value to the awk command but I am getting exception, please assist. diff=$1$2.diff id=$2 new=new_$diff echo "My id is $1" echo "I want to sync for user account $id" ##awk command I am using is as below cat $diff | awk... (2 Replies)
Discussion started by: Ashunayak
2 Replies

9. Shell Programming and Scripting

Unable to pass send command in shell script

I am trying to automate a testcase . I am installing some software and it waits for user input after displaying "Do you want to continue ? " I am trying to do this in shell scripting. #!/bin/bash #!/usr/bin/expect -f /usr/bin/expect << EOF spawn apt-get install openjdk-7-jdk expect "*Do you... (1 Reply)
Discussion started by: Abdul Navaz
1 Replies

10. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

Hi all, $ echo $SHELL /bin/bash Requirement - How to pass oracle sql script as argument to unix shell script? $ ./output.sh users.sql Below are the shell scripts and the oracle sql file in the same folder. Shell Script $ cat output.sh #!/bin/bash .... (7 Replies)
Discussion started by: a1_win
7 Replies

LEARN ABOUT DEBIAN

mdb-sql

MDBTools(1)															       MDBTools(1)

NAME

       mdb-sql - SQL interface to MDB Tools

SYNOPSIS

       mdb-sql [-HFp] [-d delimiter] [-i file] [-o file] [database]

DESCRIPTION

       mdb-sql is a utility program distributed with MDB Tools.

       mdb-sql allows querying of an MDB database using a limited SQL subset language.

OPTIONS

       -H     Supress header row.

       -F     Supress footer row.

       -p     Turn  off  pretty printing. By default results are printed in an ascii table format which looks nice but is not conducive to manipu-
	      lating the output with unix tools. This option prints output plainly in a tab separated format.

       -d     Specify an alternative column delimiter. If no delimiter is specified, columns will be delimited by a tab character if pretty print-
	      ing (-p) is turned off. If pretty printing is enabled this option is meaningless.

       -i     Specify an input file. This option allows an input file containing the SQL to be passed to mdb-sql.  See Notes.

       -o     Specify an output file. This option allows the name of an output file to be used instead of stdout.

COMMANDS

       mdb-sql in interactive mode takes some special commands.

       connect to <database>
	      If  no database was specified on the command line this command is necessary before any querys are issued. It also allows the switch-
	      ing of databases once in the tool.

       disconnect
	      Will disconnect from the current database.

       go     Each batch is sent to the parser using the 'go' command.

       reset A batch can be cleared using the 'reset' command.

       list tables
	      The list tables command will display a list of available tables in this database, similar to the mdb-tables utility on  the  command
	      line.

       describe table <table>
	      Will display the column information for the specified table.

       quit   Will exit the tool.

SQL LANGUAGE

       The  currently  implemented  SQL  subset is quite small, supporting only single table queries, no aggregates, and limited support for WHERE
       clauses. Here is a brief synopsis of the supported language.

       select:
	      SELECT [* | <column list>] FROM <table> WHERE <where clause>

       column list:
	      <column> [, <column list>]

       where clause:
	      <column> <operator> <literal> [AND <where clause>]

       operator:
	      =, =>, =<, <>, like, <, >

       literal:
	      integers, floating point numbers, or string literal in single quotes

NOTES

       When passing a file (-i) or piping output to mdb-sql the final 'go' is optional. This allow constructs like

       echo "Select * from Table1" | mdb-sql mydb.mdb

       to work correctly.

       The -i command can be passed the string 'stdin' to test entering text as if using a pipe.

ENVIRONMENT

       MDB_JET3_CHARSET
	      Defines the charset of the input JET3 (access 97) file. Default is CP1252. See iconv(1).

       MDBICONV
	      Defines the output charset. Default is UTF-8. mdbtools must have been compiled with iconv.

       MDBOPTS
	      semi-column separated list of options:

	      o  use_index

	      o  no_memo

	      o  debug_like

	      o  debug_write

	      o  debug_usage

	      o  debug_ole

	      o  debug_row

	      o  debug_props

	      o  debug_all is a shortcut for all debug_* options

HISTORY

       mdb-sql first appeared in MDB Tools 0.3.

SEE ALSO

       gmdb2(1) mdb-export(1) mdb-hexdump(1) mdb-prop(1) mdb-ver(1) mdb-array(1) mdb-header(1) mdb-parsecsv(1) mdb-schema(1) mdb-tables(1)

AUTHORS

       The mdb-sql utility was written by Brian Bruns.

BUGS

       The supported SQL syntax is a very limited subset and deficient in several ways.

0.7								   13 July 2013 						       MDBTools(1)
All times are GMT -4. The time now is 10:01 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy