Visit The New, Modern Unix Linux Community


pass null value to sql script from korn shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting pass null value to sql script from korn shell script
# 1  
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 from the korn shell script ?
# 2  
Code:
if [ -z "$1" ] 
then
        echo "param 1 is null"
fi

If you want null as in empty strings, just pass empty strings in. if you want null as in database null, pass in NULL without quotes instead.
# 3  
Is this how ? am confused Smilie
Code:
if [ -z "$1" ]     
then
        echo "param 1 is null"
        $1 = NULL   OR (is it   $1 ="")  
fi

eg:
i want to pass something like this after checking for null values
Code:
sqlplus -s /nolog @$sqlfile $1 $2 $3 $4

based on this the sql script shlould check for null .
i am using nvl function in sql to handle this.

please help.

Moderator's Comments:
Mod Comment Video tutorial on how to use code tags in The UNIX and Linux Forums.

Last edited by Franklin52; 11-09-2011 at 03:47 AM.. Reason: Please use code tags, thank you
# 4  
Not knowing how you were using these variables, I couldn't give any more detail. Now that you've shown how I can help you a little better.

Unfortunately I'm not familiar with how sqlplus assigns things on a commandline. It may need some special syntax to input a real database NULL rather than a string containing the letters "NULL". If you can do so, it may be easier to check inside your sql script for zero-length strings.

You can't assign to $1.

What you can do is
Code:
"${1-NULL}"

which will cause it to evaluate to the string NULL whenever $1's blank, and its usual content otherwise.
# 5  
Hi Corona668

Do u mean I can pass the parameters this way ?

Code:
PARAM1 = "${1-NULL}" 
 
sqlplus -s /nolog @$sqlfile $PARAM1


Last edited by megha2525; 11-09-2011 at 10:57 AM..
# 6  
Yes.

Or just do it right there.

Code:
sqlplus -s /nolog @$sqlfile "${1-NULL}"

I'm not sure if this will result in a database NULL or the string "NULL". You might have better luck just leaving it as "$1" and seeing if the resulting "" is interpreted as a database NULL.
# 7  
Code:
sqlplus -s /nolog @$sqlfile "${1-NULL}" $2 $3 $4

I have not passed any value for $1 ... so i know $1 is empty.
I passed in values for $2 $3 $4.
whenever $1 $2 $3 $4 are passed from shell to sqlplus they are referred to as &1 &2 &3 &4 .

now we know that I passed no value for $1 ,
but in sql script what is happening is the first non null value that is from shell i.e, $2 in our case now ... is being treated as &1 .

so its not working Smilie

---------- Post updated at 04:56 PM ---------- Previous update was at 04:38 PM ----------

Do u know how can I display the values passed from korn shell in the sql script?
for eg in the korn shell i am passing a value like this.

Code:
sqlplus -s /nolog @sqlpfile $1
 
 
in the sql script i am doing this.
declare
var1 varchar2(50);
begin
var1='&1';
dbms_output.put_line(var1);
end;

i am getting an error
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
The symbol ":= was inserted before "=" to continue.

not sure whats going on

Last edited by megha2525; 11-09-2011 at 10:59 AM..

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #947
Difficulty: Medium
Malware a portmanteau for malicious hardware.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pass script with parameter in korn shell script

I have written a script which will take input parameter as another script. However, if the script passed as input parameter has parameters then this script doesn't work. I have a script b.ksh which has 1 and 2 as parameters I have a script c.ksh which has 3,4 and 5 as parameters vi a.ksh... (1 Reply)
Discussion started by: Vee
1 Replies

2. Shell Programming and Scripting

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 ... (4 Replies)
Discussion started by: reddy298599
4 Replies

3. 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

4. Shell Programming and Scripting

Korn shell script - SQL statement challenges

Hi scripting experts. I have some coding challenges that I'm hoping you can help me out. I have one file#1 that contains the following sql statement that spans over multiple lines: sql Select /*+ use_has(a,b) */ * from customer a, customer_address b where a.id = b.id... (1 Reply)
Discussion started by: pchang
1 Replies

5. Programming

pass value from Oracle sql to Korn shell

Hi All , I am trying to pass a value from sqlplus to korn shell . There is a table tab1 in Oracle that has a column userdate. I need to pass the userdate to the korn shell . This is what I am doing . VALUE=`sqlplus -silent username/password << END set pagesize 0 feedback off verify off... (14 Replies)
Discussion started by: megha2525
14 Replies

6. 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

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

8. UNIX for Advanced & Expert Users

Accessing PL/SQL OUT variables in Korn Shell Script

Hello All, I was just wondering if there is any direct way to access PL/SQL OUT variables from Korn Shell Script. I could already figure out how to return a single value back from PL/SQL to Shell Script (using bind variable). But, what if we want to return multiple values? One option I... (4 Replies)
Discussion started by: bright_future
4 Replies

9. 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

10. 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

Featured Tech Videos